Oracle® Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications 11g Release (11.1.1) Part Number E16814-01 |
|
|
View PDF |
This section contains reference information about the functional configuration Tasks that you use in FSM to configure Oracle BI Applications Offerings. Use this section to find out which functional configuration Tasks are available in FSM for each Oracle BI Applications Offering.
Functional configuration Tasks in Section B.1, "Lists of Functional Configuration Tasks For Each Offering" are listed by name only; for more information about these Tasks refer to FSM. Information-only tasks (known as Informational Tasks in FSM) in Section B.2, "Informational Task Reference"are included with the text that is displayed in FSM when you click Go to Task in FSM.
This chapter contains the following sections:
Note:
This chapter lists the names of Tasks that are available in FSM for each Offering. You must use FSM to see the Task details for an Offering. The chapter also includes information only Tasks.This section lists the Functional Configuration Tasks for each Offering in Oracle Business Intelligence Applications, and contains the following sections:
Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions"
Section B.1.3, "List of Functional Configuration Tasks for Oracle Financial Analytics"
Section B.1.4, "List of Functional Configuration Tasks for Human Resources Analytics"
Section B.1.5, "List of Functional Configuration Tasks for Oracle Marketing Analytics"
Section B.1.6, "List of Functional Configuration Tasks for Oracle Partner Analytics"
Section B.1.7, "List of Functional Configuration Tasks for Oracle Procurement and Spend Analytics"
Section B.1.9, "List of Functional Configuration Tasks for Oracle Project Analytics"
Section B.1.10, "List of Functional Configuration Tasks for Oracle Sales Analytics"
This section lists Functional Configuration Tasks that apply to multiple Offerings.
How to Perform System Setups and Post Install Tasks for BI Applications
Configure Data Load Parameters for File Based Calendars
Configure Enterprise List
Configure Global Currencies
Configure Initial Extract Date
Configure Reporting Parameters for Year Prompting
Configure Slowly Changing Dimensions
Define Enterprise Calendar
How to Include and Exclude Multiple Calendar Support for Subject Areas in DAC
How to Reload the Time Dimension Tables After the Data Warehouse Is Loaded
Specify Gregorian Calendar Date Range
This section lists Functional Configuration Tasks for Oracle Customer Data Management Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Customer Data Management Analytics
Configure Initial Extract Date
Configure Enterprise List
Specify Time in Days before an Account needs an Activity
Specify Time in Days before an Account needs a Contact
Specify Time in Days before a Contact needs an Activity
Specify Order of First Name and Last Name in Position Hierarchy
Specify Interaction Type Code
Specify the Filter to be Applied on Interaction
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for Party Dimension
ETL Notes and Additional Information for Customer Data Management Analytics
This section lists Functional Configuration Tasks for Oracle Financial Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Financial Analytics
Configure Global Currencies
Specify Gregorian Calendar Date Range
Configure Data Load Parameters for File Based Calendars
Define Enterprise Calendar
How to Reload the Time Dimension Tables After the Data Warehouse Is Loaded
Configure Slowly Changing Dimensions
Configure Reporting Parameters for Year Prompting
Configure Initial Extract Date
Configure Enterprise List
Configure Data Load Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Configure Data Load Parameters for Product Inventory Catalog
Configure Reporting Parameters for Product Inventory Catalog
Configure Subledger Module to be extracted for GL Linkage information
How to Implement GL Segment and GL Account Dimensions
Manage Domains and Member Mappings for Party Dimension
Manage Domains and Member Mappings for GL Account Dimension
Manage Domains and Member Mappings for Payment Method Dimension
Manage Domains and Member Mappings for Business Location Dimension
Manage Domains and Member Mappings for Customer Location Dimension
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Time Dimension
Manage Domains and Member Mappings for Supplier Account Dimension
Manage Domains and Member Mappings for Project Dimension Group
Manage Domains and Member Mappings for Project Resource Dimension Group
Manage Domains and Member Mappings for Project Contract Dimension Group
Configure the number of historical monthly snapshots for AP Aging
Configure Reporting Parameters for the default aging method displayed in AP Aging Reports
Manage Domains and Member Mappings for AP Hold Release Reason Dimension
Manage Domains and Member Mappings for AP Transaction Source Dimension
Manage Domains and Member Mappings for AP Transaction Type Dimension
Manage Domains and Member Mappings for AP Transactions and Balance
Configure the number of historical monthly snapshots for AR Aging
Configure Reporting Parameters for Default Aging Method displayed in AR Aging Reports
Manage Domains and Member Mappings for AR Transaction Type Dimension
Manage Domains and Member Mappings for AR Transactions and Balance Fact
Manage Domains and Member Mappings for Fixed Asset Status Dimension
Manage Domains and Member Mappings for Fixed Asset Transaction Type Dimension
How to Implement Asset Category and Asset Location Dimensions
Specify the Ledger or Set of Books for which General Ledger Data is Extracted
How to Set up GL Segments Which Need to be Aggregated for GL Balances
How to Set Up Drill Down in Oracle BI Answers from General Ledger to Subledger
Manage Domains and Member Mappings for Channel Type Dimension
Manage Domains and Member Mappings for Cost Allocation Type Dimension
Manage Domains and Member Mappings for Customer Contact Dimension
Manage Domains and Member Mappings for Cost Element Dimension
Manage Domains and Member Mappings for Customer Expense Transaction Type Dimension
Manage Domains and Member Mappings for Product Expense Transaction Type Dimension
Manage Domains and Member Mappings for Expense Payment Type Dimension
Manage Domains and Member Mappings for Expense Status Dimension
Manage Domains and Member Mappings for Expense Type Dimension
Manage Domains and Member Mappings for Geography Dimension
Specify Order of First Name and Last Name in Position Hierarchy
How to Configure Group Account Numbers
How to Integrate Project Analytics with Financial Analytics
ETL Notes and Additional Information for Financial Analytics
This section lists Functional Configuration Tasks for Oracle Human Resources Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Human Resources Analytics
Configure Enterprise List
Configure Workforce Initial Extraction Date
Configure Workforce Snapshot Date
Configure Data Load Parameter for Workforce Adjusted Service Date Flag
Specify a Default Value for Number of Working Hours Per Week
Specify Order of First Name and Last Name in Position Hierarchy
Manage Domains and Member Mappings for Age Band Dimension
Manage Domains and Member Mappings for Business Location Dimension
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for Employment Dimension
Manage Domains and Member Mappings for HR Assignment Dimension
Manage Domains and Member Mappings for HR Person Legislation Dimension
Manage Domains and Member Mappings for HR Position Dimension
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Job Dimension
Manage Domains and Member Mappings for Performance Band Dimension
Manage Domains and Member Mappings for Period Of Work Band Dimension
Manage Domains and Member Mappings for Time Dimension
Manage Domains and Member Mappings for Workforce Event Fact
Manage Domains and Member Mappings for Workforce Event Type Dimension
Configure Absence and Accrual Initial Extraction Date
Manage Domains and Member Mappings for Accrual Plan Dimension
Manage Domains and Member Mappings for Absence Event Dimension
Manage Domains and Member Mappings for Absence Type Reason Dimension
Configure Payroll Initial Extraction Date
Manage Domains and Member Mappings for Payroll Balance Dimension
Configure Initial Extract Date
Specify the Ledger or Set of Books for which General Ledger Data is Extracted
Manage Domains and Member Mappings for GL Account Dimension
How to Assign Group Account Numbers to Natural Accounts for HR Analytics
How To Grant GL Data Role to HR VP Users
ETL Notes and Additional Information for Human Resources Analytics
This section lists Functional Configuration Tasks for Oracle Marketing Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Marketing Analytics
Configure Initial Extract Date
Configure Enterprise List
Configure Data Load Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Specify Order of First Name and Last Name in Position Hierarchy
Specify the Maximum Number of Days in a Sales Stage
Configure Loading of Resources into Opportunity - Resource Helper Table
Configure Opportunity Revenue Aggregate Table
Manage Domains and Member Mappings for Agreement Status Dimension
Manage Domains and Member Mappings for Agreement Type Dimension
Manage Domains and Member Mappings for Campaign History
Manage Domains and Member Mappings for Channel Type Dimension
Manage Domains and Member Mappings for Customer Contact Dimension
Manage Domains and Member Mappings for Customer Location Dimension
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for Geography Dimension
Manage Domains and Member Mappings for Interactions Fact
Manage Domains and Member Mappings for Interactions Opportunity Fact
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Marketing Lead Fact
Manage Domains and Member Mappings for Marketing Source Dimension
Manage Domains and Member Mappings for Opportunity Dimension
Manage Domains and Member Mappings for Opportunity Revenue Fact
Manage Domains and Member Mappings for Party Dimension
Manage Domains and Member Mappings for Response Fact
Manage Domains and Member Mappings for Segment Dimension
Manage Domains and Member Mappings for Time Dimension
ETL Notes and Additional Information for Marketing Analytics
This section lists Functional Configuration Tasks for Oracle Partner Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Partner Analytics
Configure Initial Extract Date
Configure Enterprise List
Specify the Maximum Number of Days in a Sales Stage
Configure Loading of Resources into Opportunity - Resource Helper Table
Configure Opportunity Revenue Aggregate Table
Manage Domains and Member Mappings for Channel Type Dimension
Manage Domains and Member Mappings for Customer Contact Dimension
Manage Domains and Member Mappings for Customer Location Dimension
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for Geography Dimension
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Party Dimension
Manage Domains and Member Mappings for Time Dimension
Manage Domains and Member Mappings for Opportunity Dimension
Manage Domains and Member Mappings for Opportunity Revenue Fact
Manage Domains and Member Mappings for Partner Enrollment Fact Group
Manage Domains and Member Mappings for Partner Program Measure Fact Group
Configure Data Load Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Specify Order of First Name and Last Name in Position Hierarchy
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Marketing Lead Fact
Manage Domains and Member Mappings for Marketing Source Dimension
Manage Domains and Member Mappings for Segment Dimension
ETL Notes and Additional Information for Partner Analytics
This section lists Functional Configuration Tasks for Oracle Procurement and Spend Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Procurement and Spend Analytics
Configure Initial Extract Date
Configure Enterprise List
Configure Data Load Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Configure Purchase Cycle Lines Aggregate Fact
Configure Purchase Receipts Aggregate Fact
Configure Data Load Parameters for Product Inventory Catalog
Configure Reporting Parameters for Product Inventory Catalog
Configure Subledger Modules to be extracted for GL Linkage information
Configure the number of historical monthly snapshots for AP Aging
Manage Domains and Member Mappings for AP Hold Release Reason Dimension
Manage Domains and Member Mappings for AP Invoice Approval Status Dimension
Manage Domains and Member Mappings for AP Invoice Payment Status Dimension
Manage Domains and Member Mappings for AP Spend Classification Dimension
Manage Domains and Member Mappings for AP Transaction Source Dimension
Manage Domains and Member Mappings for AP Transaction Type Dimension
Manage Domains and Member Mappings for AP Transactions and Balance Fact
Manage Domains and Member Mappings for Movement Type Dimension
Manage Domains and Member Mappings for PO Creation Method Dimension
Manage Domains and Member Mappings for PO Document Style Dimension
Manage Domains and Member Mappings for Purchase Agreement Fact
Manage Domains and Member Mappings for Purchase Change Order Fact
Manage Domains and Member Mappings for Purchase Line Type Dimension
Manage Domains and Member Mappings for Purchase Order Agreement Leverage Type Dimension
Manage Domains and Member Mappings for Purchase Order Shipment Type Dimension
Manage Domains and Member Mappings for Purchase Order Status Dimension
Manage Domains and Member Mappings for Purchase Order Transaction Type Dimension
Manage Domains and Member Mappings for Purchase Receipt Transaction Type Dimension
Manage Domains and Member Mappings for Purchase Receipts Fact
Manage Domains and Member Mappings for Purchase Requisition Fact
Manage Domains and Member Mappings for Spend Invoice Distribution Fact
Manage Domains and Member Mappings for Sourcing Group Type Dimension
Manage Domains and Member Mappings for Sourcing Negotiation and Response Fact
Manage Domains and Member Mappings for Sourcing Negotiation Status Dimension
Manage Domains and Member Mappings for Sourcing Outcome Type Dimension
Manage Domains and Member Mappings for Sourcing Response Line Award Status Dimension
Manage Domains and Member Mappings for Sourcing Response Status Dimension
Manage Domains and Member Mappings for Sourcing Response Visibility Type Dimension
Specify Order of First Name and Last Name in Position Hierarchy
Manage Domains and Member Mappings for Expense Payment Type Dimension
Manage Domains and Member Mappings for Expense Status Dimension
Manage Domains and Member Mappings for Expense Type Dimension
Manage Domains and Member Mappings for Geography Dimension
Manage Domains and Member Mappings for Business Location Dimension
Manage Domains and Member Mappings for Customer Location Dimension
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for GL Account Dimension
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Party Dimension
Manage Domains and Member Mappings for Payment Method Dimension
Manage Domains and Member Mappings for Supplier Account Dimension
Manage Domains and Member Mappings for Time Dimension
How to Implement GL Segment and GL Account Dimensions
How to Integrate Project Analytics with Procurement and Spend Analytics
Manage Domains and Member Mappings for Project Dimension Group
Manage Domains and Member Mappings for Project Resource Dimension Group
How to Integrate Procurement and Spend Analytics with Spend Classification
How to Remove Spend Classification Integration Metadata
How To Customize Extended Cross Functional Security for Accounts Payables
How To Customize Security for Procurement Executive / Spend Analyst
How To Customize Extended Cross Functional Security for Employee Expenses
ETL Notes and Additional Information for Procurement and Spend Analytics
This section lists Functional Configuration Tasks for Oracle Product Information Management Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Product Information Management Analytics
Configure Initial Extract Date
Configure Enterprise List
Configure Data Load Parameters for Master Organization
Configure Reporting Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Item Batch Import Fact
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Item Request Fact
Manage Domains and Member Mappings for Time Dimension
ETL Notes and Additional Information for Product Information Management Analytics
This section lists Functional Configuration Tasks for Oracle Project Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Project Analytics
Configure Initial Extract Date
Configure Enterprise List
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Job Dimension
Manage Domains and Member Mappings for Party Dimension
Manage Domains and Member Mappings for Project Cost Dimension Group
Manage Domains and Member Mappings for Project Dimension Group
Manage Domains and Member Mappings for Project Resource Dimension Group
Manage Domains and Member Mappings for Time Dimension
Configure Time Grain of Cost Aggregate Fact
Configure Time Grain of Project Commitment Snapshot Fact
Configure Data Load Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Project Control Dimension Group
Manage Domains and Member Mappings for Project Billing Dimension Group
Manage Domains and Member Mappings for Project Contract Dimension Group
Configure Time Grain of Revenue Aggregate Fact
How to Integrate Financial Analytics with Project Analytics
How to Integrate Procurement and Spend Analytics with Project Analytics
How to Perform RPD Modifications for Cost and Revenue Time Grain Changes
ETL Notes and Additional Information for Project Analytics
This section lists Functional Configuration Tasks for Oracle Sales Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Sales Analytics
Configure Initial Extract Date
Configure Enterprise List
Configure Data Load Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Specify Order of First Name and Last Name in Position Hierarchy
Specify Time in Days before an Account needs an Activity
Specify Time in Days before an Account needs a Contact
Specify Time in Days before a Contact needs an Activity
Specify the Maximum Number of Days in a Sales Stage
Configure Loading of Resources into Opportunity - Resource Helper Table
Configure Opportunity Revenue Aggregate Table
Specify Interaction Type Code
Specify the Filter to be Applied on Interaction
Manage Domains and Member Mappings for Agreement Status Dimension
Manage Domains and Member Mappings for Agreement Type Dimension
Manage Domains and Member Mappings for Channel Type Dimension
Manage Domains and Member Mappings for Customer Contact Dimension
Manage Domains and Member Mappings for Customer Location Dimension
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for Geography Dimension
Manage Domains and Member Mappings for Interactions Fact
Manage Domains and Member Mappings for Interactions Opportunity Fact
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Marketing Lead Fact
Manage Domains and Member Mappings for Marketing Source Dimension
Manage Domains and Member Mappings for Opportunity Dimension
Manage Domains and Member Mappings for Opportunity Revenue Fact
Manage Domains and Member Mappings for Party Dimension
Manage Domains and Member Mappings for Sales Forecast Fact
Manage Domains and Member Mappings for Segment Dimension
Manage Domains and Member Mappings for Service Request Fact
Manage Domains and Member Mappings for Territory Quota Fact
Manage Domains and Member Mappings for Territory Quota Dimension
Manage Domains and Member Mappings for Time Dimension
ETL Notes and Additional Information for Sales Analytics
This section lists Functional Configuration Tasks for Oracle Supply Chain and Order Management Analytics.
Note: For a list of additional common tasks that apply to multiple Offerings, see Section B.1.1, "List of Functional Configuration Tasks for Common Areas and Dimensions".
Overview of Supply Chain and Order Management Analytics
Configure Initial Extract Date
Configure Enterprise List
Configure Data Load Parameters for Master Organization
Configure Data Load Parameters for Product Catalog
Configure Reporting Parameters for Product Catalog
Configure Data Load Parameters for Product Inventory Catalog
Configure Reporting Parameters for Product Inventory Catalog
Configure Inventory Monthly Balance and the Inventory Transaction Aggregate Table
Manage Domains and Member Mappings for Business Location Dimension
Manage Domains and Member Mappings for Cost Element Dimension
Manage Domains and Member Mappings for Cost Valuation Unit Dimension
Manage Domains and Member Mappings for Customer Contact Dimension
Manage Domains and Member Mappings for Customer Location Dimension
Manage Domains and Member Mappings for Employee Dimension
Manage Domains and Member Mappings for GL Account Dimension
Manage Domains and Member Mappings for Internal Organization Dimension
Manage Domains and Member Mappings for Item Dimension
Manage Domains and Member Mappings for Movement Type Dimension
Manage Domains and Member Mappings for Party Dimension
Manage Domains and Member Mappings for Project Dimension Group
Manage Domains and Member Mappings for Project Resource Dimension Group
Manage Domains and Member Mappings for Supplier Account Dimension
Manage Domains and Member Mappings for Time Dimension
Configure Subledger Modules to be extracted for GL Linkage information
Configure the Customer Status History Fact table
Configure Early and Late Tolerances for Shipments
Configure Sales Invoice Lines and Sales Order Lines Aggregate Tables
Manage Domains and Member Mappings for AR Transaction Type Dimension
Manage Domains and Member Mappings for AR Transactions and Balance Fact
Manage Domains and Member Mappings for Order Fulfillment Fact
Manage Domains and Member Mappings for Channel Type Dimension
Manage Domains and Member Mappings for Order Shipping Fact
Manage Domains and Member Mappings for Order Scheduling Fact
Manage Domains and Member Mappings for Order Cycle Fact
Manage Domains and Member Mappings for Order Invoice Fact
Manage Domains and Member Mappings for Order Orchestration Process Fact
Manage Domains and Member Mappings for Payment Method Dimension
Configure Reporting Parameters for Default Aging Method displayed in AR Aging Reports
Configure the number of historical monthly snapshots for AR Aging
How to Configure Backlog Period Date
How to Track Multiple Attribute Changes in Bookings
How to Add Dates to the Order Cycle Time Table for Post-Load Processing
How to Add Closed Orders to Backlog Calculations
How to Include Incompleted Invoice Lines
How to Implement GL Segment Dimension and GL Segment Hierarchy
How to Incrementally Refresh the Inventory Monthly Balance Table
How to Configure the Inventory Transaction Aggregate Table for ETL Runs
How to Implement GL Account Dimension
How to Enable Project Dimensions
How to Grant Cross Functional Access to Order Management Users
ETL Notes and Additional Information for Supply Chain and Order Management Analytics
This section contains Information-only tasks that are exposed in Functional Setup Manager as Informational Tasks. Informational Tasks contain conceptual information, or steps that are performed in tools other than Functional Setup Manager (for example, in DAC Client, or Oracle BI Administration Tool).
This section contains the following topics:
Section B.5, "ETL Notes and Additional Information for Oracle Procurement and Spend Analytics"
Section B.6, "ETL Notes and Additional Information for Oracle Customer Data Management Analytics"
Section B.7, "ETL Notes and Additional Information for Oracle Project Analytics"
Section B.8, "ETL Notes and Additional Information for Oracle Partner Analytics"
Section B.9, "ETL Notes and Additional Information for Oracle Financial Analytics"
Section B.10, "ETL Notes and Additional Information for Oracle HR Analytics"
Section B.11, "ETL Notes and Additional Information for Oracle Marketing Analytics"
Section B.12, "ETL Notes and Additional Information for Oracle Sales Analytics"
Section B.13, "How to Integrate Project Analytics with Procurement and Spend Analytics"
Section B.14, "How To Integrate Procurement and Spend Analytics with Spend Classification"
Section B.15, "How to remove or hide Oracle Spend Classification Integration Metadata"
Section B.16, "How to Configure the Inventory Transaction Aggregate Table for ETL Runs"
Section B.17, "How to Add Dates to the Order Cycle Time Table for Post-Load Processing"
Section B.18, "How to Add Closed Orders to Backlog Calculations"
Section B.19, "How to Track Multiple Attribute Changes in Bookings"
Section B.20, "How to Integrate Project Analytics with Financial Analytics"
Section B.23, "How To incrementally Refresh the Inventory Monthly Balance Table"
Section B.25, "How to Integrate Project Analytics with Procurement and Spend Analytics"
Section B.26, "How to Set Up Drill Down in Oracle BI EE from General Ledger to Subledger"
Section B.27, "How to Implement GL Segment and GL Account Dimensions"
Section B.29, "How to Integrate Financial Analytics with Project Analytics"
Section B.30, "How to Perform RPD Modifications for Cost and Revenue Time Grain Changes"
Section B.33, "How To Customize Extended Cross Functional Security for Accounts Payables"
Section B.34, "How To Customize Extended Cross Functional Security for Employee Expenses"
Section B.35, "How To Customize Security for Procurement Executive / Spend Analyst"
Section B.36, "How to Grant Cross Functional Access to Order Management Users"
Section B.38, "How to Implement Asset Category and Asset Location Dimension"
Section B.39, "How to Perform System Setups and Post Install Tasks for Oracle BI Applications"
Section B.40, "How to Reload the Time Dimension Tables After the Data Warehouse Is Loaded"
Section B.41, "How To Configure Scorecard Target Before Running ETL"
Section B.42, "How to Configure Order Item and Service Request Flat Files For ETL"
Section B.44, "Getting Started With Functional Configuration"
Section B.48, "Overview of Oracle Human Resources Analytics"
Section B.49, "Overview of Oracle Customer Data Management Analytics"
Section B.51, "Overview of Oracle Product Information Management Analytics"
Section B.53, "Overview of Oracle Procurement and Spend Analytics"
Section B.54, "Overview of Oracle Supply Chain and Order Management Analytics"
List of DAC Subject Areas for this Offering:
FSCM - PIM Analytics All
PIM_AN:Item Batch Import
PIM_AN:Item Fact Group
PIM_AN:Item Request Fact
List of out-of-the-box Execution Plans:
PIM_AN - Fusion V1 - Product Management Analytics
Costing and Logistics
List of DAC Subject Areas for this Offering:
COSTING_FA
SCOM_AN: Item Cost
SCOM_AN: Valuation
FIN_AN: Cost of Goods Sold
FIN_AN: GL Revenue
List of out-of-the-box Execution Plans:
SCOM_AN - Fusion V1 Costing
LOGISTICS_FA
SCOM_AN: Inventory Balance
SCOM_AN: Inventory Cycle Count
SCOM_AN: Inventory Transactions
FIN_AN: Cost of Goods Sold
List of out-of-the-box Execution Plans:
SCOM_AN - Fusion V1 Logistics
Order Management
List of DAC Subject Areas for this Offering:
ORDRMGMT_FA
SCOM_AN: Order Credit - OMORDERCREDIT_FG
SCOM_AN: Order Shipping - OMDELIVERY_FG
SCOM_AN: Order Fulfillment - OMORDERFULFILL_FG
SCOM_AN: Order Booking - OMBOOKING_FG
SCOM_AN: Order Cycle - OMCYCLE_FG
SCOM_AN: Order Orchestration Process - OMDOOPRCSS_FG
SCOM_AN: Order Customer Status History - OMCUSTSTATHIST_FG
SCOM_AN: Order Hold - OMORDERHOLD_FG
SCOM_AN: Order Invoice - OMINVOICE_FG
SCOM_AN: Order Scheduling - OMSCHEDULE_FG
SCOM_AN: Order Backlog - OMBACKLOG_FG
SCOM_AN: Order Invoice Credit - OMINVOICECREDIT_FG
FIN_AN: GL Revenue
FIN_AN: AR Transactions and Balance
List of out-of-the-box Execution Plans:
SCOM_AN - Fusion V1 - Order Management
List of DAC Subject Areas for this Offering:
Employee Expense Functional Area
PROC_SPEND_AN: Expense Credit Card
PROC_SPEND_AN: Expense Overview
PROC_SPEND_AN: Expense Violations
Procurement Functional Area
PROC_SPEND_AN: Purchase Agreement
PROC_SPEND_AN: Purchase Change Order
PROC_SPEND_AN: Purchase Cycle
PROC_SPEND_AN: Purchase Orders
PROC_SPEND_AN: Purchase Receipts
PROC_SPEND_AN: Purchase Requisition
PROC_SPEND_AN: Spend Invoice Distribution
PROC_SPEND_AN: ScoreCard
FIN_AN: AP Transactions and Balance
Sourcing Functional Area
PROC_SPEND_AN: Sourcing Negotiation
PROC_SPEND_AN: Sourcing Response
List of out-of-the-box Execution Plans:
PROC_SPEND_AN - Fusion V1 Full
PROC_SPEND_AN - Fusion V1 Procurement
PROC_SPEND_AN - Fusion V1 Sourcing
PROC_SPEND_AN - Fusion V1 Expense Report
List of DAC Subject Areas for this Offering:
CDM_AN: Customer Data Completeness
List of out-of-the-box Execution Plans:
CRM Customer Data Quality Fusion
Applies to the Functional Area: CUSTDATAMGMT_FA Customer Data Quality
List of DAC Subject Areas for this Offering:
PROJECT_AN: Revenue
PROJECT_AN: Funding
PROJECT_AN: Forecast
PROJECT_AN: Cross Charge
PROJECT_AN: Cost
PROJECT_AN: Contract
PROJECT_AN: Commitment
PROJECT_AN: Budget
PROJECT_AN: Billing
List of out-of-the-box Execution Plans:
PROJECT_AN - Fusion V1 Full:
Applicable to all Subject Areas listed above.
PROJECT_AN - Fusion V1 - Costing & Control:
Applicable to Subject Areas:
PROJECT_AN: Budget
PROJECT_AN: Commitment
PROJECT_AN: Cost
PROJECT_AN: Cross Charge
PROJECT_AN: Forecast
PROJECT_AN - Fusion V1 - Revenue and Billing:
Applicable to Subject Areas:
PROJECT_AN: Billing
PROJECT_AN: Contract
PROJECT_AN: Funding
PROJECT_AN: Revenue
List of DAC Subject Areas for this Offering:
Functional Area 'Partner Programs' can be associated with the 'PRM_AN: Partner Program' and 'SALES_AN: Opportunity Revenue' DAC Subject Areas.
Functional Area 'Partner Performance' can be associated with DAC Subject Areas 'PRM_AN: Partner Performance', and 'SALES_AN: Opportunity Revenue'.
Functional Area 'Partner Deals' can be associated with DAC Subject Areas 'PRM_AN: Partner Deals' and 'MARKETING_AN: Marketing Lead'.
List of out-of-the-box Execution Plans:
CRM PRM - Fusion
List of DAC Subject Areas for this Offering:
Fixed Asset functional area contains:
FIN_AN: Fixed Asset Balance
FIN_AN: Fixed Asset Transactions
Account Payables functional area contains:
FIN_AN: AP Transactions and Balance
FIN_AN: AP Holds
Account Receivables functional area contains:
FIN_AN: AR Transactions and Balance
Execution Plan: FIN_AN - Fusion V1 - Receivables
Profitability functional area contains:
FIN_AN: Cost of Goods Sold
FIN_AN: GL Revenue
FIN_AN: Customer Expenses
FIN_AN: Product Expenses
Note: If you are allocating expenses by customers, you must include the Universal Subject Area: FIN_AN: Customer Expenses. If you are allocating expenses by product, you must include the Universal Subject Area: FIN_AN: Product Expenses.
General Ledger functional area contains:
FIN_AN: GL Balance
FIN_AN: GL Journals
List of out-of-the-box Execution Plans:
FIN_AN - Fusion V1 - Fixed Asset
FIN_AN - Fusion V1 - Payables
FIN_AN - Fusion V1 - Profitability
FIN_AN - Fusion V1 - General Ledger
Notes
For the General Ledger functional area, if you want to extract budget data from a non-Fusion Applications source, you must use the Subject Area named FIN_AN: GL Budget in the Universal adapter.
List of DAC Subject Areas for this Offering:
HR_AN: Absence Event (Functional Area: Absence and Accrual)
HR_AN: Accrual Transaction (Functional Area: Absence and Accrual)
HR_AN: Payroll Balance (Functional Area: Payroll)
HR_AN: Workforce Event (Functional Area: Workforce Effectiveness)
FIN_AN: GL Journals (Functional Area: Workforce Effectiveness)
List of out-of-the-box Execution Plans:
HR_AN - Fusion
List of DAC Subject Areas for this Offering:
Core Marketing Functional Area:
MARKETING_AN: Campaign History
MARKETING_AN: Campaign Opportunity
MARKETING_AN: KPI
MARKETING_AN: Offer Product
MARKETING_AN: Response
Marketing Lead Functional Area:
MARKETING_AN: Marketing Lead
Opportunity Landscape Functional Area:
MARKETING_AN: Customer Purchase
SALES_AN:Agreement Contract Item Fact
SALES_AN:Asset
MARKETING_AN: Marketing Lead
Customer Interactions Management Functional Area:
SALES_AN:Interactions
Opportunity and Revenue Functional Area:
SALES_AN: Opportunity Revenue
Note: For information about Subject Areas Customer Interactions Management and Opportunity and Revenue Management, refer to ETL Notes and Additional Information for Sales Analytics.
List of out-of-the-box Execution Plans:
None.
List of Functional Areas and DAC Subject Areas for this Offering:
Customer Interactions Management
SALES_AN:Interactions
SALES_AN:Interactions Relationship Heatmap
SALES_AN:Interactions Coverage
Opportunity and Revenue Management
SALES_AN: Opportunity Revenue
SALES_AN:Interactions Opportunity
Opportunity and Revenue Management for Segmentation
SALES_AN: Opportunity Revenue Segmentation
Sales Prediction Engine
SALES_AN:Agreement Contract Item Fact
SALES_AN:Asset
SALES_AN:Service Request
SALES_AN: Opportunity Revenue
SALES_AN: Order
MARKETING_AN: Marketing Lead
Sales Forecasting
SALES_AN: Sales Forecasting
SALES_AN: Opportunity Revenue
Quota Management
SALES_AN: Resource Quota
SALES_AN: Territory Quota
SALES_AN: Opportunity Revenue
Territory Management
SALES_AN: Opportunity Revenue
SALES_AN: Territory Quota
SALES_AN: Sales Account
SALES_AN: Sales Forecasting
MARKETING_AN: Marketing Lead
Usage Accelerator
CDM_AN: Customer Data Completeness
List of out-of-the-box Execution Plans:
None.
You can enable Oracle Procurement and Spend Analytics to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics.
To enable the integration
In DAC, select the 'Fusion' source system container, for the version of the OLTP that you are using.
Display the Subject Areas tab.
You need to edit the following Subject Areas: PROC_SPEND_AN: Purchase Orders, PROC_SPEND_AN: Purchase Requisition, PROC_SPEND_AN: Spend Invoice Distribution.
For each Subject Area, do the following:
Select the Subject Area.
Display the Configuration Tags tab in the upper pane.
Select the Enable Project Dimensions tag.
Make sure that the Inactive check box is cleared.
Assemble the Subject Areas.
Click the Execute button and build the Execution Plan for the Subject Areas that you updated.
This section contains configuration steps that apply to Oracle Procurement and Spend Analytics when deployed with Oracle Spend Classification. For implementing Oracle Spend Classification and required patches, refer to the Oracle Spend Classification product documentation.
If you are not implementing Oracle Spend Classification, you might choose to remove or hide the Oracle Spend Classification integration metadata from the Presentation layer of the BI repository (for more information about removing Oracle Spend Classification metadata, see Section B.15, "How to remove or hide Oracle Spend Classification Integration Metadata").
Note: Oracle Spend Classification is not part of the core Oracle BI Applications product suite, and is not packaged with any module of Oracle BI Applications. It is a separate solution offered by Oracle, and a separate license is required. If you are interested in licensing and implementing Oracle Spend Classification, please contact your Oracle Sales Representative.
Oracle Spend Classification is a complementary product that can be used in conjunction with Oracle Procurement and Spend Analytics to improve the accuracy of Spend by converting 'unclassified' Spend into item categories. Oracle Procurement and Spend Analytics is designed to work with or without Oracle Spend Classification.
Typical procurement systems will have many PO, Invoice, and Expense Transactions without reference to item and item categories, and in most cases they might have item descriptions in a free text format. When you implement Oracle Procurement and Spend Analytics, these transactions will come into the system as 'Unclassified' because they do not have corresponding items and/or item categories. This issue is more prominent if your organization's Spend constitutes a major portion of Indirect Spend.
Oracle Procurement and Spend Analytics is installed with infrastructure required to feed data from the data warehouse to Oracle Spend Classification, and feed the classified data back into the data warehouse. This Infrastructure is provided as an additional feature for those customers who would like to take the advantage of both Oracle Procurement and Spend Analytics and Oracle Spend Classification.
If you choose not to use Oracle Spend Classification, Oracle Procurement and Spend Analytics can be deployed as a stand alone solution, and the features of Procurement and Spend Analytics can be deployed without any dependency on Oracle Spend Classification.
This section describes the Oracle Spend Classification metadata and repository metadata that is available for use with Oracle Spend Classification.
The following facts are integrated with Oracle Data Classification to enrich and automatically assign category codes.
W_AP_INV_DIST_F
W_PURCH_COST_F
W_RQSTN_LINE_COST_F
There are five types of taxonomy supported: UNSPSC, Oracle Purchasing Categories, and three custom categories. The classification resuLogical Table Source are stored in these columns:
AUTO_UNSPSC_WID
AUTO_PURCHASING_CATEGORY_WID
AUTO_CUSTOM_CATEGORY1_WID
AUTO_CUSTOM_CATEGORY2_WID
AUTO_CUSTOM_CATEGORY3_WID
In the Analytics metadata repository (RPD), the following is configured out-of-the-box.
UNSPSC, Oracle Purchasing Categories, and Custom Category1 are configured up to the Business Model and Mapping layer. The facts and dimension names are as follows:
Fact - Spend and AP Invoice Distribution
Fact - Purchasing – Order
Fact - Purchasing – Requisition
Dim - Auto UNSPSC
Dim - Auto Purchasing Category
Dim - Auto Custom Category1
In the Presentation Layer, 'Procurement and Spend - Invoice Lines' contains the columns for data classification, under the following folders:
Data Classification
Auto UNSPSC
Auto Purchasing Category
Auto Custom Category 1
Follow these steps if you want to expose UNSPSC, Oracle Purchasing Categories, and Custom Category1 for your Purchase Order and Purchase Requisition Subject Area.
To deploy UNSPCC, Oracle Purchasing Categories, and Custom Category1:
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
The OracleBIAnalyticsApps.rpd file is located in the \bifoundation\OracleBIServerComponent\coreapplication_obisn\repository folder.
In the Presentation layer, do the following:
Expand the folder 'Procurement and Spend - Invoice Lines'.
Multi-select the following folders and right click to copy:
Data Classification
Auto UNSPSC
Auto Purchasing Category
Auto Custom Category 1
To implement Oracle Spend Classification in Purchase Orders, select the folder 'Procurement and Spend - Purchase Orders' and right click to paste in the folders.
To implement Oracle Spend Classification in Purchase Requisitions, select the folder 'Procurement and Spend - Purchase Requisitions' and right click to paste in the selected folders.
Verify the new folders.
If required, re-order the folders as you would like the folders to be displayed to business users in the Presentation Services catalog.
Save and close the repository.
To deploy Custom Category2 and Custom Catogory3:
Note: This task uses the Fact_W_AP_INV_DIST_F fact as an example, though you can also apply the steps to deploy other facts.
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
The OracleBIAnalyticsApps.rpd file is located in the \bifoundation\OracleBIServerComponent\coreapplication_obisn\repository folder.
In the Physical layer, do the following:
Right click on 'Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY1' under 'Oracle Data Warehouse' and select Duplicate.
Rename it as 'Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2'.
Join dimension 'Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2' and fact 'Fact_W_AP_INV_DIST_F' using the following condition:
Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2.ROW_WID = Fact_W_AP_INV_DIST_F.'AUTO_CUSTOM_CATEGORY2_WID
In the Business Model and Mapping layer, do the following:
Immediately below table 'Dim - Auto Custom Category1', create 'Dim - Auto Custom Category2'.
Immediately below hierarchy 'Auto Custom Category1', create 'Dim - Auto Custom Category2' based on the physical table 'Dim_W_PROD_CAT_DH_AUTO_CUSTOM_CATEGORY2'.
Join 'Dim - Auto Custom Category1' to 'Fact - Spend and AP Invoice Distribution'.
Edit 'Fact - Spend and AP Invoice Distribution'. Fact_W_AP_INV_DIST_F. Display the Content tab, and set the level of 'Auto Custom Category2' to 'Custom Hierarchy Base Level'.
In the Presentation layer, do the following:
Create a sub-folder called 'Auto Custom Category 2' in the 'Procurement and Spend - Invoice Lines' folder. Edit folder and add this exact string to the Description box.
Auto Custom Category2 becomes a sub-folder of Data Classification.
Order this folder so that it is after 'Auto Custom Category 1'.
Drag the 'Dim - Auto Custom Category1' columns from the Business Model and Mapping layer into the 'Auto Custom Category 2' folder in the Presentation layer.
Save and close the repository.
Repeat steps 2 - 5 for Custom Category3.
If you are not implementing Oracle Spend Classification, Oracle recommends that you remove or hide the Oracle Spend Classification integration metadata that is included in the Presentation layer of the out-of-the-box BI repository. Hiding or deleting this metadata avoids potential confusion among business end users.
To remove or hide Oracle Spend Classification Integration Metadata:
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
Deployed RPD files are located in ORACLE_HOME\bifoundation\OracleBIServerComponent\coreapplication_obis<n>\repository.
In the Presentation layer pane, expand the folder 'Procurement and Spend - Invoice Lines'.
The Oracle Spend Classification metadata in the Physical layer consists of the following objects:
Data Classification
Auto UNSPSC
Auto Purchasing Category
Auto Custom Category 1
To remove the metadata objects listed above, right click on the objects and select Delete.
Note:
If you decide later to implement Oracle Spend Classification, you need to do the following:In the Business Model and Mapping layer, drag and drop the following dimensions from a copy of the 'Procurement and Spend - Invoice Lines' folder into the Presentation layer or your metadata repository:
Dim - Auto UNSPSC
Dim - Auto Purchasing Category
Dim - Auto Custom Category1
To hide the objects listed above from end users, right click and select Properties, then Permissions, and clear the Read permission check box for the appropriate user or group.
Note:
If you decide later to implement Oracle Spend Classification, you need to do the following:To display the following objects to end users, right click and select Properties, then Permissions, and select the Read permission check box for the appropriate user or group:
Data Classification
Auto UNSPSC
Auto Purchasing Category
Auto Custom Category 1
Save and close the metadata repository.
Before you run the initial ETL and then the incremental ETL to load the Product Transaction aggregate table, you need to configure the Product Transaction Aggregate Table, as follows.
To configure the Product Transaction Aggregate Table
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab.
Locate the Task named PLP_ProductTransactionAggregate, display the Parameters sub-tab, and make sure that the following three parameters are set as specified:
REFRESH_PERIOD = 'MONTH'
GRAIN = 'MONTH'
NUM_OF_PERIOD = 3
Note: If any of these parameters do not exist, create them as Data Type = Text with the specified Values.
To configure the Product Transaction aggregate table for the initial ETL run
Retrieve the records in the Product Transaction fact (W_PRODUCT_XACT_F
) table, and aggregate the records to the Product Transaction aggregate (W_PRODUCT_XACT_A)
table at a certain grain level.
For example, if GRAIN=MONTH then the records in the W_PRODUCT_XACT_F
fact table are retrieved and aggregated to the W_PRODUCT_XACT_A
table at a monthly level.
Running the PLP_ProductTransactionAggregate workflow implements this step.
To configure the Product Transaction aggregate table for the incremental ETL run
Delete the refreshed records from the Product Transaction aggregate (W_PRODUCT_XACT_A
) table for a certain time.
The REFRESH_PERIOD and the NUM_OF_PERIOD parameters determine the time period for the deletion.
For example, if REFRESH_PERIOD=MONTH, NUM_OF_PERIOD=1, and the date is May 15, 2005, then all records for April and the current month (May) are deleted in the W_PRODUCT_XACT_A
table.
Running the PLP_ProductTransactionAggregate workflow implements this step.
Retrieve the records in the Product Transaction fact (W_PRODUCT_XACT_F
) table, and aggregate the records to the W_PRODUCT_XACT_A
table at a certain grain level.
For example, if GRAIN=MONTH then the records in the W_PRODUCT_XACT_F
fact table are retrieved and aggregated to the W_PRODUCT_XACT_A
table at a monthly level.
Running the PLP_ProductTransactionAggregate workflow implements this step.
To add more dates, you need to understand how the Cycle Line Fact table is populated. If you want to change the dates loaded into the Cycle Line Fact (W_SALES_CYCLE_LINE_F) table, then you have to modify the PLP_SalesCycleLinesFact_Load and PLP_SalesCycleLinesFact_Load_Full mappings that take the dates from the W_* tables and load them into the Cycle Line Fact table.
To add dates to the Cycle Time table load
In Informatica PowerCenter Designer, open the PLP folder (or Configuration for Post Load Processing folder).
In Warehouse Designer, modify the table definition for the target table to verify that it has a field to store this date.
For example, if you are loading 'Validated on Date' in the W_SALES_CYCLE_LINE_F
table, then you need to create a new column, VALIDATED_ON_DT
, and modify the target definition of the W_SALES_CYCLE_LINE_F
table.
In Source Analyzer, modify the table definition of the source table to include this new column.
Continuing with the example, you would include the VALIDATED_ON_DT
column in the W_SALES_CYCLE_LINE_F
source table.
In Mapping Designer, modify the PLP_SalesCycleLinesFact_Load and PLP_SalesCycleLinesFact_Load_Full
mappings to select the new column from any of the following source tables, and load it to the W_SALES_CYCLE_LINE_F
target table:
W_SALES_ORDER_LINE_F
W_SALES_INVOICE_LINE_F
W_SALES_PICK_LINE_F
W_SALES_SCHEDULE_LINE_F
Modify the Source Qualifier SQL Override for the mapping, and map the column in the transformation to the target table.
Backlog information is stored in the W_SALES_BACKLOG_LINE_F and W_SALES_BACKLOG_HISTORY_F tables. Many types of backlog exist in the Oracle Supply Chain and Order Management Analytics application—financial backlog, operational backlog, delinquent backlog, scheduled backlog, unscheduled backlog, and blocked backlog.
By default, the Oracle Supply Chain and Order Management Analytics application only extracts open sales orders from the Sales Order Lines (W_SALES_ORDER_LINE_F) table and Sales Schedule Lines table (W_SALES_SCHEDULE_LINE_F) for backlog calculations to populate the Backlog tables. Open sales orders are defined as orders that are not complete or not cancelled. The purpose in extracting only open orders is that in most organizations those orders that are closed are no longer a part of backlog. However, if you want to extract sales orders that are marked as closed, you may remove the default filter condition from the extract mapping.
For example, assume your customer orders ten items. Six items are invoiced and shipped, but four items are placed on operational and financial backlog. This backlog status continues until one of two things happens:
The items are eventually shipped and invoiced.
The remainder of the order is canceled.
If you choose to extract sales orders that are flagged as closed, then you must remove the condition in the Backlog flag. To do so, use the following procedure.
Note: The OPR_BACKLOG_FLG and FIN_BACKLOG_FLG in the W_SALES_ORDER_LINE_F and W_SALES_SCHEDULE_LINE_F table is also used to identify which sales orders are eligible for backlog calculations.
To remove open order extract filters
In Informatica PowerCenter Designer, open the SDE_FUSION_V1_Adaptor.
Open the mplt_SA_FUSION_SalesOrderLinesFact mapplet in the Mapplet Designer.
Double-click on the EXP_SA_SalesOrderLinesFact, and display the Ports tab.
Edit the VAR_OPR_BACKLOG_FLG and remove the code 'INP_FulfillLineOpenFlag = 'Y' AND
'.
Edit the VAR_FIN_BACKLOG_FLG and remove the code 'INP_FulfillLineOpenFlag = 'Y' AND
'.
W_SALES_SCHEDULE_LINE_FS is derived from W_SALES_ORDER_LINE_FS via SDE_FUSION_SalesScheduleLinesFact_Derive. Therefore, the changes in Steps 1 to 5 are automatically effective on the same flag columns on W_SALES_SCHEDULE_LINE_F.
Validate and save your changes to the repository.
Open the PLP folder.
Open the mappings PLP_SalesBacklogLinesFact_LoadOrderLines and PLP_SalesBacklogLinesFact_LoadScheduleLines.
In the Source Qualifier, remove the condition AND W_STATUS_D.W_STATUS_CODE <> 'Closed'
from the SQL Query.
When you modify the default VAR_BOOKING_ID
column, the SQL statement is configured as follows:
TO_CHAR(INP_FulfillLineId)||'~'||TO_CHAR(INP_FulfillLineInventoryItemId)||'~'||TO_CHAR(INP_FulfillLineFulfillOrgId)
However, if you want to track changes based on more than one attribute, then in the SQL statement you must concatenate the attribute column IDs in the EXT_BOOKING_ID column. For example, if you want to track changes in Customer Account, then concatenate the technical name IDs in the EXT_BOOKING_ID column as follows:
TO_CHAR(INP_FulfillLineId)||'~'||TO_CHAR(INP_FulfillLineInventoryItemId)||'~'||TO_CHAR(INP_FulfillLineFulfillOrgId)||'~'|| TO_CHAR(INP_HeaderSoldToCustomerId))
To track dimensional attribute changes in bookings
In Informatica PowerCenter Designer, open the SDE_FUSION_V1_Adaptor.
Open the following mapping:
mplt_SA_FUSION_SalesOrderLinesFact
Double-click the appropriate Expression transformation to open the Edit Transformation box:
EXP_SA_SalesOrderLinesFact
In the Ports tab, edit the expression for the EXT_BOOKING_ID port, and enter the ID of the attribute for which you want to track changes.
If you want to track changes in multiple attributes, then concatenate the IDs of all attributes and put the concatenated value in the VAR_BOOKING_ID
column.
Validate and save your changes to the repository.
Note: W_SALES_SCHEDULE_LINE_FS is derived from W_SALES_ORDER_LINE_FS via SDE_FUSION_SalesScheduleLinesFact_Derive. Therefore, the changes in Steps 1 to 5 are automatically effective on the same column on W_SALES_SCHEDULE_LINE_F.
You can enable Oracle Financial Analytics to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics. You can configure the following Subject Areas in Oracle Financial Analytics to use Oracle Project Analytics tables:
Financials - Payables
Financials - Receivables
The following Oracle Financial Analytics fact tables integrate with Project Analytics dimensions:
W_AP_XACT_F
W_AP_BALANCE_F
W_AR_XACT_F
W_AR_AGING_INVOICE_A
To Enable Project Analytics Integration with Financial Subject Areas:
In DAC, select the 'Fusion' source system container.
Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.
Assemble the Subject Areas.
Click the Execute button and build the Execution Plan for the Subject Areas that you updated.
By default, the Oracle Supply Chain and Order Management Analytics application is configured to extract completed sales invoices when performing the Sales Invoice data extract. Fusion Applications uses a flag to indicate whether a sales invoice is complete. In particular, completed sales invoices in Fusion Applications are those where the TransactionLinePVO.TransactionHeaderCompleteFlag = 'Y'.
To extract incomplete sales invoices, as well as complete invoices, remove the extract filter statement, as follows:
In Informatica PowerCenter Designer, open the SDE_FUSION_V1_Adapter folder.
Open the mplt_BC_FUSION_SalesInvoiceLinesFact mapplet in the Mapplet Designer.
Double-click the SQ_FscmTopModelAM_FinArTopPublicModelAM_SalesInvoiceCustomerTrxLinesPVO Source Qualifier to open the Edit Transformations box.
Display the Properties tab.
For the SQL Query Transformation Attribute, select the down arrow in the Value field to display the SQL Editor box.
In the SQL box, remove the line 'AND TransactionLinePVO.TransactionHeaderCompleteFlag = 'Y'
.
Validate and save your changes to the repository.
Repeat steps 2 - 7 for the mplt_BC_FUSION_SalesInvoiceLinesFact_Primary.
The Backlog table (W_SALES_BACKLOG_LINE_F
) stores backlog data for the current month. In contrast, the Backlog History table (W_SALES_BACKLOG_LINE_F
) stores snapshots of all previous months' historical backlog data. The periods for which the Backlog History table tracks backlog data is defined by the Backlog Period Date. By default, the date is set as the last calendar day of the month; however you may configure this date. You may want to view backlog history at a more detailed level, such as by day or by week, instead of by month. The following example describes how historical backlog data is stored and what the implications are for changing the backlog time period.
Example
Assume you represent a manufacturing company where financial backlog is defined as any item that is ordered, but not invoiced. On February 1, 2001, you received an order (Sales Order #1) for 30 products. 20 were shipped and invoiced and 10 were shipped, but not invoiced. At the end of the day, there is an entry in the Backlog table and in the Backlog History table. The entry in the Backlog History table looks like that shown in Table B-1.
Table B-1 Fusion: Backlog History Table Entry as of February 1, 2001
SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
---|---|---|---|
1 |
02/01/2001 |
02/28/2001 |
10 |
On February 2, 5 of the 10 financial backlog items are invoiced and, thus, removed from the backlog. Thus, there is an update to the existing row in the Backlog History table, as shown in Table B-2.
Table B-2 Fusion: Backlog History Table Entry as of February 2, 2001
SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
---|---|---|---|
1 |
02/01/2001 |
02/28/2001 |
Old value: 10 New value: 5 |
No further activity happens until March 1st. On March 1st, the remaining 5 items on financial backlog are invoiced and removed from financial backlog. In addition, a new sales order (Sales Order #2) comes in for 50 new items. All of the items are put on financial backlog.
Even though all items from Sales Order #1 are cleared from financial backlog, the last backlog row remains in the Backlog History table. The purpose in retaining the last row is to indicate that there was backlog for this particular order. The quantity, in this case 5 items, does not tell you how many items were initially on backlog, which was 10.
For the 50 new financial backlog items, there is a new entry into the Backlog History table. So, as of February 28, 2001, the Backlog History table looks like the Table B-3.
Table B-3 Fusion: Backlog History Table Entry as of February 28, 2001
SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
---|---|---|---|
1 |
Old value: 02/01/2001 New value: 02/02/2001 |
02/28/2001 |
Old value: 10 New value: 5 |
On March 1, 30 more items are ordered (Sales Order #3), all of which are on financial backlog. The resulting Backlog History table looks like Table B-4.
Table B-4 Fusion: Backlog History Table Entry as of March 1, 2001
SALES_ORDER_NUM(Sales Order Number) | BACKLOG _DK(Backlog Date) | BACKLOG_PERIOD_DK(Backlog Period Date) | OPEN_QTY(Backlog Quantity) |
---|---|---|---|
1 |
Old value: 02/01/2001 New value: 02/02/2001 |
02/28/2001 |
5 |
2 |
03/01/2001 |
03/31/2001 |
50 |
3 |
03/01/2001 |
03/31/2001 |
30 |
Because backlog history is maintained at the monthly level, you have a partial history of your backlogs. Based on the latest state of the Backlog History table shown in Table B-4, you can see that sales order number 1 ended up with 5 financial backlogged items. You do not have visibility into what the initial financial backlogged item quantities were for the sales orders; you only have their ending quantities.
If you decide that you want to track more details on how the items moved out of backlog, then you must maintain the history at a more granular level. For instance, if you want to know the number of items that were on backlog when it was first opened, then you track the backlog history by day, instead of by month.
For example, if you maintained backlog history at the daily level, then you are able to capture that sales order 1 had an initial backlog of 10 as of February 1 and the backlog quantity shrank to 5 as of February 2. So, by capturing history at the daily level, you could then compute cycle times on how long it took to move items out of backlog. However, if you decide to capture backlog history at a more detailed level, then you may compromise performance because tracking backlog history at the daily level can increase the size of the Backlog History table exponentially.
If you choose to change the time period for which historical backlog data is kept, then you must verify that all types of backlog are being stored at the same grain, which requires modification to multiple mappings. Table B-5 provides a list of all applicable mappings and their corresponding Expression transformations that you must modify.
Table B-5 Fusion: Backlog History Applicable Mappings and Expression Transformations
Mapping | Expression Transformation |
---|---|
|
|
|
|
The backlog history period is monthly by default. The default SQL statement in the Expression transformation for the port BACKLOG_PERIOD_DK is:
TO_DECIMAL(TO_CHAR(LAST_DAY(CALENDAR_DATE),'YYYYMMDD'))
To capture a more detailed backlog history, edit the backlog period date with the following procedure. Possible periods include daily (CAL_DAY_DT
), weekly (CAL_WEEK_DT
), monthly (CAL_MONTH_DT
), and quarterly (CAL_QTR_DT
).
To incrementally refresh the Inventory Monthly Balance table:
Delete the records from the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) aggregate table for a certain time.
The GRAIN parameter determines the time period for the deletion. For example, if GRAIN=MONTH, and the date is May 15, 2005, then all records for April and the current month (May) are deleted in the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) table.
Running the PLP_InventoryMonthlyBalance workflow mapping implements this step.
Retrieve the records in the Inventory Balance (W_INVENTORY_DAILY_BAL_F
) fact table and load the records to the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) table at a certain grain level.
For example, if GRAIN=MONTH, then the month end balance records in the W_INVENTORY_DAILY_BAL_F
fact table are stored in and aggregated to the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F).
Running the PLP_InventoryMonthlyBalance
session, and the PLP_InventoryMonthlyBalance
mapping implements this step. For the current month balance, balance records of the previous day (if it is in the same month) are deleted from W_INVENTORY_MONTHLY_BAL_F, and balance records of the current day will be loaded from W_INVENTORY_BALANCE_F to W_INVENTORY_MONTHLY_BAL_F.
Running the PLP_InventoryMonthlyBalance workflow implements this step.
Remove the old records from the W_INVENTORY_DAILY_BAL_F fact table.
To remove old records you need to use the KEEP_PERIOD and the NUM_OF_PERIOD parameters. For example, if KEEP_PERIOD=MONTH, NUM_OF_PERIOD=1, and the date is May 15, 2005, then the records for April and the current month (May) are kept and the older records are deleted.
Running the PLP_InventoryDailyBalance_Trim workflow implements this step.
Note:
The trimming process is to reduce data size in the table. It is important to emphasize that you will not be able to see the old daily balance records. But you will still be able to see the month-end balance. Therefore, please make sure that you adjust the NUM_OF_PERIOD values to reflect your data volume and data recency requirements.To Configure Inventory Monthly Balance and the Inventory Transaction Aggregate Table:
Delete the records from the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) aggregate table for a certain time.
The GRAIN parameter determines the time period for the deletion. For example, if GRAIN=MONTH, and the date is May 15, 2005, then all records for April and the current month (May) are deleted in the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) table.
Running the PLP_InventoryMonthlyBalance workflow mapping implements this step.
Retrieve the records in the Inventory Balance (W_INVENTORY_DAILY_BAL_F
) fact table and load the records to the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F) table at a certain grain level.
For example, if GRAIN=MONTH, then the month end balance records in the W_INVENTORY_DAILY_BAL_F
fact table are stored in and aggregated to the Monthly Balance (W_INVENTORY_MONTHLY_BAL_F).
Running the PLP_InventoryMonthlyBalance
session, and the PLP_InventoryMonthlyBalance
mapping implements this step. For the current month balance, balance records of the previous day (if it is in the same month) are deleted from W_INVENTORY_MONTHLY_BAL_F, and balance records of the current day will be loaded from W_INVENTORY_BALANCE_F to W_INVENTORY_MONTHLY_BAL_F.
Running the PLP_InventoryMonthlyBalance workflow implements this step.
Remove the old records from the W_INVENTORY_DAILY_BAL_F fact table.
To remove old records you need to use the KEEP_PERIOD and the NUM_OF_PERIOD parameters. For example, if KEEP_PERIOD=MONTH, NUM_OF_PERIOD=1, and the date is May 15, 2005, then the records for April and the current month (May) are kept and the older records are deleted.
Running the PLP_InventoryDailyBalance_Trim workflow implements this step.
Note:
The trimming process reduces the amount of data in the table. It is important to emphasize that after data trimming you will not be able to see the old daily balance records. However, you will still be able to see the month-end balance. Therefore, please make sure that you adjust the NUM_OF_PERIOD values to reflect your data volume and data recency requirements.You can enable Oracle Supply Chain and Order Management to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics. You can configure the Oracle Supply Chain and Order Management Subject Areas listed below to join to certain Project Dimensions: Inventory Transactions (Project Dim, Task Dim, Financial Resource Dim).
The following Supply Chain fact table integrates with Project Analytics dimensions:
W_PRODUCT_XACT_F
Due to a limitation in Fusion Applications, the following Subject Areas of Oracle Supply Chain and Order Management Analytics are included in the configuration tag 'Enable Project Dimension', but are inactivated out-of-the-box. Please note that these settings are intentional, and they should not be re-activated.
SCOM_AN: Order Backlog
SCOM_AN: Order Booking
SCOM_AN: Order Credit
SCOM_AN: Order Customer Status History
SCOM_AN: Order Cycle
SCOM_AN: Order Fulfillment
SCOM_AN: Order Hold
SCOM_AN: Order Invoice
SCOM_AN: Order Invoice Credit
SCOM_AN: Order Scheduling
SCOM_AN: Order Shipping
To Enable Project Analytics Integration with Supply Chain and Order Management Analytics:
In DAC, display the Design view and select the appropriate custom container from the drop-down list.
Display the Configuration Tags tab.
Query for the tag Enable Project Dimensions.
Display the Subject Areas sub-tab.
Clear the Inactive check box for the Subject Area named 'SCOM_AN:Inventory Transactions', and save the details.
Assemble the Subject Area.
Click the Execute button and build the Execution Plan for the Subject Area that you updated.
You can enable Oracle Procurement and Spend Analytics to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics.
You can configure the following Procurement and Spend Subject Areas to join to the following Project Dimensions:
Employee Expenses - Overview (Project Dim, Task Dim, Financial Resource Dim)
Procurement and Spend - Invoice Lines (Project Dim, Task Dim)
Procurement and Spend - Purchase Orders (Project Dim, Task Dim)
Procurement and Spend - Purchase Requisitions (Project Dim, Task Dim)
The following Oracle Procurement and Spend Analytics fact tables integrate with Project Analytics dimensions:
W_AP_XACT_F
W_EXPENSE_F
W_PURCH_COST_F
W_RQSTN_LINE_COST_F
W_AP_INV_DIST_F
To Enable Project Analytics Integration with Procurement and Spend Subject Areas:
In DAC, select the Oracle Fusion Applications source system container for the version of the OLTP that you are using.
Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.
Assemble the Subject Areas.
Click the Execute button and build the Execution Plan for the Subject Areas that you updated.
To set up drill down in Oracle BI Answers from General Ledger to subledger:
Create your subledger request from 'Financials - AP Transactions' or 'Financials - AR Transactions' catalog as applicable.
In your request, add a filter on the column 'GL Journal ID' under the 'Document Details' subfolder for the 'AP Line Details' or 'AR Line Details' folder,=65 and then set the operator of the filter to 'Is Prompted'.
Build your GL Journal request from the 'Financials - GL Detail Transactions' catalog.
To your request, add the column 'GL Journal ID' under the 'Document Details' folder.
Navigate to the Column Properties of this column, and set the Value Primary Interaction property in the Column Format Interaction tab to 'NavigateAction Links'.
Add a navigation target and set the target location to the sub ledger request you created earlier.
You may add multiple navigation targets if your GL report shows transactions from multiple subledgers and you want to drill from GL to the appropriate Subledger report. For example, if your GL report shows transactions from AP, AR and Revenue, and you have three subledger reports for each of these, you can add three navigation targets (by selecting the option 'Add Navigation TargetsAction Link') and set the locations to each of these reports. Subsequently, when you run the GL report and click on the 'GL Journal ID' column value, a popup appears, where you need to click on the appropriate target based on the journal you clicked on. This will not happen automatically. For example, if you click on a journal transaction originating from AP, you need to pick the appropriate subledger report (that is, the AP report in this case) to drill into the AP report and see the details. You can add the Group Account Number attribute from GL Account Dimension to your GL report to easily identify the subledger that the GL transaction belongs to.
Note:
For COGS, the 'GL Journal ID' column is not exposed in any presentation catalogs. It is available in the business model layer of the RPD metadata under the logical tables 'Dim - GL COGS Details'. As a workaround, you can create presentation catalogs to report on detail level transactions for COGS and expose this column under the 'Document Details' folder in the presentation catalog. You use similar steps as described above to setup a drill-down from GL to COGS.Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
The OracleBIAnalyticsApps.rpd file is located in the \bifoundation\OracleBIServerComponent\coreapplication_obisn\repository folder.
Create an empty presentation catalog (e.g. Financials – GL Cost of Goods Sold). Set properties by following other presentation catalogs.
Drag 'Dim – GL COGS Details' and 'Fact - Fins - GL Cost of Goods Sold Posted' to the presentation catalog.
Drag other dimensions.
Rename the presentation table 'Dim – GL COGS Details' to 'Document Details'.
Rename the presentation table 'Fact - Fins - GL Cost of Goods Sold Posted' to 'Facts - GL Cost of Goods Sold'. Rename other dimensions if necessary.
You might also follow this same process to create a Presentation Table for Revenue to be able to drill from GL to Revenue level detail transactions.
Follow the steps in this section to implement GL Segment and GL Segment Hierarchy Dimensions.
Guidelines
If you need to report on only concatenated segments, then no configuration is required, and you can skip this section.
If you want only Group Account Num (and related attributes), then at a minimum you need to configure just the Natural Account dimension.
If you are exposing any GL Segments (including cost center, balancing, natural account), then you must go through the full configuration.
If you are exposing any Financial fact, then at a minimum you need to configure the Natural Account dimension, because you need group account number.
Perform the following configurations prior to using BI Extender.
How to set up the biextension.properties file
Update the biextension.properties file in the location <OBIEE_HOME>\Oracle_BI1\bifoundation\javahost\lib\obisintegration\biextender with your Informatica and DAC repository details, as follows:
infa.connection.sdk.repository = <Informatica Repository Name> infa.connection.sdk.domainname = <Informatica Domain Name> infa.connection.sdk.installpath = <Informatica Install Path> db.connection.type = <Leave it url by default> db.connection.repository = <Informatica Repository Name> db.connection.dbname = <Informatica Repository DB name> db.connection.host = < Informatica Repository DB Host> db.connection.port = < Informatica Repository DB Port> db.connection.dsn = <DSN to your Informatica Repository DB> db.connection.driver = <Informatica Repository DB Driver> db.connection.url = < Informatica Repository DB Driver URL> dac.connection.sdk.container = <DAC Container Name> dac.connection.sdk.primarysource = <DAC Primary Source> dac.connection.sdk.primarytarget = <DAC Primary Target> dac.connection.sdk.driver = <DAC Repository DB Driver> dac.connection.sdk.url = < DAC Repository DB Driver URL> dac.connection.sdk.dbtype = < DAC Repository DB Type>
If you are using an Oracle DB, then the jdbc URL format is jdbc:oracle:thin:@host: port/service name
or jdbc:oracle:thin:@host:port:SID
.
Update the 'ContainerMapping.xml' file in the location <OBIEE_HOME>\Oracle_BI1\bifoundation\javahost\lib\obisintegration\biextender\etlextensionmetadata\Resource\infa\emgenerationconfiguration\object with your Informatica repository name.
For more information, see Oracle Business Intelligence Server Administration Guide.
Process Flow for BI Extender
Note: A detailed explanation of each step is provided in subsequent sections.
Step 1 - import the appropriate View Objects (VOs) from the ADF data source.
Step 2 - map the VOs to the appropriate logical tables in the BMM layer.
Step 3 - map the appropriate columns in the VO to the logical columns in the BMM layer.
Step 4 - provide connection information such as user name and password for Informatica and DAC repositories.
Step 5 - click finish, and the appropriate Informatica and DAC metadata are generated and updated in the respective repositories.
There are no out-of-the-box mappings to populate W_COST_CENTER_D, W_COST_CENTER_DH, W_NATURAL_ACCOUNT_D, W_NATURAL_ACCOUNT_DH, W_BALANCING_SEGMENT_D, W_BALANCING_SEGMENT_DH, W_GL_SEGMENT_D and W_GL_SEGMENT_DH. Informatica mappings to populate these tables are generated by the BI extension process. The BI extension process to generate the ETL to populate these tables is driven by the RPD metadata.
The logical dimensions corresponding to these tables are Dim – Cost Center, Dim – Balancing Segment, Dim – Natural Account Segment and all Dim – GL Segmentxxx dimensions. Based on the setup done in the OLTP application, these dimensions can be populated from a Tree VO or from a Value Set VO. You can identify the VOs that need to be mapped for these dimensions based on the segment qualifier of these segments given in the OLTP application. The naming conventions of the VOs are given below.
For each segment which uses a valueset with trees, two VOs will be generated (Tree and TreeCode) with the following naming structure:
a. Tree VO: FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_<XXX> _VI.
b. TreeCode VO: FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_<XXX>_VI.
For each segment which uses a valueset without trees, one VO will be generated with the following naming structure:
FscmTopModelAM.AccountBIAM.FLEX_VS_<XXX>_VI.
Note: <XXX> stands for the Segment Qualifier for that segment.
This section describes how to configure the segment dimensions, which includes the three named segment dimensions Dim – Cost Center, Dim – Balancing Segment, Dim – Natural Account Segment as well as the generic segment dimensions Dim – GL Segment1, Dim – GL Segment2 and so on.
The following section explains the configuration that needs to be done when the segment is a tree based segment. In this case you will have to use a combination of Tree VO and a Tree Code VO to map to such dimensions.
Let us assume you are mapping the cost center logical dimension, Dim – Cost Center, and the segment qualifier for your Cost Center Segment in OLTP application is FA_COST_CTR. And assume you have configured this segment to be a tree segment in the OLTP application. Then the VOs that get generated should be:
FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_FA_COST_CTR_VI
FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_FA_COST_CTR_VI
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
You will need to import the appropriate VOs to map them to the corresponding dimensions. For this right click on the connection pool under the FSCM pillar oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal and use the Import Metadata option.
In the 'Select Metadata Objects' screen, select the appropriate Tree and Tree Code VOs and import them to the physical layer. Make sure the 'Import Selected Objects Only' option is selected. When the import process is complete, click Next.
In the 'Map to Logical Model' screen select the 'Core' Business Model and select the logical table you need these VOs to be mapped to. Default logical tables will be created based on the VO name. Remove them (using the 'Clear Logical Table Mapping' option) and select the appropriate logical table for both these VOs.
For example, considering the same VOs mentioned above, let us assume that you map both these VOs to Dim – Cost Center. Note that both these VOs need to be mapped to the same logical table.
Since you are configuring this segment as a Tree Segment, you need to check the 'Hierarchy' option for both these VOs. Leave the VO Type as Normal by default.
Once you have selected the logical table for this VO, the necessary logical columns will be automatically mapped to the appropriate columns in the VO in the bottom panel. For the other columns in the VO, BI Extender will try to create a new logical column by default. (Prefixed with *). Use the 'Clear Logical Column Mapping' option to remove these default mappings and retain only the required mappings.
If you have successfully imported the VOs and mapped the logical tables accordingly, then you should see the required columns mapped. For example, assuming that you have mapped Dim – Cost Center, you should see the following columns mapped.
Tree VO – Cost Center Code, Cost Center Description, Cost Center Value Set Code, Cost Center Level1 Code – Cost Center Level31 Code, Cost Center Level1 Description – Cost Center Level31 Description and Fixed Hierarchy Level.
Note: a) Tree Code and Tree Version ID will be mapped as well. Delete these logical mappings because these will again be mapped from the Tree Code VO.b) For Dim – Natural Account Segment, an additional column Group Account Number should be mappedTree Code VO – Tree Code, Tree Version ID, Tree Version Name, Start Date and End Date.
Once you have validated your mappings, click on Next and this will take you to the 'Publish to Warehouse' screen. Provide the necessary details and click on Finish to complete the extension process.
Data Warehouse
<RPD physical layer database object which is 'Oracle Data Warehouse' by default>
Informatica
User Name – <Informatica Repository User Name>
Password - <Informatica Repository Password>
Database
User Name – <Informatica Repository DB User Name>
Password - <Informatica Repository DB Password>
DAC
User Name – <DAC Repository DB User Name>
Password - <DAC Repository DB Password>
Validate and save your changes.
If you have successfully completed the extension process, you will see Informatica mappings in your repository to populate the necessary tables. You will also see DAC tasks for these mappings.
The Informatica and DAC tasks will be named like SDE_<Logical Table Name>_<Physical Target Name>. For example, taking the same example as above where we have mapped Dim – Cost Center, the mappings will be named as SDE_Dim_Cost_Center_W_COST_CENTER_D and SDE_Dim_Cost_Center_W_COST_CENTER_DH.
The following section explains the configuration that needs to be done for non tree segments. In this case you must use a Value Set VO to map to such dimensions.
Let us assume you are mapping the GL Segment1 logical dimension, Dim – GL Segment1, and the segment qualifier for your Department Segment is DEPARTMENT. And assume you have configured this segment to be a non tree segment in your OLTP, then the VO that gets generated should be:
FscmTopModelAM.AccountBIAM.FLEX_VS_DEPARTMENT_VI.
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
Import the appropriate VOs in the 'Select Metadata Objects' screen to map them to the corresponding dimensions.
In the 'Map to Logical Model' screen select the 'Core' Business Model and select the logical table you need these VOs to be mapped to. Default logical tables will be created based on the VO name. Remove them (using the 'Clear Logical Table Mapping' option) and select the appropriate logical table for both these VOs.
For example, considering the same VO mentioned above, let us assume that you map this VO to Dim – GL Segment1.
Since you are configuring this segment as a Non Tree Segment, you should NOT check the 'Hierarchy' option for these VOs. Leave the VO Type as Normal by default.
Once you have selected the logical table for this VO, the necessary logical columns will be automatically mapped to the appropriate columns in the VO in the bottom panel. For the other columns in the VO, BI Extender will try to create a new logical column by default. (Prefixed with *). Use the 'Clear Logical Column Mapping' option to remove these default mappings and retain only the required mappings.
If you have successfully imported the VOs and mapped the logical tables accordingly, then you should see the required columns mapped. For example, assuming that you have mapped Dim – GL Segment1, you should see the following columns mapped: Segment Code, Segment Description, Segment Value Set Code.
Once you have validated your mappings, click on Next and this will take you to the 'Publish to Warehouse' screen. Provide the necessary details and click on Finish to complete the extension process.
Validate and save your changes.
If you have successfully completed the extension process, then you will see Informatica mappings in your repository to populate the necessary tables. You will also see DAC tasks for these mappings. Note that since this is a Non Tree Segment, only the segment dimension mapping will be created and there will be no hierarchy dimension mapping.
The Informatica and DAC tasks will be named like SDE_<Logical Table Name>_<Physical Target Name>. For example, taking the same example as above where you have mapped Dim – GL Segment1, the mappings will be named as SDE_Dim_GL_Segment1_W_GL_SEGMENT_D.
The RPD metadata contains multiple logical tables that represent the generic GL segments, such as Dim – GL Segment1, Dim – GL Segment2 and so on. Since these logical tables are mapped to the same physical table, W_GL_SEGMENT_D, a filter should be specified in the logical table source of these logical tables to restrain the output of the logical table to represent only that particular segment. You must set the filter on the physical column SEGMENT_LOV_ID to the Value Set Codes that are applicable for that particular segment.
To specify a filter in the Business Model and Mapping layer of the Oracle BI Repository, do the following:
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
In the Business Model and Mapping layer, right click on 'Dim – GL Segmentx' logical tables and click on the 'Auto Populate Segment Filters' option. Check to see if you get a message saying that the filters were applied. In case you get a message popup saying that no segment filters were applied, follow the steps mentioned below.
Right click on the connection pool under the FSCM pillar oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal and use the Import Metadata option.
In the 'Select Metadata Objects' screen, run the 'Synchronize with the data source' option. This brings in the Value Set Codes for each segment. Click on Finish once the synchronization is complete.
Repeat step 2 to apply the filters.
You can validate to check if the filters are applied accordingly and save your changes. The filters applied should look like those in the screenshot below.
Note: You can find the list of value set codes for a particular segment by opening the segment VO table object in the physical layer of the RPD. It will be stored in the 'description' field of the table object.
While configuring the segment dimensions as described in the first two sections, if you had mapped an incorrect VO to the segment dimension and generated the Informatica and DAC metadata, you must revert the changes and re-map using the correct VO(s). To achieve this, do the following:
Delete the existing VO LTS from the corresponding logical table to bring it to the initial state.
Delete the LTS filters if any applied on the DW LTS (only for the generic segment dimensions).
Import the new VO (re-import if the VO already exists in the physical layer) and re-do the extension process as mentioned in the previous sections.
If the process completes successfully, then you will see that the previously created Informatica mapping will be deleted and a new mapping with the new VO will be created in its place.
This section explains the configuration that needs to be done in order to populate the segment columns in the GL Account dimension W_GL_ACCOUNT_D. The fact table joins to the segment dimension tables in the RPD via the GL Account dimension. So for each segment dimension populated, the corresponding columns must be populated in the GL Account dimension in order to facilitate the join.
W_GL_ACCOUNT_D has a pair of columns for each segment dimension. For example, COST_CENTER_NUM and COST_CENTER_ATTRIB for Cost Center dimension, BALANCING_SEGMENT_NUM and BALANCING_SEGMENT_ATTRIB for Balancing Segment dimension, NATURAL_ACCOUNT_NUM and NATURAL_ACCOUNT_ATTRIB for Natural Account dimension, ACCOUNT_SEGxxx_CODE and ACCOUNT_SEGxxx_ATTRIB corresponding to the generic GL Segmentxxx dimensions. These columns are also populated using BI Extender by mapping the logical columns in Dim – GL Account to the appropriate VO columns.
The Flex BI Flattened VO FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI contains the segment columns for the Code Combination IDs. Using this VO, map these segment columns in the Dim – GL Account logical dimension. This VO will also have a pair of columns for each segment. These columns will be named as <Segment Qualifier>_ and <Segment Qualifier>_c, corresponding to the segment value and the value set code. For example, if your Cost Center segment has a qualifier FA_COST_CTR, then the columns in these VOs will be FA_COST_CTR_ and FA_COST_CTR_c. You will need to map these two columns to the appropriate columns in the logical dimension.
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
You will need to import ONLY the required columns from the Flex BI Flattened VO FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI to map to the Dim – GL Account dimension.
Right click on the connection pool under the FSCM pillar oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal and use the Import Metadata option.
In the 'Select Metadata Objects' screen, select the required columns from the VO and import them to the physical layer. For example, if you have mapped two dimensions Dim – Cost Center (qualifier FA_COST_CTR) and Dim – GL Segment1 (qualifier DEPARTMENT), then import a total of 4 columns FA_COST_CTR_, FA_COST_CTR_c, DEPARTMENT_, and DEPARTMENT_c. When the import process is complete, click Next.
In the 'Map to Logical Model' screen select the 'Core' Business Model and select the logical table Dim – GL Account.
This VO has a lot of aliases created in the RPD. Therefore, default logical tables are created based on each of these objects. Remove the default mapping for these objects (using the 'Clear Logical Table Mapping' option) and map Dim – GL Account logical table to only FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI. Do not select the 'Hierarchy' option for this VO. Leave the VO Type as Normal by default.
By default, BI Extender will try to create a new logical column for each VO column imported. (Prefixed with *). Use the 'Clear Logical Column Mapping' option to remove the default mappings. Then map the imported columns to the appropriate logical columns as described below.
For example, if you have mapped the 'Cost Center' segment to 'Dim - Cost Center' and 'Department' segment to 'Dim - GL Segment1' in the above sections, you will have to map FA_COST_CTR and FA_COST_CTR_c columns in the VO to 'Cost Center Segment Code' and 'Cost Center Segment Value Set Code' logical columns respectively and 'DEPARTMENT' and 'DEPARTMENT_c' columns to 'Account Segment1 Code' and 'Account Segment1 Value Set Code' logical columns respectively.
Once you have validated your mappings, click on Next and this will take you to the 'Publish to Warehouse' screen. Provide the necessary details and click on Finish to complete the extension process.
Validate and save your changes.
If you have successfully completed the extension process, then you will see the Informatica mapping SDE_FUSION_GLAccountDimension in your repository extended to populate these new columns you have just mapped.
Note: The BI Extender architecture will allow you to map all your segment dimensions (section 3.1) and the GL Account dimension (section 3.2) together in one single flow. It is not mandatory to map only one dimension at a time.
Aggregated GL balances are populated in W_GL_BALANCE_A. The GL balances are not summarized by non-qualifier segments as installed out-of-the-box. If you want to include the non-qualifier segments, then you must modify the SQL statements as follows.
To Set Up GL Balance Segment Aggregates
Open Informatica Designer.
Open PLP_GLBalanceAggrByAcctSegCodes.
Open mplt_GLBalanceAggrByAcctSegCodes.
In the SQL code, replace 'NULL' with the GL segments that you want to use to calculate aggregate the balances.
For example:
SELECT .. W_GL_BALANCE_F.TENANT_ID, W_GL_BALANCE_F.TRANSLATED_FLAG, W_GL_ACCOUNT_D.ACCOUNT_SEG1_CODE, -- NULL W_GL_ACCOUNT_D.ACCOUNT_SEG1_ATTRIB, -- NULL W_GL_ACCOUNT_D.ACCOUNT_SEG2_CODE, -- NULL W_GL_ACCOUNT_D.ACCOUNT_SEG2_ATTRIB, -- NULL NULL, NULL FROM W_GL_BALANCE_F, ... W_GL_ACCOUNT_D, ... WHERE ... GROUP BY ... W_GL_BALANCE_F.X_CUSTOM, W_GL_BALANCE_F.TRANSLATED_FLAG, W_GL_ACCOUNT_D.ACCOUNT_SEG1_CODE, -- NULL W_GL_ACCOUNT_D.ACCOUNT_SEG1_ATTRIB, -- NULL W_GL_ACCOUNT_D.ACCOUNT_SEG2_CODE, -- NULL W_GL_ACCOUNT_D.ACCOUNT_SEG2_ATTRIB, -- NULL NULL, NULL
Open Informatica Workflow Manager.
Open session PLP_GLBalanceAggrByAcctSegCodes_Full.
Click the Mapping tab.
Click Source mplt_GLbalanceAggrByAcctSegCodes.SQ_W_GL_BALANCE_F.
Open SQL editor for 'Sql Query'.
Modify the override SQL by following step 4.
You can enable Oracle Financial Analytics to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics.
You can configure the following Oracle Financial Analytics Subject Areas to join to certain Project Dimensions:
Financials - Payables (Project, Task, Financial Resource and Expenditure Organization Dimensions)
Financials - Receivables (Contract Dim)
The following Oracle Financial Analytics fact tables integrate with Project Analytics dimensions:
W_AP_XACT_F
W_AR_XACT_F
W_AR_AGING_INVOICE_A
To Enable Project Analytics Integration with Financial Subject Areas
In DAC, select the Oracle Fusion Applications source system container for the version of the OLTP that you are using.
Select the Subject Area listed above, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.
Assemble the Subject Area.
Click the Execute button and build the Execution Plan for the Subject Area that you updated.
This topic explains how to configure the grain of Cost aggregate (W_PROJ_COST_A) and Revenue aggregate (W_PROJ_REVENUE_A) to Period, Quarter, or Year. As installed out-of-the-box, the grain of the cost aggregate and revenue aggregate are set at Fiscal Quarter. However, you can modify the grain of the aggregate to either Period or Quarter or Year. This is done by configuring the Parameter COST_TIME_GRAIN and REVENUE_TIME_GRAIN to Period or Quarter or Year. In addition, you must make the metadata repository changes that are included in this section.
Note: This section only covers the metadata repository changes that you must make using Oracle BI Administration Tool.
This task involves the following sub-tasks:
"Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Period".
"Changing the Time Grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Period".
"Changing the Time grain of the Cost Aggregate table to Fiscal/Project/Enterprise Quarter".
"Changing the Time grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Quarter".
"Changing the Time grain of the Cost Aggregate table to Fiscal/Project/Enterprise Year".
"Changing the Time grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Year".
Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Period
If the grain of Cost aggregate is at period level, the following metadata changes should be made for the Fiscal, Project, and Enterprise calendars:
Delete the joins to Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_QTR_D_Project_Quarter/Dim_W_ENT_QTR_D.
Delete the existing physical joins between Fact_Agg_W_PROJ_COST_A_Project_Cost (under logical fact 'Fact – Project Cost') to Dim_W_MCAL_QTR_D_Fiscal_Quarter (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_QTR_D_Project_Quarter (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_QTR_D (under logical dimension 'Dim - Date').
Keeping these joins will result in a loss of records when the report is at the Quarter level, because some records in the Aggregate table have their GL_ACCT_PERIOD_START_DAY_WID and ENT_QTR_START_DT_WID coincide not with the Quarter Start Date but rather with a Period Start Date.
Create joins to Dim_W_MCAL_PERIOD_D_Fiscal_Period.
Additional physical joins need to be created between following facts:
Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Fact_Agg_W_PROJ_COST_A_Project_Cost Dim_W_MCAL_PERIOD_D_Fiscal_Period
In the Business Model and Mapping layer, select the 'Dim_W_MCAL_PERIOD_D_Fiscal_Period' Logical Table Source from the 'Dim - Date Fiscal Calendar' and the 'Fact_Agg_W_PROJ_COST_A_Project_Cost' and 'Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD' Logical Table Sources in 'Fact - Project Cost and then right click and select 'physical diagram->selected objects only' and create the following physical join, then click OK.
Dim_W_MCAL_PERIOD_D_Fiscal_Period.MCAL_PERIOD_START_DAY_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost.GL_ACCT_PERIOD_START_DAY_WID
Create the following complex join between Dim_W_MCAL_PERIOD_D_Fiscal_Period and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD:
Dim_W_MCAL_PERIOD_D_Fiscal_Period.MCAL_PERIOD_END_DAY_WID >= Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.GL_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_PERIOD_D_Fiscal_Period.MCAL_CAL_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.GL_MCAL_CAL_WID
Create joins to Dim_W_MCAL_PERIOD_D_Project_Period.
Create additional physical joins between following facts:
Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Fact_Agg_W_PROJ_COST_A_Project_Cost Dim_W_MCAL_PERIOD_D_Project_Period
Create the following physical join between Dim_W_MCAL_PERIOD_D_Project_Period and Fact_Agg_W_PROJ_COST_A_Project_Cost:
Dim_W_MCAL_PERIOD_D_Project_Period.MCAL_PERIOD_START_DAY_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost.PROJ_ACCT_PERIOD_START_DAY_WID
Create the following complex join between Dim_W_MCAL_PERIOD_D_Project_Period and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD:
Dim_W_MCAL_PERIOD_D_Project_Period.MCAL_PERIOD_END_DAY_WID >= Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.PROJ_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_PERIOD_D_Project_Period.MCAL_CAL_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.PROJ_MCAL_CAL_WID
Create joins to Dim_W_ENT_PERIOD_D.
Additional physical joins need to be created between following facts:
Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Fact_Agg_W_PROJ_COST_A_Project_Cost Dim_W_ENT_PERIOD_D
In the Business Model and Mapping layer, select the 'Dim_W_ENT_PERIOD_D' Logical Table Source from the 'Dim - Date' and the 'Fact_Agg_W_PROJ_COST_A_Project_Cost' and 'Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD' Logical Table Sources in 'Fact - Project Cost and then right click and select 'physical diagram->selected objects only' and create the following physical join, then click OK.
'Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID"
Create the following complex join between Dim_W_ENT_PERIOD_D and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD:
"Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
Change the Content Aggregation Level in the Business Model and Mapping layer.
As installed out-of-the-box, the grain for cost aggregate is set to Fiscal Quarter against the dimensions Dim-Date Fiscal Calendar and Dim-Date Project Calendar. This must be modified to Period for both Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Sources. In the Business Model and Mapping layer open these two Logical Table Sources in 'Fact – Project Cost'.
Ensure that there are joins between Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Source in 'Fact - Project Cost' to Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Sources from the 'Dim - Date Fiscal Calendar', Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_MCAL_YEAR_D_Project_Year Logical Table Sources from the 'Dim - Date Project Calendar' and Dim_W_ENT_PERIOD_D /Dim_W_ENT_YEAR_D Logical Table Sources from 'Dim - Date'. These are done out-of-the-box.
Save the changes.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear OBIEE Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.
Changing the Time Grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Period
If the grain of the Revenue aggregate is at period level, then the following metadata changes should be made for the Fiscal, Project, and Enterprise calendars:
Delete the joins to Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_QTR_D_Project_Quarter.
Delete the existing physical joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue (under logical fact 'Fact – Project Revenue') to Dim_W_MCAL_QTR_D_Fiscal_Quarter (under logical dimension 'Dim – Date Fiscal Calendar') and Dim_W_MCAL_QTR_D_Project_Quarter (under logical dimension 'Dim – Date Project Calendar').
Keeping these joins will result in a loss of records when the report is at the Quarter level, because some records in the Aggregate table have their GL_ACCT_PERIOD_START_DAY_WID coincide not with the Quarter Start Date but rather with a Period Start Date.
Create joins to Dim_W_MCAL_PERIOD_D_Fiscal_Period.
Create additional physical joins between following facts:
Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Fact_Agg_W_PROJ_REVENUE_A_Revenue Dim_W_MCAL_PERIOD_D_Fiscal_Period
In the Business Model and Mapping layer, select the 'Dim_W_MCAL_PERIOD_D_Fiscal_Period' Logical Table Source from the 'Dim - Date Fiscal Calendar' and the 'Fact_Agg_W_PROJ_REVENUE_A_REVENUE' and 'Fact_Agg_W_PROJ_REVENUE_A_Revenue _ITD' Logical Table Sources in 'Fact - Project Revenue and then right click and select 'physical diagram->selected objects only' and create the following physical join, then click OK.
Dim_W_MCAL_PERIOD_D_Fiscal_Period.MCAL_PERIOD_START_DAY_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue.GL_ACCT_PERIOD_START_DAY_WID
Create the following complex join between Dim_W_MCAL_PERIOD_D_Fiscal_Period and Fact_Agg_W_PROJ_ REVENUE_A_Revenue _ITD:
Dim_W_MCAL_PERIOD_D_Fiscal_Period.MCAL_PERIOD_END_DAY_WID >= Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.GL_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_PERIOD_D_Fiscal_Period.MCAL_CAL_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.GL_MCAL_CAL_WID
Create joins to Dim_W_MCAL_PERIOD_D_Project_Period.
Create additional physical joins between following facts:
Fact_Agg_W_PROJ_ REVENUE_A_Revenue _ITD Fact_Agg_W_PROJ_ REVENUE_A_Revenue Dim_W_MCAL_PERIOD_D_Project_Period
Create the following physical join between Dim_W_MCAL_PERIOD_D_Project_Period and Fact_Agg_W_PROJ_REVENUE_A_Revenue:
Dim_W_MCAL_PERIOD_D_Project_Period.MCAL_PERIOD_START_DAY_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue.PROJ_ACCT_PERIOD_START_DAY_WID
Create the following complex join between Dim_W_MCAL_PERIOD_D_Project_Period and Fact_Agg_W_PROJ_REVENUE_A_Revenue _ITD:
Dim_W_MCAL_PERIOD_D_Project_Period.MCAL_PERIOD_END_DAY_WID >= Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.PROJ_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_PERIOD_D_Project_Period.MCAL_CAL_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.PROJ_MCAL_CAL_WID
Create joins to Dim_W_ENT_PERIOD_D.
Create additional physical joins between following facts:
Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Fact_Agg_W_PROJ_REVENUE_A_Revenue Dim_W_ENT_PERIOD_D
In the Business Model and Mapping layer, select the 'Dim_W_ENT_PERIOD_D' Logical Table Source from the 'Dim - Date' and the 'Fact_Agg_W_PROJ_REVENUE_A_Revenue' and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD' Logical Table Sources in 'Fact - Project Revenue', and then right click and select 'physical diagram->selected objects only' and create the following physical join, then click OK:
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID"
Create the following complex join between Dim_W_ENT_PERIOD_D and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD:
"Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
As installed out-of-the-box, the grain for cost aggregate is set to Fiscal Quarter against the dimensions Dim-Date Fiscal Calendar and Dim-Date Project Calendar. This must be modified to Period for both Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Sources. In the Business Model and Mapping layer open these two Logical Table Sources in 'Fact – Project Revenue'.
Make sure that there are joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Source in 'Fact - Project Revenue' to Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Sources from the 'Dim - Date Fiscal Calendar', Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_MCAL_YEAR_D_Project_Year Logical Table Sources from the 'Dim - Date Project Calendar', and Dim_W_ENT_PERIOD_D /Dim_W_ENT_YEAR_D Logical Table Sources from 'Dim - Date'. These are done out-of-the-box.
Save the changes.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear OBIEE Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.
Changing the Time grain of the Cost Aggregate table to Fiscal/Project/Enterprise Quarter
This is default out-of-the-box configuration. Please ensure that the following joins are in place.
Verify the joins to Fiscal Calendar (Dim-Date Fiscal Calendar).
In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Source from the 'Dim - Date Fiscal Calendar' and the Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Sources in 'Fact - Project Cost and then right click and select 'physical diagram->selected objects only' and verify the following out-of-the-box joins between the following objects:
Dim_W_MCAL_QTR_D_Fiscal_Quarter.MCAL_QTR_START_DAY_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost.GL_ACCT_PERIOD_START_DAY_WID Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.GL_ACCT_PERIOD_END_DAY_WID <= Dim_W_MCAL_QTR_D_Fiscal_Quarter.MCAL_QTR_END_DAY_WID AND Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.GL_MCAL_CAL_WID=Dim_W_MCAL_QTR_D_Fiscal_Quarter.MCAL_CAL_WID Dim_W_MCAL_YEAR_D_Fiscal_Year.MCAL_YEAR_END_DAY_WID >=Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.GL_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_YEAR_D_Fiscal_Year.MCAL_CAL_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.GL_MCAL_CAL_WID
Verify the joins to Project Calendar (Dim-Date Project Calendar).
In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and the Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Sources in 'Fact - Project Cost and then right click and select 'physical diagram->selected objects only' and verify the following out-of-the-box joins between the following objects.
Dim_W_MCAL_QTR_D_Project_Quarter.MCAL_QTR_START_DAY_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost.PROJ_ACCT_PERIOD_START_DAY_WID Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.PROJ_ACCT_PERIOD_END_DAY_WID <= Dim_W_MCAL_QTR_D_Project_Quarter.MCAL_QTR_END_DAY_WID AND Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.PROJ_MCAL_CAL_WID=Dim_W_MCAL_QTR_D_Project_Quarter.MCAL_CAL_WID Dim_W_MCAL_YEAR_D_Project_Year.MCAL_YEAR_END_DAY_WID >= Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.PROJ_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_YEAR_D_Project_Year.MCAL_CAL_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD.PROJ_MCAL_CAL_WID
Verify the joins to Enterprise Calendar (Dim-Date).
In the Business Model and Mapping layer, select the Dim_W_ENT_QTR_D / Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date' and the Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Sources in 'Fact - Project Cost and then right click and select 'physical diagram->selected objects only' and verify the following out-of-the-box joins between the following objects.
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID" "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_END_DT_WID" "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID"
Removing extraneous joins.
Delete any additional time dimension joins between Time Dimension physical table aliases in Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date to Project Cost Fact physical table aliases Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD under Logical cost 'Fact - Project Cost'.
Change the Content Aggregation Level in the Business Model and Mapping layer.
Out-of-the-box, the grain for cost aggregate is set to Quarter against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.
Instead of Fiscal/Project Period you must set this to Fiscal Quarter for Dim – Date Fiscal Calendar, Project Quarter for Dim – Date Project Calendar and Enterprise Quarter for Dim - Date.
Save the changes.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear OBIEE Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.
Changing the Time grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Quarter
This is default out-of-the-box configuration. Please ensure that the following joins are in place.
Verify the joins to Fiscal Calendar (Dim-Date Fiscal Calendar).
In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Source from the 'Dim - Date Fiscal Calendar' and the Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Sources in 'Fact - Project Revenue and then right click and select 'physical diagram->selected objects only' and verify the following out-of-the-box joins between the following objects.
Dim_W_MCAL_QTR_D_Fiscal_Quarter.MCAL_QTR_START_DAY_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue.GL_ACCT_PERIOD_START_DAY_WID Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.GL_ACCT_PERIOD_END_DAY_WID <= Dim_W_MCAL_QTR_D_Fiscal_Quarter.MCAL_QTR_END_DAY_WID AND Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.GL_MCAL_CAL_WID=Dim_W_MCAL_QTR_D_Fiscal_Quarter.MCAL_CAL_WID Dim_W_MCAL_YEAR_D_Fiscal_Year.MCAL_YEAR_END_DAY_WID >=Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.GL_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_YEAR_D_Fiscal_Year.MCAL_CAL_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.GL_MCAL_CAL_WID
Verify the joins to Project Calendar (Dim-Date Project Calendar).
In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and the Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Sources in 'Fact - Project Revenue and then right click and select 'physical diagram->selected objects only' and verify the following out-of-the-box joins between the following objects.
Dim_W_MCAL_QTR_D_Project_Quarter.MCAL_QTR_START_DAY_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue.PROJ_ACCT_PERIOD_START_DAY_WID Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.PROJ_ACCT_PERIOD_END_DAY_WID <= Dim_W_MCAL_QTR_D_Project_Quarter.MCAL_QTR_END_DAY_WID AND Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.PROJ_MCAL_CAL_WID=Dim_W_MCAL_QTR_D_Project_Quarter.MCAL_CAL_WID Dim_W_MCAL_YEAR_D_Project_Year.MCAL_YEAR_END_DAY_WID >= Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.PROJ_ACCT_PERIOD_END_DAY_WID AND Dim_W_MCAL_YEAR_D_Project_Year.MCAL_CAL_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD.PROJ_MCAL_CAL_WID
Verify the joins to Enterprise Calendar (Dim-Date).
In the Business Model and Mapping layer, select the Dim_W_ENT_QTR_D / Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date' and the Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Sources in 'Fact - Project Revenue and then right click and select 'physical diagram->selected objects only' and verify the following out-of-the-box joins between the following objects.
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID" "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_END_DT_WID" "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID"
Removing extraneous joins.
Delete any additional time dimension joins between Time Dimension physical table aliases in Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim – Date to Project Revenue Fact physical table aliases Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD under Logical cost 'Fact - Project Revenue'.
Change the Content Aggregation Level in the Business Model and Mapping layer.
Out-of-the-box, the grain for revenue aggregate is set to Quarter against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.
Instead of Fiscal/Project Period, you must set this to Fiscal Quarter for Dim – Date Fiscal Calendar, Project Quarter for Dim – Date Project Calendar and Enterprise Quarter for Dim - Date.
Save the changes.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear OBIEE Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.
Changing the Time grain of the Cost Aggregate table to Fiscal/Project/Enterprise Year
Delete joins to Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_ENT_QTR_D.
Delete the existing physical joins between Fact_Agg_W_PROJ_COST_A_Project_Cost (under logical fact 'Fact – Project Cost') to Dim_W_MCAL_QTR_D_Fiscal_Quarter (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_QTR_D_Project_Quarter (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_QTR_D (under logical dimension 'Dim – Date').
Delete the existing physical joins between Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD (under logical fact 'Fact – Project Cost') to Dim_W_MCAL_QTR_D_Fiscal_Quarter (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_QTR_D_Project_Quarter (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_QTR_D (under logical dimension 'Dim – Date').
Create joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year/ Dim_W_ENT_YEAR_D.
Additional physical joins need to be created between following Logical Table Source fact Fact_Agg_W_PROJ_COST_A_Project_Cost (under logical fact 'Fact – Project Cost') and Dim_W_MCAL_YEAR_D_Fiscal_Year (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_YEAR_D_Project_Year (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_YEAR_D (under logical dimension 'Dim – Date').
The following joins need to be created (foreign key join):
Dim_W_MCAL_YEAR_D_Fiscal_Year.MCAL_YEAR_START_DAY_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost.GL_ACCT_PERIOD_START_DAY_WID Dim_W_MCAL_YEAR_D_Project_Year.MCAL_YEAR_START_DAY_WID = Fact_Agg_W_PROJ_COST_A_Project_Cost.PROJ_ACCT_PERIOD_START_DAY_WID "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID"
Verify the joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year/ Dim_W_ENT_YEAR_D.
Ensure that there are joins between Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Source in 'Fact - Project Cost' to Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Source from the 'Dim - Date Fiscal Calendar', Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date'.These are done out-of-the-box.
Delete any additional time dimension joins between Time Dimension Logical Table Sources in Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim – Date to Logical Table Sources Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD under Logical cost 'Fact - Project Cost'.
Changing Content Aggregation Level in the Business Model and Mapping layer
As installed out-of-the-box, the grain for cost aggregate is set to Quarter against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.
Instead of Fiscal/Project Period you must set this to Fiscal Year for Dim – Date Fiscal Calendar, Project Year for Dim – Date Project Calendar and Enterprise year for Dim - Date.
Save the changes.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear OBIEE Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.
Changing the Time grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Year
Delete the joins to Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_QTR_D_Project_Quarter.
Delete the existing physical joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue (under logical fact 'Fact – Project Revenue') to Dim_W_MCAL_QTR_D_Fiscal_Quarter (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_QTR_D_Project_Quarter (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_QTR_D (under logical dimension 'Dim – Date').
Delete the existing physical joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD (under logical fact 'Fact – Project Revenue') to Dim_W_MCAL_QTR_D_Fiscal_Quarter (under logical dimension 'Dim – Date Fiscal Calendar', Dim_W_MCAL_QTR_D_Project_Quarter (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_QTR_D (under logical dimension 'Dim – Date').
Create joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year.
Additional physical joins need to be created between following Logical Table Source fact Fact_Agg_W_PROJ_REVENUE_A_Revenue (under logical fact 'Fact – Project Cost') and Dim_W_MCAL_YEAR_D_Fiscal_Year (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_YEAR_D_Project_Year (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_YEAR_D (under logical dimension 'Dim – Date').
The following joins need to be created (foreign key join):
Dim_W_MCAL_YEAR_D_Fiscal_Year.MCAL_YEAR_START_DAY_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue.GL_ACCT_PERIOD_START_DAY_WID Dim_W_MCAL_YEAR_D_Project_Year.MCAL_YEAR_START_DAY_WID = Fact_Agg_W_PROJ_REVENUE_A_Revenue.PROJ_ACCT_PERIOD_START_DAY_WID "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID"
Verify the joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year.
Ensure that there are joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Source in 'Fact - Project Revenue' to Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Source from the 'Dim - Date Fiscal Calendar', Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date'. These are done out-of-the-box.
Delete any additional time dimension joins between Time Dimension Logical Table Source in Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim – Date to Logical Table Sources Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD under Logical cost 'Fact - Project Revenue'.
Changing Content Aggregation Level in the Business Model and Mapping layer
As installed out-of-the-box, the grain for revenue aggregate is set to Quarter against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.
Instead of Fiscal/Project Period you must set this to Fiscal Year for Dim – Date Fiscal Calendar and Project Year for Dim – Date Project Calendar and Enterprise year for Dim - Date.
Save the changes.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear OBIEE Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.
You must assign the following group account numbers in Fusion Applications for HR: (You can skip this task if you have already completed this for General Ledger.)
'CONT EXP' for 'Contracting Expenses'
'COGS' for 'Cost of Goods Sold'
'DEPCN' for 'Depreciation Expenses'
'EMP BENFT' for 'Employee Benefits Related Expenses'
'EMP OVERTIME' for 'Employee Overtime Expenses'
'EMP SUPP' for Employee Support Expenses'
'GEN PAYROLL' for General Admin and Other Payroll'
'MISC OPER EXP' for 'Miscellaneous Operating Expenses'
'MKTG PAYROLL' for 'Payroll Expenses'
'SLS PAYROLL' for 'Payroll Expenses'
'R&D PAYROLL' for 'Payroll Expenses' (GEN PAYROLL is already listed)
'VARIANCE EXP' for 'Product Variance Expenses'
'REVENUE' for 'Revenue'
Note: 'Other Operating Expenses' is a derived column. It does not need a group account number assignment.
How to Assign Group Account Numbers to Natural Accounts:
Login to Fusion Applications.
Click the Applcore menu.
Identify the value set used for your natural account.
Open the window to maintain value set values.
Assign financial categories to each natural account from the list of values.
The following group account numbers (financial categories) are seeded:
ACC DEPCN - Accumulated Depreciation ACC LIAB - Accrued Liabilities AP - Account Payables AR - Account Receivables CASH - Cash CMMN STOCK - Common Stock COGS - Cost Of Goods Sold CONT EXP - Contracting Expenses DEFERRED COGS - Deferred Cost of Goods Sold DEFERRED REVENUE - Deferred Revenue DEPCN - Depreciation Expenses EMP BENFT - Employee Benefits Related Expenses EMP OVERTIME - Employee Overtime EMP SUPP - Employee Support and Cafeteria Expenses FG INV - Finished Goods Inventory, FREIGHT - Freight Expenses GEN PAYROLL - General Admin And Other Payroll GOODWILL - Goodwill INC TAX - Income Tax INT EXP - Interest Expenses LT DEBT - Long Term Debt MISC OPER EXP - Miscellaneous Operating Expenses MKTG PAYROLL - Marketing Payroll OTHER ASSET - Other Assets OTHER CA - Other Current Assets OTHER CL - Other Current Liabilities OTHER EQUITY - Other Equity Related OTHER INC - Other Income OTHER LIAB - Other Liabilities OTHER MKTG EXP - Other Marketing Expenses OTHER R&D EXP - Other R&D Expenses OTHER SLS EXP - Other Sales Expenses PPAID EXP - Prepaid Expenses PPE - PPE PREF STOCK - Preferred Stock PURCH - Purch R&D PAYROLL - R&D Payroll RET EARNING - Retained Earning REVENUE - Sales Revenue RM CONS - RM Cons RM INV - Raw Material Inventory SLS PAYROLL - Sales Payroll ST BORR - ST Borr TAX LIAB - Tax Liabilities TRAVEL & ENT EXP - Travel & Entertainment Expenses VARIANCE EXP - Product Variance Expenses WIP INV - WIP Inventory
Assign Financial Categories (Group Account Num) to natural accounts as follows. You need access to Fusion Applications - Application Core Setup.
In Fusion Applications, go to Application Core Setup.
Click Manage Key Flexfields.
Search Key Flexfield Code 'GL#'
Click Manage Structure Instance.
Find a structure instance for your chart of accounts.
Select the structure instance and click Edit.
Click Value Set Code for the Account segment to open Manage Value Sets.
Click Manage Values.
Search a natural account to which you want to assign financial categories.
Select a value and click Edit.
Assign a financial category from the list of values.
Save the changes.
Note: The following section describes a post-installation and optional configuration task.
To facilitate procurement users (such as Category Managers and Procurement Managers) to perform deeper and cross functional analysis apart from their regular duty, Oracle Procurement and Spend Analytics includes configured data and functional security out-of-the-box to access the accounts payable transactions (such as invoices, payments, payment schedules) through extended duty roles. To implement these duties, please follow the instructions in this task.
Understanding Extended Duty Roles: Seeded security roles for Oracle BI Applications for Fusion Applications includes the following additional duty roles. These extended roles are not mapped to any enterprise job roles out-of-the-box, but they are pre-configured within Oracle BI Applications to enforce object and data level security for Accounts Payables.
'Procurement Managerial Extended Analysis Duty' role (Role name: OBIA_PROCUREMENT_MANAGERIAL_ANALYSIS_DUTY) – This BI Duty role enables users to perform cross functional analysis outside of Category Management. Internally, data security on Oracle BI Applications is implemented using 'Extended Procurement and Payable Business Unit Data Security' (Role name: OBIA_EXTENDED_PROCUREMENT_AND_PAYABLE_BUSINESS_UNIT_DATA_SECURITY).
'Category Manager Extended Analysis Duty' role (Role name: OBIA_CATEGORY_MANAGER_ANALYSIS_DUTY) – This BI Duty role enables to perform cross functional analysis outside of Procurement Management. Internally, data security on Oracle BI Applications is implemented using 'Extended Procurement and Payable Business Unit Data Security' (Role name: OBIA_EXTENDED_PROCUREMENT_AND_PAYABLE_BUSINESS_UNIT_DATA_SECURITY).
'Procurement Executive Analysis Duty' role (Role name: OBIA_PROCUREMENT_EXECUTIVE_ANALYSIS_DUTY) to act also like a Spend Analyst/ Executive duty. Internally, data security on Oracle BI Applications is implemented using 'Extended Procurement and Spend Business Unit Data Security' (Role name: OBIA_EXTENDED_PROCUREMENT_AND_SPEND_BUSINESS_UNIT_DATA_SECURITY). This data security role enables cross functional analysis by manage spend Business Unit Data Security.
Follow the steps below to implement 'Procurement Executive Analysis Duty' role:
Assign BI duty 'Procurement Managerial Extended Analysis Duty' to Fusion Applications job role, 'Procurement Manager' or similar.
Assign BI duty 'Category Manager Extended Analysis Duty' to Fusion Applications job role, 'Category Manager' or similar.
Create 'VP of Procurement' or similar executive job role in your Fusion Applications deployment and assign BI duty 'Procurement Executive Analysis Duty' to 'VP of Procurement'.
Assign appropriate Fusion Applications duty roles to the job role - 'VP of Procurement' and assign BU privileges. Data security of 'Procurement Executive Analysis Duty' (OBIA duty role) is controlled by the BUs assigned to the user in the agent access 'manage spend' action.
Customize Presentation catalog permissions (for Supplier Performance – AP Transactions related content) and Subject Area permissions as desired for above mentioned roles.
For more information on how to create and manage job roles in Fusion Applications, refer to section 'Understanding How to Secure Oracle Fusion Applications' in Oracle Fusion Applications Administrator's Guide. For more information on how to define and customize security in Oracle BI Applications, refer to Oracle Fusion Middleware Reference Guide for Oracle Business Intelligence Applications.
Note: The following section describes a post-installation and optional configuration task.
To facilitate procurement users (such as Procurement VP or Spend Analyst) to perform deeper and cross functional analysis apart from their regular duty, Oracle Procurement and Spend Analytics have configured data and functional security out-of-the-box to access the employee expenses transactions (such as expense report, credit card transaction and expense violation) through extended duty roles. If you would like to provision such duty to the procurement and spend users, please follow the instructions in this task.
Understanding Extended Duty Roles: BI seeded duty roles for Fusion Applications includes a 'Procurement Executive Analysis Duty' role (Role name: OBIA_PROCUREMENT_EXECUTIVE_ANALYSIS_DUTY) to act like a Spend Analyst/ Executive duty. This extended role is not mapped to any enterprise job roles out-of-the-box, but it is pre-configured within Oracle BI Applications to enforce object and data level security for Employee Expenses. Internally, data security is implemented using 'Extended Procurement and Spend Business Unit Data Security' (Role name: OBIA_EXTENDED_PROCUREMENT_AND_SPEND_BUSINESS_UNIT_DATA_SECURITY). This data security role enables cross functional analysis by manage spend Business Unit Data Security.
Follow the steps below to implement 'Procurement Executive Analysis Duty' role:
Create 'VP of Procurement' or similar executive job role in your Fusion Applications deployment and assign BI duty 'Procurement Executive Analysis Duty' to 'VP of Procurement'.
Assign appropriate Fusion Applications duty roles to the job role - 'VP of Procurement' and assign BU privileges. Data security of 'Procurement Executive Analysis Duty' (OBIA duty role) is controlled by the BUs assigned to the user in the agent access 'manage spend' action.
Customize Presentation catalog permissions (for Employee Expense dashboard and related reports) and Subject Area permissions as desired for 'Procurement Executive Analysis Duty' role.
For more information on how to create and manage job roles in Fusion Applications, refer to section 'Understanding How to Secure Oracle Fusion Applications' in Oracle Fusion Applications Administrator's Guide. For more information on how to define and customize security in Oracle BI Applications, refer to Oracle Fusion Middleware Reference Guide for Oracle Business Intelligence Applications.
Note: The following section describes a post-installation and optional configuration task.
To enable procurement users (such as Procurement VP or Spend Analyst) to perform deeper and cross functional analysis apart from their regular duties, Oracle Procurement and Spend Analytics includes data and functional security out-of-the-box to access the employee expenses transactions (such as expense report, credit card transaction and expense violation) through extended duty roles. If you would like to provision such duty to the procurement and spend users, then please follow the instructions in this task.
Understanding Extended Duty Roles: BI seeded duty roles for Fusion Applications includes 'Procurement Executive Analysis Duty' role (Role name: OBIA_PROCUREMENT_EXECUTIVE_ANALYSIS_DUTY) to act also like a Spend Analyst/ Executive duty. This extended role is not mapped to any enterprise job roles out-of-the-box, but it is pre-configured within Oracle BI Applications to enforce object and data level security for Spend Analysis. Internally, data security on Oracle BI Applications is implemented using 'Extended Procurement and Spend Business Unit Data Security' (Role name: OBIA_EXTENDED_PROCUREMENT_AND_SPEND_BUSINESS_UNIT_DATA_SECURITY). This data security role enables cross functional analysis by manage spend Business Unit Data Security.
Follow the steps below to implement 'Procurement Executive Analysis Duty' role:
Create 'VP of Procurement' or similar executive job role in your Fusion Applications deployment and assign BI duty 'Procurement Executive Analysis Duty' to 'VP of Procurement'.
Assign appropriate Fusion Applications duty roles to the job role - 'VP of Procurement' and assign BU privileges. Data security of 'Procurement Executive Analysis Duty' (OBIA duty role) is controlled by the BUs assigned to the user in the agent access 'manage spend' action.
Customize Presentation catalog permissions (for Spend Analyzer dashboard and related reports) and Subject Area permissions as desired for 'Procurement Executive Analysis Duty' role.
For more information on how to create and manage job roles in Fusion Applications, refer to section 'Understanding How to Secure Oracle Fusion Applications' in Oracle Fusion Applications Administrator's Guide. For more information on how to define and customize security in Oracle BI Applications, refer to Oracle Fusion Middleware Reference Guide for Oracle Business Intelligence Applications.
Note: The following section describes a post-installation and optional configuration task.
To facilitate OM users (such as Order Manager and Shipping Manager) to perform deeper and cross functional analysis apart from their regular duty, Oracle Supply Chain and Order Management Analytics has configured data and functional security out-of-the-box to access cross functional information (such as inventory, backlog , shipping information) through extended duty roles. If you would like to provision such a duty to the Order Management users, please follow the instructions in this task.
Understanding Extended Duty Roles: Seeded security roles for Oracle BI Applications for Fusion Applications includes the following additional duty roles. These extended roles are not mapped to any enterprise job roles out-of-the-box, but they are pre-configured within Oracle BI Applications to enforce object and data level security for Inventory transactions.
'Extended Order Management Analysis Duty' role (Role name: OBIA_EXTENDED_ORDER_MANAGEMENT_ANALYSIS_DUTY) – This duty role provides cross-module access to the order manager job role for stand-alone Oracle BI Applications content. The cross-module access will include invoice, inventory, backlog, AR and shipping information. Data security on Oracle BI Applications is implemented using OBIA_ORDER_FULFILLMENT_ORCHESTRATION_BUSINESS_UNIT_DATA_SECURITY.
'Extended Shipping Management Analysis Duty' role (Role name: OBIA_EXTENDED_SHIPPING_MANAGEMENT_ANALYSIS_DUTY) – This duty role provides cross-module access to the shipping manager job role for stand-alone Oracle BI Applications content. The cross-module access will include inventory, backlog and orders information. Data security on Oracle BI Applications is implemented using 'OBIA_INVENTORY_ORGANIZATION_SHIPMENT_DATA_SECURITY'.
Follow the steps below to implement Extended Duty roles in Supply Chain and Order Management Analytics
Assign BI duty 'OBIA_EXTENDED_ORDER_MANAGEMENT_ANALYSIS_DUTY' to Fusion Applications job role, 'Order Manager' or similar.
Assign BI duty 'OBIA_EXTENDED_SHIPPING_MANAGEMENT_ANALYSIS_DUTY' to Fusion Applications job role, 'Shipping Manager' or similar.
Assign appropriate Fusion Applications duty roles to the job role - 'Order Manager' and assign BU privileges. Data security of 'OBIA_ORDER_MANAGEMENT_ANALYSIS_DUTY' (OBIA duty role) is controlled by the BUs assigned to the user in the agent access 'manage orders' action.
Customize Presentation catalog permissions for subject areas including cross functional content (for example Sales - Inventory and Backlog ) and Subject Area permissions as desired for above mentioned roles.
For more information on how to create and manage job roles in Fusion Applications, refer to section 'Understanding How to Secure Oracle Fusion Applications' in Oracle Fusion Applications Administrator's Guide. For more information on how to define and customize security in Oracle BI Applications, refer to Oracle Fusion Middleware Reference Guide for Oracle Business Intelligence Applications.
In Oracle Business Intelligence Applications, in order for a BI user with VP of HR job role to see GL data, he/she needs to be provisioned with GL data role pertaining to a Financial Analyst job role. The GL data role provisioned will control the data security that will be enforced upon the GL data the user is trying to view. To understand more details on how GL data are provisioned in Fusion Applications, please refer to GL User's Guide for more information.
This section explains how to configure Asset Category Dimension and Asset Location Dimension.
How to implement Fixed Asset Category Dimension
This section explains the configurations that are required to populate the segment columns in Asset Category Dimension, W_ASSET_CATEGORY_D.
The Flex BI Flattened VO, FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI will have the segment columns for the Code Combination IDs. You will be able to see the segment columns only when the segments are configured in Fusion Applications. Using this VO, map the segment columns in the logical dimension, 'Dim - Asset Category'. This VO will also have a column for each segment. These columns will be named as s_L_2, VSC_s_L_3 and so on. You will need to map these columns to the appropriate columns in the logical dimension.
Note: The segment names generated by the Flex Modeler are dependent on the customer setup. The actual names getting generated in the Flex BI VOs are dependent on how you set up the names for the segments. Therefore, the segment names given in this document may not match with the names you see when you import the VOs.
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
You only need to import the required columns from the Flex BI Flattened VO FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI to map to the 'Dim - Asset Category' dimension, as described below.
Right click on the connection pool under the FSCM pillar oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal and use the Import Metadata option.
In the 'Select Metadata Objects' screen, select the required columns from the VO and import them to the physical layer. When the import process is complete, click on Next.
In the 'Map to Logical Model' screen select the 'Core' Business Model and select the logical table 'Dim - Asset Category'. Map 'Dim - Asset Category' logical table to FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI. Do not select the 'Hierarchy' option for this VO. Leave the VO Type as Normal by default.
By default, BI Extender will try to create a new logical column for each VO column imported. (Prefixed with *). Use the 'Clear Logical Column Mapping' option to remove the default mappings. Then map the imported columns to the appropriate logical columns as described below.
For Example: s_L_2 represents the Segment2 and it should be mapped to the Dim-Asset Category Logical column, Segment2. VSC_s_L_3 represents the Segment3 and it should be mapped to the Dim-Asset Category Logical column, Segment3. Similarly, do the mapping for other columns.
When you have validated your mappings, click on Next and this will take you to the 'Publish to Warehouse' screen. Provide the necessary details and click on Finish to complete the extension process:
Informatica
User Name – <Informatica Repository User Name>
Password - <Informatica Repository Password>
Database
User Name – <Informatica Repository DB User Name>
Password - <Informatica Repository DB Password>
DAC
User Name – <DAC Repository DB User Name>
Password - <DAC Repository DB Password>
Validate and save your changes.
If you have successfully completed the extension process, you will see the Informatica mapping SDE_FUSION_FixedAssetCategoryDimension in your repository extended to populate these new columns that you have just mapped.
How to implement Fixed Location Dimension
This section explains the configurations that are required to populate the segment columns in Asset Location Dimension, W_ASSET_LOCATION_D.
The Flex BI Flattened VO, FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI will have the segment columns for the Code Combination IDs. You will be able to see the segment columns only when the segments are configured in Fusion Applications. Using this VO, map the segment columns in the logical dimension, 'Dim - Asset Location'. This VO will also have a column for each segment. These columns will be named as s_Sub_2, VSC_s_Sub3 and so on; you need to map these columns to the appropriate columns in the logical dimension.
Note: The segment names generated by the Flex Modeler are dependent on the Customer setup. The actual names getting generated in the Flex BI VOs are dependent on how you set up the names for the segments. Therefore, the segment names given in this document may not match with the names you see when you import the VOs.
Using Oracle BI Administration Tool, open the RPD file (for example, OracleBIAnalyticsApps.rpd).
You only need to import the required columns from the Flex BI Flattened VO FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI to map to the 'Dim - Asset Location' dimension.
Right click on the connection pool under the FSCM pillar oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal and use the Import Metadata option.
In the 'Select Metadata Objects' screen, select the required columns from the VO and import them to the physical layer. When the import process is complete, click on Next.
In the 'Map to Logical Model' screen select the 'Core' Business Model and select the logical table 'Dim - Asset Location'. Map 'Dim - Asset Location' logical table to FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI. Do not select the 'Hierarchy' option for this VO. Leave the VO Type as Normal by default.
By default, BI Extender will try to create a new logical column for each VO column imported. (Prefixed with *). Use the 'Clear Logical Column Mapping' option to remove the default mappings. Then map the imported columns to the appropriate logical columns as described below.
For Example: s_Sub_2 represents the Segment2 and it should be mapped to the Dim-Asset Location Logical column, Segment2. VSC_s_Sub3 represents the Segment3 and it should be mapped to the Dim-Asset Location Logical column, Segment3. Similarly do the mapping for other columns.
When you have validated your mappings, click on Next and this will take you to the 'Publish to Warehouse' screen. Provide the necessary details and click on Finish to complete the extension process.
Data Warehouse
<RPD physical layer database object which is 'Oracle Data Warehouse' by default>
Informatica
User Name – <Informatica Repository User Name>
Password - <Informatica Repository Password>
Database
User Name – <Informatica Repository DB User Name>
Password - <Informatica Repository DB Password>
DAC
User Name – <DAC Repository DB User Name>
Password - <DAC Repository DB Password>
Validate and save your changes.
If you have successfully completed the extension process, you will see the Informatica mapping SDE_FUSION_FixedAssetLocationDimension in your repository extended to populate these new columns that you have just mapped.
Make sure that you have followed the steps in Chapter 4 of Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications.
Steps for 11g Fusion Applications:
You will see a Subject Areas named Common Dimension and Extend Day Dimension. If you have Multiple calendar in your Day Dimension, then choose the configuration tag Extend Day Dimension Multiple Calendar Support, or else remove it. Then assemble the Subject Area.
Choose the Task Sil_DayDimension_XTND. Choose a new START_DATE (= @ END_DATE +1) and new END_DATE and set the parameter values.
Choose the Task SDE_FUSION_TimePeriodMCalPeriod_XTND. Retain the START_DATE and choose a new END_DATE.
Build the corresponding Execution Plan with same name.
Remember to change FILE_MCAL_CAL_D, FILE_MCAL_CONTEXT_G, FILE_MCAL_PERIOD_DS (these 3 in universal) and FILE_MCAL_CONFIG_G, in case you use them as source.
Execution Plan steps for an Fusion Applications container:
You will see a subject Area 'Common-Extend Day Dimension'. If you have Multiple calendars in your Day Dimension, then choose the configuration tag Extend Day Dimension Multiple Calendar Support, or else remove it. Then, assemble the Subject Area.
Choose the Task Sil_DayDimension_XTND. Choose a new START_DATE (= END_DATE +1) and new END_DATE and set the parameter values.
Choose the Task SDE_FUSION_TimePeriodMCalPeriod_XTND. Retain the START_DATE and choose a new END_DATE.
Build the corresponding EP with name 'Common-Extend Day Dimension Fusion'.
Open <INFASRC>\file_purch_scorecard_target.csv to specify the target for the KPI. The supported dimensions are time dimension and procurement Business Unit dimension. You must specify the following values in the source file in the required data format:
Quarter start date
Procurement business Unit ID
KPI name
KPI target value
The following KPIs are supported for KPI target value:
# of Negotiation Lines Awarded Per Category
# of POs Per Buyer
# of Suppliers Per Category
% of Fulfilled Requisition Lines Past Expected Date
% of Late Receipts
% of Processed Requisition Lines Past Expected Date
% of Realized Savings
% of Supplier Diversity Spend
% of Unfulfilled Requisition Lines Past Expected Date
Average Negotiation Cycle Time
Average Requisition to Receipt Cycle Time
Electronic Invoice %
Manual Requisition Lines Rate
Non-Agreement Purchase Rate
Overall Accepted %
Perfect Invoices %
Purchase Order Schedule Line Return Rate
Received On Time %
Background
In Fusion Applications, there are several entities that are sourced from non-Fusion Applications systems. Fusion Applications CRM is leveraging OBIA (Oracle Business Intelligence Applications) to integrate data from Fusion Applications and non-Fusion Applications source systems. The Oracle BI Applications metadata layer consolidates disparate physical data sources and makes it ready for analysis by Fusion Applications users. Sales Prospector (SPE) is a brand new Fusion application for sales users helping them to manage their pipeline and whitespace effectively. SPE expects Order Item and Service Request data to be supplied from non-Fusion applications.
ETL from Flat Files
Non-Fusion Applications data such as Order Item and Service Request can be directly loaded into the Oracle Business Analytics Warehouse as long as the data can be presented in the specified flat file format. The ETL process loads the non-Fusion Applications data from the flat files and Fusion Applications data from Fusion Applications database tables into staging tables; then loads data from the staging tables into the Data Warehouse.
SPE ETL Preparation
SPE needs non-Fusion Applications data for Order Item Fact, Service Request Fact and Service Request Dimension. The data should be presented in flat files according to the following specifications:
Data should be in CSV files (*.csv).
For full ETL, the files should contain all initial records that are supposed to be loaded into Data Warehouse; for incremental ETL, the files should contain only new or updated records.
The files are specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only. All columns in the files should follow Fusion application data model terms and standards, and all ID columns in the files are expected to have corresponding Fusion Integration ID.
Data should start from line six of each file. The first five lines of each file will be skipped during ETL process.
Each row represents one record in staging table.
All date values should be in the format of YYYYMMDDHH24MISS. For example, 20071231140300 should be used for December 31, 2007, 2:03 pm.
Columns DATASOURCE_NUM_ID and INTEGRATION_ID in all flat files cannot be NULL.
Column DATASOURCE_NUM_ID needs to be fixed to 200, which is also the Fusion Applications data source number.
The Flat files for Order Item Fact, Service Request Fact and Service Request Dimension are:
file_orderitem_fs.csv - for more information about the structure of this file, see Section B.42.1, "Flat file file_orderitem_fs.csv".
file_srvreq_fs.csv - for more information about the structure of this file, see Section B.42.2, "Flat file file_srvreq_fs.csv".
file_srvreq_ds.csv - for more information about the structure of this file, see Section B.42.3, "Flat file file_srvreq_ds.csv".
Before starting the ETL run, the flat files should be prepared based on the formats provided in sections below and then copied to the SrcFiles directory on the Informatica server machine (for example, C:\Program Files\Informatica\PowerCenter8.6.x\server\infa_shared\SrcFiles).
The file is generic and therefore does not support any source order system specific features, such as recurring order lines and etc. Each line in this file will contribute to the total order amount. The granularity of this file is each order line.
The file is specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only.
Table B-6 File Structure for file_orderitem_fs.csv
Column Name | Data Type | Sample Data | Description |
---|---|---|---|
CUSTOMER_ID |
VARCHAR(80) |
999997551042159 |
Customer Party Id. There could be more than one customer IDs in an order. Among the possible customer IDs of bill to, ship to, invoice to and so on; this is the primary ID for BI analysis use. Foreign key to HZ_PARTIES.PARTY_ID. |
CURCY_CD |
VARCHAR(20) |
USD |
Currency Code, the currency that the order line amounts are based on. |
CRM_CURR_EXCHANGE_RATE |
NUMBER(28,10) |
1.00 |
CRM Currency Exchange Rate, which is for the conversion of the order line amounts to the CRM common currency. |
CRM_CORP_CURR_CODE |
VARCHAR(20) |
USD |
CRM Common Currency Code. |
ORDER_ID |
VARCHAR(80) |
4171787 |
Order header ID. |
PROD_ID |
VARCHAR(80) |
999997500678718 |
Product Inventory Item ID. Foreign key to EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID. |
PROD_GROUP_ID |
VARCHAR(80) |
Null |
Product Group ID. Optional for SPE ETL use. Leave null. |
RESOURCE_ID |
VARCHAR(80) |
123445623 |
Resource ID, order owner Resource ID for order. Foreign key to HZ_PARTIES.PARTY_ID |
RESOURCE_ORG_ID |
VARCHAR(80) |
3453453453 |
Resource Organization ID, order owner's organization ID. Foreign key to HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID. |
SOURCE_ID |
VARCHAR(80) |
100000016742344 |
Marketing campaign source code defined in MKT_SC_SOURCE_CODES. |
ORDER_DT |
DATE |
20061220000000 |
Order Date in the format of YYYYMMDDHH24MISS. It is the date when order is placed. This date is used in ETL as canonical date for resolving dimensional FKs. |
DATASOURCE_NUM_ID |
NUMBER(10) |
200 |
Data Source Number ID. Need to be fixed to 200, which is the same value for Fusion Applications data source in ETL. |
INTEGRATION_ID |
VARCHAR(80) |
12149813 |
Integration ID, the order Line ID. Typically, each order may have one order header and multiple order lines. |
DISCNT_AMT |
NUMBER(28,10) |
2.33 |
Discount Amount, deduction made to the unit price. |
NET_PRI |
NUMBER(28,10) |
45.752 |
Net Price of order line item. This is the final price after deducting discount amount. |
QTY_REQ |
NUMBER(28,10) |
12 |
Quantity Ordered for the line item. |
PR_TERR_ID |
VARCHAR(80) |
1000000000023112 |
Primary Territory ID, ID of primary sales territory where order is placed. Territory ID is defined in MOT_TERRITORIES. |
CREATED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Created By ID, Login ID of the user who created the row. |
CREATED_ON_DT |
DATE |
20071231140300 |
Created On Date in the format of YYYYMMDDHH24MISS. |
CHANGED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Changed By ID, Login ID of the user who modified the row. |
CHANGED_ON_DT |
DATE |
20071231140300 |
Changed On Date in the format of YYYYMMDDHH24MISS. |
DELETE_FLG |
VARCHAR(1) |
Null, Y or N |
Delete Flag, indicates if the record is deleted since last ETL. Default to N if null. |
X_CUSTOM |
VARCHAR(10) |
Null |
ETL reserved. Leave null. |
The columns listed below are required for SPE ETL use. The granularity of this file is each Service Request. The file is specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only.
Table B-7 File Structure for Flat file file_srvreq_fs.csv
Column Name | Data Type | Sample Data | Description |
---|---|---|---|
DATASOURCE_NUM_ID |
NUMBER(10) |
200 |
Data Source Number ID. Data Source Number ID needs to be fixed to 200, the same value for Fusion Applications data source in ETL. |
INTEGRATION_ID |
VARCHAR(80) |
12149813 |
Integration ID, unique IDentifier ID for each Service Request. |
CLOSE_DT |
DATE |
20030616174947 |
Closed Date, date in the format of YYYYMMDDHH24MISS when service request was closed. |
OPEN_DT |
DATE |
20020516174947 |
Open Date, date in the format of YYYYMMDDHH24MISS when service request was open. |
DELETE_FLG |
VARCHAR(1) |
Null, Y or N |
Delete Flag, indicates if the record is deleted since last ETL. Default to N if null. |
CREATED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Created By ID, Login ID of user who created the row. |
CREATED_ON_DT |
DATE |
20071231140300 |
Created On Date in the format of YYYYMMDDHH24MISS. |
CHANGED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Changed By ID, Login ID of the user who modified the row. |
CHANGED_ON_DT |
DATE |
20071231140300 |
Changed On Date in the format of YYYYMMDDHH24MISS. |
X_CUSTOM |
VARCHAR(10) |
Null |
ETL reserved. Leave null. |
CUSTOMER_ID |
VARCHAR(80) |
999997551042159 |
Customer Party Id. Foreign key to HZ_PARTIES.PARTY_ID. |
PROD_ID |
VARCHAR(80) |
999997500678718 |
Product Inventory Item ID. Foreign key to EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID. |
The columns listed below are required for SPE ETL use. The granularity of this file is each Service Request. The file is specially formatted for Fusion Sales Prediction Engine (SPE) data mining use only.
Table B-8 File Structure for Flat file file_srvreq_ds.csv
Column Name | Data Type | Sample Data | Description |
---|---|---|---|
DATASOURCE_NUM_ID |
NUMBER(10) |
200 |
Data Source Number Id. Data Source Number Id needs to be fixed to 200, the same value for Fusion Applications data source in ETL. |
INTEGRATION_ID |
VARCHAR(80) |
1-10E-5 |
Integration ID, unique Identifier ID for each Service Request. |
CLOSE_DT |
DATE |
20020516174947 |
Closed Date, date in the format of YYYYMMDDHH24MISS when service request was closed. |
OPEN_DT |
DATE |
20020516174947 |
Open Date, date in the format of YYYYMMDDHH24MISS when service request was open. |
SEV_CD |
VARCHAR(80) |
SR_SEVERITY~3-Medium |
Severity Code of the Service Request. Possible values are: SR_SEVERITY~1-Critical, SR_SEVERITY~2-High, SR_SEVERITY~3-Medium, SR_SEVERITY~4-Low. |
STATUS |
VARCHAR(80) |
SR_STATUS~Open |
Service Request Status. Possible values are: SR_STATUS~Approved, SR_STATUS~Cancelled, SR_STATUS~Closed, SR_STATUS~Completed, SR_STATUS~Open, SR_STATUS~Pending. |
DELETE_FLG |
VARCHAR(1) |
Null, Y or N |
Delete Flag, indicates if the record is deleted since last ETL. Default to N if null. |
CREATED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Created By ID, Login ID of the user who created the row. |
CREATED_ON_DT |
DATE |
20071231140300 |
Created On Date in the format of YYYYMMDDHH24MISS. |
CHANGED_BY_ID |
VARCHAR(80) |
SALES_ADMIN |
Changed By ID, Login ID of the user who modified the row. |
CHANGED_ON_DT |
DATE |
20071231140300 |
Changed On Date in the format of YYYYMMDDHH24MISS. |
X_CUSTOM |
VARCHAR(10) |
Null |
ETL reserved. Leave null. |
In order to extract payroll balances into Oracle Business Analytics Warehouse, the balances must be assigned to the BI Balance Group in the Fusion Applications system. By limiting the balances extracted, the performance of ETL and reports will be improved. In addition, only certain types of balance are suitable for including in the warehouse. You should only extract run balances, as other types of balances may not be fully additive (for example year-to-date balances cannot be added together).
Pre-requisites
Access to Fusion Applications Payroll Administration area.
Office 2007 with Oracle ADF 11g Plug In.
List of defined balances required to add to BI Balance Group:
Listed by Balance Dimension (which must be Run) and Balance Type.
Listed by Legislative Data Group.
Instructions
These instructions cover the steps required to add balances to the BI Balance Group for inclusion in OBIA Data Warehouse. There will be more details on the Payroll Administration documentation which will cover exceptions and verification reports to validate any setup.
Steps to create a batch:
Log into Fusion Applications and navigate to the Payroll Administration area (Navigator => Payroll => Payroll Administration).
In the Task pane select Batch Processing => Batch Loader.
Click the Download button to open the Batch Loader Spreadsheet, re-entering your login details as requested.
In the Batch Header Sheet tab, enter a name for the batch and the Legislative Data Group and Save.
Double-click the batch name to select the batch and open the Batch Content Sheet tab.
Click the Add button and select the 'Add a Defined Balance' action.
Enter the details for each defined balance to be added to the BI Balance Group:
Line Sequence.
Attribute Definition – 'Global BI Attribute'.
Legislative Data Group – as entered in step 4.
Balance Dimension – balance dimension name; this should be a simple run balance without any contexts.
Balance Type – balance type name for the defined balance
Click Save.
Steps to transfer the batch:
Back in Fusion Applications navigate to the Checklists page (Navigator => Payroll => Checklists).
In the Task pane select Payroll Flows => Submit a Process or Report.
Select the Legislative Data Group for the batch.
Select the 'Transfer Batch' process and click Next.
Enter the details:
Give a name for the Payroll Flow.
For the batch parameter, select the batch name entered in step 4 of 'Steps to create a batch'.
Submit the process
To get started with Functional Configuration, see Section 6.2, "Roadmap for Functional Configuration".
A BI Application Offering and one or more Functional Areas are selected during the creation of an Implementation Project. A list of Functional Setup tasks is generated based on the selected Oracle BI Applications Offering and Functional Area(s).
There are four types of Functional Task:
Tasks to configure Data Load Parameters - Clicking on the Go To Task button for these tasks launches Configuration Manager and the Manage Data Load Parameter setup user interface is displayed with the appropriate set of Data Load Parameters required to perform a task.
Tasks to manage Domains and Mappings - Clicking on the Go To Task button for these tasks launches Configuration Manager and the Manage Domains and Mappings setup user interface is displayed with the appropriate set of Domain Mappings.
Tasks to configure Reporting Parameters - Clicking on the Go To Task button for these tasks launches Configuration Manager and the Manage Reporting Parameter setup user interface is displayed with the appropriate set of Reporting Parameters required to perform a task.
Tasks which are informational - These tasks provide instructions for configurations which are to be performed outside of Configuration Manager either in Informatica PowerCenter, DAC, or the RPD using the BI Administration Tool. Some Informational Tasks provide conceptual, background or supporting information and do not describe a functional configuration.
Oracle Sales Analytics helps sales executives, sales managers and front line sales representatives obtain insight into sales performance at all stages of the sales cycle. Coverage includes all key areas in Sales, namely, Opportunity and Pipeline management, Win Loss Analysis, Quota Management, Forecasting and Customer Analytics.
Oracle Marketing Analytics is a comprehensive analytical solution that provides timely fact-based insight into the marketing activities of the entire organization. It provides new levels of information richness, usability, and reach to marketing professionals throughout the enterprise. It provides actionable intelligence in the following Marketing areas: Marketing Effectiveness, Customer Insight, and Lead Analysis.
The main functional areas within Marketing Analytics are:
Core Marketing - Helps to analyze customer and prospect responses to campaigns, marketing activities and marketing offers.
Marketing Leads - Helps to do a detailed analysis on leads as they move through the lifecycle. Analysis includes lead to opportunity conversion, what percentage of the leads are getting rejected and retired by the sales team, what are the main reasons, how effective the sales force in converting the leads and so on.
Customer Interactions Management - Helps to analyze various marketing and sales interactions a company has had with their customers and prospects.
Opportunity Revenue Management - Helps to analyze the opportunity revenue generated from marketing activities, helping marketers to calculate the Return on Marketing Investment (ROMI).
Please note that you must implement the above 4 functional areas for a complete end to end analysis of marketing campaigns and other activities.
Opportunity Landscape - Opportunity Landscape is a functional area included within Marketing Analytics, but is not necessary for the Marketing Analytics to function. This module provides analysis for Fusion Opportunity Landscape application. Please refer to the product documentation of Fusion Opportunity Landscape for more details.
Partner Analytics helps channel and partner account managers assess partner and program performance on all key fronts - lead generation, deals registered, revenue and enrollments. It also enables partner organization sales representatives and managers to assess their own sales performance.
Oracle Human Resources contains information for workforce effectiveness, workforce deployment, headcount gain and losses, compensation, payroll, absence and leave accrual. The Oracle HR Analytics application has the following functional areas:
Workforce Effectiveness Functional Area
The Workforce Effectiveness functional area allows senior HR executives to monitor key HR effectiveness metrics at the enterprise level. It combines key HR metrics with the organization's financial data to analyze industry benchmark metrics such as revenue per employee, contribution per employee, and so on. The correlation of workforce and financial metrics provides insight into how workforce trends directly impact the organization's operations and financial strength.
Sample questions answered by the Workforce Effectiveness area are:
What is the trend in revenue per employee?
What is the gross margin contribution per headcount?
What is the ratio of workforce cost to gross margin contribution per headcount?
What are the trends in workforce costs, e.g. employee benefit expenses, payroll expenses, contracting expenses, and so on.?
Workforce Deployment Functional Area
The Workforce Deployment functional area is the foundation for workforce analysis including three subject areas, Workforce Deployment, Headcount Gain and Loss, and Compensation.
Workforce Deployment Subject Area
Workforce Deployment provides the comprehensive data analysis on head count, retention, workforce diversity, employee performance, and contingent labor utilization. Key workforce profile information such as employee, organization, supervisor, performance band, and service band are shared with other HR functional areas. Sensitive personal attributes like birth date, age, and marital status are organized in a separate folder to allow for restricted access. Configured HR event analysis is another key feature of Workforce Deployment functional area. Customers can configure various employee assignment actions to support analysis in voluntary/involuntary termination, hires, transfers, promotions, or layoffs, and so on. In addition, changes in an employee's job, organization, location, supervisor and salary are tracked to support workforce movement analysis.
Specifically, the following analysis is supported by Workforce Profile functional area:
Headcount analysis
Workforce diversity
Employee attrition and retention
Employee performance
Span of control
Internal mobility
Sample questions answered by the Workforce Deployment area are:
What is the current staffing level? What is the headcount growth rate?
What is the contingent labor usage trend by job, by location, by department?
Is employee turnover within the target? How is employee turnover trending?
Is there a retention hotspot?
What is the workforce demographics by ethnicity, by age group, by education, and so on?
Who are the top performers and under performers?
Is there a shortage of workers by job by geography?
Workforce Gain and Loss Subject Area
An internal transfer can result in a headcount gain or loss or no change effect at different levels of an organization. The Workforce Gain and Loss area is designed to track headcount and FTE change at all levels of the supervisor hierarchy. At any supervisor level, it can easily identify net headcount changes and analyze inter- and intra-unit headcount gain and loss resulting from external or internal movements such as hires, transfer-ins/outs, reorganizations and terminations.
Sample questions answered by the Headcount Gain and Loss area are:
How is current headcount changed from last year?
What is the headcount gain from new hire?
What is the headcount loss from termination?
What is the net headcount change due to reorganization?
What is the internal headcount movement due to inter-unit transfers?
Compensation Subject Area
The delivered compensation metrics allow Compensation analysts and managers to analyze employee salary and compensation-ratio analysis at different levels of granularity. It proactively detects over or under-compensated employees, which can have big impact on your company's ability to maintain a competitive edge. It provides the capability to correlate employee pay with performance and perform pay equity and salary compression analysis by various factors e.g. job, grade and length of service.
Sample questions answered by the Compensation area are:
What it the average salary based on length of service?
How do the average salaries and compa-ratios compare for each job/job family?
How many workers are outside their salary range?
What is the salary compression between jobs, between grades and between new hire and seasoned workers?
Payroll Functional Area
HR Payroll functional area captures employee pay period balances as well as monthly, quarterly and annual payroll balances. It allows you to analyze earnings, deductions, employee and employer-paid benefits and taxes that may comprise an employee's total compensation. It helps HR and line managers to assess employees' total compensation and the relationship between base salary and variable pay components. It analyzes total payroll spend and monitors cost differences by geography. It also helps line managers and finance to monitor overtime usage and overtime spend and keep employees' payroll costs within budget.
Sample questions answered by the Payroll area are:
How has the total cost of compensation changed over time?
How does the total cost of compensation this month compare to the same month 1, 2, 3, and 4 years ago?
How has the proportion of base pay versus bonus versus other types of compensation changed over the years?
What is the trend in overtime hours and cost? Where is the biggest overtime spending?
What is the total payroll cost trend by country?
Absence and Leave Accrual Functional Area
Absence and Leave Accrual includes two components, absence tracking and accrual balance tracking. Unplanned absenteeism impedes workforce productivity and increase workforce cost. Absence and Leave Accrual functional area analyzes employee leave accrual balances and absence trends, working days lost, and hot spots in absenteeism.
Sample questions answered by the Absence and Leave Accrual area are:
What is the trend in employee absences by absence type?
What is the lost productivity due to employee absences?
Which employees have a high Bradford score (a measure to track habitual sick leave)?
What is the current leave accrual liability cost?
Which departments and/or locations have the highest leave accrual balances?
What is the percentage of employees who have reached their maximum accrual balance?
Fusion Customer Data Management Analytics provides insight into the data quality of an organization's customer data.This solution provides a set of data completeness reports which allow you to monitor, measure, and analyze the completeness of the underlying party information of your enterprise, including organization and person information.
Oracle Financial Analytics comprises the following Functional Areas:
Employee Expenses - The Oracle Employee Expenses Analytics application has been designed to provide visibility into an organization's employee related expenditures, including corporate card usage, expense policy violations, and the overall submission and approval process. Gain control of the drivers of employee expenses by isolating top spenders across expense categories and identifying recurring policy violations. Visibility into overall expense trends improve ability to negotiate with key merchants. The default configuration for the Oracle Employee Expenses Analytics application is based on what is identified as the most-common level of detail or granularity. However, you may configure and modify the extracts to best meet your business requirements.
Fixed Assets - The Oracle Fixed Assets Analytics application provides finance controllers, asset managers, and cost center managers with a complete picture of the asset's life cycle from acquisition through to retirement. Fixed assets comprise approximately 40 to 50% of the balance sheet and are a key component for both the commercial and public sector customers. Tracking asset life cycle value and measuring returns on some of the key assets are important to increase the overall return of the organization. The default configuration for the Oracle Fixed Assets Analytics application is based on what is identified as the most-common level of detail or granularity. However, you may configure and modify the extracts to best meet your business requirements.
General Ledger - The General Ledger Analytics application has been designed to provide insight into key financial areas of performance, including balance sheet, cash flow, expenses, budget vs. actual, working capital, liquidity. Identify root cause of discrepancies for more timely, informed decisions at all levels of the organization. Gain access to reporting and analysis from intra-period financial information before books are closed. The default configuration for the Oracle General Ledger Analytics application is based on what is identified as the most-common level of detail or granularity. However, you may configure and modify the extracts to best meet your business requirements.
Payables - The Oracle Payables Analytics application has been designed to provide an overview of the health of the payables side of the business and enables Finance to best manage its cash outflows and ensure timely payments to its suppliers. The need for analysis is increasingly important because suppliers are becoming strategic business partners with the focus on increased efficiency for just in time, and quality purchasing relationships. The default configuration for the Oracle Payables Analytics application is based on what is identified as the most- common level of detail, or granularity. However, you can configure or modify the extracts to best meet your business requirements.
Profitability - The Oracle Profitability Analytics application has been designed to provide key data pertaining to profitability, including Profit and Loss Statements, Customer and Product profitability, Margin Analysis, ROA, and ROE. Insight into Revenue and Cost Drivers help drive financial accountability, and proactive behavior. The default configuration for the Oracle Profitability Analytics application is based on what is identified as the most-common level of detail or granularity. However, you may configure and modify the extracts to best meet your business requirements.
Receivables - The Receivables Analytics application has been designed to provide key data pertaining to receivables, including receivables due, credit risk, payments, collector efficiency and enables Finance to best manage cash inflows and their ability to collect debt. Each day that your receivables are past the due date represents a significant, opportunity-cost to your company. Keeping a close eye on the trends, and clearing of AR is one way to assess the efficiency of your sales operations, the quality of your receivables, and the value of key customers. The default configuration for the Oracle Receivables Analytics application is based on what is identified as the most-common level of detail or granularity. However, you may configure and modify the extracts to best meet your business requirements.
Oracle Product Information Management (PIM) Data Hub is an enterprise data management solution that enables customers to centralize all product information from heterogeneous systems. It allows organizations to create a single, enterprise view of their product information, by integrating, standardizing and synchronizing fragmented product data from multiple source systems into a central, operational, data repository ('Hub').
PIM Data Hub solution centralizes the disparate sources of product information and provides a full, 360-degree view of products across all channels. It enables articulated management and communication of product information both within the organization as well as externally to customers and value-chain partners.
Oracle Product Information Management Analytics application comprises the following Subject Areas:
PIM - Item: This subject area provides information on creation and approval activities related to items of different Item class, type, phase and status.
PIM - Change Orders: This subject area provides information on activities related to Change Orders such as number of change orders in different age range, average age of change orders, different stages of change order life cycle, e.g. approved, rejected, draft, pending effective.
PIM - New Item Request: This subject area provides information on activities related to New Item requests such as number of new item requests in different age range, average age of new item requests, New Item Request Cycle Time and different stages of new item request life cycle, e.g. new, approved, rejected.
PIM - Item Catalog : This subject area provides information on activities related to Item Catalogs like number of new catalogs, categories, and shared categories.
PIM - Item Batch : This subject area provides information on activities related to Item Import from any external system such as number of items excluded, partially imported, successfully imported, and so on during the batch import process.
Project Analytics includes the following Subject Areas:
Commitments: A detailed subject area that provides the ability to report on the obligations for future expenditures that a project has made. Reporting can be done across organizations, projects, tasks, resources and periods. There are metrics showing raw and burdened amounts for requisitions, purchase orders and supplier invoices.
Cross Charges: A detailed subject area that provides the ability to report on expenditures that projects or organizations charge to each other for resources that they share. Reporting is possible across periods, organizations, projects, task and resources. Metrics include charges generated by the Intercompany Billing or the Borrowing and Lent methods. There are metrics for current and previous periods.
Note: For reporting on Cross Fact Analysis, Oracle recommends that you always have a filter on Canonical BU ( Business Unit Name column under Organizations folder in presentation area).
Table B-9 List of Facts and Business Units/Organization
Fact | Canonical BU (Business Unit)/Organization |
---|---|
Project Billing |
Contract BU / Organization |
Project Budget |
Project BU / Organization |
Project Budget - Linear Spread |
Project BU / Organization |
Project Commitment |
Project BU / Organization |
Project Commitment Snapshot |
Project BU / Organization |
Project Contract |
Contract BU / Organization |
Project Cost |
Expenditure BU / Organization |
Project Cross Charge - Invoice |
Project BU / Organization |
Project Cross Charge - Provider |
Expenditure BU / Organization |
Project Cross Charge - Receiver |
Project BU / Organization |
Project Cross Charge - Revenue |
Contract BU / Organization |
Project Forecast |
Project BU / Organization |
Project Funding |
Contract BU / Organization |
Project Revenue |
Contract BU / Organization |
Notes
The Canonical BU (Canonical Organization) is the Common Logical BU (Organization) against which data is analyzed across different fact tables. From each fact table one main BU (Org) is selected to be used for analyzing data in that fact table (for example, for Cost Fact, the canonical BU is the Expenditure BU; for Revenue Fact, the canonical BU is the Contact BU) and use the corresponding foreign key to join to the logical dimension Dim - Business Unit (Dim - Project Organization). These dimensions Dim - Business Unit and Dim - Project Organization are called Canonical BU and Canonical Project Organizations dimensions respectively. For example, for the Cost Fact the join would be
Dim_W_INT_ORG_D_Business_Unit.SCD1_WID = Fact_W_PROJ_COST_LINE_F_Project_Cost.EXPENDITURE_OPER_UNIT_WID
For Revenue Fact the join would be:
Dim_W_INT_ORG_D_Business_Unit.SCD1_WID =Fact_W_PROJ_REVENUE_LINE_F_Revenue_Lines.CONTRACT_BU_WID
In addition the Canonical BU calendar is used when forming the foreign key to the Fiscal Calendar Day dimension (W_MCAL_DAY_D).For Cross Fact Analysis, you must always ensure that you have a filter on Canonical BU ( Business Unit Name column under Organizations folder in presentation area). This filter on Canonical BU is required in all dashboards because it ensures the calendar is unique and prevents double counting.
The table below lists the Canonical BU (Canonical Organizations) that are available for the Logical Facts supported in Project Analytics solution.
Table B-10 List of Facts, Canonical BUs, and Canonical Organization
Fact | Canonical BU (Business Unit)/Organization | Canonical Organization |
---|---|---|
Project Billing |
Contract BU |
Contract Organization |
Project Budget |
Project BU |
Project Organization |
Project Budget - Linear Spread |
Project BU |
Project Organization |
Project Commitment |
Project BU |
Project Organization |
Project Commitment Snapshot |
Project BU |
Project Organization |
Project Contract |
Contract BU |
Contract Organization |
Project Cost |
Expenditure BU |
Expenditure Organization |
Project Cross Charge - Invoice |
Project BU |
Project Organization |
Project Cross Charge - Provider |
Expenditure BU |
Expenditure Organization |
Project Cross Charge - Receiver |
Project BU |
Contract Organization |
Project Cross Charge - Revenue |
Contract BU |
Contract Organization |
Project Forecast |
Project BU |
Project Organization |
Project Funding |
Contract BU |
Contract Organization |
Project Revenue |
Contract BU |
Contract Organization |
Oracle Procurement and Spend Analytics comprises of Procurement Analytics, Sourcing Analytics, and Employee Expense Analytics.
Oracle Procurement and Spend Analytics enable organizations to optimize their supply side performance by integrating data from across the enterprise value 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 Corporate Spend and complete source-to-pay process, including comprehensive sourcing and procurement analysis, supplier performance analysis, supplier payables analysis, and Employee Expenses analysis. Through complete end-to-end insight into the savings, spend patterns, and supplier performance, organizations can significantly reduce costs, enhance profitability, increase customer satisfaction, and gain competitive advantage. Oracle Procurement and Spend Analytics also integrates with the other applications in the Oracle Business Intelligence Applications product line, such as Oracle Financial Analytics. They deliver this insight across the organization to increase the company's effectiveness in managing its customers, suppliers, and financial decisions.
Oracle Procurement and Spend Analytics provides visibility into sourcing, direct and indirect spending across the enterprise, payment, and employee expenses. Oracle Procurement and Spend Analytics comprises the following Subject Areas:
Procurement and Spend - Change Orders: This subject area provides the ability to report on changes to purchasing documents post approval, showing count of changes/ cancellations and processing time by Supplier, BU, Buyer, and Change Order attributes such as method, type, initiator, and so on.
Procurement and Spend - Invoice Lines: This is a detailed subject area that provides the ability to report on total spend of an organization across suppliers, products, item categories, business units, cost centers, buying locations, supplier locations and associated hierarchy. In addition, this subject area also provides detailed information at invoice distribution level.
Procurement and Spend - Procure to Pay: This is a summary subject area that provides the ability to do comparative analysis and report on requested spend, committed spend and actual spend and receipts across business units, buying locations, suppliers, products, item categories 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.
Procurement and Spend - Purchase Agreement: This subject area provides ability to report on Purchase Agreements, showing agreement amount, its consumption and expiration, number of different agreement types, buyers, supplier and supplier sites, agreement lines across Supplier, Supplier Site, Buyer, Item, BUs, and Agreement details.
Procurement and Spend - Purchase Cycle Lines: This is a summary subject area that provides the ability to report cycle time performance, such as requisition to purchase order lead time, purchase order to receipt lead time, P2P lead time of the suppliers of an organization.
Procurement and Spend - Purchase Orders: This is a detailed subject area that combines the information from Purchase Orders, Purchase Order Costs and Purchase Schedules with the ability to report on committed spend, contract compliance and Purchase orders of the suppliers of an organization across suppliers, company, products, item categories and associated hierarchies at purchase order line level.
Procurement and Spend - Purchase Orders BU Summary: This is the same as 'Procurement and Spend - Purchase Orders' Subject Area, except that they do not have data security enabled, and is used in Fusion Applications embedded reports only by explicit data filter.
Procurement and Spend - 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, item categories and associated hierarchies at purchase receipt line level, including reporting based on receiving time.
Procurement and Spend - Purchase Requisition BU Summary: This is the same as 'Procurement and Spend - Purchase Receipts' Subject Area, except that they do not have data security enabled, and is used in Fusion Applications embedded reports only by explicit data filter.
Procurement and Spend - 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. This subject area is only populated by the Universal adapter.
Procurement and Spend - Purchase Requisitions: This is a detailed subject area that provides the ability to report on requested spend and purchase requisitions (including cyclic requisitions) of the suppliers of an organization across suppliers, company, products, item categories and associated hierarchies at purchase requisition line level.
Procurement and Spend - Scorecard: This subject area supports Procurement Scorecard. It includes metrics/ KPIs and its targets that provide the ability to monitor and analyze trends of procurement organization's performance. It provides performance and goal attainment information, across time and business units, from different perspectives such as finance, internal customer, operations and supplier.
Supplier Performance - 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. (Note: In order to populate Supplier Payables component, you must implement the Accounts Payables module of Oracle Financial Analytics. If you do not implement the Accounts Payables module, then some of the Supplier Payables reports will not be populated.)
Supplier Performance - Supplier Performance: This subject area (built on Purchase Cycle Lines) contains targeted metrics that allow users to analyze the timeliness, reliability, cost, and quality of goods provided by the suppliers. It helps you to understand how well suppliers are contributing to the success of your organization.
Sourcing - Award: This subject area provides the ability to report on Sourcing Awards, showing projected and realized savings, award amount, quantity, price, PO amount, number of suppliers and BUs awarded across sourcing negotiation types, BUs, Suppliers, Buyers and Categories.
Sourcing - Negotiation: This subject area provides the ability to report on Sourcing Negotiations, showing negotiation amounts, header/ line counts and cycle times across sourcing negotiation types, BUs, Suppliers, Buyers and Categories.
Sourcing - Overview: This is a detailed subject area that provides the ability to report on supplier participation and response to sourcing documents, projects and realized savings, award amount, quantity, price, PO amount, number of suppliers and BUs awarded, and various cycle times across Sourcing negotiation types, BUs, Suppliers, Buyers and Categories.
Sourcing - Response: This subject area provides the ability to report on Sourcing Responses, showing supplier response and participation across sourcing negotiation types, BUs, Suppliers, Buyers and Categories.
Employee Expenses - Credit Card: This subject area provides the ability to report on the corporate card spend of an organization, showing the number and amount of outstanding transactions by business unit, employee, and expense categories.
Employee Expenses - Overview: 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, including Approvers and cycle time measurements related to Approval, and Employee Expenses by various expense types.
Employee Expenses - Violations: This subject area provides the ability to report on policy violations for submitted employee expenses of an organization, across employee and business.
The Oracle Supply Chain and Order Management Analytics application for Fusion Applications allows you to analyze:
Bookings
Financial and Operational Backlogs
Invoices
The movement of sales orders through different stages of the sales cycle
Orchestration orders analysis
Order Hold analysis
Inventory held by an organization
Inventory movements in, out, and through manufacturing plants, distribution centers, or storage locations
Inventory Valuation
Inventory cycle count with Hit or Miss and Exact Match analysis
Product Information Management covering analytics for Item, Item-Batch and Item-Catalog attributes
Product Information analytics to support New Item Requests and Change Order processes in Fusion
The Oracle Supply Chain and Order Management Analytics application consists of orders, invoices, order orchestration, backlogs, inventory, logistics and product information management. Sales orders are the entry point for the sales process. Invoices are the exit point from the fulfillment process. Backlogs are points of congestion in your fulfillment process. This coverage includes insight into orchestration orders and process durations and which items are booked, backlogged, and invoiced. This allows you to evaluate the sales performance of individual sales representatives or departments. Oracle Supply Chain and Order Management Analytics application also provides you with information on Inventory Transactions, Inventory Balances and Customer and Supplier Returns. This enables companies to monitor inventory levels trend to Sales performance to improve cost exposure, increase turnover through inventory level reduction and increased velocity, properly deploy inventory at the right place / right time and better understand Customer and Supplier Returns to maintain quality.
In addition to the above, the Oracle Supply Chain and Order Management Analytics for has new content for Fusion Applications source that includes new subject areas in Costing, Distributed Order Orchestration, Logistics and Product Information Management.
Initial Extract Date is required when you extract data for a full load. It reduces the volume of data in the initial load. The specified initial extract date will be used as a filter on the creation date of the transactional data in the selected full extract mapping. The default date is January 01, 1970.
When you set the Initial Extract Date parameter, ensure that you set it to the beginning of an accounting period and not a date in the middle of an accounting period. For example, if you decide to extract data from June 2005, and the June 2005 accounting period starts on June 5, set the date to June 5, 2005.
The following tables use INITIAL_EXTRACT_DATE:
PROJECTS: W_PROJ_BUDGET_F W_PROJ_COMMITMENT_F W_PROJ_COMMITMENT_SNP_F W_PROJ_CONTRACT_LINE_F W_PROJ_COST_LINE_F W_PROJ_CROSS_CHARGE_DIST_F W_PROJ_FORECAST_F W_PROJ_FUNDING_LINE_F W_PROJ_INVOICE_DIST_F W_PROJ_REVENUE_LINE_F FINANCE: W_GL_OTHER_F W_GL_BALANCE_F W_GL_REVN_F W_GL_COGS_F W_GL_COST_REVN_F W_AP_HOLDS_F W_FA_BALANCE_F W_FA_XACT_F OM: W_SALES_ORDER_LINE_F W_SALES_INVOICE_LINE_F W_SALES_SCHEDULE_LINE_F W_SALES_PICK_LINE_F W_SALES_ORDER_HOLD_F W_SALES_ORDER_HOLD_1_F W_DOO_PROCESS_F W_SALES_ORDER_CREDIT_F W_SALES_INVOICE_CREDIT_F PIM: W_ITEM_REQUEST_F W_ITEM_REQUEST_STATUS_SNP_F W_ITEM_INTERFACE_F W_ITEM_F PRM: No INITIAL_EXTRACT_DATE usage Procurement: W_PURCH_RQSTN_LINE_F W_RQSTN_LINE_COST_F W_PURCH_AGREEMENT_HEADER_F W_PURCH_AGREEMENT_LINE_F W_PURCH_SCHEDULE_LINE_F W_PURCH_COST_F W_PURCH_RCPT_F W_AP_INV_DIST_F W_PURCH_CHANGE_ORDER_F Sourcing: W_NEG_INVITATIONS_F W_NEG_LINES_F W_NEG_RESPONSES_F Expense: W_EXPENSE_F W_EXPENSE_CC_F W_EXPENSE_VIOLATION_F SCM: W_CST_ITEM_COST_DAILY_F W_CST_INTRANSIT_DAILY_F W_CST_INTRAN_ACCNTED_DAILY_F W_CST_ONHAND_ACCNTED_DAILY_F W_CST_ONHAND_DAILY_F W_INVENTORY_CYCLE_COUNT_F W_PRODUCT_XACT_F HCM: W_WRKFC_EVT_F
Note: HR needs a specific extract date (used in place of 'initial') such as HR_WRKFC_EXTRACT_DATE, HR_ABSENCE_EXTRACT_DATE, HR_PAYROLL_EXTRACT_DATE and HR_ACCRUAL_EXTRACT_DATE. The requirement for setting up the common INITIAL_EXTRACT_DATE parameter is only that this date should be earlier than any of our four specific extract date values.
Currency conversions are required because your business might have transactions involving multiple currencies. To create a meaningful report, you have to use a common currency. The Oracle Business Analytics Warehouse stores amounts in the following currencies:
Document currency. The document currency is the currency of the transaction. For example, if you purchase a chair from a supplier in Mexico, the document currency is probably the Mexican peso. Or, if you made a business trip to the United Kingdom and filed an expense report for meal expenses in the UK, the document currency of the expense report will most likely be in GBP.
Local currency. The local currency is the base currency of your ledger, or the currency in which your accounting entries are recorded in.
Global currencies. Oracle BI Applications provides three global currencies, which are the common currencies used by the Oracle Business Analytics Warehouse. For example, if your organization is a multinational enterprise that has its headquarters in the United States, you probably want to choose US dollars (USD) as one of the three global currencies.
The global currency is useful when creating enterprise-wide reports. For example, a user might want to view enterprise-wide data in other currencies. For every monetary amount extracted from the source, the load mapping loads the document and local amounts into the target table. It also loads the exchange rates required to convert the document amount into each of the three global currencies. For fact tables, there are two amount columns covering the Local currency amount and the Document currency amount. In addition, there are three columns covering the Global currency (for example, global _amount1) and their corresponding exchange rate columns.
In most cases, the source system provides the document currency amount. This is the most common situation, and, thus, is the Oracle Business Analytics Warehouse's default for handling currency. If the source system provides only the document currency amount, the source adapter performs lookups to identify the local currency codes based on the source system the appropriate currencies are assigned. After the lookups occur, the extract mapping provides the load mapping with the document currency amount and the document and local currency codes. The load mapping will then use the provided local currency codes and perform currency conversion to derive the local amount. The load mapping will also fetch the global currencies setup from DAC parameters and look up the corresponding exchange rates to each of the three global currencies.
To specify global currencies, use the parameters GLOBAL1_CURR_CODE, GLOBAL2_CURR_CODE, and GLOBAL3_CURR_CODE.
This topic contains additional information about using FSM to configure calendars for Oracle BI Applications Offerings.
This task is a pre-requisite for all types of calendars. Calendar date ranges are set by parameters START_DATE and END_DATE. These parameters decide the dates that will be loaded into W_DAY_D and in related calendar tables like W_MCAL_PERIOD_D and W_MCAL_DAY_D. The START_DATE needs to be set to the oldest transaction date that will be brought into the warehouse. The END_DATE needs to be large enough to cover at least 10 years in the future from the date of implementation. Example values can be 1980 to 2020. If your oldest transaction starts in 2000, then there is no need to go as far as 1980. The parameters START_DATE and END_DATE need to include all dates covered by any of the fiscal calendars brought into the warehouse as well. These parameters are the boundaries for the date dimension and related tables.
An Enterprise calendar (or reporting calendar) enables cross Subject Area analysis. Enterprise calendars can be set to one of the OLTP sourced fiscal calendars or to one of the warehouse generated calendars. This can be done by setting the parameters GBL_CALENDAR_ID and GBL_DATSOURCE_NUM_ID. out-of-the-box the Enterprise Calendar is set to one of the pre-seeded generated calendars.
If you are setting a warehouse generated calendar as the Enterprise calendar:
GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 period type of Calendars). By default the 4-4-5 calendar has a CALENDAR_ID of '10000' and the 13-period calendar has a CALENDAR_ID of '10001'.
GBL_DATASOURCE_NUM_ID: If Global Calendar is Generated Calendar: It should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse)
If you are setting a Fusion Applications source calendar as the Enterprise calendar:
GBL_CALENDAR_ID: This parameter should be the MCAL_CAL_NAME~MCAL_PERIOD_TYPE for Non-Generated Calendars. For example GBL_CALENDAR_ID will be 'Accounting~41', if the Enterprise Calendar id='Accounting' and the calendar period_type='41'. The value of GBL_CALENDAR_ID for Fusion Applications will be GL_PERIODS .period_name~ GL_PERIODS .period_type of the particular calendar chosen as the Enterprise calendar. This calendar will be the one used for cross reporting. In this release the Enterprise calendar and CRM calendar are synonymous. Please make sure you set the value to the same value as the CRM calendar in Fusion.
GBL_DATASOURCE_NUM_ID: If Enterprise Calendar is not a Generated Calendar: It should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if you have two Fusion Applications data sources, and the Global Calendar is from an data source 1, then this parameter value should specify the value of source 1.
Oracle Business Intelligence Applications Release 11.1.1.5.0 supports the following types of generated calendars:
13 period calendars.
4-4-5 calendars (and variants).
When you set up calendars, note the following:
The W_MCAL_CONFIG_G table controls how generated calendars are created.
If generating the 4-4-5 or 13 period calendars, W_MCAL_CONFIG_G needs to have at least one row for the 4-4-5 period or 13 period. There is no entry needed in this table for Oracle Fusion Applications source calendars.
W_MCAL_WEEK_D will be populated only for the generated calendars (i.e. 13 period or 4-4-5 type of calendars), and hence the W_DAY_D week Enterprise columns will be null for non-generated calendars (known as OLTP sourced fiscal calendars). W_ENT_WEEK_D will not be populated if a non-generated calendar is chosen as the Enterprise Calendar.
For 13 period calendars, there is no concept of Quarter, therefore all Quarter columns in W_MCAL_WEEK_D, W_MCAL_PERIOD_D, W_MCAL_YEAR_D will be null. W_ENT_QTR_D will not be populated if a 13 period calendar is chosen as the Enterprise Calendar.
The following table outlines columns in the W_MCAL_CONFIG_G table, which is loaded from the file_mcal_config_g.csv.
Table B-11 Columns in configuration table W_MCAL_CONFIG_G
Column Name | Column Description |
---|---|
CALENDAR_ID |
The ID of the calendar that is being configured. This is the primary key for this table. |
CALENDAR_NAME |
The name of the calendar that is being configured. |
CALENDAR_CLASS |
Automatically generated. |
PERIOD_TYPE |
The type of the calendar period that is being configured e.g. '4-4-5'. |
CAL_ST_DT |
The date from which the calendar generation begins. Note: This should cover the date range required in the data warehouse. |
CAL_END_DT |
The date at which the calendar generation ends. Note: This should cover the date range required in the data warehouse. |
CAL_OFFSET |
The offset that identifies the start date of the calendar. Valid Start Day and Offset values are:
|
REFERENCE_DATE |
The date (in MMDD format) that the fiscal year starts. For example, if an organization's fiscal year is from October to September, then the REFERENCE_DATE value should be '0929'. The previous fiscal year must end between three days before and three days after the date specified by REFERENCE_DATE (that is, within the range (REFERENCE_DATE - 3) to (REFERENCE_DATE + 3). In other words, if a REFERENCE_DATE is '0131' for 31st January, the previous fiscal year cannot exceed 3rd February. |
WEEK_ALLOCATION_RULE |
This parameter determines how weeks are allocated in the calendar that is being configured. E.g. '4-4-5' or '5-4-4' or '4-5-4' or '13 period'. |
Other standard columns |
W_INSERT_DT, W_UPDATE_DT, TENANT_ID, X_CUSTOM etc. |
The following table outlines Parameters needed for Generated Calendars.
Table B-12 Parameters required for Generated Calendars
Parameter Name | DAC Parameter Description |
---|---|
13P_CALENDAR_ID |
SIL_TimeDimension_MCalWeek13Period. Required if you want to populate the 13 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 13 period type of calendar. |
445P_CALENDAR_ID |
Task: SIL_TimeDimension_MCalWeek445. Required if you want to populate the 445 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 445 period type of calendar. |
If there is a week (starting on a Sunday and ending on a Saturday) that falls across two calendar years, the week is counted in both years. For example, the week that starts on 12/30/2007 will be counted in both 2007 and 2008. In 2007, the week start date will 12/30/2007 and the end date will be 12/31/2007. In 2008, this will be the first week with start date as 01/01/2008 and end date as 01/05/2008.
W_DAY_D stores 31 records for each month regardless of whether the month actually has 31 days. If the month has a fewer number of days, there will be records with null values in the Calendar Date and Day Date columns. These extra records are loaded for the calculation of Period Ago metrics in the Oracle BI Repository and will not affect the ETL or reporting.
There are some attributes on the W_DAY_D table that are not mapped in the Physical layer of the Oracle BI Repository. Therefore, before creating any new attribute in the repository, check whether the attribute is already available in the Physical layer and if it can be mapped directly.
If your fiscal calendar contains more than 12 months, the extra months will be assigned a value of 0 for the fiscal quarter. The same holds for the fiscal trimester and fiscal half values.
By default, Oracle BI Applications can generate up to 65536 rows. If you need more than 65536 rows, you can increase the capacity to 262144 rows (718 years) by doing the following:
Duplicate 'SIL_DayDimension_GenerateRows7'.
Rename it 'SIL_DayDimension_GenerateRows8'.
Run this immediately after 'SIL_DayDimension_GenerateRows7'.
The Oracle Business Analytics Warehouse provides Category 2 slowly changing dimension (SCD) functionality, which allows you to track the history of updates to dimension records. When a record in the Oracle Business Analytics Warehouse has an update, the updated information is posted into a new row and the old information is kept for historical reporting purposes.
The Oracle Business Analytics Warehouse identifies and applies the slowly changing dimension logic chosen by the user after data has been extracted and transformed to be source-independent. Users may configure Oracle BI Applications to support both Category 1 SCDs, in which data is overwritten with updates, and Category 2 SCDs, in which the original records are maintained while a new record stores the updated data. Choosing Category 1 or Category 2 SCDs depends on identifying your historically significant attributes.
Users can choose Category 1 or Category 2 by setting the value for $$TYPE2_FLG to Y or N in the configuration manager.
The following tables have TYPE2 defined out-of-the-box (by default, it's ON):
Common Dimensions:
W_PRODUCT_D
W_INVENTORY_PRODUCT_D
W_POSITION_D
W_USER_D
W_INT_ORG_DH
W_PARTY_ORG_D
W_PARTY_PER_D
HCM:
W_HR_PERSON_LEG_D
W_HR_POSITION_D
W_JOB_D
W_PAY_GRADE_D
W_SUPERVISOR_D and W_SUPERVISOR_STATUS_D:
Note: These are not the classical Type-2 dimensions. They have EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT and are set out-of-the-box to Type2.
However, HCM handles the dates internally and does not rely on the SCDUpdate mappings for these two. These tables are used to build the Supervisor Hierarchy, and not exposed in RPD after the physical layer.
Finance:
W_FIXED_ASSET_D
The following tables have TYPE2 supported in applications but not out-of-the-box (by default, it is OFF; it can be turned ON if required).
Common Dimensions:
W_COST_CENTER_D
W_COST_CENTER_DH
W_BUSN_LOCATION_D
W_TERR_DH
Finance:
W_AP_TERMS_D
W_BALANCING_SEGMENT_D
W_BANK_D
W_ASSET_BOOK_D
W_ASSET_CATEGORY_D
W_ASSET_LOCATION_D
W_GL_ACCOUNT_D
W_GL_SEGMENT_D
W_NATURAL_ACCOUNT_D
W_PAYMENT_TERMS_D
CRM/OM/PIM:
No SCD2 dims
SCM/Procurement/Sourcing/Expense:
No SCD2 dims