Configuring Flat Files in Price Analytics for Siebel Applications
Perform these configuration tasks for Oracle Price Analytics for Siebel applications.
The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:
-
Source-independent files:
<Oracle Home for BI>\biapps\etl\data_files\src_files\. -
Source-specific files:
<Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.
Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.
Configuration Required Before a Full Load for Oracle Price Analytics
Perform these configuration steps on Oracle Price Analytics before you do a full data load.
Configuration Steps for Universal Sources
Oracle Price Analytics relies on data from universal sources, such as flat files, for waterfall related data.
The Table lists the flat file source tables and the corresponding Oracle Business Analytics Warehouse tables for waterfall related data.
| 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 |
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 the table below.
| 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.
Note:
Even if not supplementing QUOTEITEM or ORDERITEM – empty files should be available in the adaptor source files folder so that extract tasks do not fail.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_FS.
-
The Quote Line IDs in Quote Item Waterfall fact source must be a part of the fact file source FILE_QUOTEITEM_FS.
-
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.
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.
Price Waterfall Element Sample Data
This is price waterfall element sample data.
Example of an Order for a Simple Product
In this scenario, a simple order is created for a company that manufactures and sells lap tops. The graphic below shows an example of the order information in the Order Item fact table.
Sample Data for a Simple Product:
The graphic below shows an example of the Order Item waterfall log fact data for the transaction.
Order Item Waterfall Log Fact Data for a Simple Product:
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.
Example of an Order for a Configured Product
This section shows an example of an order for an assembled product that has multiple child products.
Sample Data for an Assembled Product:
The Price Waterfall is stored for the packaged product and not the individual child items. The graphic shows an example of the Order Item waterfall log fact data for the transaction.
Order Item Waterfall Log Fact Data for an Assembled Product:
