Skip Headers
Oracle® Business Intelligence Applications Installation and Configuration Guide for Oracle Data Integrator Users
Version 7.9.5.2

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

Go to previous page
Previous
Go to next page
Next
View PDF

6 Configuring Oracle Procurement and Spend Analytics

This chapter describes how to configure Oracle Procurement and Spend Analytics for particular sources to meet your business needs, and contains the following topics:

To find out about other possible tasks required to deploy Oracle Business Intelligence Applications, see Section 2.4, "Roadmap To Installing, Configuring, and Customizing Oracle Business Intelligence Applications With ODI".

6.1 Overview of Oracle Procurement and Spend Analytics

Oracle Procurement and Spend Analytics comprises the following:

Oracle Procurement and Spend Analytics enable organizations to optimize their supply chain performance by integrating data from across the enterprise supply chain and enabling executives, managers, and frontline employees to make more informed and actionable decisions. Organizations using Oracle Procurement and Spend Analytics benefit from increased visibility into the complete Procurement and Spend process, including comprehensive supplier performance analysis and supplier payables analysis. Through complete end-to-end insight into the factors that impact Procurement and Spend performance, organizations can significantly reduce costs, enhance profitability, increase customer satisfaction, and gain competitive advantage. Oracle Procurement and Spend Analytics also integrate with the other applications in Oracle Business Intelligence Applications product line. They deliver this insight across the organization to increase the company's effectiveness in managing its customers, suppliers, and financial decisions.

6.1.1 Oracle Procurement and Spend Analytics Module

Provides complete visibility into direct and indirect spend across the enterprise, payment, and employee expenses. Example analyses are spend by Commodity & Supplier, by Purchase Org, Cost Center, and expense by Employee, Buyer, etc.

The Oracle Procurement and Spend Analytics application is comprised of these subject areas:

  • Total Spend: This is a summary subject area that provides the ability to do comparative analysis and report on requested spend, committed spend and actual spend across suppliers, company, products, commodities and associated hierarchies for both direct and indirect spend (indirect spend being MRO and employee expenses) in detail to allow complete visibility of spending across your organization.

  • Purchase Orders: This is a detailed subject area that provides the ability to report on committed spend, and Purchase orders of the suppliers of an organization across suppliers, company, products, commodities and associated hierarchies at purchase order line level

  • Purchase Order Costs: This is a detailed subject area that provides the ability to report on committed spend and Purchase orders of the suppliers of an organization across suppliers, company, products, and commodities and associated hierarchies at cost center (distribution line) level.

  • Purchase Cycle Lines: This is a summary subject area that provides the ability to report cycle time performance such as Requisition to PO lead time, PO to Receipt lead time, P2P lead time of the Suppliers of an organization.

  • Purchase Schedules: This is a detailed subject area that provides the ability to report on purchase order shipments of an organization across suppliers, company, products, commodities and associated hierarchies at purchase schedule line level

  • Purchase Requisitions: This is a detailed subject area that provides the ability to report on requested spend and Purchase requisitions of the suppliers of an organization across suppliers, company, products, commodities and associated hierarchies at purchase requisition line level

  • Purchase Requisition Status: This is a summary subject area that provides the ability to report on requisition status along the approval cycle of Purchase requisitions of the suppliers of an organization. It's populated only by Universal adapter.

  • Purchase Receipts: This is a detailed subject area that provides the ability to report on actual spend and Purchase Receipts of the suppliers of an organization across suppliers, company, location, products, commodities and associated hierarchies at purchase receipt line level

  • Employee Spend: This is a detailed subject area that provides the ability to report on employee spend of an organization across employees, company, cost center and associated hierarchies. The Expenses subject area contains targeted metrics and reports that examine travel and expense costs in relationship to your organization's overall spending patterns. In contrast to analyzing direct spending patterns, where you may review purchasing, Expenses examines indirect spending—the cost of employee related expenses. It's populated only by Universal adapter.

6.1.2 Supplier Performance Analytics Module

Enables organizations to have a complete picture of the performance of their suppliers, including complete supplier scorecards, procurement cycle times, supplier price performance, delivery performance, product receipt quality, on-time payment ratings, payment activity and volume and payments due / overdue analysis.

The Supplier Performance Analytics application is comprised of these subject areas:

  • Supplier Performance. The Suppliers functional area contains targeted reports and metrics that allow you to analyze the timeliness, reliability, cost, and quality of goods provided by your suppliers. It helps you to understand how well suppliers are contributing to success of your organization, and to evaluate the price, quality, and delivery timing in procuring materials

  • Supplier AP Transactions: This is a summary subject area that provides the ability to analyze payment performance and payment due analysis of the suppliers of an organization across suppliers, company, location, products, commodities and associated hierarchies. In addition to monitoring supplier performance, it is important to monitor organization's performance of making on time payments. This will help the Organizations to maintain better relationships with their best suppliers.

6.2 Configuration Required Before A Full Load for Oracle Procurement and Spend Analytics

This section contains configuration steps that you need to perform on Oracle Procurement and Spend Analytics before you do a full data load, and contains the following topics:

6.2.1 How to Deploy Stored Procedures

Stored procedures are a group of SQL statements that perform particular tasks on the database. For example, stored procedures can help to improve the performance of the database.

You can deploy stored procedures by copying the stored procedure files from your Oracle Business Intelligence installation and deploying them to the target data warehouse.

Note:

Some sessions may fail if these procedures are not compiled in the database before running the workflows.

To deploy stored procedures:

  1. On the ODI machine, navigate to the $ODI_HOME\biapps_odi\odifiles\odidatafiles\storedprocfiles directory, and locate the files 1_OFS_BI_TIME_PACKAGE.sql and 2_OFS_BI_LOAD_PACKAGE.sql.

  2. Copy the SQL commands in the 1_OFS_BI_TIME_PACKAGE.sql file or the 2_OFS_BI_LOAD_PACKAGE.sql file as required.

  3. Compile the stored procedures in the target data warehouse database.

    Note:

    If you have problems deploying the stored procedures, see your database reference guide, or contact your database administrator.

6.2.2 How to Configure the Parameter for Purchase Cycle Line

To load the purchase cycle line table (W_PURCH_CYCLE_LINE_F), the ELT tasks need to distinguish data that originates in Oracle 11i applications.

To configure the parameter for Purchase Cycle Line:

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Supply Chain Analytics from the Select BI Application drop down list.

  4. Locate the following parameters and use the Parameter Value field to set the value:

    • ORA_DATASOURCE_NUM_ID_LIST

      Change the values of parameter ORA_DATASOURCE_NUM_ID_LIST from 4 (the default value) to the list of Data Source NUM IDs that you defined for your Oracle data sources.

    • SIL_PURCHASECYCLELINESFACT

    • SIL_PURCHASECYCLELINESFACT_EXTRACT

  5. Save your changes.

    For more information about specifying parameter values, see Section 4.6.3.3.1, "How to Set E-LT Parameters In Oracle BI Applications Configuration Manager".

6.2.3 Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics

If you have modify or extend a seeded list of values, you must configure the CSV files for Oracle Procurement and Spend Analytics by mapping values from your source systems to the domain values.

This section explains 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 to the seeded values, you need to follow the instructions to configure the Domain Values and CSV Worksheet Files.

6.2.3.1 List of Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics

The table below lists the CSV worksheet files and the domain values for Oracle Procurement and Spend Analytics in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\LkpFiles directory on the ODI machine.

Table 6-1 Domain Values and CSV Worksheet Files for Oracle Procurement and Spend Analytics

Worksheet File Name Description Session

domainValues_Status_Purch_Approve_ora11i.csv

Lists the Purchasing Approval Status column and the corresponding domain values for the Oracle 11i application. For information about how to edit this file, see Section 6.2.3.2, "To configure domainValues_Status_Purch_Approve_ora11i.csv".

SDE_ORA_StatusDimension_PurchaseApprove

domainValues_Status_Purch_Cycle_ora11i.csv

Lists the Purchasing Cycle Status column and the corresponding domain values for the Oracle 11i application. For information about how to edit this file, see Section 6.2.3.3, "To configure domainValues_Status_Purch_Cycle_ora11i.csv".

SDE_ORA_StatusDimension_PurchaseCycle

domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv

List the Purchase Basis Type and the corresponding domain values for the Oracle EBS application. For information about how to edit this file, see Section 6.2.3.7, "To configure domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv".

SDE_ORA_TransactionTypeDimension_PO_Line_Type

domainValues_Xact_Types_PO_Line_Type_ora11i.csv

Lists the Purchasing Line Type and the corresponding domain Values for the Oracle EBS application. For information about how to edit this file, see Section 6.2.3.6, "To configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv".

SDE_ORA_TransactionTypeDimension_PO_Line_Type

domainValues_Xact_Types_Purch_Orders_ora11i.csv

Lists the Purchase Order Transaction Type column and the corresponding domain values for the Oracle EBS application. For information about how to edit this file, see Section 6.2.3.4, "To configure domainValues_Xact_Types_Purch_Orders_ora11i.csv".

SDE_ORA_TransactionTypeDimension_PurchaseOrder

domainValues_Xact_Types_Purch_Requisitions_ora11i.csv

Lists the Purchase Requisition Transaction Type column and the corresponding domain values for the Oracle EBS application. For information about how to edit this file, see Section 6.2.3.5, "To configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv".

SDE_ORA_TransactionTypeDimension_PurchaseRequest


6.2.3.2 To configure domainValues_Status_Purch_Approve_ora11i.csv

This section explains how to configure domainValues_Status_Purch_Approve_ora11i.csv.

  1. Identify the Purchase Approval Status in your Oracle EBS source system by using the following SQL:

    SELECT A.LOOKUP_CODE FROM FND_LOOKUP_VALUES A WHERE A.LOOKUP_TYPE='AUTHORIZATION STATUS' AND A.LANGUAGE = 'US'

  2. Open the domainValues_Status_Purch_Approve_ora11i.csv file in a text editor.

  3. Copy the LOOKUP_CODE to the STATUS_CODE column in the file.

    The data must be copied starting from the 8th line.

  4. Map each LOOKUP_CODE to one Purchase Approval (PURCH_APPROVAL) domain value.

    Use commas to separate the entries.

  5. Save and close the file.

6.2.3.3 To configure domainValues_Status_Purch_Cycle_ora11i.csv

This section explains how to configure domainValues_Status_Purch_Cycle_ora11i.csv.

  1. Identify the Purchase Cycle Status in your Oracle EBS source system by using the following SQL:

    SELECT A.LOOKUP_CODE FROM FND_LOOKUP_VALUES A WHERE A.LOOKUP_TYPE='DOCUMENT STATE' AND A.LANGUAGE = 'US'

  2. Open the domainValues_Status_Purch_Cycle_ora11i.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles directory.

  3. Copy the LOOKUP_CODE to the STATUS_CODE column in the file.

    The data must be copied starting from the 8th line.

  4. Map each LOOKUP_CODE to one Purchase Cycle (PURCH_CYCLE) domain value.

    Use commas to separate the entries.

  5. Save and close the file.

6.2.3.4 To configure domainValues_Xact_Types_Purch_Orders_ora11i.csv

This section explains how to configure domainValues_Xact_Types_Purch_Orders_ora11i.csv.

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

    SELECT DISTINCT PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_SUBTYPE

    FROM PO_DOCUMENT_TYPES_ALL_TL

    WHERE PO_DOCUMENT_TYPES_ALL_TL.LANGUAGE='US' AND PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_TYPE_CODE IN ('PO', 'PA') AND PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_SUBTYPE <> 'CONTRACT'

    UNION SELECT 'COMPLEXWORK_ACTUAL' FROM DUAL

    UNION SELECT 'COMPLEXWORK_FINANCING' FROM DUAL;

  2. Open the domainValues_Xact_Types_Purch_Orders_ora11i.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles directory.

  3. Copy the DOCUMENT_SUBTYPE to the XACT_SUBTYPE_CODE column in the file.

    The data must be copied starting from the 8th line.

  4. Map each DOCUMENT_SUBTYPE to one Purchase Order transaction type (PURCH_ORDERS) domain value.

    Use commas to separate the entries.

  5. Save and close the file.

6.2.3.5 To configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv

This section explains how to configure domainValues_Xact_Types_Purch_Requisitions_ora11i.csv.

  1. Identify the Purchase Requisition Type in your Oracle EBS source system by using the following SQL:

    SELECT DISTINCT PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_SUBTYPE

    FROM PO_DOCUMENT_TYPES_ALL_TL

    WHERE PO_DOCUMENT_TYPES_ALL_TL.LANGUAGE='US' AND PO_DOCUMENT_TYPES_ALL_TL.DOCUMENT_TYPE_CODE = 'REQUISITION'

  2. Open the domainValues_Xact_Types_Purch_Requisitions_ora11i.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles directory.

  3. Copy the DOCUMENT_SUBTYPE to the XACT_SUBTYPE_CODE column in the file.

    The data must be copied starting from the 7th line.

  4. Map each DOCUMENT_SUBTYPE to one Purchase Requisition Type (PURCH_RQLNS) domain value.

    Use commas to separate the entries.

  5. Save and close the file.

6.2.3.6 To configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv

This section explains how to configure domainValues_Xact_Types_PO_Line_Type_ora11i.csv.

  1. Identify the Purchase Order Line Type in your Oracle EBS source system by using the following SQL:

    SELECT DISTINCT PO_LINE_TYPES_V.ORDER_TYPE_LOOKUP_CODE FROM PO_LINE_TYPES_V

  2. Open domainValues_Xact_Types_PO_Line_Type_ora11i.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles directory.

  3. Copy the ORDER_TYPE_LOOKUP_CODE to the XACT_TYPE_CODE column in the file.

    The data must be copied starting from the 8th line.

  4. Map each ORDER_TYPE_LOOKUP_CODE to one PO Line Type transaction type (PO_LINE_TYPE) domain value.

    Use commas to separate the entries.

  5. Save and close the file.

6.2.3.7 To configure domainValues_Xact_Types_PO_Line_Type_CODE1_ora11i.csv

This section explains how to configure domainValues_Xact_Types_Code1_PO_Line_Type_ora11i.csv.

  1. Identify the Purchase Order Line Purchase Basis in your Oracle EBS source system by using the following SQL:

    SELECT DISTINCT PO_LINE_TYPES_V.PURCHASE_BASIS FROM PO_LINE_TYPES_V

  2. Open the domainValues_Xact_Types_Code1_PO_Line_Type_ora11i.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles directory.

  3. Copy the PURCHASE_BASIS to the XACT_TYPE_CODE1 column in the file.

    The data must be copied starting from the 8th line.

  4. Map each PURCHASE_BASIS to one PO Line Type Purchase Basis Type (PO_LINE_TYPE) domain value.

    Use commas to separate the entries.

  5. Save and close the file.

6.2.4 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle Procurement and Spend Analytics.

6.2.4.1 About Configuring the Purchase Receipts Aggregate Table

The Purchase Receipts aggregate table (W_PURCH_RCPT_A) is used to capture information about the product receipts received from your suppliers and the purchase orders placed on them by your purchasing organization.

For your initial ELT run, you need to configure the GRAIN parameter for the time aggregation level in the Purchase Receipts Aggregate fact table.

For the incremental ELT run, you need to configure the time aggregation level and the source identification. The source identification value represents the source system you are sourcing data from.

You need to configure two parameters to aggregate the Purchase Receipts table for your incremental run:

  • GRAIN

  • TIME_GRAIN

These parameters have a preconfigured value of Month. The possible values for the parameter are:

  • DAY

  • WEEK

  • MONTH

  • QUARTER

  • YEAR

The Purchase Receipt Lines aggregate table is fully loaded from the base table in the initial ELT run. The table can grow to millions of records. Thus, the Purchase Receipts aggregate table is not fully reloaded from the base table after each incremental ELT run. Oracle Business Analytics Warehouse minimizes the incremental aggregation effort, by modifying the aggregate table incrementally as the base table is updated. This process is done in four steps:

  1. Oracle Business Analytics Warehouse finds the records to be deleted in the base table since the last ELT run, and loads them into the W_PURCH_RCPT_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreSoftDeleteImage, and it is run before the records are deleted from the base table. The mapping is run in the source-specific workflow.

  2. Oracle Business Analytics Warehouse finds the records to be updated in the base table since the last ELT run, and loads them into the W_PURCH_RCPT_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreLoadImage, and it is run before the records are updated in the base table. It is run in the source-specific workflow.

  3. Oracle Business Analytics Warehouse finds the inserted or updated records in the base table since the last ELT run, and loads them into the W_PURCH_RCPT_TMP table, without changing their sign. The mapping responsible for this task is suffixed with _Derive_PostLoadImage, and it is run after the records are updated or inserted into the base table. It is run in the post load-processing workflow.

  4. Oracle Business Analytics Warehouse aggregates the W_PURCH_RCPT_TMP table, and joins it with the W_PURCH_RCPT_A aggregate table to insert new or update existing buckets to the aggregate table. This step is part of the post load-processing workflow, and the mapping is suffixed with _Derive.

6.2.4.2 How to Configure the Purchase Receipts Aggregate Table

To load the Purchase Receipts aggregate table (W_PURCH_RCPT_A), you need to configure the post-load-processing parameter file and the source system parameter files, and run the initial workflow and then the incremental workflow.

To configure the Purchase Receipts Aggregate Table:

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Order Management Analytics from the Select BI Application drop down list.

  4. Locate the following parameters and use the Parameter Value field to set the value:

    • TIME_GRAIN (default is MONTH, for the SIL_PurchaseReceiptAggregate_Derive_PreLoadImage scenario).

  5. Save your changes.

    For more information about specifying parameter values, see Section 4.6.3.3.1, "How to Set E-LT Parameters In Oracle BI Applications Configuration Manager".

6.2.4.3 About Configuring the Purchase Cycle Lines Aggregate Table

To aggregate the Purchase Cycle Lines table (W_PURCH_CYCLE_LINE_A), you need to configure the parameters in Oracle BI Applications Configuration Manager, and run the initial ELT workflow and then the incremental ELT workflow.

For your initial ELT run, you need to configure the GRAIN parameter for the time aggregation level in the Purchase Cycle Lines Aggregate fact table.

For the incremental ELT run, you need to configure the time aggregation level and the source identification. The source identification value represents the source system you are sourcing data from.

You need to configure two parameters to aggregate the Purchase Cycle Lines table for your incremental run:

  • GRAIN

  • TIME_GRAIN

These parameters have a preconfigured value of Month. The possible values for parameters are:

  • DAY

  • WEEK

  • MONTH

  • QUARTER

  • YEAR

The Purchase Cycle Lines aggregate table is fully loaded from the base table in the initial ELT run. The table can grow to millions of records. The Purchase Cycle Lines aggregate table is not fully reloaded from the base table after an ELT run. Oracle Business Analytics Warehouse minimize the incremental aggregation effort, by modifying the aggregate table incrementally as the base table gets updated. This process is done in four steps:

  1. Oracle Business Analytics Warehouse finds the records to be deleted in the base table since the last ELT run, and loads them into the W_PURCH_CYCLE_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreSoftDeleteImage, and it is run before the records are deleted from the base table. It is run in the source-specific workflow.

  2. Oracle Business Analytics Warehouse finds the records to be updated in the base table since the last ELT run, and loads them into the W_PURCH_CYCLE_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with _Derive_PreLoadImage, and it is run before the records are updated in the base table. It is run in the source-specific workflow.

  3. Oracle Business Analytics Warehouse finds the inserted or updated records in the base table since the last ELT run, and loads them into the W_PURCH_CYCLE_LINE_TMP table, without changing their sign. The mapping responsible for this task is suffixed with _Derive_PostLoadImage, and it is run after the records are updated or inserted into the base table. It is run in the post load-processing workflow.

  4. Oracle Business Analytics Warehouse aggregates the W_PURCH_CYCLE_LINE_TMP table, and joins it with the W_PURCH_CYCLE_LINE_A aggregate table to insert new or update existing buckets to the aggregate table. This step is part of the post load-processing workflow, and the mapping is suffixed with _Derive.

6.2.4.4 How to Configure the Purchase Cycle Lines Aggregate Table

Before you load the Purchase Cycle Lines aggregate table (W_PURCH_CYCLE_LINE_A), you need to configure the post-load-processing parameter run the initial workflow and then the incremental workflow, as follows.

To configure the Purchase Cycle Lines Aggregate Table:

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Order Management Analytics from the Select BI Application field.

  4. Locate the following parameters and use the Parameter Value field to set the value:

    • TIME_GRAIN (default is MONTH, for the SIL_PurchaseCycleLinesAggregate_Derive_PreLoadImage scenario).

    • GRAIN (default is MONTH, for the PLP_PurchaseCycleLinesAggregate_Load_Full scenario).

  5. Save your changes.

    For more information about specifying parameter values, see Section 4.6.3.3.1, "How to Set E-LT Parameters In Oracle BI Applications Configuration Manager".