Qlik Community

Qlik Compose for Data Warehouses

Discussion board for collaboration on Qlik Compose for Data Warehouses.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
suvbin
Contributor II
Contributor II

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

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.  

Labels (1)
  • Other

1 Solution

Accepted Solutions
TimGarrod
Employee
Employee

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.

View solution in original post

3 Replies
TimGarrod
Employee
Employee

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.

View solution in original post

suvbin
Contributor II
Contributor II
Author

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
Employee
Employee

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.