Custom TSV on Assignment causes Null investment records in DWH_INV_ASSIGN_PER_FACTS_W_V

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Issue with DWH schema View (DWH_INV_ASSIGN_PER_FACTS_W_V) with TSV attribute added to the Assignment object. The view contains null values for columns joined from the DWH_INV_ASSIGN_PERIOD_FACTS table due to the fact it adds a FULL OUTER JOIN

 

STEPS TO REPRODUCE:

1. Make sure there are no TSV custom attributes on the Assignment object
2. Run the Load Data Warehouse job – Full Load
3. Run the following query:
   •  select * from DWH_INV_ASSIGN_PER_FACTS_W_V where investment_key is null
4. Check the DDL for the view DWH_INV_ASSIGN_PER_FACTS_W_V:
   •  select DWH_INV_ASSIGN_PERIOD_FACTS.ACTUAL_HOURS,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.TEAM_KEY,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.ASSIGNMENT_KEY,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.BASE_COST,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.BASE_HOURS,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.DW_UPDATED_DATE,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.EAC_COST,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.TASK_KEY,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.RESOURCE_KEY,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.PERIOD_KEY,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.INVESTMENT_KEY,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.ETC_HOURS,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.ETC_COST,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.EAC_HOURS,
   •  DWH_INV_ASSIGN_PERIOD_FACTS.ACWP_COST
   •  from DWH_INV_ASSIGN_PERIOD_FACTS
   •  inner join DWH_CMN_PERIOD TP on (DWH_INV_ASSIGN_PERIOD_FACTS.PERIOD_KEY = TP.PERIOD_KEY AND TP.PERIOD_TYPE_KEY = 'WEEKLY')
   •  Note there is an inner join here
5. Add a new TSV attribute N_TEST to the Assignment object.
6. Make sure the attribute is DWH enabled.
7. Add the attribute to the Assignment View in Studio
8. Open an Assignment and add a value on that TSV
9. Run Time Slicing job
10. Run Load Data Warehouse job – Full Load
11. Check the DDL for the view DWH_INV_ASSIGN_PER_FACTS_W_V  
    •  select DWH_ASSIGNMENT_FACTS.N_TEST,
    •  coalesce(DWH_ASSIGNMENT_FACTS.PERIOD_KEY, DWH_INV_ASSIGN_PERIOD_FACTS.PERIOD_KEY) as PERIOD_KEY,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.ACWP_COST,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.BASE_COST,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.BASE_HOURS,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.EAC_COST,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.EAC_HOURS,
    •  coalesce(DWH_ASSIGNMENT_FACTS.DW_UPDATED_DATE, DWH_INV_ASSIGN_PERIOD_FACTS.DW_UPDATED_DATE) as DW_UPDATED_DATE,
    •  coalesce(DWH_ASSIGNMENT_FACTS.ASSIGNMENT_KEY, DWH_INV_ASSIGN_PERIOD_FACTS.ASSIGNMENT_KEY) as ASSIGNMENT_KEY,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.TEAM_KEY,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.TASK_KEY,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.RESOURCE_KEY,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.INVESTMENT_KEY,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.ETC_HOURS,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.ETC_COST,
    •  DWH_INV_ASSIGN_PERIOD_FACTS.ACTUAL_HOURS
    •  from DWH_INV_ASSIGN_PERIOD_FACTS  
    •  full outer join DWH_ASSIGNMENT_FACTS on ( DWH_INV_ASSIGN_PERIOD_FACTS.ASSIGNMENT_KEY = DWH_ASSIGNMENT_FACTS.ASSIGNMENT_KEY and DWH_INV_ASSIGN_PERIOD_FACTS.PERIOD_KEY = DWH_ASSIGNMENT_FACTS.PERIOD_KEY )
    •  where coalesce(DWH_ASSIGNMENT_FACTS.PERIOD_KEY, DWH_INV_ASSIGN_PERIOD_FACTS.PERIOD_KEY) in (SELECT PERIOD_KEY FROM DWH_CMN_PERIOD TP WHERE TP.PERIOD_TYPE_KEY = 'WEEKLY')
    •    Note the join is a FULL OUTER JOIN
12. Run the following query to see if any null values exist from the DWH_INV_ASSIGN_PERIOD_FACTS columns joined in the "full outer join".
    •  select * from DWH_INV_ASSIGN_PER_FACTS_W_V where investment_key is null

Expected Results: No null investment_key entries as each assignment is expected to have a task and investment ID

Actual Results:  Null values exist in DWH_INV_ASSIGN_PER_FACTS_W_V

Environment

Clarity 16.3.3, 16.4.0

Cause

DE181868

Resolution

In Review by Engineering

Workaround:

1. Update the view from using "Full Outer Join" to "Left Outer Join" and recompile
2. Query the records and no null records will display