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

This is by design, here is the explanation of the behavior

Custom TSV Creation: 

• Creating a custom TSV (e.g., n_test) generates entries in prj_blb_slicerequests (Fiscal/Month/Week).
• Unlike standard TSVs, Custom TSV data is not loaded into prj_blb_slices. Instead, it flows from ODF_CA_<object> into a specific DWH table: dwh_<object>_FACTS.
• These custom fact tables contain only the Dimension Key, Period Key, and TSV Data. They do not store auxiliary keys (Investment, Team, Resource, etc.) found in standard period fact tables.

Possible Period Mismatch

• Source View Logic: The source view (DWH_FCT_<object>_V) dynamically calculates periods based on the custom TSV slice requests.
• The Discrepancy: Custom TSV data can exist outside the date ranges of standard OOTB slices (ETC/Actuals).
• Standard Views determine ranges based on static OOTB slice logic.
• Custom Views determine ranges based on custom data input.
  • The Custom Fact table often contains periods that do not exist in the Standard Fact table.

Full JOIN usage

• To ensure no data is lost from either table, a FULL OUTER JOIN is used between the Standard Period Facts and Custom Period Facts.
• When the join pulls a record that exists only in the Custom table (because the date is outside the standard range), it cannot find the auxiliary keys (Investment, Resource, etc.) in the Standard table.
• Since the Custom table does not store these auxiliary keys, the query returns NULL for columns like investment_key or resource_key for those specific periods.

Workaround

• To prevent NULL keys, you must align the data ranges:
• Ensure the time slice coverage matches the other OOTB slices as per best practices
• Populate standard TSV data (e.g., ETC Cost) for the same periods used by the Custom TSV.
• This forces the Standard Fact table to generate records for those dates. The FULL JOIN can then match the periods, allowing the query to pull the correct auxiliary keys from the Standard table.

Alternate Workaround:

• When querying the records, use joins with other tables, i.e. DWH_INV_ASSIGNMENT to retrieve the investment_key information