Project Cost Variance % /Schedule Variance % Not Matching DWH_INV_SUMMARY_FACTS

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Comparing the values in Clarity versus the values in the Data Warehouse (DWH) are not matching for some Project fields such as Schedule Variance % or Cost Variance %. 

Project Cost Variance % /Schedule Variance % not matching the DWH table DWH_INV_SUMMARY_FACTS columns
DWH_INV_TASK_RU_SUM_FACTS_EV_COST_VAR_PCT
DWH_INV_TASK_RU_SUM_FACTS_EV_COST_VAR
DWH_INV_TASK_RU_SUM_FACTS_EV_SCHEDULE_VAR
DWH_INV_TASK_RU_SUM_FACTS_EV_SCHEDULE_PCT

Steps to Reproduce: 

1. Configure the following fields to the Project List

Cost Variance %
Schedule Variance %

2. Review a Project that has updated cost totals and a Baseline with values in the fields 

3. Run the Load DWH job 

4. Compare the the example fields to the fields in the DWH table with query produced 

Expected Results: The values in DWH should match the values in Clarity. 

Actual Results: The values are not matching. 

Environment

Release : 15.9.3, 16.0.0, 16.0.1, 16.0.2 

 

Cause

DE64835 

Workaround: Use adhoc view/report using the Project Management domain.

  

Resolution

Targeted Fix in Release 16.0.3 

Additional Information

Use query produced:

select * from ( select "DWH_INV_INVESTMENT"."INVESTMENT_ID" as "DWH_INV_INVESTMENT_INVESTMENT_ID", 
"DWH_INV_INVESTMENT"."INVESTMENT_NAME" as "DWH_INV_INVESTMENT_INVESTMENT_NAME", 
"DWH_INV_TASK_RU_SUM_FACTS_V"."EV_COST_VAR" as "DWH_INV_TASK_RU_SUM_FACTS_V_EV_COST_VAR", 
"DWH_INV_TASK_RU_SUM_FACTS_V"."EV_COST_VAR_PCT" as "DWH_INV_TASK_RU_SUM_FACTS_V_EV_COST_VAR_PCT", 
"DWH_INV_TASK_RU_SUM_FACTS_V"."EV_SCHEDULE_VAR" as "DWH_INV_TASK_RU_SUM_FACTS_V_EV_SCHEDULE_VAR", 
"DWH_INV_TASK"."TASK_ID" as "DWH_INV_TASK_TASK_ID", 
"DWH_INV_TASK"."TASK_NAME" as "DWH_INV_TASK_TASK_NAME"
from "PPM_DWH"."DWH_INV_INVESTMENT" "DWH_INV_INVESTMENT"
inner join "PPM_DWH"."DWH_INV_TASK" "DWH_INV_TASK" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_INV_TASK"."INVESTMENT_KEY")
inner join "PPM_DWH"."DWH_INV_TASK_RU_SUM_FACTS_V" "DWH_INV_TASK_RU_SUM_FACTS_V" on ("DWH_INV_TASK"."TASK_KEY" = "DWH_INV_TASK_RU_SUM_FACTS_V"."TASK_KEY")
inner join "PPM_DWH"."DWH_INV_SECURITY_V" "DWH_INV_SECURITY_V" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_INV_SECURITY_V"."INVESTMENT_KEY")
where ((("DWH_INV_SECURITY_V"."USER_UID" = 'admin') and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project') and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project'
--and "DWH_INV_TASK_RU_SUM_FACTS_V"."EV_COST_VAR" < 0
and "DWH_INV_INVESTMENT"."INVESTMENT_KEY" = 'PR0021123'
)
order by DWH_INV_INVESTMENT.INVESTMENT_ID
 ) where ROWNUM <= 1000