Information on DWH Instantaneous Sync

Products

Clarity PPM SaaS Clarity FedRAMP Clarity PPM On Premise

Issue/Introduction

This article describes the Data Warehouse Instant Sync that keeps the Data Warehouse schema up-to-date with changes in Clarity. The goal of the instant sync is to reduce the need to run the Load Data Warehouse jobs, whether incremental or full load, to minimize the stale aspect of the Data Warehouse data.

Instantaneous Sync Between Clarity and the Data Warehouse will Not Update Fact Tables in the Data Warehouse. SQL Curve Tables and Columns were created as an alternative to Fact Tables and Columns in the Data Warehouse for improved capability and better performance

When Include SQL Curve Data is enabled in Clarity Classic UX _-> System Options under Administration, SQL Curves will also be processed

Resolution

Key Tables to look for troubleshooting

DWH_ODF_SCHEMA_CHANGES - Tracks Schema Changes to Process
DWH_ODF_INST_CHANGES - Tracks Instance Changes to Process
DWH_ODF_INST_CHANGES_STAGING - Instance Changes Currently Being Processed
DWH_ODF_INST_CHANGES_ARCHIVE - Archive of Instance Changes Processed

Instantaneous Sync works by tracking Object instance modification events (insert, update, and delete) and Studio Schema changes (new Objects or Attributes that are Data Warehouse enabled) in 4 Tables with the Status of the Sync

Status	Updated
1 = Ready for sync	Set by Clarity when the record is inserted into the table(s).
2 = Schema or instance data currently being synced	Set by the BG ETL jobs when they are working on the records.
3 = Schema with historical data being synced	Set by the BG ETL jobs when historical data is being copied from Clarity to the Data Warehouse
0 = Sync successful	Set by the BG ETL jobs when it has successfully processed the record.

Additional Information

Common Troubleshooting

DWH Instant Sync is running slow -
- Potential solutions - Check count of data in table DWH_ODF_INST_CHANGES_STAGING and if there are large amount of changes done using any integration/automation this is expected
Is DWH Full and Incremental Load Needed -
- Immediately prior to enabling DWH instant sync it is recommended to run the Full Load to completion
- After this, DWH (Full and Incremental Load) will still be needed and can be scheduled as described in Clarity DWH Sync - Best Practices
I have done a large data load and DWH instant sync is taking longer however my reports are impacted as its not showing data -
- Potential solutions for On Premise Customer only
  - Stop the BG services
  - Uncheck the Enable instantaneous sync from Administration → System Options
  - Start the BG services
  - Run the Full load
I have created a new instance and data is seen in DWH schema but using reporting workspace I am not seeing the data
- Potential solutions - Run the Load DWH rights Jobs in order to populate the rights tables
Load DWH jobs are in waiting state for long
- Potential solutions - Check the Job Monitor page and DWH_ODF_INST_CHANGES_STAGING table counts to see if the DWH Instanteous sync is running or not and co-relate with bg-dwh logs to check for any potential issues.