6 New Features and Updates for Oracle Project Analytics

This chapter contains the following topics:

Section 6.1, "New Project Commitments Subject Area for Oracle EBS"
Section 6.2, "Updates to Universal Adapter for Oracle Project Analytics"
Section 6.3, "Configuration Update for Project Customer for Oracle EBS"
Section 6.4, "My Oracle Support Technical Note on How to Configure RPD if the Cost Aggregate Grain is Set to Period or Year"
Section 6.5, "New Dimension for Project Budget and Forecast Fact"
Section 6.6, "Linear Spread Metrics for Project Budget Fact"

6.1 New Project Commitments Subject Area for Oracle EBS

This section contains the following topics:

Section 6.1.1, "Overview"
Section 6.1.2, "Before You Begin"
Section 6.1.3, "Configuration Steps for the Project Commitments Subject Area"

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
domainValues_Project_CommitmentType_ora<ver>.csv	W_PROJ_COMMITMENT_F.W_COMMITMENT_TYPE	Lists the Commitment Typecodes and their correspondingdomain values of Commitment Type for the Oracle11i/12 Application	SDE_ORA_ProjectCommitmentFact

To configure domainValues_Project_CommitmentType_ora<ver>.csv:

Identify the Project Commitment Types in your Oracle EBS source system by using the following SQL:
```
SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'COMMITMENT LINE TYPE'
```
Using a text editor, open the domainValues_Project_CommitmentType_ora<ver>.csv file located in the $pmserver\lkpfiles folder.
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.
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.
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:

Oracle BI Applications Configuration Guide for Informatica PowerCenter Users, Section 15.2.4 "Configuration Steps for Project Analytics for Universal"
Oracle Business Analytics Warehouse Data Model Reference

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:

file_codes_project_bill_hold.csv
file_codes_project_budget_changereason.csv
file_codes_project_budget_class.csv
file_codes_project_budget_entrylevel.csv
file_codes_project_budget_status.csv
file_codes_project_budget_timephasedtype.csv
file_codes_project_budget_versiontype.csv
file_codes_project_budgettype.csv
file_codes_project_contract_type.csv
file_codes_project_cost_code_type.csv
file_codes_project_event_class.csv
file_codes_project_event_source_code.csv
file_codes_project_expenditure_category.csv
file_codes_project_expenditure_class.csv
file_codes_project_finplantype.csv
file_codes_project_funding_category.csv
file_codes_project_fundingapproval.csv
file_codes_project_invoice_class.csv
file_codes_project_invoice_line_type.csv
file_codes_project_invoice_status.csv
file_codes_project_priority_code.csv
file_codes_project_projecttype.csv
file_codes_project_resource_class.csv
file_codes_project_resource_type.csv
file_codes_project_resource_type_code.csv
file_codes_project_revenue_category.csv
file_codes_project_revenue_distributed.csv
file_codes_project_revenue_status.csv
file_codes_project_role.csv
file_codes_project_securitylevel.csv
file_codes_project_service_type.csv
file_codes_project_status.csv
file_codes_project_task_priority_code.csv
file_codes_project_task_status.csv
file_codes_project_task_type.csv
file_codes_project_transfer_status.csv
file_codes_project_type_class.csv
file_codes_project_uom.csv

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 a.name tbl, b.name col, aa.name fk_tbl, bb.name fk_col
from W_ETL_TABLE a, W_ETL_TABLE_COL b,W_ETL_TABLE aa, W_ETL_TABLE_COL bb
where a.row_wid  = b.table_wid
and b.fk_table_wid = aa.row_wid  
and b.fk_col_wid = bb.row_wid
and a.name like 'W_PROJ%F'
and b.name like '%WID'
AND B.NAME NOT IN ('ETL_PROC_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:

Open the PowerCenter Designer and connect to your Informatica repository where you want to effect these changes.
Open the SDE_ORA_11510_Adaptor or SDE_ORA_R12_Adaptor folder of the Informatica repository.
Open the mapping SDE_ORA_ProjectDimension and open the mplt_SA_ORA_ProjectDimension mapplet in it.
Check out the mplt_SA_ORA_ProjectDimension mapplet.
Open the lookup LKP_PROJ_CUST, select the Properties tab, and then open the SQL query.

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.

SELECT MAX(PA_PROJECT_CUSTOMERS.CUSTOMER_ID) AS CUSTOMER_ID,
PA_PROJECT_CUSTOMERS.PROJECT_ID            AS PROJECT_ID
FROM PA_PROJECT_CUSTOMERS
WHERE UPPER(PA_PROJECT_CUSTOMERS.PROJECT_RELATIONSHIP_CODE) in ('PRIMARY','OVERRIDE CUSTOMER')
AND PA_PROJECT_CUSTOMERS.CUSTOMER_BILL_SPLIT                   =
  (SELECT MAX(CUSTOMER_BILL_SPLIT)
  FROM PA_PROJECT_CUSTOMERS A
  WHERE A.PROJECT_ID = PA_PROJECT_CUSTOMERS.PROJECT_ID
  and UPPER(a.PROJECT_RELATIONSHIP_CODE) in ('PRIMARY','OVERRIDE CUSTOMER')
  )
GROUP BY PA_PROJECT_CUSTOMERS.PROJECT_ID

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