DWH_SYNC of CRV_FACTS tables pulls unfiltered curve data on every run instead of only changed rows (Oracle Only)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

On Oracle, incremental DWH_SYNC of CRV_FACTS tables fetches all curves of the source view across the DB link, instead of only rows with pending changes

STEPS TO REPRODUCE: 

1. Enable Include SQL Curve Data and DWH_SYNC on an Oracle instance
2. Run Data Warehouse Full - Load once so DWH_INV_INVESTMENT_CRV_FACTS exists and DWH_TMP_TABLE_MAP holds a row for it
3. Check how many rows are actually pending for that table right now: SELECT COUNT() FROM dwh_odf_inst_changes WHERE status IN (1,2) AND LOWER(dwh_table_name)='dwh_inv_investment_crv_facts';
4. Make an update to investment curves by updating allocations in Bulk in Project - Staff
5. Once this triggers DWH_SYNC run, capture the INSERT statement Oracle sends for the table from v$sql: SELECT sql_id, sql_text FROM v$sql WHERE UPPER(sql_text) LIKE 'INSERT INTO DWH_INV_INVESTMENT_CRV_FACTS%';
6. Inspect the captured statement's source query. It has no join to dwh_odf_inst_changes_staging, only WHERE 1=1 AND "language_code"=:1, so it scans and pulls every row of dwh_dw_investment_v across the link regardless of the count from step 3.

Expected Results: The %CRV_FACTS INSERT should pull only rows with a pending change

Actual Results: The %CRV_FACTS INSERT is pulling all curve columns of the source view across the DB link on every sync run

Environment

Clarity 16.3.3, 16.4.0, 16.4.1, 16.4.2, 16.4.3

Cause

DE210474

Resolution

Fixed in:

• Clarity 16.5.0
• Clarity 16.4.3 Patch 1 (16.4.3.1)