Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.4 Part Number E35272-01 |
|
|
PDF · Mobi · ePub |
This section describes how to configure Oracle Price Analytics.
It contains the following topics:
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:
Identify the true realized price, as well as understand how price is diluted at appropriate summary and transactional levels.
Monitor price and discounting trends across segment, strategy, channel, geography and time.
Understand price and margin variation within the same products or customers through price band analysis.
Look for "outliers" in customer and product profitability to determine the root cause as to why some groups perform better than others and to identify surgical pricing policy and deal opportunities.
Combine insights from historical data with Oracle data mining and predictive technologies to improve forward-looking decisions
Break down price elements at the transaction level and identify the true realized price and margin through detailed waterfall analysis
Highlight discounts that are disproportionate to volume and determine which regions, products or customers are responsible
The following sources can populate pricing data:
Siebel CRM 8.1 and 8.1.1
Universal source
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:
Section 10.2.1, "Configuration Steps for Oracle Price Analytics for All Source Systems"
Section 10.2.2, "About Configuring Domain Value and CSV Worksheet Files for Oracle Price Analytics"
Section 10.2.3, "How to Configure the domainValues_PriceWaterfallElement_Name.csv"
For configuration steps that apply to all Oracle BI Applications modules, see Chapter 3, "Configuring Common Areas and Dimensions."
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 |
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
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).
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.
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).
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.
Map each waterfall element name and type to one domain value.
Save and close the file.
For information about loading Oracle Price Analytics data using Universal Adapter, see Section A.4, "Configuring Universal Adapter for Oracle Price Analytics".
This section provides price waterfall element sample data.
Table 10-2 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 |
|
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 |
In this scenario, a simple order is created for a company that manufactures and sells laptops. Table 10-3 provides an example of the order information in the Order Item fact table.
Table 10-3 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-4 provides an example of the Order Item waterfall log fact data for the transaction represented in Table 10-3.
Table 10-4 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.
Table 10-5 provides an example of an order for an assembled product that has multiple child products.
Table 10-5 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-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 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 |