Configuring Job Dimension in Projects Analytics for E-Business Suite

The Job dimension is maintained in the Human Resources Analytics module.

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.

Configuring Project Customer in Projects Analytics for E-Business Suite

By default, E-Business Suite only has the 'PRIMARY' relationship code in the PA_PROJECT_CUSTOMERS table. Therefore, the value is included in the ODI filter used in the source extract mapping for the Project dimension to get the customer for a project.

Customers can define an additional value such as 'OVERRIDE CUSTOMER' as the relationship value. In this case, the filter must be edited to include any additional values.

  1. In ODI Designer Navigator, connect to your ODI repository.
  2. Open the folder appropriate to your source system (for example, SDE_ORA_11510_Adaptor for Oracle V11.5.10, or SDE_ORA_R12_Adaptor for Oracle V12).
  3. Expand the SDE_ORA_ProjectDimension folder and open the interface SDE_ORA_Project.W_PROJECT_DS.LKP_PROJ_CUST and click on the Quick-Edit tab.
  4. Expand the Filters tab and edit the expression column for the second filter.
  5. Remove the existing SQL and add the following sample SQL where it is assumed the values are 'PRIMARY' and 'OVERRIDE CUSTOMER'.

    Modify it according to your configuration. If you want it to be independent of any relationships, then just remove the filters on PROJECT_RELATIONSHIP_CODE - UPPER(PA_PROJECT_CUSTOMERS.PROJECT_RELATIONSHIP_CODE (+)) IN ('PRIMARY' . 'OVERRIDE CUSTOMER').

    Note:

    If the lookup returns more than one customer, then apply a MAX function on the id so that it always returns one row.
  6. Review the mapping to ensure it is valid then click OK and save the interface.
  7. Regenerate the scenario by expanding the Packages folder and right-click the scenario to regenerate.

About Configuring Project Classification Dimension in Projects Analytics for E-Business Suite

Every project can be optionally classified into different categories. Within these categories, a project can be further categorized into different classification codes. Depending on how these classification categories are defined in the application, for some categories, a project can be classified with more than one classification code.

The Project Classification Table (W_PROJ_CLASSIFICATION_D) is at the grain of Project, Classification Category and Classification Code. The Project facts do not have an explicit foreign key for joining with Project Classification Dimension; instead the join is on the Project Foreign Key. As specifying a Classification Category is optional for a project, so the logical join in the BI metadata repository (that is, the RPD file) between the Facts and Project Classification Dimension has been set as right outer join to avoid losing records in case the project has not been classified.

Note:

A particular classification code might exist for more than one classification category. Therefore, to avoid double counting, it is important that a classification category is fixed in a report that has classification code as one of the reporting attributes. If a Project belongs to more than one Classification Category under the same Classification, the Project metrics (Cost, Revenue, and so forth) will be double-counted.

About Configuring Project Funding Fact for E-Business Suite

Funding is based on Funding Line, which represents allocations made to a project or task. The line level funding information is held in the Funding Line fact (W_PROJ_ FUNDING_ LINE_F), which is based on PA_PROJECT_FUNDINGS table in the Billing Module of E-Business Suite.

Also, data is extracted from the Summary Funding table (PA_SUMMARY_PROJECT_FUNDINGS) to retrieve additional metrics like Unbaselined Amount, Baselined Amount, Invoiced Amount, Revenue Accrued; which are not available in the Funding line Fact; these would be available in Funding Header Fact (W_PROJ_FUNDING_HDR_F). Before running any ODI etl job, you need to run the following process in E-Business Suite to update this table: PRC: Refresh Project Summary Amounts.

Note:

For E-Business Suite, Funding Currency is the Document Currency for this fact.

Funding Fact Canonical Date GL Date is not populated in the OLTP application. So in Oracle Business Analytics Warehouse, the GL Date for E-Business Suite is based on the Funding Allocation Date, using the GL Calendar of the Project OU. This enables cross-functional analysis on GL Calendar. For example, cross analysis of funding and billing by Fiscal Year is not possible if there is no GL Date in the Funding fact. Customers who do not want to perform analysis based on GL Calendar can instead base it on Enterprise Calendar.

  • Project_Funding_Category: Used for categorizing funding allocation types. Project_Funding_Level: This flat file is used to indicate whether a funding line is for a Task or a Project. It is not used in any default metric definition.

  • The GL date (Funding Allocation Date) is the canonical date for this table and is also used for global exchange rate calculation.