Extending the Project Task Hierarchy Dimension for E-Business Suite
By default, Oracle BI Applications support 20 levels in the flattened hierarchy. The levels are Base, 1, 2, and so forth up to 18, and Top. The base level represents the hierarchy record, and Top level is the Top hierarchy under the Project. If your financial structure contains more than 20 levels, then you can extend the number of levels in the schema and ETL to support all levels.
Task dimension data is sourced from the task table (PA_TASKS) in E-Business Suite, as well as from other task-related OLTP tables such as:
-
PA_PROJ_ELEMENTS
-
PA_PROJ_ELEMENT_VERSIONS
-
PA_PROJ_ELEM_VER_STRUCTURE
-
PA_PROJ_ELEM_VER_SCHEDULE
Attributes such as WBS_NUMBER, PRIORITY_CODE, SCHEDULE_START_DATE, and SCHEDULE_END_DATE are sourced from these tables. Oracle BI Applications support only the latest version of the Financial Structure by using the following filter conditions:
-
PA_PROJ_ELEM_VER_STRUCTURE.STATUS_CODE = 'STRUCTURE_PUBLISHED'
-
AND PA_PROJ_ELEM_VER_STRUCTURE.LATEST_EFF_PUBLISHED_FLAG = 'Y'
The W_TASK_DH hierarchy table stores the flattened hierarchy for every task in W_TASK_D. It is at the same grain as W_TASK_D and is modeled as a Type I dimension. All tasks in the hierarchy support these columns:
-
TASK_NAME
-
TASK_NUMBER
-
WBS_LEVEL
-
WBS_NUMBER
Because both tables, W_TASK_D and W_TASK_DH, are at the same grain, fact tables do not have a separate foreign key to join with this table; instead, the join is on the Task Foreign Key.
To Extend the Project Task Hierarchy Dimension for E-Business Suite: