| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Version 7.9.6.1 Part Number E14844-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Version 7.9.6.1 Part Number E14844-01 |
|
|
View PDF |
This section describes how to configure Oracle Project Analytics. It contains the following topics:
Oracle Project Analytics offers organizations a comprehensive analytics solution that delivers pervasive insight into forecast, budgets, cost, revenue, billing, profitability, and other aspects of project management to help effectively track project life cycle status. It provides consolidated and timely information that is personalized, relevant, and actionable to improve performance and profitability. Oracle Project Analytics is also integrated with other applications in the Oracle BI Applications family to deliver cross functional analysis, such as AR and AP invoice aging analysis and procurement transactions by project.
Oracle Project Analytics provides role-based reporting and analysis for various roles involved in the project life cycle. Typical roles include Project Executive, Project Manager, Project Cost Engineer/Analyst, Billing Specialist, Project Accountant and Contract Administrator.
Executives can closely monitor the organization's performance and the performance of the projects that the organization is responsible for by looking into a particular program and project and verifying how the period, accumulated, or estimated-at-completion cost is doing compared to budget and forecast. Cost variances and trends can be analyzed so that prompt actions can be taken to get projects on track or make any necessary changes in estimates, minimizing undesired results and reactive measures.
Oracle Project Analytics shows past, present, and future performance, and includes estimated metrics at project completion. Further analysis can be done on each project by drilling down to detailed information including profitability and cost at the task level.
Project managers can view the projects that they are responsible for, compare key metrics between projects, and analyze the details of a particular project such as cost distribution by task, resource, and person. Oracle Project Analytics provides a comprehensive, high-level view of accumulated and trending information for a single project or group of projects, as well as detailed information, such as budget accuracy and details by project and financial resource. Project managers can view cost and revenue by task, expenditure category, or resource type; and by project or resource. The level of analysis can be as granular as cost, revenue, or budget transaction.
Oracle Project Analytics provides out-of-the-box adapters for Oracle EBS 11.5.10 (Family Pack M) and R12, and PeopleSoft 8.9 and 9.0. It also provides universal adapters to extract and load data from legacy sources such as homegrown systems or from sources that have no prepackaged source-specific ETL adapters.
Oracle Project Analytics application comprises the following Subject Areas:
Funding. A detailed Subject Area that provides the ability to track Agreement Amount, Funding Amount, Baselined Amount, and all changes in funding throughout the life cycle of the project. In addition, it provides the ability to do comparative analysis of Agreement Amount, Funding Amount, Invoice Amount, and the remaining funding amount across projects, tasks, customers, organizations, and associated hierarchies.
Budgets. A detailed Subject Area that provides the ability to report on Cost Revenue, Margin for Budgets, and Budget changes including tracking original and current budgets across projects, tasks, organizations, resources, periods and associated hierarchies at budget line level.
Forecast. A detailed Subject Area that provides the ability to report on Cost, Revenue and Margin for Forecasts, and Forecast changes. Forecast change analysis includes tracking original and current forecasts across projects, tasks, organizations, resources, periods and associated hierarchies. It provides the ability to track the metrics that indicate the past, present and future performance of cost, revenue, and margin.
Cost. A detailed Subject Area that provides the ability to report on Cost (Burdened Cost), Raw Cost, Burden Cost for the past and current periods including inception-to-date, year-to-date comparisons across projects, tasks, organizations, resources, suppliers, and associated hierarchies. It provides the ability to track the cost at cost distribution level.
Revenue. A detailed Subject Area that provides the ability to report on Revenue transactions for the past, and current periods including inception-to-date, year-to-date comparisons across projects, tasks, organizations, resources, and associated hierarchies. It provides the ability to track the revenue at Revenue distribution level.
Billing. A detailed Subject Area that provides the ability to report on Billing Amount, Retention Amount, Unearned Amount, and Unbilled Receivables Amounts across the projects, tasks, organizations, resources, and associated hierarchies. It provides the ability to track the invoice amount at invoice (draft invoice) line level only. Note: Invoice tax amount is not captured in this release.
Performance. A consolidated Subject Area that includes combined information from Budgets, Forecasts, Cost, Revenue, and provides the ability to do performance by comparing the actual (cost, revenue, margin and margin percentage) with budgets, and forecasts across projects, tasks, organizations, resources, and associated hierarchies.
This section contains configuration steps that you need to perform on Oracle Project Analytics before you do a full data load. It contains the following topics:
Section 15.2.1, "Configuration Steps for Project Analytics for All Source Systems"
Section 15.2.2, "Configuration Steps for Project Analytics for Oracle EBS"
Section 15.2.3, "Configuration Steps for Project Analytics for PeopleSoft"
Section 15.2.4, "Configuration Steps for Project Analytics for Universal"
Section 15.2.5, "Configuration Steps for Controlling Your Data Set"
This section contains configuration steps before you do a full load that apply to all source systems. It contains the following topics:
Section 15.2.1.1, "Configuring Dimension Hierarchies for Project Analytics"
Section 15.2.1.2, "Configuring DAC Parameters for Project Analytics"
Note:
For configuration steps that apply to all Oracle BI Application modules, see Chapter 3, "Configuring Common Areas and Dimensions".Out of the box, Oracle BI Applications supports the following hierarchies between the Project Analytics dimensions:
Project to Task
Project Organization to Project Manager to Project
Expenditure Class to Expenditure Category
You can set any type of hierarchy drill-downs between dimensions in the Oracle BI Applications analytics repository (RPD).
To configure dimension hierarchies
Select the dimension that you want to drill down from.
Go to the Hierarchy in the RPD that corresponds to this dimension.
Go to the Detail Level of this hierarchy and double-click it.
Go to the Preferred Drill Path tab.
Click the Add button.
In the new window, choose the hierarchy and level from the Core Business Model to which you want a user to drill down when a user clicks a record from the current dimension.
Note:
The Resource Breakdown Structure (RBS) available in Oracle EBS has not been added to the data warehouse because of the flexibility of setting hierarchies and drill down between dimensions in the RPD.Table 15-1 lists the metadata in DAC that pertains to Oracle Project Analytics for Oracle EBS and PeopleSoft.
Table 15-1 DAC Metadata for Oracle Project Analytics for Oracle EBS and PeopleSoft
| DAC Metadata Type | Metadata Name |
|---|---|
|
Supported Container |
|
|
Subject Areas |
Projects |
|
Task Groups |
|
|
Execution Plans |
|
|
Configuration Tag |
|
Note:
In DAC, the two configuration tags, Project Dimensions and Multiple Calendar Support, are specific to Project Analytics. They are used to bring in all the correct tasks for populating the Project Analytics tables in the data warehouse.Before you run the initial ETL run, you need to configure the COST_TIME_GRAIN parameter for the time aggregation level in the Project Cost Lines aggregate fact table.
To configure the COST_TIME_GRAIN parameter
In DAC, display the Design view, then display the Source System Parameters tab.
Select the Cost_Time_Grain parameter and then in the Edit tab, select the appropriate value in the Value field. Valid values are:
PERIOD
QUARTER
YEAR
To configure the Global Currency parameters
In DAC, display the Design view, then display the Source System Parameters tab.
Select the global currency code parameters and modify them as appropriate.
This section contains configuration steps before you do a full data load that apply to Oracle EBS. It contains the following topics:
Section 15.2.2.2, "How to Configure the DomainValues_Project_Type_Class_code_ora<ver>.csv"
Section 15.2.2.3, "How to Configure the domainValues_Project_Status_ora<ver>.csv"
Section 15.2.2.4, "How to Configure the domainValues_Project_ServiceType_ora<ver>.csv"
Section 15.2.2.5, "How to Configure the domainValues_Project_ResourceClass_ora<ver>.csv"
Section 15.2.2.6, "How to Configure the domainValues_Project_InvoiceRevenue_Status_ora<ver>.csv"
Section 15.2.2.7, "How to Configure the domainValues_Project_Invoice_Line_Type_ora<ver>.csv"
Section 15.2.2.8, "How to Configure the domainValues_Project_Funding_Level_code_ora<ver>.csv"
Section 15.2.2.9, "How to Configure the domainValues_Project_Funding_Category_ora<ver>.csv"
Section 15.2.2.10, "How to Configure the domainValues_Project_Cost_Transfer_Status_ora<ver>.csv"
Section 15.2.2.11, "How to Configure the domainValues_Project_Budget_Time_Phased_Code_ora<ver>.csv"
Section 15.2.2.12, "About Configuring the Project Dimension for Oracle EBS"
Section 15.2.2.13, "About Configuring the Task Dimension for Oracle EBS"
Section 15.2.2.14, "About Configuring the Financial Resource Dimension"
Section 15.2.2.15, "About Configuring the Project Classification Dimension"
Section 15.2.2.16, "About Configuring the Work Type Dimension"
Section 15.2.2.18, "About Operating Unit Based Security with Oracle EBS"
You configure Oracle Project Analytics by mapping domain values to columns in the CSV files located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
For more information about configuring domain values with CSV worksheet files, see Chapter 15, "About Configuring Domain Values and CSV Worksheet Files for Oracle Project Analytics".
For more information about domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Note:
Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.Table 15-2 lists the CSV worksheet files and the domain values for Oracle Project Analytics in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Table 15-2 CSV Worksheet Files and Domain Values for Oracle Project Analytics
| Domain Values | Domain Value Table Column | Description | Session |
|---|---|---|---|
|
domainValues_Project_Type_Class_code_ora<ver>.csv |
W_PROJECT_D.W_PROJECT_TYPE_CLASS_CODE |
Lists the Project Type Class codes and their corresponding domain values of Project Type Class Code for the Oracle 11i/12 Application |
SDE_ORA_Project |
|
domainValues_Project_Status_ora<ver>.csv |
W_STATUS_D.W_STATUS_CODE |
Lists the Project Status codes and their corresponding domain values of Project Status Code for the Oracle 11i/12 Application |
SDE_ORA_StatusDimension_ProjectStatus |
|
domainValues_Project_ServiceType_ora<ver>.csv |
W_XACT_TYPE_D.W_XACT_TYPE_CODE |
Lists the Service Type codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_TransactionTypeDimension_ServiceType |
|
domainValues_Project_Security_Level_code_ora<ver>.csv |
W_PROJECT_D.W_SECURITY_LEVEL |
Lists the Project Security Level codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_Project |
|
domainValues_Project_ResourceClass_ora<ver>.csv |
W_XACT_TYPE_D.W_XACT_TYPE_CODE |
Lists the Resource Class codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_TransactionTypeDimension_ResourceClass |
|
domainValues_Project_InvoiceRevenue_Status_ora<ver>.csv |
W_STATUS_D.W_STATUS_CODE |
Lists the Invoice/Revenue Status codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_StatusDimension_Project_InvoiceStatus, SDE_ORA_StatusDimension_Project_RevenueStatus |
|
domainValues_Project_Invoice_Line_Type_<ver>.csv |
W_XACT_TYPE_D.W_XACT_TYPE_CODE |
Lists the Invoice Line Type codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_TransactionTypeDimension_Invoice_Line_Type |
|
domainValues_Project_Funding_Level_code_ora<ver>.csv |
W_PROJECT_D.W_PROJECT_FUNDING_LEVEL_CODE |
Lists the Project Funding Level codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_Project |
|
domainValues_Project_Funding_Category_ora<ver>.csv |
W_XACT_TYPE_D.W_XACT_TYPE_CODE |
Lists the Funding Category codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_TransactionTypeDimension_FundingCategory |
|
domainValues_Project _Cost_Transfer_Status_ora<ver>.csv |
W_STATUS_D.W_STATUS_CODE |
Lists the Cost Transfer Status codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_StatusDimension_Project_TransferStatus |
|
domainValues_Project_Budget_Time_Phased_Code_ora<ver>.csv |
W_BUDGET_D.W_TIME_PHASED_CODE |
Lists the Budget Time Phased codes and their corresponding domain values for the Oracle 11i/12 Application |
SDE_ORA_BudgetDimension_BudgetType, SDE_ORA_BudgetDimension_FinPlanType |
This section explains how to configure the domainValues_Project_Type_Class_code_ora<ver>.csv.
Identify the Project Types in your Oracle EBS source system by using the following SQL:
SELECT DISTINCT PROJECT_TYPE_CLASS_CODE FROM PA_PROJECT_TYPES_ALL
Using a text editor, open the domainValues_Project_Type_Class_code_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the PROJECT_TYPE_CLASS_CODE to the PROJECT_TYPE_CLASS_CODE column in the file. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each Project Type Class Code (PROJECT_TYPE_CLASS_CODE) to one domain value. For more information about Project Type domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_Status_ora<ver>.csv.
Identify the Project Statuses in your Oracle EBS source system by using the following SQL:
SELECT PROJECT_STATUS_CODE, PROJECT_STATUS_NAME FROM PA_PROJECT_STATUSES WHERE STATUS_TYPE = 'PROJECT'
Using a text editor, open the domainValues_Project_Status_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the PROJECT_ STATUS _CODE and PROJECT_ STATUS NAME to the STATUS_CODE and STATUS_NAME columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each Project Status Code (PROJECT_STATUS_CODE) to one domain value. For more information about Project Status domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_ServiceType_ora<ver>.csv.
Identify the Project Service Types in your Oracle EBS source system by using the following SQL:
SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'SERVICE TYPE'
Using a text editor, open the domainValues_Project_ServiceType_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the LOOKUP_CODE to the XACT_TYPE_CODE column in the file. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each Lookup Code (XACT_TYPE_CODE) to one domain value. For more information about Project Service domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_ResourceClass_ora<ver>.csv.
Identify the Project Resource Classes in your Oracle EBS source system by using the following SQL:
SELECT PB.RESOURCE_CLASS_CODE,PT.NAME,PT.DESCRIPTION FROM PA_RESOURCE_CLASSES_B PB,PA_RESOURCE_CLASSES_TL PT WHERE PB.RESOURCE_CLASS_ID = PT.RESOURCE_CLASS_ID AND PT.LANGUAGE = 'US'
Using a text editor, open the domainValues_Project_ResourceClass_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the RESOURCE_CLASS_CODE, NAME, and DESCRIPTION to the XACT_TYPE_CODE, XACT_TYPE_NAME, XACT_TYPE_DESC columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each Resource Class Code (XACT_TYPE_CODE) to one domain value. For more information about Resource Class domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_InvoiceRevenue_Status_ora<ver>.csv.
Identify the Invoice/Revenue Statuses in your Oracle EBS source system by using the following SQL:
SELECT LOOKUP_CODE,MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'INVOICE/REVENUE STATUS' AND LANGUAGE = 'US'
Open the domainValues_Project_InvoiceRevenue_Status_ora<ver>.csv file using a text editor in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy LOOKUP_CODE and MEANING to the STATUS_CODE and STATUS_NAME columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each STATUS_CODE to one domain value. For more information about Invoice Revenue domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_ Invoice_Line_Type _ora<ver>.csv.
Identify the Invoice Line Types in your Oracle EBS source system by using the following SQL:
SELECT LOOKUP_CODE,MEANING,DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE LIKE 'INVOICE LINE TYPE' AND LANGUAGE = 'US'
Using a text editor, open the domainValues_Project_Invoice_Line_Type_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy LOOKUP_CODE, MEANING, and DESCRIPTION to the XACT_TYPE_CODE, XACT_TYPE_NAME, and XACT_TYPE_DESC columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each XACT_TYPE_CODE to one domain value. For more information about Invoice Line Type domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_Funding_Level_code_ora<ver>.csv.
Identify the Funding Levels in your Oracle EBS source system by using the following SQL:
SELECT DISTINCT PROJECT_LEVEL_FUNDING_FLAG FROM PA_PROJECTS_ALL
Using a text editor, open the domainValues_Project_Funding_Level_code_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the PROJECT_FUNDING_LEVEL_FLAG to the PROJECT_FUNDING_LEVEL_CODE column in the file. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each PROJECT_FUNDING_LEVEL_CODE to one domain value. For more information about Project Funding domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_Funding_Category_ora<ver>.csv.
Identify the Funding Category Types in your Oracle EBS source system by using the following SQL:
SELECT LOOKUP_CODE,MEANING,DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'FUNDING CATEGORY TYPE' AND LANGUAGE = 'US'
Using a text editor, open the domainValues_Project_Funding_Category_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the LOOKUP_CODE, MEANING, and DESCRIPTION to the XACT_TYPE_CODE, XACT_TYPE_NAME, and XACT_TYPE_DESC columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each XACT_TYPE_CODE to one domain value. For more information about Project Funding domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_Cost_Transfer_Status_ora<ver>.csv.
Identify the Project Transfer Statuses in your Oracle EBS source system by using the following SQL:
SELECT LOOKUP_CODE,MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'TRANSFER STATUS' AND LANGUAGE = 'US'
Using a text editor, open the domainValues_Project_Cost_Transfer_Status_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the LOOKUP_CODE and MEANING to the STATUS_CODE and STATUS_NAME columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each STATUS_CODE to one domain value. For more information about Status Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Project_Budget_Time_Phased_Code_ora<ver>.csv.
Identify the Budget Time Phase Codes in your Oracle EBS source system by using the following SQL:
SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'BUDGET TIME PHASED TYPE'AND LANGUAGE = 'US'
Using a text editor, open the domainValues_Project_Budget_Time_Phased_Code_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the LOOKUP_CODE to the TIME_PHASED_CODE column in the file. The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each TIME_PHASED_CODE to one domain value. For more information about Budget Time Phase Codes, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
The Project dimension (W_PROJECT_D) data is sourced from the PA_PROJECTS_ALL table in Oracle EBS. Other important project attributes such as Project Type, Project Manager, Country, State, City, Project Organization, Project Status, Project Operating Unit ID, Project Primary Customer ID, and Project Primary Customer Account ID are sourced from the following Oracle EBS tables:
PA_LOCATIONS
HR_ORGANIZATIONS_ALL
PA_PROJECT_TYPES
PA_PROJECT_STATUSES
PA_IMPLEMENTATIONS_ALL
Out of the box, Oracle BI Applications supports Projects and not Project Templates. The following filter prevents Project Template data from appearing:
PA_PROJECTS_ALL.TEMPLATE_FLAG = N
If you need to source Project Template information, you can remove this filter condition. However, Project Templates cannot be used for Billing, Costing, or Revenue.
All analysis in pre-built reports is based on Project Operating Unit, which is the operating unit of a project.
The Project dimension is also referenced in some Financial Analytics and Supply Chain Analytics facts. For more information about the integration with other Oracle BI Analytics solutions, Section 15.3.1, "Integrations with Project Analytics for Oracle EBS."
Task dimension data is sourced from the task table (PA_TASKS) in Oracle EBS, 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.
Out of the box, 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, you can extend the number of levels in the schema and ETL to support all levels.
To extend levels, you need to add all the change capture columns (TASK_NAME, TASK_NUMBER, WBS_LEVEL and WBS_NUMBER) for every new level that you want in the W_TASK_DH table. In the SILOS folder, update the following mappings for the ETL:
SIL_Project_TaskDimensionHierarchy
SIL_Project_TaskDimensionHierarchy_Full
SIL_Project_TaskDimensionHierarchy_IdentifyBaseModified
SIL_Project_TaskDimensionHierarchy_IdentifyModifiedRecords
In addition, you need to update the DAC schema by importing the new columns from the physical schema. You must update the following objects in the metadata repository:
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
Revenue and Cost are often analyzed side by side in the same report. The data for Revenue is typically based on Expenditure Type and Event Type. The data for Cost is typically based on Expenditure Type. Therefore, both Expenditure Types and Event Types are stored in the same dimension.
The same set of columns are overloaded so that all the Revenue data by Expenditure and Event types can be seen in the same report.
The Expenditure Type data is sourced from the PA_EXPENDITURE_TYPES table. The EXPENDITURE_TYPE, EXPENDITURE_CATEGORY_CODE, and REVENUE_CATEGORY_CODE columns in the OLTP populate the RESOURCE_TYPE_CODE, EXPENDITURE_CATEGORY_CODE and REVENUE_CATEGORY_CODE columns respectively in the dimension table.
The Event Type data is sourced from the PA_EVENT_TYPES table. The EVENT_TYPE, EVENT_TYPE_CLASSIFICATION, and REVENUE_CATEGORY_CODE columns in the OLTP populate the RESOURCE_TYPE_CODE, EVENT_TYPE_CLASS_CODE, and REVENUE_CATEGORY_CODE columns respectively in the dimension table.
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 metadata repository 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.The W_WORK_TYPE_D dimension stores the implementation defined work types to be performed on various projects. For ORACLE EBS sources, the "work type id" can be defined at the Project level, Task level, or Expenditure Item level. While populating the Cost & Revenue Facts, the work type id is defined at the Task level if it is null at the Expenditure Item level.
The Job dimension is maintained in the Human Resources Analytics module.For information about how to configure this dimension, see Chapter 7, "How to Configure the Key Flexfields for the Job Dimension".
All fact tables in Project Analytics are secured by Operating Unit (or Business Unit) security. This security filter uses the OU_ORG session variable, which is initialized by the list of Operating Units the user has access to in the OLTP system. An additional join is added to each fact report, so only transaction data for Projects that are owned by the Project Operating Units that the user is allowed to access are shown in the report. Dimension reports are not secured by filters.
For more information about adding security to dimensions and implementation steps for operating unit based security with Oracle EBS, see Oracle Business Intelligence Applications Security Guide.
This section contains configuration steps before you do a full data load that apply to PeopleSoft. It contains the following topics:
Section 15.2.3.1, "About Persisted Stage for Oracle Project Analytics for PeopleSoft"
Section 15.2.3.3, "How to Configure Resource Class Domain Values for the Transaction Type Dimension"
Section 15.2.3.4, "How to Configure Invoice Line Domain Values for the Transaction Type Dimension"
Section 15.2.3.5, "How to Configure Project Type Class Code Domain Values for the Project Dimension"
Section 15.2.3.6, "How to Configure Project Status Domain Values for the Status Dimension"
Section 15.2.3.7, "How to Configure Transfer Status Domain Values for the Status Dimension"
Section 15.2.3.8, "How to Configure Revenue Status Domain Values for the Status Dimension"
Section 15.2.3.9, "How to Configure Invoice Status Domain Values for the Status Dimension"
Section 15.2.3.12, "Updating Project Analytics Logical Table Sources"
Section 15.2.3.13, "Operating Unit Based Security with PeopleSoft"
Oracle Project Analytics for PeopleSoft is delivered using a persisted stage strategy for the incremental load process for the following PeopleSoft Project Analytics areas:
Budget
Revenue
Cost
The PeopleSoft source system tables for these analytics areas do not reliably update the date columns. This prevents the use of the standard incremental load process.
The persisted stage strategy enables incremental loads of data from these tables. Each time the persisted stage workflows are run, a full extract from the OLTP to the OLAP is executed. Each time a record is added to the OLAP persisted stage table or on any full load to the OLAP persisted stage table, dates are updated in the persisted stage table to the current system date. If a record already exists in the OLAP persisted stage table and has been updated in the OLTP, the dates are also updated to the current system date. Once the persisted stage tables are populated, ETLs are then run to extract only new and updated records from the persisted stage tables to the fact staging tables.
The benefit of using persisted stage for incremental loads is that the system only updates records in the fact staging tables that have changed since the previous ETL run. The persisted stage tables require additional space in the database. Therefore, Oracle gives you the option to configure the ETL process to run in a non-persisted stage (full extract and full load for every ETL run) instead of persisted stage.
You can configure persisted stage or non-persisted stage for each individual area: Budget, Cost, or Revenue. However, you cannot configure any of these areas for both persisted stage and non-persisted stage.
To configure non-persisted stage for Budget, Revenue, or Cost
In DAC, select the applicable version of the PeopleSoft Container, for example PeopleSoft 8.9.
On the Subject Areas tab, select the Projects Subject Area.
Add any of the following Configuration tags, depending on the area that you want to change to non-persisted stage:
Project_NonPersistedStage_Budget
Project_NonPersistedStage_Cost
Project_NonPersistedStage_Revenue
For each Configuration tag that you added for non-persisted stage, remove its persisted stage Configuration tag.
For example, remove any or all of the following tags:
Project_PersistedStage_Budget
Project_PersistedStage_Cost
Project_PersistedStage_Revenue
Reassemble the Projects Subject Area.
You must perform this step to save the updated tags.
Rebuild the applicable version of the PeopleSoft Execution Plan, for example Projects - PeopleSoft 8.9.
Note:
To revert to persisted stage, reverse the steps in this task by removing Configuration tags for non-persisted stage and adding Configuration tags for persisted stage.If you modify or extend a seeded list of values, you must configure the CSV files for Oracle Project Analytics by mapping values from your source systems to the domain values.
The following sections explain how to extract the lists of values from your source system, which you then compare with the seeded values. If the lists of values are different from the seeded values, you need to follow the instructions to configure the domain values and CSV worksheet files.
Table 15-3 Domain Values and CSV Worksheet Files for Oracle Project Analytics and PeopleSoft
| Worksheet File Name | Description | Session |
|---|---|---|
|
domainValues_Resource_Class_XACT_TYPE_D_PSFT.csv |
Lists the Transaction types and the corresponding domain values for the Oracle PSFT 89/90 application. For more information about updating the values in this file, see |
SDE_PSFT_TransactionTypeDimension_ResourceClass |
|
domainValues_Project_Invoice_Line_Type_psft.csv |
Lists the Transaction Types and the corresponding domain values for the Oracle PSFT 89/90 application. For more information about updating the values in this file, see |
SDE_PSFT_TransactionTypeDimension_InvoiceLineType |
|
domainValues_Project_Type_Class_code_psft.csv |
Lists the Project Type and the corresponding domain values for the Oracle PSFT 89/90 application. For more information about updating the values in this file, see |
SDE_PSFT_CodeDimension_Project_Project_Type |
|
domainValues_Project_Status_code_psft.csv |
Lists the Project Status and the corresponding domain values for the Oracle PSFT 89/90 application. For more information about updating the values in this file, see |
SDE_PSFT_StatusDimension_ProjectStatus_Full |
|
domainValues_transfer_status_code_psft.csv |
Lists the Transfer Status and the corresponding domain values for the Oracle PSFT 89/90 application. For more information about updating the values in this file, see |
SDE_PSFT_StatusDimension_TransferStatus_Full |
|
domainValues_revenue_status_code_psft.csv |
Lists the Revenue Status and the corresponding domain values for the Oracle PSFT 89/90 application. For more information about updating the values in this file, see |
SDE_PSFT_StatusDimension_RevenueStatus_Full |
|
domainValues_invoice_status_code_psft.csv |
Lists the Invoice Status and the corresponding domain values for the Oracle PSFT 89/90 application. For more information about updating the values in this file, see |
SDE_PSFT_StatusDimension_InvoiceStatus_Full |
This section describes how to configure Resource Class domain values for the Transaction Type dimension.
Identify the Resource Class Types in the PeopleSoft source system by using the following SQL:
SELECT
FIELDVALUE,
XLATSHORTNAME,
XLATLONGNAME,
LASTUPDDTTM,
LASTUPDOPRID
FROM
PSXLATITEM
WHERE
FIELDNAME='RESOURCE_CLASS'
AND EFF_STATUS = 'A';
Using a text editor, open the domainValues_Resource_Class_XACT_TYPE_D_PSFT.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
In the text editor view, distinct records are listed in separate lines and the attributes are separated by commas. The records start on the next line after the last updated line.
Copy the Lookup Code and Meaning from the SQL results to the SOURCE_TYPE_CODE and SOURCE_TYPE_NAME columns in the CSV file respectively.
The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each FIELDVALUE to the XACT_TYPE_CODE column.
Enter the corresponding W_XACT_TYPE_CODE and W_XACT_TYPE_DESC.
For more information about Transaction Type, Resource Class domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section describes how to configure Invoice Line domain values for the Transaction Type dimension.
Identify the Invoice Line Type in your PeopleSoft source system by using the following SQL:
SELECT
FIELDVALUE,
XLATSHORTNAME,
XLATLONGNAME,
LASTUPDDTTM,
LASTUPDOPRID
FROM
PSXLATITEM
WHERE
FIELDNAME='LINE_TYPE'
AND EFF_STATUS = 'A';
Using a text editor, open the domainValues_Project_Invoice_Line_Type_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
If the CSV file is opened with a text editor, distinct records are listed in separate lines and the attributes are separated by commas. The records start on the next line after the Last Updated line.
Copy the Lookup Code and Meaning from the SQL results to the SOURCE_TYPE_CODE and SOURCE_TYPE_NAME columns in the CSV file respectively.
The data must be copied starting from the 6th line. Use commas to separate the entries.
Map each of these values from the SQL query results to the CSV file:
FIELDVALUE to the XACT_TYPE_CODE column
XLATSHORTNAME to the XACT_TYPE_NAME column
XLATLONGNAME to the XACT_TYPE_DESC column
Enter the corresponding W_XACT_TYPE_CODE and W_XACT_TYPE_DESC.
These domain values are user defined.
For more information about Invoice Line Type, Transaction Type domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section describes how to configure Project Type Class Code domain values for the Project dimension.
Identify the Project Type Class Codes in your Project Dimension by using the following SQL:
SELECT T.PROJECT_TYPE||'~'||T.SETID AS PROJECT_TYPE,
T.DESCR
FROM PS_PROJ_TYPE_TBL T
WHERE T.EFFDT = (SELECT MAX(EFFDT) FROM PS_PROJ_TYPE_TBL T1
WHERE T1.SETID = T.SETID AND T1.PROJECT_TYPE = T.PROJECT_TYPE
AND T1.EFFDT <= CURRENT_TIMESTAMP
GROUP BY T1.SETID, T1.PROJECT_TYPE)
Note:
PeopleSoft does not have a value directly corresponding to Project Type Class, so you must use this flat file to specify a value to use for this column. This flat file populates both the PROJECT_TYPE_CLASS_CODE and the W_PROJECT_TYPE_CLASS_CODE. The flat file also populates the W_PROJECT_TYPE_CLASS_DESC column.Using a text editor, open the domainValues_Project_Type_Class_code_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the data from the PROJECT_TYPE column to the PROJECT_TYPE_CLASS_CODE column in the CSV file.
Map each Project Type Class to a domain value.
For more information on Project Type Class Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Map each Project Type Class to a value of Y or N in the CAPITALIZABLE_FLG column. If the Project Type Class is considered capitalizable, enter Y. Otherwise, enter N.
Save and close the file.
This section describes how to configure Project Status domain values for the Status dimension.
Identify the Project Type Class Codes in your Project Dimension by using the following SQL:
SELECT T.PROJECT_STATUS ||'~'||T.SETID, T.DESCR, T.PROCESSING_STATUS,
UPPER(P.XLATSHORTNAME) AS PROCESSING_STATUS_DESC
FROM PS_PROJ_STATUS_TBL T, PSXLATITEM P
WHERE T.PROCESSING_STATUS = FIELDVALUE AND FIELDNAME = 'PROCESSING_STATUS' AND
T.EFFDT = (SELECT MAX(EFFDT) FROM PS_PROJ_STATUS_TBL X
WHERE X.EFFDT <= SYSDATE AND X.PROJECT_STATUS=T.PROJECT_STATUS AND X.SETID = T.SETID
GROUP BY X.SETID, X.PROJECT_STATUS)
AND P.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM X1
WHERE X1.EFFDT <= SYSDATE AND X1.FIELDNAME = 'PROCESSING_STATUS' AND X1.FIELDVALUE = P.FIELDVALUE AND X1.EFF_STATUS = 'A'
GROUP BY FIELDNAME)
PeopleSoft uses the Project Status for Status Code and the Processing Status for the Substatus Code.
Using a text editor, open the domainValues_Project_Status_code_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the data in the PROJECT_STATUS column to STATUS_CODE column in the CSV file.
Copy the data from the PROCESSING_STATUS_DESC column to the W_SUBSTATUS and W_SUBSTATUS_DESC columns in the CSV file.
Map each Project Status to a domain value.
For more information on Project Status domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section describes how to configure Transfer Status domain values for the Status dimension.
Identify the Transfer Status codes in your Status dimension by using the following SQL:
SELECT FIELDVALUE AS TRANSFER_STATUS, EFF_STATUS, XLATSHORTNAME AS TRANSFER_STATUS_NAME FROM PSXLATITEM WHERE FIELDNAME = 'GL_DISTRIB_STATUS' AND EFFDT <= SYSDATE
PeopleSoft uses the GL Distrib Status for Transfer Status.
Using a text editor, open the domainValues_transfer_status_code_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the data in the TRANSFER_STATUS column to the STATUS_CODE column in the CSV file.
Map each Transfer Status to a domain value.
For more information on Transfer Status domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
The source for the Revenue Status records in the Status dimension is the sourceValues_revenue_status_code_psft.csv flat file. These values are derived based on GL_DISTRIB_STATUS and BI_DISTRIB_STATUS, which are system codes. These values are determined based on whether or not the GL Status and the Billing Status are D-Distributed.
To configure Revenue Status domain values for the Status dimension
Using a text editor, open the domainValues_revenue_status_code_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the data from the STATUS_CODE column to the STATUS_CODE column in the CSV file.
Map each Revenue Status to a domain value.
For more information on Revenue Status domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section describes how to configure Invoice Status domain values for the Status dimension.
Identify the Invoice Status codes in your Status dimension by using the following SQL:
SELECT DISTINCT FIELDVALUE AS INVOICE_STATUS, EFF_STATUS, XLATSHORTNAME AS INVOICE_STATUS_DESC FROM PSXLATITEM WHERE FIELDNAME = 'BILL_STATUS'
Using a text editor, open the domainValues_invoice_status_code_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the data from the INVOICE_STATUS column to the STATUS_CODE column in the CSV file.
Map each Invoice Status to a domain value.
For more information on Invoice Status domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
Cost Budget is extracted from Project Costing for all Analysis Types within the project's Cost Budget Analysis Group. All extracted Cost Budgets are loaded into the Budget fact table as Raw Cost unless you perform one or both of the following configurations described in this section:
In addition, you must perform the steps in this section:
The ETL process uses the file_Project_Budget_Burden_Analysis_Type_psft.csv flat file to list all Analysis Types for Project Budget Burden Cost. If the ETL process finds the Analysis Type in this flat file, it will not perform further lookups against other lookup tables to determine Project Budget Burden Cost.
To configure the file_Project_Budget_Burden_Analysis_Type_psft.csv
Using a text editor, open the file_Project_Budget_Burden_Analysis_Type_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Enter a list of Analysis Types to be considered as Project Budget Burden Costs. The format is XXX,1 where XXX is an Analysis Type. The 1 is used as a return value to indicate that this is a Project Budget Burden Cost.
The following is an example of classifying Costs with BUR and BRD Analysis Types as Project Budget Burden Costs:
BUR,1 BRD,1
Save and close the file.
You must configure the following flat files to identify Project Budget Burden Costs based on a Source Type, Category, and Subcategory combination of values:
file_Project_Cost_Burden_TypeCatSub_config_psft.csv
The ETL process uses this flat file to designate which columns (Source Type, Category, and Subcategory) are used in the lookup. A DAC parameter determines if this lookup is performed for an implementation.
file_Project_Cost_Burden_TypeCatSub_psft.csv
The ETL process uses this flat file to list all Source Type, Category, and Subcategory combination of values to use for Project Budget Burden Cost.
Note:
Both Project Budget and Project Cost use these flat files, along with a DAC parameter, to identify Burden Cost. You can customize these files if the requirements differ between Budget and Cost in your implementation.For information about how to configure Project Cost, see Section 15.2.3.11, "How to Configure Project Cost."
To configure the file_Project_Cost_Burden_TypeCatSub_config_psft.csv
Using a text editor, open the file_Project_Cost_Burden_TypeCatSub_config_psft.csv file located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Enter only one row with RowID of 1. Enter a Y in each column that represents the combination to be evaluated as a Burden Cost. The columns are:
Row ID
Source Type
Category
Subcategory
The following example shows how to use combinations of Source Type and Category:
1,Y,Y,
Save and close the file.
To configure the file_Project_Cost_Burden_TypeCatSub_psft.csv
Using a text editor, open the file_Project_Cost_Burden_TypeCatSub_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Enter a list of Resource Type, Resource Category, and Resource Subcategory combinations to be considered as Burden costs. The format is:
XXXXX,XXXXX,XXXXX,1
XXXXX is a combination of Resource Type, Resource Category, and Resource Subcategory
The 1 is a return value that indicates that this is a Project Budget Burden Cost. Each combination of lookup values must be specified. Wildcards are not supported.
The following is an example of classifying costs with G&A or FRNG Source Type as Project Budget Burden Costs:
G&A,,,1 FRNG,,,1
Note:
This CSV file is used in conjunction with the file_Project_Cost_Burden_TypeCatSub_config_psft.csv configuration file. In this example, this configuration file would contain the value 1,Y.Given the example configuration above, Project Costing records with the following Source Type, Category, Subcategory combinations are considered Project Budget Burden Costs:
| Source Type | Category | Subcategory |
|---|---|---|
| G&A | ||
| FRNG | LUX | TEMP |
| FRNG | BONUS |
Note:
You must specify each combination of lookup values. The lookup will use columns with a Y in the configuration file.Save and close the file.
This section describes how to configure Project Budget Analytic.s
In DAC, display the Design View, and then display the Source System Parameters tab.
For information about how to log into DAC, see Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
Set the following parameters:
| Parameter Name | Description |
|---|---|
| $$BURDEN_ANALYSIS_TYPE | Use this parameter to specify Analysis Types as Burden Cost for the lookup. Valid values are:
|
| $$BURDEN_TYPECATSUB | Use this parameter to specify a combination of Source Type, Category, and Subcategory values as Burden Cost for the lookup. Valid values are:
|
Note:
The $$BURDEN_ANALYSIS_TYPE and $$BURDEN_TYPECATSUB parameters are used for both Project Budget and Project Cost analytics. Changing these parameters for Project Budget affects Project Cost.Actual Costs are extracted from Project Costing for all Analysis Types within the project's Actual Cost Analysis Group.
All costs extracted will be loaded into the Cost Fact Line table as Raw Cost unless you perform one or both of the following configurations:
All extracted Costs are loaded into the Cost Fact Line table without a Resource Class assigned unless you perform one or both of the following configurations:
In addition, you must perform the steps in this section:
The ETL process uses the file_Project_Cost_Burden_Analysis_Type_psft.csv flat file to list all Analysis Types for Project Cost Burden Cost. If the ETL process finds the Analysis Type in this flat file, it will not perform further lookups against other lookup tables to determine Project Cost Burden Cost.
To identify the Project Cost Burden Costs based on Analysis Type
Using a text editor, open the file_Project_Cost_Burden_Analysis_Type_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Enter a list of Analysis Types to be considered as Burden Costs. The format is XXX,1, where XXX is an Analysis Type. The 1 is a return value that indicates that this is a Burden Cost.
The following example shows how to classify Costs with BUR and BRD Analysis Types as Burden Costs:
BUR,1 BRD,1
Save and close the file.
You must configure the following flat files to identify Project Cost Burden Costs based on a Source Type, Category, and Subcategory combination of values:
file_Project_Cost_Burden_TypeCatSub_config_psft.csv
Use this flat file to specify the columns (Source Type, Category, and Subcategory) to use in the lookup. A DAC parameter determines if this lookup should be performed for an implementation.
file_Project_Cost_Burden_TypeCatSub_psft.csv
Use this flat file to list all Source Type, Category, and Subcategory combination of values to use as Project Cost Burden Cost.
Note:
Both Project Budget and Project Cost use these flat files, along with a DAC parameter, to identify Burden Costs. You can customize these files if the requirements differ between Project Budget and Project Cost in your implementation.For information about how to configure Project Budget, see Section 15.2.3.10, "How to Configure Project Budget."
To configure the file_Project_Cost_Burden_TypeCatSub_config_psft.csv
Using a text editor, open the file file_Project_Cost_Burden_TypeCatSub_config_psft.csv file located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Enter only one row with RowID of 1. Enter a Y in each column that represents the combination to be evaluated as a Project Cost Burden Cost. The columns are:
Row ID
Source Type
Category
Subcategory
The following is an example of using a combination of Source Type and Category:
1,Y,Y
Save and close the file.
To configure the file_Project_Cost_Burden_TypeCatSub_psft.csv
Using a text editor, open the file_Project_Cost_Burden_TypeCatSub_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Enter a list of Resource Type, Resource Category, and Resource Subcategory combinations to be considered as Project Cost Burden Costs. The format is:
XXXXX,XXXXX,XXXXX,1
XXXXX represents each combination of Resource Type, Resource Category, and Resource Subcategory.
The 1 is a return value that indicates that this is a Burden Cost. Each combination of lookup values must be specified. Wildcards are not supported.
The following example shows how to classify Costs with G&A or FRNG Source Type as Project Cost Burden Costs:
G&A,,,1
FRNG,,,1
Note:
This CSV file is used in conjunction with the file_Project_Cost_Burden_TypeCatSub_config_psft.csv configuration file. In this example, this configuration file would contain the value 1,Y.In the example above, Project Costing records with the following Source Type, Category, and Subcategory combinations would be considered Project Cost Burden Costs:
| Source Type | Category | Subcategory |
|---|---|---|
| G&A | ||
| FRNG | LUX | TEMP |
| FRNG | BONUS |
You must specify each combination of lookup values. The lookup will use columns with a Y in the configuration file.
Save and close the file.
The ETL process uses the domainValues_Project_Cost_Resource_Class_TypeCatSub_psft.csv flat file to assign Resource Class to project cost records. If the ETL process finds the Resource Class in this flat file, it will not perform further lookups against other lookup tables to determine Project Cost Resource Class.
You must configure the following flat files to identify Resource Class based on a Source Type, Category, and Subcategory Combination of Values:
file_Project_Cost_Resource_Class_TypeCatSub_config_psft.csv
Use this file to specify the columns (Source Type, Category, and Subcategory) to use in the lookup. A DAC parameter determines if this lookup should be performed for an implementation.
domainValues_Project_Cost_Resource_Class_TypeCatSub_psft.csv
The ETL process uses this flat file to list all Source Type, Category, Subcategory combinations of values to use for Resource Class. Enter values for only the columns that are selected in the file_Project_Cost_Resource_Class_TypeCatSub_config_psft.csv file. All columns must be included in the flat file and unselected columns must not contain a value. You must identify each row as either People (L) or Equipment (A) as the last value.
To configure the file_Project_Cost_Resource_Class_TypeCatSub_config_psft.csv
Using a text editor, open the file file_Project_Cost_Resource_Class_TypeCatSub_config_psft.csv file located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Enter only one row with RowID of 1. Enter a Y in each column that represents the combination to be assigned a Resource Class. The columns are:
Row ID
Source Type
Category
Subcategory
The following is an example of using a combination of Source Type and Category:
1,Y,Y,
In this example, Source Type and Category combinations stored in domainValues_Project_Cost_Resource_Class_TypeCatSub_psft.csv are classified as People or Equipment when the values match.
Save and close the file.
To configure the domainValues_Project_Cost_Resource_Class_TypeCatSub_psft.csv
Using a text editor, open the domainValues_Project_Cost_Resource_Class_TypeCatSub_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Enter Resource Type, Category, and Subcategory combinations that are to be considered as Resource Class People or Equipment. For Resource Class of People, enter an L as the last value. For Resource Class of Equipment, enter an A as the last value. The format is:
XXXXX,XXXXX,XXXXX,X
You must specify each combination of lookup values. Wildcards are not supported.
The following is an example of classifying costs with LABOR or SUBCN Source Type/no Category as People costs and costs with DIRCT Source Type/HRDWR Category as Equipment costs:
LABOR,,,L
SUBCN,,,L
DIRCT,HRDWR,,A
Note:
This CSV file is used in conjunction with the file_Project_Cost_Resource_Class_TypeCatSub_config_psft.csv configuration file. In this example, this configuration file would contain the value 1,Y,Y,In the example above, Project Costing records with the following Source Type, Category, and Subcategory combinations are classified as Resource Class:
| Source Type | Category | Subcategory | Resource Class |
|---|---|---|---|
| LABOR | People | ||
| LABOR | TEMP | ||
| SUBCN | People | ||
| SUBCN | ANAL | ||
| DIRCT | |||
| DIRCT | HRDWR | Equipment |
Note:
You must specify each combination of lookup values. The lookup will use columns with a Y in the configuration file.If the Resource Class is found in the Resource Class Type flat file, further lookups against other lookup tables for Resource Class will not be made.
Save and close the file.
The ETL process uses the domainValues_Project_Cost_Resource_Class_ChartField_psft.csv flat file to assign Resource Class to Project Cost records.
You must configure the following flat files to assign Resource Class based on a Chartfield combination of values:
file_Project_Cost_Resource_Class_ChartField_config_psft.csv
Use this flat file to specify the Chartfield columns to use in the lookup. A DAC parameter determines if this lookup is performed in an implementation.
domainValues_Project_Cost_Resource_Class_ChartField_psft.csv
Use this flat file to assign all ChartField combinations of values to a Resource Class. Enter values for only the columns that are selected in the file_Project_Cost_Resource_Class_ChartField_config_psft.csv file. All columns must be included in the flat file and unselected columns must not contain a value. You must identify each row as either People (L) or Equipment (A) as the last value.
To configure the file_Project_Cost_Resource_Class_ChartField_config_psft.csv
Using a text editor, open the file_Project_Cost_Resource_Class_ChartField_config_psft.csv file located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Enter only one row with RowID of 1. Enter a Y in each column that represents the combination to be assigned a Resource Class. The columns are:
Row ID
Account
Alternate Account
Operating Unit
Fund
Dept ID
Program
Class
Budget
Project
Business Unit
Project
Activity
Source Type
Category
Subcategory
Affiliate
Affiliate 1
Affiliate 2
ChartField 1
ChartField 2
ChartField 3
The following is an example of using a combination of Fund Code and Program:
,,,Y,,Y,,,,,,,,,,,,,,,
In this example, Fund Code and Program Code combinations stored in the domainValues_Project_Cost_Resource_Class_ChartField_psft.csv are classified as People or Equipment when the values match.
Save and close the file.
To configure the domainValues_Project_Cost_Resource_Class_ChartField_psft.csv
Using a text editor, open the domainValues_Project_Cost_Resource_Class_ChartField_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Enter ChartField combinations that are to be considered as Resource Class People or Equipment. For Resource Class of People, enter an L as the last value. For Resource Class of Equipment, enter an A as the last value. The format is:
X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X
X represents a Chartfield combination.
Each combination of lookup values must be specified. Wildcards are not supported.
The following example shows how to classify costs with Fund Code FND01 and Program Code P2008 as People costs:
,,,FND01,, P2008,,,,,,,,,,,,,,,,L
Note:
This CSV file is used in conjunction with the file_Project_Cost_Resource_Class_ChartField_config_psft.csv configuration file. In this example, this configuration file would contain the value ,,,Y,,Y,,,,,,,,,,,,,,,In the above example, Project Costing records with the Fund Code FND01 and Program Code P2008 are classified as Resource Class People.
You must specify each combination of lookup values. Columns with a Y in the configuration file will be considered in the lookup.
Save and close the file.
This section describes how to configure Project Cost Analytics.
In DAC, select the Design view, and display the Source System Parameters tab.
For information about how to log into DAC, see Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
Set the following parameters:
| Parameter Name | Description |
|---|---|
| $$BURDEN_ANALYSIS_TYPE | Use this parameter to specify Analysis Types as Burden Cost for the lookup. Valid values are:
|
| $$BURDEN_TYPECATSUB | Use this parameter to specify a combination of Source Type, Category, and Subcategory values as Burden Cost for the lookup. Valid values are:
|
| $$RESOURCE_CLASS_TYPECATSUB | Use this parameter to specify a combination of Source Type, Category, and Subcategory values to determine Resource Class for the lookup. Valid values are:
|
| $$RESOURCE_CLASS_CHARTFIELD | Use this parameter to specify a ChartField combination of values to determine Resource Class for the lookup. Valid values are:
|
Note:
The $$BURDEN_ANALYSIS_TYPE and $$BURDEN_TYPECATSUB parameters are used for both Project Cost and Project Budget analytics. Changing these parameters for Project Cost affects Project Budget.In the repository file (RPD), logical table sources are set by default to settings in Oracle Project Analytics. Before using the Project Analytics applications, you must update the logical source for one table in the repository file. This logical source must be deactivated in order for Oracle Project Analytics reports to point to the correct logical model and retrieve the correct data. Do this by deactivating the Dim_W_MCAL_PERIOD_D_Fiscal_Period source for the Dim-Date Fiscal Calendar logical table in the Core subject, as shown in the following procedure.
To update Project Analytics logical table sources
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
Go to the Business Model and Mapping dialog (the logical layer dialog) and open the Core folder.
Scroll down to the Dim - Date Fiscal Calendar logical table and open its Sources folder.
In the list of logical table sources, right-click Dim_W_MCAL_PERIOD_D_Fiscal_Period.
Select Properties.
Display the General tab in the Properties dialog and make sure that the Active check box is not selected.
Click OK and save the repository.
Restart Oracle BI Server.
For more information about administering logical tables, see Oracle Business Intelligence Server Administration Guide.
All fact tables in Project Analytics are secured by Operating Unit (or Business Unit) security. This security filter uses the OU_ORG session variable, which is initialized by the list of Operating Units the user has access to in the OLTP system. An additional join is added to each fact report, so only transaction data for Projects that are owned by the Project Operating Units that the user is allowed to access are shown in the report. Dimension reports are not secured by filters.
For more information about how to add security to dimensions and implementation steps for operating unit based security with PeopleSoft, see Oracle Business Intelligence Applications Security Guide.
This section contains the following topics:
Section 15.2.4.1, "What is Universal Adapter for Project Analytics?"
Section 15.2.4.3, "Sample Source Files for Universal Adapter for Project Analytics"
Section 15.2.4.4, "How to Create Data Files for Universal Adapter for Project Analytics"
Universal Adapter for Project Analytics enables you to extract data from Project transactional applications for analysis, when pre-packaged business adapters for these sources are not available. Unlike the pre-packaged business adapters that can only be used for particular source systems, Universal Adapter for Project Analytics can be used for any source system as long as the source data can be presented in the specified flat file format.
Project source data could also reside in custom applications or legacy systems.
Universal Adapter for Project Analytics is shipped as part of any of the Oracle Project Analytics application.
To use Universal Adapter for Project Analytics, present source data in flat files according to the following specifications:
Data should be in comma delimited format (*.csv files).
All date values should be in the format of YYYYMMDDHH24MISS. For example, 20071231140300 should be used for December 31, 2007, 2:03 pm.
If a string data value contains one or more commas, then it should be double quoted.
Data should start from the 6th line of each file. The first five lines of each file are skipped during the ETL process.
Oracle Business Intelligence Applications provides a sample source file for each supported source file type. Typically, each sample source file contains sample data rows. Sample source files are located in <OracleBI>\dwrep\Informatica\Sample Universal Source Files.
The sample source files for Universal Adapter for Project Analytics are:
FILE_BUDGET.csv
FILE_EVENT_DS.csv
FILE_FINANCIAL_RESOURCE_DS.csv
FILE_MFG_RESOURCE_DS.csv
FILE_PROJ_BUDGET_FS.csv
FILE_PROJ_CLASSIFICATION_DS.csv
FILE_PROJ_CONTRACT_DS.csv
FILE_PROJ_COST_LINE_FS.csv
FILE_PROJ_EXP_LINE_FS.csv
FILE_PROJ_FORECAST_FS.csv
FILE_PROJ_FUNDING_HDR_FS.csv
FILE_PROJ_FUNDING_LINE_FS.csv
FILE_PROJ_INVOICE_LINE_FS.csv
FILE_PROJ_RETENTION_FS.csv
FILE_PROJ_REVENUE_LINE_FS.csv
FILE_PROJECT_DS.csv
FILE_TASK_DS.csv
FILE_WORK_TYPE_DS.csv
To create data files, Oracle recommends that you use the sample source files that are provided. For a list of sample source files, see Section 15.2.4.3, "Sample Source Files for Universal Adapter for Project Analytics."
To create data files for Universal Adapter for Project Analytics:
Copy the sample source files (file_xxx.csv) from the <OracleBI>\dwrep\Informatica\Sample Universal Source Files folder to the $pmserver\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Delete the sample data rows from every sample source file.
You must first have all of the empty files in the <Informatica PowerCenter>\server\infa_shared\SrcFiles directory for the ETL Execution Plans to run without interruptions. Some ETL Execution Plans might contain tasks that you do not need, but would fail and cause interruption in your ETL run if source files expected by the tasks cannot be found in the <Informatica PowerCenter>\server\infa_shared\SrcFiles directory.
Populate the files that you need in the <Informatica PowerCenter>\server\infa_shared\SrcFiles directory, making sure that the data starts at line six.
For example, to load budget data, you might populate the file_budget.csv file.
In DAC, create an Execution Plan to load the data from these files.
This section contains additional configuration steps for Oracle Project Analytics. It contains the following topics:
Section 15.2.5.1, "Configuration Steps for Oracle Project Analytics for All Source Systems"
Section 15.2.5.2, "Configuration Steps for Oracle Project Analytics for Oracle EBS"
Section 15.2.5.3, "Configuration Steps for Oracle Project Analytics for PeopleSoft"
Section 15.2.5.4, "Configuration Steps for Oracle Project Analytics for Universal"
This section contains the following topics
Section 15.2.5.1.1, "Configuring the Project Cost Aggregate Table"
Section 15.2.5.1.2, "About Configuring Multi Calendar Support in Oracle Project Analytics"
Section 15.2.5.1.3, "Configuring Multi Currency Support in Oracle Project Analytics"
The Project Cost aggregate table (W_PROJ_COST_A) is used to capture information about the project cost distributions for the expenditure items. You need to configure the Project Cost Lines aggregate table before the initial ETL run and subsequent incremental ETL.
Before the initial ETL run, you need to configure the COST_TIME_GRAIN parameter in DAC (refer to the section above for more information) for the time aggregation level in the Project Cost Lines aggregate fact table.
Out of the box, the RPD joins are configured to use Quarter for the aggregation level. You need to modify the joins in the RPD file if you want to use a level of aggregation other than QUARTER. Out of the box, the Cost aggregate table includes a physical join to the W_MCAL_FISCAL_QTR_D. You must configure this join to the correct Fiscal Time table if the grain is modified to either Period or Year.
By default, the COST_TIME_GRAIN parameter has a value of QUARTER. The possible values for the COST_TIME_GRAIN parameter are:
PERIOD
QUARTER
YEAR
The Project Cost Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. Therefore, the Project Cost aggregate table is not fully reloaded from the base table after each incremental ETL run. The Oracle Business Analytics Warehouse minimizes the incremental aggregation effort by modifying the aggregate table incrementally as the base table is updated. The process is as follows:
Oracle Business Analytics Warehouse finds the records to be updated in the base table since the last ETL run, and loads them into the W_PROJ_COST_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is SIL_ProjectCostLinesFact_Derive_PreLoadImage.
Oracle Business Analytics Warehouse finds the inserted or updated records in the base table since the last ETL run, and loads them into the W_PROJ_COST_LINE_TMP table, without changing their sign. The mapping responsible for this task is SIL_ProjectCostLinesFact_Derive_PreLoadImage, which is run before PLP_ProjectCostLinesFact_Derive_PostLoadImage updates or inserts records in the base table.
Oracle Business Analytics Warehouse aggregates the W_PROJ_COST_LINE_TMP table and load to W_PROJ_COST_A_TMP, which has the same granularity as the W_PROJ_COST_A table.
The PLP_ProjectCostLinesAggregate_Derive mapping looks up the W_PROJ_COST_A aggregate table to update existing buckets or insert new buckets in the aggregate table (the mapping is PLP_ProjectCostLinesAggregate_Load).
Oracle Project Analytics supports both the GL Calendar and Project Calendar. Most facts have two separate keys: one that points to the GL Calendar Date and another that points to the Project Calendar Date. In Oracle EBS tables, the facts are typically sourced from GL_DATE and PA_DATE respectively.
Note:
In PeopleSoft, GL Calendar and Project Calendar are the same.The Canonical Date is the GL Date for all facts. Because GL_ACCOUNTING_DT_WID is pointing to the multi calendar dimension (W_MCAL_DAY_D) table, and reports cannot combine dates from different calendars, all dashboards have to filter on the Project OU to have correct data in the Ledger Calendar.
The same thing applies to analysis by Project Dates because PROJ_ACCOUNTING_DT_WID points to the same table (W_MCAL_DAY_D). However, it uses the Project Calendar instead of the GL Calendar.
Users can use the Enterprise Calendar to view data across Operating Units in the same screen. Enterprise dates point to W_DAY_D, which is the same across all Operating Units in the data warehouse.
If a user wants to build a report showing all data for an Expenditure Operating Unit, this report must use the Enterprise Date fields instead of the GL or Project Date fields. This is because transactions owned by one Expenditure OU can reference multiple projects (in case of Cross Charge), and thus multiple Project Calendars. To view data across Operating Units in the same screen, users can use the Enterprise Calendar.
For information about how to configure fiscal calendars, see Chapter 3, "Configuring Common Areas and Dimensions".
All metrics are supported in multiple currencies in the dashboard. The supported currency types include:
Transaction (Document) Currency
Project Functional (Local) Currency
Project Currency
Three global currencies
Note:
By default, Oracle BI Answers displays all metrics in local currency. Some metrics such as Unearned Revenue, Unbilled Receivables, Realized Gains, and Realized Losses are only available in local currencyOut of the box, every Project dashboard has a prompt called Currency Type. The values in this prompt are Local Currency, Project Currency, Global1 Currency, Global2 Currency, and Global3 Currency. When the user chooses one of these values and clicks the Go button for that prompt, all monetary values in the page are converted to that particular currency, and the Currency Code field in the report shows the currency code corresponding to the selected currency type.
Note:
Some metrics, such as Unearned Revenue, are not available in the Project Currency.The user can add the same functionality in custom reports by adding a similar prompt. For more information on extending this feature, see support documentation about dynamic reporting on multiple currencies on the My Oracle Support Web site.
To configure multi currency support in Oracle Project Analytics
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
Click the Manage menu, Variables, Repository, and then Static.
Change the values of the GLOBAL1_CURR_CODE, GLOBAL2_CURR_CODE, and GLOBAL3_CURR_CODE variables to the global currencies configured in DAC.
Out of the box, the currencies are set to USD. For information about how to configure global currencies, see Chapter 3, "Configuring Common Areas and Dimensions".
This section contains the following topics:
Section 15.2.5.2.1, "About Configuring Budget Fact for Oracle EBS"
Section 15.2.5.2.2, "About Configuring Forecast Fact for Oracle EBS"
Section 15.2.5.2.3, "About Configuring Funding Fact for Oracle EBS"
Section 15.2.5.2.4, "About Configuring Cost Fact for Oracle EBS"
Section 15.2.5.2.5, "Configuring Revenue Fact for Oracle EBS"
Section 15.2.5.2.6, "Configuring Invoice Fact for Oracle EBS"
Section 15.2.5.2.7, "How to Tune Performance for Project Analytics for Oracle EBS"
Baselined Budgets are extracted into the Budget Fact (W_PROJ_BUDGET_F) table, the grain of which is Budget Line. Because only the baselined budgets are extracted, the records in this table are not updated after they are loaded into the data warehouse; only new records are inserted during the incremental ETL run. Budgets are stored in the Budget dimension (W_BUDGET_D).
Note:
For Oracle EBS, Transaction Currency is the Document Currency for this fact.Defining Filters on Budget Metrics
A user can create multiple budgets for a single Project and multiple versions for the same budget type. Therefore, all exposed metrics are filtered by the following filters:
Approved Budget Type. A project can have only one Cost Budget with a budget type as "Approved Cost Budget" and one Revenue Budget with a budget type as "Approved Revenue Budget." Therefore, all Cost Budget metrics are filtered by the Approved Cost Budget and Approved Revenue Budget flags to make sure that the metrics include data from one budget only.
Current or Original Budget. Each Project forecast can have multiple versions. The Current Version may not be the same as the Original version. Therefore, to show only one Forecast version at a time, there are separate metrics for Current version and Original version. These flags are set automatically in OLTP when the forecast is baselined, but users can update them manually.
The user can still see the metrics for any other budget type or version by bringing the non-filtered metrics from Fact - Project Budget fact table into the Presentation area. But to avoid duplicate data, the report must have a filter on "Dim - Budget.Budget Type" and "Dim - Budget.Budget Version".
Before running the ETL for the first time, go to the Financial Plan Type page in the HTML application and set your Approved Cost Budget Type and your Approved Revenue Budget Types. Also, you must select the Approved Cost Budget and Approved Revenue Budget types.
Budgets Created in Forms Client
For budgets entered through the Form client, the PA_BUDGET_ TYPES.PLAN_TYPE column is not populated for the two predefined budget types, AC and AR. Therefore, the following ETL logic is incorporated in mplt_BC_ORA_BudgetVersion_BudgetType in the SDE_ORA_Budget Dimension_BudgetType mapping:
IIF(ISNULL(INP_PLAN_TYPE),DECODE(NAME,'AC','BUDGET','AR','BUDGET','FC','FORECAST','FR', 'FORECAST',INP_PLAN_TYPE) , INP_PLAN_TYPE)
Budget Fact Canonical Date
The Budget Fact contains the following two sets of Accounting Date and Period WIDs:
PROJ_ACCT_START_DT_WID, PROJ_ACCT_END_DT_WID, and PROJ_PERIOD_WID
PROJ_ACCT_START_DT_WID and PROJ_ACCT_END_DT_WID are populated using START_DATE and END_DATE of budget line only for budgets that are time-phased using the Project Accounting (PA) Calendar.
GL_ACCT_START_DT_WID, GL_ACCT_END_DT_WID, and GL_PERIOD_WID
The GL_ACCT_START_DT_WID and GL_ACCT_END_DT_WID are populated using the START_DATE and END_DATE of budget line for budgets that are time-phased by the General Ledger (GL) Calendar.
For budgets defined with Time Phase equal 'P'(PA), 'N'(No Time Phase) or 'R'(Date Range), the GL_ACCT_START_DT_WID and GL_PERIOD_WID are resolved using the START_DATE of the budget line by choosing the period containing that date in the GL Calendar (pinned by the GL_MCAL_CAL_WID).
This approach assumes that for time phase 'P','N', and 'R', there is a period containing the START_DATE for the given GL Calendar in the OLTP database.
For Forms -based budgets, even though the application does not allow creating budget lines in a different currency than the Project Functional currency, the currency from Project Functional Currency is used for the default value of the Document Currency field. This enables Budget Amounts to be analyzed in the Global Currencies. For example, Doc Raw Cost Amount is populated as:
IIF(ISNULL(INP_DOC_RAW_COST),IIF((TXN_CURRENCY_CODE = PROJFUNC_CURRENCY_CODE),INP_LOC_RAW_COST,NULL),INP_DOC_RAW_COST)
The Forecast fact table is based on PA_BUDGET_LINES. A filter is applied to the Budget Version table to extract only baselined Forecasts for the Forecast fact. The grain of this table is a Forecast line. The ETL extracts only baselined forecasts, so the records in this table are not updated after they are loaded to the data warehouse; only new records are inserted during an incremental run. Forecasts are stored in the Budget dimension (W_BUDGET_D) as well.
Note:
For Oracle EBS, Transaction Currency is the Document Currency for this fact.Defining Filters on Forecast Metrics
Users can create multiple forecasts for a single Project and multiple versions for the same forecast type. Therefore, Oracle BI Applications filter all exposed metrics using the following filters:
Primary Forecast Type. One project can have only one Cost Forecast with a forecast type of "Primary Cost Forecast" and one Revenue Forecast with a Forecast type of "Primary Revenue Forecast." Therefore, all Cost and Revenue Forecast metrics are filtered on two flags, Primary Cost Forecast and Primary Revenue Forecast, to make sure we are showing data for only one forecast.
Current or Original Forecast. One Project forecast can have multiple versions. To show only one forecast version at a time, every metric for the Current Version and the Current Original Version is shown. These flags are set automatically in OLTP when the forecast is baselined, but users can update them manually.
Users can still view metrics for any other forecast type or version by bringing the non-filtered metrics from the Fact - Project Forecast fact table into the Presentation area. But to avoid duplicate data, the report must have a filter on Dim - Budget.Budget Type and Dim - Budget.Budget Version.
Before running the ETL for the first time, access the Financial Plan Type page in the HTML client, and select your Primary Cost forecast and Primary Revenue forecast types.
Forecasts Created in Forms Client
For Forecasts entered through the Form client, the PA_BUDGET_ TYPES.PLAN_TYPE column is not populated for the two predefined budget types, 'FC' and 'FR'. Therefore, the following ETL logic is incorporated in mplt_BC_ORA_BudgetVersion_BudgetType in the SDE_ORA_Budget Dimension_BudgetType mapping:
IIF(ISNULL(INP_PLAN_TYPE),DECODE(NAME,'AC','BUDGET','AR','BUDGET','FC','FORECAST','FR', 'FORECAST',INP_PLAN_TYPE) , INP_PLAN_TYPE)
For 'FC' and 'FR' types of Forecast versions created in the Forms client, the PRIMARY_COST_FORECAST _FLAG and PRIMARY_REV_FORECAST_FLAG are not populated in PA_BUDGET_VERSIONS. Therefore, the following ETL logic is incorporated in mplt_BC_ORA_BudgetVersion_BudgetType in the SDE_ORA_Budget Dimension_BudgetType mapping:
IIF(ISNULL(INP_PRIMARY_COST_FORECAST_FLAG),IIF(NAME='FC','Y'),INP_PRIMARY_COST_FORECAST_FLAG) IIF(ISNULL(INP_PRIMARY_REV_FORECAST_FLAG),IIF(NAME='FR','Y'),INP_PRIMARY_REV_FORECAST_FLAG)
For Forms based forecasts, even though the application does not allow the creation of forecast lines in a different currency than the Project Functional currency, we are defaulting the Project Functional Currency in the Document Currency field, so that the Forecast Amounts can also be analyzed in the Global Currencies. For example Doc EAC Raw Cost Amount is populated as:
IIF(ISNULL(INP_DOC_EAC_RAW_COST),IIF((DOC_CURRENCY_CODE = LOC_CURRENCY_CODE),INP_LOC_EAC_RAW_COST,NULL),INP_DOC_EAC_RAW_COST)
Forecast Fact Canonical Date
The Forecast fact has the following two sets of Accounting Date and Period WIDs:
PROJ_ACCT_START_DT_WID, PROJ_ACCT_END_DT_WID & PROJ_PERIOD_WID
PROJ_ACCT_START_DT_WID and PROJ_ACCT_END_DT_WID are populated using START_DATE and END_DATE of forecast line only for Forecasts that are time phased using the Project Accounting (PA) Calendar.
GL_ACCT_START_DT_WID, GL_ACCT_END_DT_WID & GL_PERIOD_WID
The GL_ACCT_START_DT_WID and GL_ACCT_END_DT_WID are populated using START_DATE and END_DATE of forecast line for Forecasts time phased by the General Ledger (GL) Calendar.
For Forecasts with a Time Phase equal to 'P' (PA), 'N' (No Time Phase), or 'R' (Date Range), the GL_ACCT_START_DT_WID and GL_PERIOD_WID are resolved using the START_DATE of the forecast line by choosing the Period containing that date in the corresponding GL Calendar.
This approach assumes that for time phase equal 'P', 'N' or 'R', there will always be a period containing the START_DATE for the given GL Calendar in OLTP database.
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 Oracle EBS.
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 ETL job, you need to run the following process in Oracle EBS to update this table: PRC: Refresh Project Summary Amounts.
Note:
For Oracle EBS, Funding Currency is the Document Currency for this fact.You need to use the following CSV files to configure the Project Funding domain values:
domainValues_Project_Funding_Category_ora<ver>.csv. Used for categorizing funding allocation types. If you change the domain values, you must also change corresponding metric definitions in the RPD. For more information about how to configure this file, see Section 15.2.2.9, "How to Configure the domainValues_Project_Funding_Category_ora<ver>.csv."
domainValues_Project_Funding_Level_code_ora<ver>.csv. This flat file is used to indicate whether a funding line is for a Task or a Project. It is not used in any out of the box metric definition. For information about how to configure this file, see Section 15.2.2.8, "How to Configure the domainValues_Project_Funding_Level_code_ora<ver>.csv."
Funding Fact Canonical Date
GL Date is not populated in the OLTP application. So in the data warehouse, the GL Date for Oracle EBS 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.
Note:
The GL date (Funding Allocation Date) is the canonical date for this table and is also used for global exchange rate calculation.Actual Costs are extracted from the Cost Distribution Lines table in the Project Costing module in Oracle EBS and loaded into the Cost Line Fact (W_PROJ_COST_LINE_F) table.
For Oracle EBS, Transaction Currency is the Document Currency for this fact.
Note:
Oracle EBS concurrent programs (such as PRC: Distribute Labor Costs and PRC: Distribute Usage and Miscellaneous Costs) for distributing Cost should be run before running the ETL to load the data warehouse. If the Cost Distribution program is not run before every incremental ETL run, the data in Cost Distribution Fact will not be synchronized with the actual expenditures in the Expenditure Fact table.Expenditure Fact
The Expenditure Fact (W_PROJ_EXP_LINE_F) is based on PA_EXPENDITURE_ITEMS_ALL. It shows the actual expenditure data before distribution. This fact should be used by customers who do not distribute their Expenditure on a daily basis, but who have some users who need to see a frequently updated view of Expenditure data.
Note:
The GL Date is assigned to the Cost Distribution Line only (during Cost distribution) and not to the Expenditure Item records. Therefore, the Expenditure data can only be analyzed by the Enterprise Calendar dimension and not by the GL calendar. Also, the Expenditure data cannot be analyzed by the GL Account because the GL account is associated only when the data is distributed.Cost Fact Canonical Date
The Canonical Date dimension for the Cost fact is based on the RCVR_GL_DATE from Distribution Line table, whereas the Canonical Date dimension for the Expenditure fact is based on the EXPENDITURE_DATE from the Expenditure Items table.
The multi calendar date dimension contains calendars for multiple organizations. It is essential that all records in a report analyzing data by the Fiscal Calendar (Dim - Fiscal Calendar) point to the same calendar. For this reason, all reports in the dashboard are filtered on the Project Business Unit. To make all Cost records in a Project Business Unit point to the same calendar, the RCVR_GL_DATE and RCVR_PA_DATE columns are used to populate the GL_ACCOUNTING_DT_WID and PROJ_ACCOUNTING_DT_WID columns in the fact table respectively. Expenditure OU view (in Cost Fact) can be built using Enterprise Calendar as well.
About Domain Values for Cost Fact
The Project Cost Transfer Status has been modeled as a domain value and can be configured using the domainValues_Project _Cost_Transfer_Status_ora11i.csv. For information, see Section 15.2.2.10, "How to Configure the domainValues_Project_Cost_Transfer_Status_ora<ver>.csv."
Incremental Logic for Cost Fact
The incremental extract logic for the Cost fact table depends on the 'REQUEST_ID' field of the Cost Distribution Lines table. The W_PROJ_ETL_PS parameter table facilitates this logic. Using a separate Informatica process, the maximum Request Id in the source table at the time of the ETL run is stored in this table, which is subsequently used to populate the SDE task (SDE_ORA_ProjectCostLine) level DAC variable $$EBS_REQUEST_ID_1. It is initialized using the following query:
SELECT COALESCE(PRE_REQUEST_ID,0) FROM W_PROJ_ETL_PS WHERE TBL_NAME = 'PA_COST_DISTRIBUTION_LINES_ALL'
Cost Aggregate Table
The Cost aggregate table facilitates high performance analysis. For more information about configuring this table, see Section 15.2.5.1.1, "Configuring the Project Cost Aggregate Table."
Actual Revenue Line records are extracted from the Revenue/Event Distribution Lines tables (PA_CUST_REV_DISTRIB_LINES_ALL and PA_CUST_EVENT_DIST_ALL) in the Project Costing module in Oracle EBS and are loaded into the Revenue Line Fact (W_PROJ_REVENUE_LINE_F) table.
For Oracle EBS, Revenue Transaction Currency Code is the Document Currency Code for this fact.
Note:
Oracle EBS concurrent programs (such as PRC: Generate Draft Revenue for a Single Project or PRC: Generate Draft Revenue for a Range of Projects) for distributing revenue should be run before the ETL is run to load the data warehouse.For the Revenue Header Fact (W_PROJ_REVENUE_HDR_F), the primary source is the PA_DRAFT_REVENUES table. Revenue line metrics, such as Bill and Revenue amounts, are aggregated in this table as well.
Revenue Fact Canonical Date
The Canonical Date dimension is based on the GL_DATE from the Draft Revenues table.
Revenue Facts Staging Table
This is a common staging table that loads both the header and the line level revenue fact tables.
Revenue Fact Multicurrency Support
Some metrics such as Unearned Revenue, Unbilled Receivables, Realized Gains, and Realized Losses are only available in Local Currency and Global Currencies. There are three columns in w_proj-revenue_line_f and w_proj-revenue_hdr_f respectively for revenue amounts in global currencies configured in DAC.
Revenue Fact Domain Values
The project revenue status has been modeled as a domain value and can be configured using the domainValues_Project _ InvoiceRevenue _Status_ora11i.csv. For information about how to configure this file, see Section 15.2.2.6, "How to Configure the domainValues_Project_InvoiceRevenue_Status_ora<ver>.csv."
Incremental Logic for Revenue Fact
The incremental extract logic for the Revenue fact table depends on the REQUEST_ID field of the Revenue Distribution Lines table. The W_PROJ_ETL_PS parameter facilitates this logic, and through a separate Informatica process, the maximum Request Id in the source table at the time of the ETL run is stored in this table, which is subsequently used to populate the following variables for the SDE_ORA_ProjectRevenueLine task in DAC:
$$EBS_REQUEST_ID_2
$$EBS_REQUEST_ID_3
$$EBS_REQUEST_ID_4
They are initialized using the following queries:
SELECT COALESCE(PRE_REQUEST_ID,0) FROM W_PROJ_ETL_PS WHERE TBL_NAME ='PA_CUST_EVENT_RDL_ALL'
SELECT COALESCE(PRE_REQUEST_ID,0) FROM W_PROJ_ETL_PS WHERE TBL_NAME ='PA_CUST_REV_DIST_LINES_ALL'
SELECT COALESCE(PRE_REQUEST_ID,0) FROM W_PROJ_ETL_PS WHERE TBL_NAME ='PA_DRAFT_REVENUES_ALL'
Line level invoice information is extracted from the Invoice Line table (PA_DRAFT_INVOICE_ITEMS) in the Billing Module of Oracle EBS and loaded into Invoice Line Fact (W_PROJ_INVOICE_LINE_F). All invoices at any stage of the invoice generation process, such as creation, approval, release, transfer, and so forth, are loaded into this table so that customers can see a full view of the invoice generation process. Some of the information available in the Invoice Header Table (PA_DRAFT_INVOICES_ALL) such as GL Date and PA Date; and flags such as Write-Off Flag, Concession Flag, Cancelled Flag, and Retention Invoice Flag in Oracle EBS, have also been denormalized into Invoice Line Fact.
For Oracle EBS, Invoice Currency is the Document Currency for this fact.
Note:
The Oracle EBS concurrent programs, such as PRC: Generate Draft Invoices for a Single Project or PRC: Generate Draft Invoices for a Range of Projects, for generating draft invoices, or PRC: Interface streamline Process, for transferring invoice to Receivables, should be run before the ETL is run to load the warehouse.Invoice Fact Canonical Date
The Canonical Date for this fact table is the GL Date, which is available in the PA_DRAFT_INVOICES_ALL table. This date is also used for global exchange rate calculation.
About Invoice Fact Domain Values
The invoice status has been modeled as a domain value and can be configured using the domainValues_Project_ InvoiceRevenue_ Status_ora11i.csv file. For information about how to configure this file, see Section 15.2.2.6, "How to Configure the domainValues_Project_InvoiceRevenue_Status_ora<ver>.csv."
The invoice line type has also been modeled as a domain value and can be configured using the domainValues_Project_ Invoice_Line_ Type_ora11i.csv file. For more information about how to configure this file, see Section 15.2.2.7, "How to Configure the domainValues_Project_Invoice_Line_Type_ora<ver>.csv."
Retention Fact
The Retention fact table (W_PROJ_RETENTION_F) holds aggregated information about Retention Billing Metrics. It is based on PA_SUMMARY _PROJECT_ RETN table in Oracle EBS. Before running any ETL job, you need to run the PRC: Refresh Project Summary Amounts process in Oracle EBS to update this table. Otherwise, the data in the Invoice Line fact table and aggregated Retention fact table will be inconsistent.
Out of the box, the Logical Table Source based on this table in the metadata repository (RPD) is deactivated. If you want to use this table, you need to activate it by opening the RPD using Oracle BI Administration Tool and then going to Fact_W_PROJ_RETENTION_F_Project_Retention LTS under Fact - Project Billing in Business Model Layer and selecting the active check box.
The corresponding ETL tasks have not been deactivated out of the box in DAC. Therefore, if you do not use this fact table, access the Oracle 11510 and Oracle R12 containers in DAC and deactivate the SDE_ORA_ProjectRetention task. Also, access the DataWarehouse container in DAC and deactivate the following tasks:
SIL_ProjectRetention_Fact
SIL_ProjectRetentionFact_IdentifyDelete
SIL_ProjectRetentionFact_SoftDelete
You can increase the ETL performance for Project Analytics for Oracle EBS by using hash joins in the Oracle database optimizer. In Informatica, configure the $$HINT1 parameter for each of the mappings listed in Table 15-4.
Note:
This configuration applies to Oracle databases only. Out of the box, this parameter is blank.Table 15-4 Parameters for Increasing Performance for Project Analytics for Oracle EBS
| Mapping | $$HINT1 Parameter |
|---|---|
|
SDE_ORA_ProjectCostLine_Fact |
|
|
SDE_ORA_ProjectInvoiceLine_Fact |
|
|
SDE_ORA_ProjectFundingHeader_Fact |
|
This section contains the following topics:
Section 15.2.5.3.1, "About Configuring Project Funding Fact"
Section 15.2.5.3.2, "About Configuring Project Retention Fact"
Section 15.2.5.3.3, "About Configuring Budget Fact for PeopleSoft"
Funding is based on Funding Line, which represents allocations made to a project or task. The line level funding information is held in the Project Funding Line fact (W_PROJ_ FUNDING_ LINE_F). PeopleSoft Enterprise will only source the Agreement Amount which is based on the Contracting Detail table PS_CA_DETAIL.
The Project Funding Header fact (W_PROJ_FUNDING_HDR_F) contains an additional aggregated Agreement Amount metric per PeopleSoft Enterprise Contract.
Funding Fact Canonical Date
Both GL Date and Funding Allocation Date are not populated in the OLTP application. Therefore in the data warehouse, the GL Date and Funding Allocation Date for PeopleSoft Enterprise are based on the Last Update Date Time Stamp, using the GL Calendar of the Project Business Unit. 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.
Note:
The GL date (Last Update Date Time Stamp) is the canonical date for this table and is also used for global exchange rate calculation.The Retention fact table (W_PROJ_RETENTION_F) holds aggregated information about Retention Billing Metrics. Out of the box, the logical table source based on this table in the metadata repository (RPD) is deactivated. If you want to use this table, you need to use Oracle BI Administration Tool to open the RPD and activate it. In the Oracle BI Administration Tool, access the Fact_W_PROJ_RETENTION_F_Project_Retention LTS, which is located under Fact - Project Billing in the Business Model Layer, and select the Active check box.
The corresponding ETL tasks have not been deactivated out of the box in DAC. Therefore, if you do not use this fact table, access the PeopleSoft 8.9 and PeopleSoft 9.0 containers in DAC and deactivate these tasks:
SDE_PSFT_ProjectRetentionFact
SDE_PSFT_ProjectRetentionFact_Primary
Also, in DAC, access the DataWarehouse container and deactivate these tasks:
SIL_ProjectRetention_Fact
SIL_ProjectRetentionFact_IdentifyDelete
SIL_ProjectRetentionFact_SoftDelete
Note:
To retrieve metrics from this fact, you must apply the PeopleSoft Enterprise fix 1831692000 from My Oracle Support.Baselined Budgets are extracted into the Budget Fact (W_PROJ_BUDGET_F) table, the grain of which is Budget Line.
Defining Filters on Budget Metrics
A user can create multiple versions for the same Budget type. Therefore, all exposed metrics are filtered by this filter: Current or Original Budget. One Project budget can have multiple versions. To show only one budget version at a time, every metric for the Current Version and the Original Version are shown. These flags are set based on the version number of Budget. If the budget has only one version, the Current and Original budget would show the same information.
Not applicable to Oracle BI Applications release 7.9.6.
This section contains the following topics:
Section 15.3.2, "Integrations with Project Analytics for PeopleSoft"
Section 15.3.1, "Integrations with Project Analytics for Oracle EBS"
You can configure other analytics modules for Oracle EBS to use dimensions from Project Analytics for Oracle EBS.
This section contains the following topics:
Section 15.3.1.1, "About the Integration with Procurement and Spend Analytics for Oracle EBS"
Section 15.3.1.2, "About the Integration with Financial Analytics for Oracle EBS"
Oracle Project Analytics comes with an out-of-the box integration with Oracle Procurement and Spend Analytics. Leveraging this information, you can analyze the Project Commitments and historical procurement transactions (such as Requisitions from Projects and Purchase Orders associated with that) for a project. However, to leverage the commitments information in Project Analytics, you need to license and implement Oracle Procurement and Spend Analytics.
You must configure both Procurement and Spend Analytics and Project Analytics modules for Oracle EBS before you can configure this integration. For instructions on how to configure the integration between these two modules, see Section 4.3.2.2, "How to Enable Project Analytics Integration with Procurement and Spend Subject Areas".
Note:
Make sure that you have upgraded the Oracle Business Analytics Warehouse to 7.9.6 before you configure an integration. For more information, see the Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users.Oracle Project Analytics provides an out-of-the-box integration with Oracle Financial Analytics. Leveraging this information, Project users can get details of both AR invoices and AP invoices at a summary level for each project in multiple buckets in order to understand the cash flow. The users can then drill down to the receivable or payables information to view the specific customer invoices or suppler invoices causing the cash flow issues, and take remedial actions before they occur. Project Invoices functionality is currently part of Oracle Financial Analytics. You need to license Oracle Financials Analytics and implement AP and AR modules to leverage this functionality.
You must configure both Financial Analytics and Project Analytics modules for Oracle EBS before you can configure this integration. For instructions on how to configure the integration between these two modules, see Section 5.3.2.10, "How to Enable Project Analytics Integration with Financial Subject Areas".
Note:
Make sure that you have upgraded the Oracle Business Analytics Warehouse to 7.9.6 before you configure an integration. For more information, see the Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users.You can configure other analytics modules for PeopleSoft to use dimensions from Project Analytics for PeopleSoft.
This section contains the following topics:
Section 15.3.2.1, "About the Integration with Procurement and Spend for PeopleSoft"
Section 15.3.2.2, "About the Integration with Financial Analytics for PeopleSoft"
Oracle Project Analytics for PeopleSoft comes with out-of-the box integration with Procurement and Spend Analytics for PeopleSoft. To perform this integration, you need to license and implement Oracle Procurement and Spend Analytics.
You must configure both Project Analytics and Procurement and Spend Analytics modules for PeopleSoft before you can configure this integration. For instructions on how to configure the integration between these two modules, see Section 4.3.3.1, "How to Enable Project Analytics Integration with Procurement and Spend Subject Areas".
Note:
Make sure that you have upgraded the Oracle Business Analytics Warehouse to 7.9.6 before you configure an integration. For more information, see Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users.Financial Analytics for PeopleSoft can use dimensions from Project Analytics for PeopleSoft. To perform this integration, you need to license and implement Oracle Financial Analytics.
You must configure both Project Analytics and Financial Analytics modules for PeopleSoft before you can configure this integration. For instructions on how to configure the integration between these two modules, see Section 5.3.3.5, "How to Enable Project Analytics Integration with Financial Subject Areas".
Note:
Make sure that you have upgraded the Oracle Business Analytics Warehouse to 7.9.6 before you configure an integration. For more information, see the Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users.
|
 Copyright © 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|