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:

  1. In ODI Designer Navigator, display the Models tab, and add the change capture columns (TASK_NUMBER, WBS_LEVEL and WBS_NUMBER) for every new level that you want in the W_TASK_DHS and W_TASK_DH tables.
  2. Extend the interfaces in the SDE and SILOs folder.
    1. Depending on the source navigate to the correct SDE folder for EBS or PSFT.
    2. Edit and update the correct main interface for example, SDE_ORA_TaskDimensionHierarchy.W_TASK_DHS or SDE_PSFT_TaskDimensionHierarchy.W_TASK_DHS by providing the correct mappings for the new columns.
    3. Open the SILOS folder and edit and update the ODI interface SIL_Project_TaskDimensionHierarchy.
  3. Regenerate the SDE/SILOS scenarios by expanding the Packages folder and right click the scenario to regenerate.
    This screenshot is described in surrounding text.
    Description of the illustration GUID-B1726169-3128-4C08-871F-A53F2EEE38E3-default.gif

    You must also use Oracle BI EE Administration Tool to update the following objects in the BI metadata repository (that is, the RPD file):

    • W_TASK_DH table in the physical layer.

    • Dim - Task Hierarchy Logical Table and Task Hierarchy Dimension in the logical layer.

    • All the Task Hierarchy Presentation tables in the Presentation Area.