My Oracle Support Banner

P6 EPPM ETL Exhibits Slow Performance While Running the 'P6_SCD_W_ASSIGNMENT_SPREAD_HF_PHYSICAL' Scenario From the 'For Slowly Changing Dimension Projects' Step (Doc ID 2393961.1)

Last updated on MAY 08, 2018

Applies to:

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

Symptoms

ACTUAL BEHAVIOR
While running an ETL for a P6 source (using ODI), experience a long duration for the "For slowly changing dimension projects" step which executes package RUNNER_SLOWLY_CHANGING_DIM_P6.

The RUNNER_SLOWLY_CHANGING_DIM_P6 package runs the following scenarios:


When reviewing the scenarios within the ODI studio:

The P6_SCD_W_ASSIGNMENT_SPREAD_HF_PHYSICAL" > "Insert flow into I$ table task executes the following SQL:

insert /*+ APPEND */ into ODI_TGT_USER.I$W_A8UIA7CIHESTQ2FL6KM92E97N5
(
  RESOURCE_WID,
ASSIGNMENT_WID,
ROLE_WID,
PROJECT_WID,
ACTIVITY_WID,
WBS_WID,
DAY_WID,
COST_ACCOUNT_WID,
DATASOURCE_ID,
RESOURCE_ASSIGNMENT_OBJECT_ID,
START_DATE,
END_DATE,
RESOURCE_OBJECT_ID,
ROLE_OBJECT_ID,
ACTIVITY_OBJECT_ID,
WBS_OBJECT_ID,
PROJECT_OBJECT_ID,
COST_ACCOUNT_OBJECT_ID,
ACTUAL_REGULAR_UNITS,
ACTUAL_OVERTIME_UNITS,
ACTUAL_UNITS,
AT_COMPLETION_UNITS,
PLANNED_UNITS,
REMAINING_UNITS,
REMAINING_LATE_UNITS,
STAFFED_REMAINING_UNITS,
STAFFED_REMAINING_LATE_UNITS,
UNSTAFFED_REMAINING_UNITS,
UNSTAFFED_REMAINING_LATE_UNITS,
ACTUAL_REGULAR_COST,
ACTUAL_OVERTIME_COST,
ACTUAL_COST,
AT_COMPLETION_COST,
PLANNED_COST,
REMAINING_COST,
REMAINING_LATE_COST,
STAFFED_REMAINING_COST,
STAFFED_REMAINING_LATE_COST,
UNSTAFFED_REMAINING_COST,
UNSTAFFED_REMAINING_LATE_COST,
W_UPDATE_DT,
W_CREATE_DT,
LAST_RUN_PER_DAY_FLAG,
DELETE_FLAG,
  EFFECTIVE_START_DATE,
  IND_UPDATE
)
select *
from (
  select
  D1W_RESOURCE_HD.ROW_WID RESOURCE_WID,
D1W_ASSIGNMENT_HD.ROW_WID ASSIGNMENT_WID,
D1W_ROLE_HD.ROW_WID ROLE_WID,
D1W_PROJECT_HD.ROW_WID PROJECT_WID,
D1W_ACTIVITY_HD.ROW_WID ACTIVITY_WID,
D1W_WBS_HD.ROW_WID WBS_WID,
TRUNC(D1_J0_A.STARTDATE - TO_DATE('01/01/1980','MM/DD/YYYY')) DAY_WID,
D1W_COST_ACCOUNT_HD.ROW_WID COST_ACCOUNT_WID,
#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID DATASOURCE_ID,
D1_J0_A.RESOURCEASSIGNMENTOBJECTID RESOURCE_ASSIGNMENT_OBJECT_ID,
TRUNC(D1_J0_A.STARTDATE) START_DATE,
TRUNC(D1_J0_A.ENDDATE) END_DATE,
D1_J0_A.RESOURCEOBJECTID RESOURCE_OBJECT_ID,
D1_J0_A.ROLEOBJECTID ROLE_OBJECT_ID,
D1_J0_A.ACTIVITYOBJECTID ACTIVITY_OBJECT_ID,
D1_J0_A.WBSOBJECTID WBS_OBJECT_ID,
D1_J0_A.PROJECTOBJECTID PROJECT_OBJECT_ID,
D1_J0_A.COSTACCOUNTOBJECTID COST_ACCOUNT_OBJECT_ID,
D1_J0_A.ACTUALREGULARUNITS ACTUAL_REGULAR_UNITS,
D1_J0_A.ACTUALOVERTIMEUNITS ACTUAL_OVERTIME_UNITS,
D1_J0_A.ACTUALUNITS ACTUAL_UNITS,
D1_J0_A.ATCOMPLETIONUNITS AT_COMPLETION_UNITS,
D1_J0_A.PLANNEDUNITS PLANNED_UNITS,
D1_J0_A.REMAININGUNITS REMAINING_UNITS,
D1_J0_A.REMAININGLATEUNITS REMAINING_LATE_UNITS,
D1_J0_A.STAFFEDREMAININGUNITS STAFFED_REMAINING_UNITS,
D1_J0_A.STAFFEDREMAININGLATEUNITS STAFFED_REMAINING_LATE_UNITS,
D1_J0_A.UNSTAFFEDREMAININGUNITS UNSTAFFED_REMAINING_UNITS,
D1_J0_A.UNSTAFFEDREMAININGLATEUNITS UNSTAFFED_REMAINING_LATE_UNITS,
D1_J0_A.ACTUALREGULARCOST ACTUAL_REGULAR_COST,
D1_J0_A.ACTUALOVERTIMECOST ACTUAL_OVERTIME_COST,
D1_J0_A.ACTUALCOST ACTUAL_COST,
D1_J0_A.ATCOMPLETIONCOST AT_COMPLETION_COST,
D1_J0_A.PLANNEDCOST PLANNED_COST,
D1_J0_A.REMAININGCOST REMAINING_COST,
D1_J0_A.REMAININGLATECOST REMAINING_LATE_COST,
D1_J0_A.STAFFEDREMAININGCOST STAFFED_REMAINING_COST,
D1_J0_A.STAFFEDREMAININGLATECOST STAFFED_REMAINING_LATE_COST,
D1_J0_A.UNSTAFFEDREMAININGCOST UNSTAFFED_REMAINING_COST,
D1_J0_A.UNSTAFFEDREMAININGLATECOST UNSTAFFED_REMAINING_LATE_COST,
D1_J0_A.LASTUPDATEDATE W_UPDATE_DT,
D1_J0_A.LASTUPDATEDATE W_CREATE_DT,
1 LAST_RUN_PER_DAY_FLAG,
D1W_COST_ACCOUNT_HD.DELETE_FLAG DELETE_FLAG,
  TO_DATE('#PRIMAVERA_ANALYTICS_PROJECT.VAR_EFFECTIVE_START_DATE', 'YYYY-MM-DD HH24:MI:SS') EFFECTIVE_START_DATE,
  JRN_FLAG IND_UPDATE
  from (((((((ODI_TGT_USER.C$0DA1HF2J4A9PB81JGA634IB51IRU D1_J0_A INNER JOIN STARUSER.ETL_PROJECTLIST D1ETL_PROJECTLIST
  ON D1_J0_A.PROJECTOBJECTID=D1ETL_PROJECTLIST.OBJECTID AND D1ETL_PROJECTLIST.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID
  ) INNER JOIN STARUSER.W_ASSIGNMENT_HD D1W_ASSIGNMENT_HD
  ON D1_J0_A.RESOURCEASSIGNMENTOBJECTID=D1W_ASSIGNMENT_HD.ASSIGNMENT_OBJECT_ID and D1W_ASSIGNMENT_HD.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID and D1W_ASSIGNMENT_HD.CURRENT_FLAG = 1
  ) INNER JOIN STARUSER.W_ACTIVITY_HD D1W_ACTIVITY_HD
  ON D1_J0_A.ACTIVITYOBJECTID=D1W_ACTIVITY_HD.ACTIVITY_OBJECT_ID and D1W_ACTIVITY_HD.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID and D1W_ACTIVITY_HD.CURRENT_FLAG = 1
  ) INNER JOIN STARUSER.W_PROJECT_HD D1W_PROJECT_HD
  ON D1_J0_A.PROJECTOBJECTID=D1W_PROJECT_HD.PROJECT_OBJECT_ID and D1W_PROJECT_HD.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID and D1W_PROJECT_HD.CURRENT_FLAG = 1
  ) INNER JOIN STARUSER.W_WBS_HD D1W_WBS_HD
  ON D1_J0_A.WBSOBJECTID=D1W_WBS_HD.WBS_OBJECT_ID and D1W_WBS_HD.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID and D1W_WBS_HD.CURRENT_FLAG = 1
  ) LEFT OUTER JOIN STARUSER.W_ROLE_HD D1W_ROLE_HD
  ON D1_J0_A.ROLEOBJECTID=D1W_ROLE_HD.ROLE_OBJECT_ID and D1W_ROLE_HD.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID and D1W_ROLE_HD.CURRENT_FLAG = 1
  ) LEFT OUTER JOIN STARUSER.W_COST_ACCOUNT_HD D1W_COST_ACCOUNT_HD
  ON D1_J0_A.COSTACCOUNTOBJECTID=D1W_COST_ACCOUNT_HD.ACCOUNT_OBJECT_ID and D1W_COST_ACCOUNT_HD.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID and D1W_COST_ACCOUNT_HD.CURRENT_FLAG = 1
  ) LEFT OUTER JOIN STARUSER.W_RESOURCE_HD D1W_RESOURCE_HD
  ON D1_J0_A.RESOURCEOBJECTID=D1W_RESOURCE_HD.RESOURCE_OBJECT_ID and D1W_RESOURCE_HD.DATASOURCE_ID=#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID and D1W_RESOURCE_HD.CURRENT_FLAG = 1
  where (1=1)
) S
where NOT EXISTS (
  select 'x'
  from STARUSER.W_ASSIGNMENT_SPREAD_HF T
  where T.DATASOURCE_ID = S.DATASOURCE_ID
and T.RESOURCE_ASSIGNMENT_OBJECT_ID = S.RESOURCE_ASSIGNMENT_OBJECT_ID
  and ((S.RESOURCE_WID = T.RESOURCE_WID) or (S.RESOURCE_WID IS NULL and T.RESOURCE_WID IS NULL))
and ((S.ASSIGNMENT_WID = T.ASSIGNMENT_WID) or (S.ASSIGNMENT_WID IS NULL and T.ASSIGNMENT_WID IS NULL))
and ((S.ROLE_WID = T.ROLE_WID) or (S.ROLE_WID IS NULL and T.ROLE_WID IS NULL))
and ((S.PROJECT_WID = T.PROJECT_WID) or (S.PROJECT_WID IS NULL and T.PROJECT_WID IS NULL))
and ((S.ACTIVITY_WID = T.ACTIVITY_WID) or (S.ACTIVITY_WID IS NULL and T.ACTIVITY_WID IS NULL))
and ((S.WBS_WID = T.WBS_WID) or (S.WBS_WID IS NULL and T.WBS_WID IS NULL))
and ((S.DAY_WID = T.DAY_WID) or (S.DAY_WID IS NULL and T.DAY_WID IS NULL))
and ((S.COST_ACCOUNT_WID = T.COST_ACCOUNT_WID) or (S.COST_ACCOUNT_WID IS NULL and T.COST_ACCOUNT_WID IS NULL))
and ((S.START_DATE = T.START_DATE) or (S.START_DATE IS NULL and T.START_DATE IS NULL))
and ((S.END_DATE = T.END_DATE) or (S.END_DATE IS NULL and T.END_DATE IS NULL))
and ((S.RESOURCE_OBJECT_ID = T.RESOURCE_OBJECT_ID) or (S.RESOURCE_OBJECT_ID IS NULL and T.RESOURCE_OBJECT_ID IS NULL))
and ((S.ROLE_OBJECT_ID = T.ROLE_OBJECT_ID) or (S.ROLE_OBJECT_ID IS NULL and T.ROLE_OBJECT_ID IS NULL))
and ((S.ACTIVITY_OBJECT_ID = T.ACTIVITY_OBJECT_ID) or (S.ACTIVITY_OBJECT_ID IS NULL and T.ACTIVITY_OBJECT_ID IS NULL))
and ((S.WBS_OBJECT_ID = T.WBS_OBJECT_ID) or (S.WBS_OBJECT_ID IS NULL and T.WBS_OBJECT_ID IS NULL))
and ((S.PROJECT_OBJECT_ID = T.PROJECT_OBJECT_ID) or (S.PROJECT_OBJECT_ID IS NULL and T.PROJECT_OBJECT_ID IS NULL))
and ((S.COST_ACCOUNT_OBJECT_ID = T.COST_ACCOUNT_OBJECT_ID) or (S.COST_ACCOUNT_OBJECT_ID IS NULL and T.COST_ACCOUNT_OBJECT_ID IS NULL))
and ((S.ACTUAL_REGULAR_UNITS = T.ACTUAL_REGULAR_UNITS) or (S.ACTUAL_REGULAR_UNITS IS NULL and T.ACTUAL_REGULAR_UNITS IS NULL))
and ((S.ACTUAL_OVERTIME_UNITS = T.ACTUAL_OVERTIME_UNITS) or (S.ACTUAL_OVERTIME_UNITS IS NULL and T.ACTUAL_OVERTIME_UNITS IS NULL))
and ((S.ACTUAL_UNITS = T.ACTUAL_UNITS) or (S.ACTUAL_UNITS IS NULL and T.ACTUAL_UNITS IS NULL))
and ((S.AT_COMPLETION_UNITS = T.AT_COMPLETION_UNITS) or (S.AT_COMPLETION_UNITS IS NULL and T.AT_COMPLETION_UNITS IS NULL))
and ((S.PLANNED_UNITS = T.PLANNED_UNITS) or (S.PLANNED_UNITS IS NULL and T.PLANNED_UNITS IS NULL))
and ((S.REMAINING_UNITS = T.REMAINING_UNITS) or (S.REMAINING_UNITS IS NULL and T.REMAINING_UNITS IS NULL))
and ((S.REMAINING_LATE_UNITS = T.REMAINING_LATE_UNITS) or (S.REMAINING_LATE_UNITS IS NULL and T.REMAINING_LATE_UNITS IS NULL))
and ((S.STAFFED_REMAINING_UNITS = T.STAFFED_REMAINING_UNITS) or (S.STAFFED_REMAINING_UNITS IS NULL and T.STAFFED_REMAINING_UNITS IS NULL))
and ((S.STAFFED_REMAINING_LATE_UNITS = T.STAFFED_REMAINING_LATE_UNITS) or (S.STAFFED_REMAINING_LATE_UNITS IS NULL and T.STAFFED_REMAINING_LATE_UNITS IS NULL))
and ((S.UNSTAFFED_REMAINING_UNITS = T.UNSTAFFED_REMAINING_UNITS) or (S.UNSTAFFED_REMAINING_UNITS IS NULL and T.UNSTAFFED_REMAINING_UNITS IS NULL))
and ((S.UNSTAFFED_REMAINING_LATE_UNITS = T.UNSTAFFED_REMAINING_LATE_UNITS) or (S.UNSTAFFED_REMAINING_LATE_UNITS IS NULL and T.UNSTAFFED_REMAINING_LATE_UNITS IS NULL))
and ((S.ACTUAL_REGULAR_COST = T.ACTUAL_REGULAR_COST) or (S.ACTUAL_REGULAR_COST IS NULL and T.ACTUAL_REGULAR_COST IS NULL))
and ((S.ACTUAL_OVERTIME_COST = T.ACTUAL_OVERTIME_COST) or (S.ACTUAL_OVERTIME_COST IS NULL and T.ACTUAL_OVERTIME_COST IS NULL))
and ((S.ACTUAL_COST = T.ACTUAL_COST) or (S.ACTUAL_COST IS NULL and T.ACTUAL_COST IS NULL))
and ((S.AT_COMPLETION_COST = T.AT_COMPLETION_COST) or (S.AT_COMPLETION_COST IS NULL and T.AT_COMPLETION_COST IS NULL))
and ((S.PLANNED_COST = T.PLANNED_COST) or (S.PLANNED_COST IS NULL and T.PLANNED_COST IS NULL))
and ((S.REMAINING_COST = T.REMAINING_COST) or (S.REMAINING_COST IS NULL and T.REMAINING_COST IS NULL))
and ((S.REMAINING_LATE_COST = T.REMAINING_LATE_COST) or (S.REMAINING_LATE_COST IS NULL and T.REMAINING_LATE_COST IS NULL))
and ((S.STAFFED_REMAINING_COST = T.STAFFED_REMAINING_COST) or (S.STAFFED_REMAINING_COST IS NULL and T.STAFFED_REMAINING_COST IS NULL))
and ((S.STAFFED_REMAINING_LATE_COST = T.STAFFED_REMAINING_LATE_COST) or (S.STAFFED_REMAINING_LATE_COST IS NULL and T.STAFFED_REMAINING_LATE_COST IS NULL))
and ((S.UNSTAFFED_REMAINING_COST = T.UNSTAFFED_REMAINING_COST) or (S.UNSTAFFED_REMAINING_COST IS NULL and T.UNSTAFFED_REMAINING_COST IS NULL))
and ((S.UNSTAFFED_REMAINING_LATE_COST = T.UNSTAFFED_REMAINING_LATE_COST) or (S.UNSTAFFED_REMAINING_LATE_COST IS NULL and T.UNSTAFFED_REMAINING_LATE_COST IS NULL))
and ((S.W_UPDATE_DT = T.W_UPDATE_DT) or (S.W_UPDATE_DT IS NULL and T.W_UPDATE_DT IS NULL))
and ((S.W_CREATE_DT = T.W_CREATE_DT) or (S.W_CREATE_DT IS NULL and T.W_CREATE_DT IS NULL))
and ((S.LAST_RUN_PER_DAY_FLAG = T.LAST_RUN_PER_DAY_FLAG) or (S.LAST_RUN_PER_DAY_FLAG IS NULL and T.LAST_RUN_PER_DAY_FLAG IS NULL))
and ((S.DELETE_FLAG = T.DELETE_FLAG) or (S.DELETE_FLAG IS NULL and T.DELETE_FLAG IS NULL))
  and CURRENT_FLAG = 1
  and EFFECTIVE_END_DATE >= to_date ('01-01-2400', 'mm-dd-yyyy')
)

EXPECTED BEHAVIOR
For improved execution time of the SQL which runs within the P6_SCD_W_ASSIGNMENT_SPREAD_HF_PHYSICAL > Insert flow into I$ table task.


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

  1. Login to the Analytics Administration application
  2. Execute an ETL for a P6 source
  3. Note the issue which occurs

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.