I need to query the last refreshed date whenever i run the Mart.

[suvbin]

Need to write the query to get the last refreshed timestamp (local time) when ever i run the Datamart.  And along with that i need to get the history of the run , like how many inserts, updates and deletes happend.  

[TimGarrod · Accepted solution]

Hi Sundeep548 - 

There are metadata tables in the dw that ComposeDW writes to that store some metrics to help with your question. 

• TPIL_RUNS - contains history of all ETL runs (DW and DM ETL processes are logged here).
• TLOG_NO_AFFECTED_ROWS - contains the number of inserts / updates for each table in each ETL run.
• TDWM_TABLES - contains metadata for DWH tables.  <Unfortunately this does not include data mart tables currently>

TO view statistics for DW tables you can execute this query  (replace schema names with your DW schema)- 

SELECT N.RUNNO as ETL_RUNNO, ETL.RUN_AT as START_TIME, ETL.STOP_AT as END_TIME , N.*, TABLENAME, UPD_OR_INS, NO_AFFECTED
FROM dbo.TLOG_NO_AFFECTED_ROWS N
JOIN dbo.TDWM_TABLES TBL
on N.ENTITYNR = TBL.ENTITYKEY
AND N.BLOCK = TBL.BLOCKNR
JOIN dbo.TPIL_RUNS ETL
on ETL.RUNNO = N.RUNNO

This will provide DWH statistics. 

For data mart stats - we have a powershell utility that can create sql statements to populate a metadata structure with info from the data mart.  <Note this is not a supported utility from Qlik, nor part of the product and as such comes with no expressed support or warranty from Qlik> . 

Please PM me if you are interested in this utility and I can provide it to you.

Hope the above helps.

[suvbin]

Thank you for the reply. Any table that contains Data Mart name as well in the Table. So that we can know the last refresh date on a particular datamart. 

[TimGarrod]

Shoot me a PM. The script I have can populate a table that you can join with the TPIL_RUNS table to see the name of the etl set / data mart.