Hello Lucas,
As you are purging Sa_table regularly. So, We can use Sa table loadid to find the respective Batch id in Md table via looking in MTA_INTEG_LOAD and MTA_INTEG_BATCH table.
Here is my query by which we are able to find the respective data in MD_table.
select * from semarchy_emp_tutorial4.md_person md where md.b_batchid in (select mib.batchid from semarchy_repository9.mta_integ_batch mib where mib.r_integload in (select mil.uuid from semarchy_repository9.mta_integ_load mil join semarchy_emp_tutorial4.sa_person sa on mil.loadid =sa.b_loadid))
Create query in Dashboard and Enable drill down feature while creating query in dashboard. It will help you to see detailed view of data.
Here we create a chart for the above query. Here we show total records created with respective of batch id (You can choose data to display according to your choice ).
Here is the view of chart data after drill down in dashboard.
Hello,
This solution will not work for us as our retention policy is very short on the SA tables.
We were looking for something like this :
select md.* from WF_WORKFLOW_INSTANCE wfi
LEFT join semarchy_repository.dbo.MTA_INTEG_LOAD il ON wfi.ID = il.WORKFLOW_INSTANCE_ID
LEFT JOIN semarchy_repository.dbo.MTA_INTEG_BATCH ib on ib.R_INTEGLOAD = il.UUID
LEFT JOIN md_site md ON md.b_batchid = ib.BATCHID
could you confirm it's is a viable option to retrieve the data and if we should change something in our WHERE clause ?
Best regards
Lucas
Hello,
We cannot proceed that way as we will not keep any data in the SA tables.
we were thinking about this solution. can you confirm it's the good way to proceed ?
select md.* from WF_WORKFLOW_INSTANCE wfi
INNER join semarchy_repository.dbo.MTA_INTEG_LOAD il ON wfi.ID = il.WORKFLOW_INSTANCE_ID
INNER JOIN semarchy_repository.dbo.MTA_INTEG_BATCH ib on ib.R_INTEGLOAD = il.UUID
INNER JOIN md_site md ON md.b_batchid = ib.BATCHID
You can try this query .
Lucas.spina
Hi,
We need to setup a dashboard tracking the workflow activity.
As our SA tables are purged regularly, we would like to retrieve the MD records created during the workflow.
we are thinking about using the MTA_INTEG_LOAD_ID and MTA_INTEG_BATCH_ID tables but for now we were not able to retrieve our data.
How should we proceed ?
Thanks & regards,
Lucas