The Job dimension is maintained in the Human Resources Analytics module.
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:
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.
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.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.