| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19039-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19039-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.
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 |
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.
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.
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.
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 |
|
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-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.
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 |
|
 Copyright © 2009, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
