Skip Headers
Oracle® Business Intelligence Applications New Features Guide

Part Number E16811-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

6 New Features and Updates for Oracle Project Analytics

This chapter contains the following topics:

6.1 New Project Commitments Subject Area for Oracle EBS

This section contains the following topics:

6.1.1 Overview

The following new features have been added to Oracle Project Analytics for Oracle EBS:

  • Project Commitments subject area

  • Commitments metrics to the Project Performance subject area

The Project Commitments subject area provides the ability to report on project commitments, which includes total raw and burdened amounts for requisitions, purchase orders, and supplier invoices for organizations, projects, tasks, resources, suppliers, and associated hierarchies. It provides the ability to track commitments at the commitment document level.

To support the new subject area, there is a new star schema in the Oracle Business Analytics Warehouse. This star contains metrics to report on total commitments and its components, which includes quantity and amount (raw and burdened) for requisitions, purchase orders, and supplier invoices.

The W_PROJ_COMMITMENT_F fact table at the center of the star schema stores the latest commitment data, sourced from the transactional source PA_COMMITMENT_TXNS.

Existing commitment reports have been redesigned to use the new metrics and dimensions.

6.1.2 Before You Begin

Before you follow the configuration steps in this chapter, perform the source-independent configuration steps in the following chapters of the Oracle Business Intelligence Analytics Configuration Guide for Informatica PowerCenter Users:

  • "Common Areas and Dimensions"

  • "Configuring Oracle Project Analytics"

6.1.3 Configuration Steps for the Project Commitments Subject Area

To use the new project commitments subject area, you need to configure the domainValues_Project_CommitmentType_ora<ver>.csv file with the commitment types that your organization uses in the Oracle EBS implementation.

Table 6-1 describes the CSV worksheet file and domain values for the Project Commitments subject area.

Table 6-1 CSV Worksheet File and Domain Values for the Project Commitments Subject Area

Domain Values Domain Value Table Column Description Session



Lists the Commitment Typecodes and their correspondingdomain values of Commitment Type for the Oracle11i/12 Application


To configure domainValues_Project_CommitmentType_ora<ver>.csv:

  1. Identify the Project Commitment Types in your Oracle EBS source system by using the following SQL:

  2. Using a text editor, open the domainValues_Project_CommitmentType_ora<ver>.csv file located in the $pmserver\lkpfiles folder.

  3. Copy the LOOKUP_CODE to the COMMITMENT_TYPE column in the file. The data must be copied starting from the 6th line. Use commas to separate the entries.

  4. Map each Lookup Code (COMMITMENT_TYPE) to one domain value and a corresponding meaning. For more information about Project Commitment Type domain values, see Oracle Business Analytics Warehouse Data Model Reference.

  5. Save and close the file.

6.2 Updates to Universal Adapter for Oracle Project Analytics

Refer to the following guides before performing the configuration described in this section:

In addition to the CSV files mentioned in the configuration documentation, the following CSV files also need to be configured so that the code dimension is loaded properly and the code – name pairs are properly resolved in the dimension tables:

As a general rule, use the default value 0 for numeric columns and 'N/A' or 'Unspecified' for string columns so that you do not encounter 'Not Null' errors when running the ETLs. Date fields can be null as well.

The dimension ID fields in the fact staging tables have to be populated with the integration_id of the various dimensions. This is important, otherwise the dimension wids fields in the fact tables will default to 0.

Use the following SQL to retrieve the foreign key information for project facts from the DAC metadata. In addition, refer to the Oracle Business Analytics Warehouse Date Model Reference for the star schema diagrams and other foreign key information:

select tbl, col, fk_tbl, fk_col
where a.row_wid  = b.table_wid
and b.fk_table_wid = aa.row_wid  
and b.fk_col_wid = bb.row_wid
and like 'W_PROJ%F'
and like '%WID'
order by 1,2;

Similarly, the common dimensions that Projects uses such as W_INT_ORG_D, W_MCAL_DAY_D, W_MCAL_CONTEXT_G, W_EMPLOYE_D, W_JOB_D, W_INVENTORY_PRODUCT_D, and so forth also need to be populated correctly from the source files.

W_MCAL_CONTEXT_G has a class field that holds two values— GL or PROJECTS. To resolve the project accounting dates in the fact tables, there must be data present in this table for class 'PROJECTS.'

6.3 Configuration Update for Project Customer for Oracle EBS

By default, Oracle EBS only has 'PRIMARY' relationship code in the PA_PROJECT_CUSTOMERS table. Therefore, the value has been added in the Informatica lookup 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 lookup needs to be edited to include any additional values.

To edit the lookup:

  1. Open the PowerCenter Designer and connect to your Informatica repository where you want to effect these changes.

  2. Open the SDE_ORA_11510_Adaptor or SDE_ORA_R12_Adaptor folder of the Informatica repository.

  3. Open the mapping SDE_ORA_ProjectDimension and open the mplt_SA_ORA_ProjectDimension mapplet in it.

  4. Check out the mplt_SA_ORA_ProjectDimension mapplet.

  5. Open the lookup LKP_PROJ_CUST, select the Properties tab, and then open the SQL query.

  6. 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.

  7. Validate the lookup SQL and press OK twice, and then save the repository and check in the mapplet. Review the mapping to ensure it is valid.

  8. Open PowerCenter Workflow Manager and connect to one of these repository folders: SDE_ORA_11510_Adaptor or SDE_ORA_R12_Adaptor.

    1. Check out the SDE_ORA_Project session in the Task Developer tab.

    2. Validate, save, and check in the session.

    3. Save your repository.

6.4 My Oracle Support Technical Note on How to Configure RPD if the Cost Aggregate Grain is Set to Period or Year

A technical note has been posted to My Oracle Support to explain how to configure the grain of Cost aggregate (W_PROJ_COST_A) to Fiscal Period or Fiscal Quarter or Fiscal Year.

To locate the note in My Oracle Support, enter 1088171.1 in the search field.

6.5 New Dimension for Project Budget and Forecast Fact

The Expenditure Category dimension was added to Oracle Project Analytics. This new dimension is an addition to the dimensions for Project Budget and Forecast Fact listed in the Oracle Business Analytics Warehouse Data Model Reference.

6.6 Linear Spread Metrics for Project Budget Fact

In the Oracle EBS Project application, the Budget Line amount is split equally between all the days (including weekend days) in the budget line duration when calculating the Cost Amount, Cost ITD Amount, or Cost PTD Amount for a certain Period. By default, Oracle Project Analytics allocates the full Budget Line Amount to the Period containing the Budget Line Start Date. All the necessary metadata is provided in the RPD to support the calculation method adopted by the Oracle Project application that uses Linear Spreading to calculate PTD and ITD metrics.

A new logical table, Fact – Project Budget Linear Spread, has been created that contains Budget metrics built using a Linear Spreading logic in the business model and mapping layer. However, the new metrics are not exposed in the presentation layer. You can either replace the existing Budget metrics in presentation layer with the new ones (the same metric names are used in the old and new logical Budget fact tables), or create a new subject area to expose the new metrics. The first option enables you to reuse the existing reports with Budget metrics without any modification.