DWH Sync is slow with little record change with Calculated Attributes (Oracle)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Data Warehouse Instantaneous Sync reads the whole object from the source on every cycle instead of only the changed records, so a sync of a 15K project dataset with one changed project may take over two hours.

STEPS TO REPRODUCE:

1. Connect to Clarity with over 15K projects
2. Go to Administration - Studio - Objects - Project - Attributes and set Include in the Data Warehouse on a set of lookup caption attributes and on aggregate calculated attributes, for example AGG_Sum(cop_prj_statusrpt.cop_overall_status), AGG_Sum(team.prallocsum) and AGG_Sum(team.practsum).
3. Run Load Data Warehouse - Full Load to load all the data
4. Enable DWH Sync in System Options
5. Open one project in Project - Staff, change the Allocation on any team member and Save, so exactly one project is queued for sync.
6. Monitor the DWH Sync job for runtime

Expected Results: With one changed project the sync completes in seconds.
Actual Results: The sync takes over two hours, about 8187 seconds of database time, and reads all 15K+ projects from the source rather than the one that changed.

Environment

Clarity 16.3.3, 16.4.x

Cause

DE210855

Resolution

In review by Engineering

Workaround: Uncheck the calculated attributes from the Data Warehouse as mitigation