P6 EPPM Code and UDF Mapping Values Are Not Brought Into Data Warehouse When Running the Scheduled ETL (LOAD_PLAN_DAILY) Using Oracle Data Integrator (ODI)

(Doc ID 2347818.1)

Last updated on JANUARY 25, 2018

Applies to:

Primavera Analytics Cloud Service - Version 17.11.1.0 and later
Primavera Analytics - Version 17.11.1.0 and later
Primavera Data Warehouse - Version 17.11.1.0 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
When running the LOAD_PLAN_DAILY ODI ETL for a P6 source:

Issue #1:
The w_activitycodeassignment_ds staging table does not get populated causing the code values to not be populated within the w_codes_activity_d / w_codes_project_d / w_codes_resource_d (and associated history dimension tables).

Issue #2:
The w_udfvalue_ds staging table does not get populated causing the associated P6 UDF dimension and fact tables to not get populated with the UDF value.

EXPECTED BEHAVIOR
For the code and UDF values (for mapped codes and udfs) to be populated in the Data Warehouse to be populated in the associated staging tables for loading into associated dimension and fact tables.

STEPS
The issue can be reproduced at will with the following steps:

  1. Login to P6 Professional
  2. Assign an activity code value to an activity
  3. Add a Activity UDF column to the activity view (text UDf for example) and enter text for the UDF
  4. Commit changes
  5. Publish the project within P6
  6. Confirm the data is present in PX tables with a proper update_date (see SQL in additional information below utilized for confirmation)
  7. Map the associated codes/udfs for P6 from the Analytics Administration Application
  8. Execute a scheduled ETL (LOAD_PLAN_DAILY)
  9. Note that the data does not get populated in the associated staging tables preventing the source dimension/fact tables from being populated with code/udf values (see SQL in additional information below utilized for confirmation)

BUSINESS IMPACT
This prevents the Analytics subject areas for P6 to not display values for mapped codes or udfs when running a scheduled ETL for P6.


ADDITIONAL INFORMATION
The following direct SQL can be utilized to confirm the noted issues above:

Reference:


--px schema
--activity code assignment detail
--this will include the code assignment "lastupdatedate". If the date is less than the last execution of the daily ETL, then the data will not bebrought by the load_plan_daily execution

select p.id, p.name, ac.activityid, ac.activityname, ac.activitycodetypescope, ac.activitycodetypename, ac.activitycodevalue, ac.activitycodedescription, ac.lastupdatedate
from activitycodeassignment@dslink01 ac, project@dslink01 p
where ac.projectobjectid=p.objectid
order by p.id, ac.activityid;


--px schema
--this will show a count for the activity code assignment lastupdatedate values per project
--this is a high level perspective to gauge if any counts could be older than the last load_plan_daily execution

select p.id, p.name, to_char(ac.lastupdatedate, 'DD-MON-YYYY') "actvcodeasnment_updatedate", count(1)
from activitycodeassignment@dslink01 ac, project@dslink01 p
where ac.projectobjectid=p.objectid
group by p.id, p.name, to_char(ac.lastupdatedate, 'DD-MON-YYYY')
order by p.id, to_char(ac.lastupdatedate, 'DD-MON-YYYY');

--star schema
--will list out the activity code mappings from w_xlat_s (which are actually mapped)
--The translation name ties to the activity code type

select display_name, translation_name
from w_xlat_s
where lang_id='en' and display_name like '¬tivity_Codes%(Activity)%'
and display_name in (select unmapped_col from w_obi_unmapped_cols_s where hidden=0);

--star schema
--will list out the activity UDF mappings from w_xlat_s (which are actually mapped)
--The translation name ties to the activity UDF name

select display_name, translation_name
from w_xlat_s
where lang_id='en' and display_name like '¬tivity_UDF%' and display_name in (select unmapped_col from w_obi_unmapped_cols_s where hidden=0);

--star schema
--this will show what is being brought into the activity code assignment dimension staging table
--Note, this will include projects which exist in w_project_d but do not have corresponding data in w_activitycodeassignment_ds

select p.project_id, p.project_name, ac.activityid, ac.activityname, ac.activitycodetypescope, ac.activitycodetypename, ac.activitycodevalue, ac.activitycodedescription
from w_activitycodeassignment_ds ac, w_project_d p
where ac.projectobjectid(+)=p.project_object_id
order by p.project_id, ac.activityid;

--star schema
--this shows what is in the actual code assignment dimension table based on code mappings
--will need to identify which code value/description column to search based on the code type and w_xlat_s mapping
--This will include records which do not exist in the w_activitycodeassignment_ds but have a record in w_project_d and w_codes_activity_d (but no value in w_codes_activity_d)

select p.project_id, p.project_name, ac.activityid, ac.activityname, ac_d.*
from w_codes_activity_d ac_d, w_activitycodeassignment_ds ac, w_project_d p
where ac_d.activity_object_id=ac.activityobjectid(+) and
ac_d.project_object_id=p.project_object_id
order by p.project_id, ac_d.activity_object_id;


--start schema
--this displays data validitity in teh udfvalue staging table for a specific project

select * from w_udfvalue_ds where projectobjectid = (select project_object_id
from w_project_d where project_id = '&Replace_With_Project_ID');

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms