DWH_INV_SUMMARY_FACTS Shows 0 for Actual Cost After Running an Incremental Load DWH Job

Products

• Clarity PPM SaaS
• Clarity FedRAMP
• Clarity PPM On Premise

Issue/Introduction

Following the execution of an incremental Load Data Warehouse (DWH) job, the actual_total_cost value in the DWH_INV_SUMMARY_FACTS table incorrectly resets to 0 for certain investments.

Prerequisites

• A Full Load Data Warehouse job has previously completed successfully.
• An active Project is flagged as Include in Data Warehouse.
• The Project has posted actuals (actual_total_cost > 0).
• The Project contains at least one Task with an associated To-Do item.

Steps To Reproduce:

1. Verify Baseline Data: Execute the following SQL query to identify an investment meeting the prerequisites:

   SELECT 
       i.investment_name, 
       f.investment_key, 
       f.actual_total_cost, 
       f.actual_labor_cost, 
       f.actual_total_hours, 
       f.acwp_total_cost
   FROM dwh_inv_summary_facts f
   JOIN dwh_inv_investment i ON f.investment_key = i.investment_key
   WHERE f.actual_total_cost > 0
     AND EXISTS (
         SELECT 1 
         FROM dwh_cmn_todo t 
         WHERE t.assoc_parent_key = f.investment_key 
           AND t.assoc_parent_type = 'projects'
     );

2. Delete the To-Do:
   • Navigate to Projects > select the target Project > open the Tasks module.
   • Locate and delete the To-Do item associated with the task/project.
3. Run Incremental DWH Load:
   • Go to Home > Reports and Jobs > Jobs.
   • Run the Load Data Warehouse job with the Incremental Load option.
4. Verify Data Post-Execution:
   • Once the job completes, re-run the query from Step 1 to review the actual_total_cost column in DWH_INV_SUMMARY_FACTS.
     
     

Expected Result: Deleting a To-Do item should not affect financial metrics. The actual_total_cost value in DWH_INV_SUMMARY_FACTS should remain unchanged.

Actual Result: For the investment where the To-Do was deleted, DWH_INV_SUMMARY_FACTS.actual_total_cost incorrectly resets to 0.

Environment

Clarity 16.4.2

Cause

DE209517

 

Resolution

Fixed in 16.5.0.

Workaround:

• Run full Load DWH job.
• Configure Instanteous sync and pause the incremental job runs if Facts are not used