Skip Headers
Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users
Release 7.9.6.3

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

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Configuring Oracle Price Analytics

This section describes how to configure Oracle Price Analytics. It contains the following topics:

10.1 Overview of Oracle Price Analytics

Oracle Price Analytics is aimed at pricing analysis, sales operations, product marketing and management, and finance. It provides pricing analytics across the full price waterfall of contracts, quotes, orders and competitor pricing, allowing business users to do the following:

The following sources can populate pricing data:

10.2 Configuration Required Before a Full Load for Oracle Price Analytics

This section contains configuration steps that you need to perform on Oracle Price Analytics before you do a full data load. It contains the following topics:

10.2.1 Configuration Steps for Oracle Price Analytics for All Source Systems

For configuration steps that apply to all Oracle BI Applications modules, see Chapter 3, "Configuring Common Areas and Dimensions."

10.2.2 About Configuring Domain Value and CSV Worksheet Files for Oracle Price Analytics

Table 10-1 lists the CSV worksheet files and the domain values for Oracle Price Analytics that are located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

Table 10-1 Domain Values and CSV Worksheet Files for Oracle Price Analytics

Worksheet File Name Description Session

domainValues_PriceWaterfallElement_Name_SBL.csv

Lists the Price Waterfall Element Name column and the corresponding domain values for the Siebel 8.1.1 application. For information about how to edit this file, see Section 10.2.3, "How to Configure the domainValues_PriceWaterfallElement_Name.csv."

SDE_PriceWaterfallElementDimension


10.2.3 How to Configure the domainValues_PriceWaterfallElement_Name.csv

This section provides instructions for how to configure the domainValues_PriceWaterfallElement_Name.csv file.

The waterfall element names you will use are taken from the Siebel CRM flat file FILE_PWF_ELEMENT.csv. The different element types used in the sample data in this file are the following:

  • Segment

    The revenues that are part of a waterfall, such as ceiling revenue, list revenue, and so on.

  • Revenue Adjustment

    The adjustments made to the segment elements, for example, ceiling adjustment, invoice adjustment, and so on.

  • Cost Adjustment

    All other adjustments that are not part of any segment.

To configure the domainValues_PriceWaterfallElement_Name.csv file

  1. Identify the different waterfall elements and the names that are currently used by looking at the file FILE_PWF_ELEMENT.csv located in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

  2. Copy the ELEMENT_NAME and ELEMENT_TYPE columns.

    Note:

    Copy the data starting after the fifth line. The first five lines contain descriptions and header information.
  3. Open the domainValues_PriceWaterfallElement_Name_SBL.csv file using a text editor. This file is located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

  4. Paste the ELEMENT_NAME and ELEMENT_TYPE columns you copied from the FILE_PWF_ELEMENT.csv file into the domainValues_PriceWaterfallElement_Name_SBL.csv file.

  5. Map each waterfall element name and type to one domain value.

  6. Save and close the file.

10.2.4 Configuration Steps for Universal Sources

Oracle Price Analytics relies on data from universal sources, such as flat files, for waterfall related data.

Table 10-2 lists the flat file source tables and the corresponding data warehouse tables for waterfall related data.

Table 10-2 Flat File Source Tables and Corresponding Warehouse Tables

Flat File Description Loads Target

FILE_PRI_STRATEGY_DS

This file holds information about the different pricing strategies being used.

W_PRI_STRATEGY_D

FILE_PRI_SEGMENT_DS

This file holds the different pricing segment details.

W_PRI_SEGMENT_D

FILE_PWF_ELEMENT

This file contains information about the different waterfall elements.

W_PWF_ELEMENT_D

FILE_ORDIT_WTR_LOG_FS

This file holds the waterfall information for all the transaction data for Order Item.

W_ORDIT_WTR_LOG_F

FILE_QTEIT_WTR_LOG_FS

This file holds the waterfall information for all the transaction data for Quote Item.

W_QTEIT_WTR_LOG_F


10.2.4.1 Populating Flat File Data For Siebel Sources

This section provides guidelines for populating pricing data into flat files when the source is Siebel.

Oracle Price Analytics does not provide a way to load pricing strategy, pricing segment or price waterfall element information from a Siebel source. All such dimensions must be loaded with a universal source, such as flat files.

The source files for the pricing-related dimensions must conform to the following rules:

  • The Pricing Segment and Pricing Strategy IDs provided in the flat file must be the same for all the order lines in any given order.

  • The ROW_ID must be unique in all the flat files because they are used to form the Integration IDs.

  • The information added must be consistent with the existing data in the Siebel system. For instance, the Competitor Name added in the file must exist in the source system for proper resolution.

  • The Order Line IDs in the Order Item Waterfall fact source must exist in the source table S_ORDER_ITEM.

  • The Quote Line IDs in Quote Item Waterfall fact source must be a part of source table S_QUOTE_ITEM.

The Oracle Price Analytics facts W_ORDIT_WTR_LOG_F and W_QTEIT_WTR_LOG_F are loaded using the Order Item and Quote Item facts as well as flat files.

The pricing columns in the Order Item and Quote Item facts are loaded as shown in Table 10-3.

Table 10-3 Pricing Column in Order Item and Quote Item Facts

Column Name Expression

CEIL_PRI

IIF(ISNULL(FLAT_FILE_DATA),START_PRI,FLAT_FILE_DATA)

SEG_PRI

IIF(ISNULL(FLAT_FILE_DATA),START_PRI,FLAT_FILE_DATA)

INV_PRI

IIF(ISNULL(FLAT_FILE_DATA),NET_PRI,FLAT_FILE_DATA)

PKT_PRI

IIF(ISNULL(FLAT_FILE_DATA),NET_PRI,FLAT_FILE_DATA)

PKT_MARGIN

IIF(ISNULL(FLAT_FILE_DATA),START_PRI-NET_PRICE,FLAT_FILE_DATA)


If you need to load different values for the pricing columns other than the existing prices, you can use the flat files FILE_ORDERITEM_FS.csv and FILE_QUOTEITEM_FS.csv. Based on the Integration IDs, the pricing data is looked up from these flat files and loaded into the fact tables.

10.2.4.2 Populating Flat File Data for Non-Siebel Sources

This section provides guidelines for populating pricing data into flat files for non-Siebel sources.

For non-Siebel sources, the source files for the pricing-related dimensions must conform to the following rules:

  • The Order Line IDs in the Order Item Waterfall fact source must exist in fact file source FILE_ORDERITEM_DS.

  • The Quote Line IDs in Quote Item Waterfall fact source must be a part of the fact file source FILE_QUOTEITEM_DS.

  • Ensure all the ROW_IDs are unique so as to avoid any duplication or index issues.

  • All the fact IDs added must be consistent with the ROW_ID of dimension file sources for proper resolution.

10.2.4.3 Data Standards for Flat Files

The flat files being used for Oracle Price Analytics facts, such as FILE_ORDIT_WTR_LOG_FS and FILE_QTEIT_WTR_LOG_FS, must be consistent with the line item tables. The prices in the waterfall log table must be the aggregated price in the line item tables. And, in the case of assembled or packaged products, the item tables store the package or assembly and the individual items that make up the package or assembly as separate line items. The line items in the flat file must store the individual prices and not rolled up prices; that is, if a package does not have a price and only the items inside it have prices, either the price of the package should be 0 and the items should have the prices or the package should have the rolled up prices and the item prices should be 0 to prevent double counting. Also, the Waterfall log table should store only the package or assembly and not the items that comprise it, and the price should be the rolled up price for a unit package or assembly.

10.3 Price Waterfall Element Sample Data

This section provides price waterfall element sample data.

Table 10-4 Price Waterfall Element Sample Data

ELEMENT_NAME ELEMENT_TYPE GROUP_NAME BASIS_SEGMENT TOKEN ORDER_INDEX REVN_COST_IND DISP_ON_ZERO

Ceiling Revenue

Segment

Revenue

Ceiling Revenue

CEILING

1

0

Y

Segment Revenue

Segment

Revenue

Segment Revenue

SEGMENT

4

0

Y

Invoice Revenue

Segment

Revenue

Invoice Revenue

INVOICE

7

0

Y

Pocket Revenue

Segment

Revenue

Pocket Revenue

POCKET

10

0

Y

Pocket Margin

Segment

Revenue

Pocket Margin

POCKET MARGIN

15

0

Y

Cost

Adjustment

Cost

Pocket Revenue

COST

13

1

Y

Cost Adjustment

Cost Adjustment

Cost Adjustment

Pocket Revenue

OFF_COST

14

1

Y

Ceiling Adjustment

Adjustment Revenue

Customer Adjustment

Ceiling Revenue

OFF_CEILING

2

0

Y

Volume Adjustment

Revenue Adjustment

Rebates

Segment Revenue

OFF_SEGMENT

5

0

Y

Invoice Adjustment

Revenue Adjustment

Memo

Invoice Revenue

OFF_INVOICE

8

0

Y

Pocket Adjustment

Revenue Adjustment

Services

Pocket Revenue

OFF_POCKET

11

0

Y

Product Adjustment

Revenue Adjustment

Rebates

Segment Revenue

OFF_SEGMENT

6

0

Y


10.3.1 Example of an Order for a Simple Product

In this scenario, a simple order is created for a company that manufactures and sells laptops. Table 10-5 provides an example of the order information in the Order Item fact table.

Table 10-5 Sample Data for a Simple Product

Sales Order Number LINE ID PRODUCT QUANTITY UNIT PRICE TOP_LVL_LN_FLG INC_CALC_IND

100

1001

Laptop

10

$1,248

Y

1


Table 10-6 provides an example of the Order Item waterfall log fact data for the transaction represented in Table 10-5.

Table 10-6 Order Item Waterfall Log Fact Data for a Simple Product

Order Identifier Line ID Row Identifier PWF Element Identifier Extended Qty Unit Price Element Amount

100

1001

2001

Ceiling Revenue

10

$1,248

$12,480

100

1001

2002

Ceiling Revenue

10

$(200)

$(2000)

100

1001

2003

Segment Revenue

10

$1,048

$10,480

100

1001

2004

Volume Adjustment

10

$(100)

$(1000)

100

1001

2005

Product Adjustment

10

$(50)

$(500)

100

1001

2006

Invoice Revenue

10

$898

$8980

100

1001

2007

Invoice Adjustment

10

$(120)

$(1200)

100

1001

2008

Pocket Revenue

10

$778

$7780

100

1001

2009

Pocket Adjustment

10

$(88)

$(880)

100

1001

2010

Cost

10

$(400)

$(4000)

100

1001

2011

Pocket Margin

10

$290

$2900


As this example shows, each waterfall element is stored as an individual record and the Waterfall Element dimension identifies whether the element is a revenue or an adjustment.

10.3.2 Example of an Order for a Configured Product

Table 10-7 provides an example of an order for an assembled product that has multiple child products.

Table 10-7 Sample Data for an Assembled Product

Sales Order Number LINE ID PRODUCT QUANTITY UNIT PRICE TOP_LVL_LN_FLG INCL_CALC_IND

101

1002

Desktop

1

$1,200

Y

1

101

1003

Monitor

1

$800

Y

0

101

1004

Keyboard

1

$250

N

0

101

1005

Mouse

1

$150

N

0


The Price Waterfall is stored for the packaged product and not the individual child items. Table 10-8 provides an example of the Order Item waterfall log fact data for the transaction represented in Table 10-7.

Table 10-8 Order Item Waterfall Log Fact Data for an Assembled Product

Order Identifier Line ID Row Identifier PWF Element Identifier Extended Qty Unit Price Element Amount

101

1002

2012

Ceiling Revenue

1

$1,200

$1,200

101

1002

2013

Ceiling Adjustment

1

$(200)

$(200)

101

1002

2014

Segment Revenue

1

$1,000

$1,000

101

1002

2015

Volume Adjustment

1

$(100)

$(100)

101

1002

2016

Product Adjustment

1

$(50)

$(50)

101

1002

2017

Invoice Revenue

1

$850

$850

101

1002

2018

Invoice Adjustment

1

$(120)

$(120)

101

1002

2019

Pocket Revenue

1

$730

$730

101

1002

2020

Pocket Adjustment

1

$(80)

$(80)

101

1002

2021

Cost

1

$(400)

$(400)

101

1002

2022

Pocket Margin

1

$250

$250