Activity Level History Or Slowly Changing Dimension Data Not Moving To Data Warehouse Through The Scheduled ODI ETL (LOAD_PLAN_DAILY)

Last updated on MAY 01, 2018

Applies to:

Symptoms

ACTUAL BEHAVIOR
When turning on Activity Level history - Activity Daily, Activity Weekly or Activity Monthly - for a P6 EPPM project, the ODI scheduled ETL (LOAD_PLAN_DAILY) for a P6 EPPM source is not writing data (inserts or updates) for both Slowly Changing Dimension (SCD) history tables or the underlining activity fact history table (example, W_ACTIVITY_HISTORY_F).

Additionally, if data for a project is present in the history tables, changes to data are not reflected in the history tables executing the scheduled ETL (LOAD_PLAN_DAILY) when using activity level history.

EXPECTED BEHAVIOR
When turning on Activity level history for a P6 EPPM project, data should be written to the underlining SCD (*_HD) tables as well as the underlining activity fact history table (W_ACTIVITY_HISTORY_F).

STEPS TO REPRODUCE
The following steps can be utilized to duplicate this issue:

1. Login to P6 and create the following project data:
   
   1. Create a project **Do not yet enable history for the project
   2. Create a single WBS record
   3. Create 4 activities underneath the WBS
   4. Link all activities with start to finish relationships
   5. Schedule Project. 
2. Publish the Project
3. Execute 'Publish Security'  **This will move security records into W_PROJECT_SECURITY_S
4. Execute LOAD_PLAN_DAILY for P6  **This will move the new project into DW
5. Set the project within P6 to Activity level history:
   1. On Project > EPS page, right-click project
   2. Click 'Set Project Preferences'
   3. Click Anaalytics & Services page
   4. Select one of following from History Level dropdown:
      • Activity (Monthly)
      • Activity (Weekly)
      • Activity (Daily)
   5. Select OK
6. Publish the project
7. Execute LOAD_PLAN_DAILY for P6
   • Results:
     • w_project_d properly displays history_level="HL_TASK_MN"
     • w_project_history_f properly displays the project record
     • all history dimension (*_HD) tables do not display results. Example: w_project_hd, w_activity_hd
     • w_activity_history_f does not display the activity records.  This is because the merge_history package bases is insert from records existing in the _HD tables (which do not have rows)
8. Execute LOAD_PLAN_INITIAL_LOAD
   • Results:
     • All SCD and activity level history tables now display results for the projects configured to use activity level history.
9. Adjust the activity name within P6 for one of the activities which now have data present in the w_activity_hd table.
10. Publish the project
11. Execute LOAD_PLAN_DAILY for P6
    1. Results:
       • The w_activity_hd table does not reflect the activity name change.

Changes

Cause