Clarity DWH Sync - Best Practices

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

What are some best practices and guidelines around using the DWH Sync ( Instantaneous Sync) option. How should the DWH jobs be scheduled and what is getting updated? 

Environment

• Beta in Clarity 16.3.1, 16.3.2
• Clarity 16.3.3+ 

Resolution

Recommendations for Load Data Warehouse Job Scheduling with Instantaneous Sync Enabled

 

Load Data Warehouse job - Full Load

• We recommend it is scheduled the same as before prior to when Instantaneous Sync was enabled
• Instantaneous Sync is mutually incompatible with the Load Data Warehouse job, we handle this internally so it is not listed as an Incompatible Job
• Most Customers do not run this on a regular basis

Load Data Warehouse job - Incremental Load

• We recommend it is scheduled the same as before prior to when Instantaneous Sync was enabled
• Instantaneous Sync is mutually incompatible with the Load Data Warehouse job, we handle this internally so it is not listed as an Incompatible Job
• Most customers run this regularly

Load Data Warehouse Access Rights job

• We recommend it is scheduled the same as before prior to when Instantaneous Sync was enabled. Instantaneous Sync does not sync security so it is important to keep running this job.
• Most customers run this regularly

When ‘Include SQL Curve Data’ is Enabled or not Enabled

• Available beginning in 16.3.0
• Enabling ‘Include SQL Curve Data’ is independent of Instantaneous Sync. SQL Curve data is required for per-period metric data in our new Reporting solution.
  • SQL Curve data is populated by both the Load Data Warehouse job (Full and Incremental) and during Instantaneous Sync so Instantaneous Sync is not required
  • After enabled or re-enabled, a Full Load Data Warehouse job must be run
• We recommend the Load Data Warehouse job (Full or Incremental) is scheduled the same as before prior to when ‘Include SQL Curve Data’ was enabled or disabled

 

Instantaneous Sync Processing 

• Available beginning in 16.3.1 for PostgreSQL and Oracle environments and in 16.3.2 for MSSQL environments. Leverages BG Service.
• When enabling Instantaneous Sync the first time, we recommend the Load Data Warehouse job - Full Load be run to completion, this to be done immediately prior to enabling the toggle for DWH Sync
• Instantaneous Sync is mutually incompatible with the Load Data Warehouse job, we handle this internally so it is not listed as an Incompatible Job
• Instantaneous Sync is not required for ‘Include SQL Curve Data’ to be enabled
• What DWH Sync Updates:
  • Captures data for Objects and Attributes enabled for the Data Warehouse
    • Relevant Clarity Schema changes
      • Both OOTB and Custom Objects and Attributes
      • Adding, Modifying, Deactivating, or Deleting Objects and Attributes in Classic Studio
      • Including or Excluding Objects and Attributes in the Data Warehouse in Classic Studio
      • Historical Data is Synced for existing Objects/Attributes newly Included in the Data Warehouse
      • Changes made in ODF
      • Some XOG Modifications (See Gaps below)
    • Clarity Object/Attribute Instance data that is Inserted, Updated, or Deleted
      • Both OOTB and Custom Objects and Attributes
      • Changes in Modern and Classic
      • Some XOG Modifications (See Gaps below)
      • Limited Updates via Job or Process (See Gaps below)
• What is NOT updated by DWH Sync - Reasons why Load DWH Jobs should still be ran
  • • Instantaneous Sync does not process or update the following in the Data Warehouse:
      • All Fact tables in the Data Warehouse
        • Example: DWH_INV_SUMMARY_FACTS
      • Security
      • Fiscal Time Periods
      • Time Reporting Periods
      • On the Status Report Object, Latest Attribute (Boolean that designates a Status Report as the Latest)
      • Calendar Periods

Example : After enabling DWH Sync, a customer is scheduling a Full Load of DWH once weekly after hours, and also whenever there is changes that require the job to be run that cannot wait for the Full Load to run.

Additional Information

For details on how to enable DWH Instantaneous Sync, and other information, check our Broadcom Documentation