DWH Sync (Instantaneous Sync) Progress

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

How to check if DWH Sync is actually progressing? 

Environment

All Supported Clarity Releases

Resolution

Run the below SQL query on the DWH database:

select 'DWH_ODF_SCHEMA_CHANGES' as Table_name, count(*) as record_count from DWH_ODF_SCHEMA_CHANGES 
union all
select 'DWH_ODF_INST_CHANGES' as Table_name, count(*) as record_count from DWH_ODF_INST_CHANGES 
union all
select 'DWH_ODF_INST_CHANGES_STAGING' as Table_name, count(*) as record_count from DWH_ODF_INST_CHANGES_STAGING 
union all
select 'DWH_ODF_INST_CHANGES_ARCHIVE' as Table_name, count(*) as record_count from DWH_ODF_INST_CHANGES_ARCHIVE 

The records should be decreasing and moving to DWH_ODF_INST_CHANGES_ARCHIVE table.

Additional Information

You can also check DWH Sync Status in the job tables by using the below query and adjusting it:

select *  from cmn_sch_job_runs jr, cmn_sch_jobs j, cmn_sch_job_definitions jd
                       where jr.job_id = j.id
                       and j.job_definition_id = jd.id
                       and jd.job_code ='DWH_SYNC'