Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.4 Part Number E35272-01 |
|
|
PDF · Mobi · ePub |
This appendix provides instructions for loading data using Universal Adapter.
It contains the following topics:
Section A.4, "Configuring Universal Adapter for Oracle Price Analytics"
Section A.5, "How to Create Data Files for Universal Adapter"
Section A.6, "List of Sample Source Files for Universal Adapter"
Universal Adapter enables you to extract data from transactional applications for analysis, when pre-packaged business adapters for these sources are not available. Unlike the pre-packaged business adapters that can only be used for particular source systems, Universal Adapter can be used for any source systems as long as the source data can be presented in the specified flat file format.
The source data can reside in custom applications or legacy systems. Universal Adapter is shipped as part of Oracle Business Intelligence Applications, and is not separately priced.
To use Universal Adapter, source data should be presented in flat files according to the following specifications:
Data should be in comma delimited format (*.csv files).
All date values should be in the format of YYYYMMDDHH24MISS. For example, 20071231140300 should be used for December 31, 2007, 2:03 pm.
If a string data value contains one or more commas, then it should be double quoted.
Data should start from line six of each file. The first five lines of each file will be skipped during ETL process.
Oracle BI Applications provides a sample source file for each supported source file type. Typically, each sample source files contain sample data rows. Sample source files are located in:
MW_HOME\biapps\dwrep\Informatica\Sample Universal Source Files
For a list of sample source files, see Section A.6, "List of Sample Source Files for Universal Adapter."
The sample source file file_budget.csv is shown in Figure A-1.
Table A-4 lists the flat file source tables and the corresponding data warehouse tables for waterfall related data.
Table A-1 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 A-2.
Table A-2 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.
To create data files, Oracle recommends that you use the sample source files provided. For a list of sample source files, see Section A.6, "List of Sample Source Files for Universal Adapter."
To create data files for Universal Adapter for CRM Analytics:
Copy the sample source files (file_xxx.csv) from the MW_HOME\biapps\dwrep\Informatica\Sample Universal Source Files folder to the INFA_HOME\server\infa_shared\SrcFiles directory.
Delete the sample data rows from every sample source file.
You must have all of the empty files in the INFA_HOME\server\infa_shared\SrcFiles folder first for ETL Execution Plans to run without interruptions. Some ETL Execution Plans might contain tasks that you do not need but that would fail and cause interruption in your ETL run if source files expected by the tasks cannot be found in the INFA_HOME\server\infa_shared\SrcFiles folder.
Populate the files that you need in the INFA_HOME\server\infa_shared\SrcFiles folder, making sure that the data starts at line six.
For example, to load opportunity dimension data, you could populate the file_opty.ds.csv file.
In DAC, create an Execution Plan to load the data from these files.
For instructions on creating an Execution Plan, see the Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
This section lists sample source files for Universal Adapter, and contains the following topics:
Section A.6.1, "List of Source Files for Seed Data and Common Dimensions"
Section A.6.2, "List of Source Files for Specific Subject Areas"
This section lists source files for seed data and common dimensions. These source files are mandatory and should be populated when using Universal Adapter.
Table A-3 Seed Data and Common Dimension Source Files
Subject Area | Associated Source Files |
---|---|
Seed Data |
|
Common Dimension |
|
This section lists Subject Areas and their related source files. Populate these source files selectively, depending on your business needs.
Table A-4 Subject Areas and Associated Source Files
Subject Area | Associated Source Files |
---|---|
Automotive Vehicle Sales |
|
Consumer Goods - Performance |
|
Consumer Goods - Promotion |
|
Consumer Goods - Retail Audit |
|
Consumer Goods Trade Funds |
|
Forecasting |
|
Loyalty |
|
Marketing |
|
CRM - Price |
|
Sales |
|
Service |
|
Telecom |
|
Pharma - Account Call |
|
Pharma - Contact Call |
|
Pharma - Direct Sales |
|
Pharma - Indirect Sales |
|
Pharma - Medical Education |
|
Pharma - Objective |
|
CRM - Pharma - Physician Plan Prescription |
|
Pharma - Physician Prescription |
|
Pharma - Plan Prescription |
|
Pharma - Weekly Physician Prescription |
|
Pharma - Profile Rank |
|
Financial Service - Accounts and Application |
|
Financial Service - Accounts and Insurances |
|
Financial Service - Balance History |
|
Public Sector - Benefit |
|
Public Sector - Case |
|
Public Sector - Incident |
|
Public Sector - Lead |
|
For instructions on how to create data files for Universal Adapter, see Section A.5, "How to Create Data Files for Universal Adapter". Use the following table to determine the files that you need to use.
Table A-5 Subject Areas and Associated Source Files for Oracle Enterprise Management Analytics and Oracle Manufacturing Analytics
Subject Area | Associated Source Files |
---|---|
EAM - Asset Failure Analysis |
|
EAM - Asset History |
|
EAM - Asset Maintenance Cost |
|
EAM - Asset Maintenance Transaction |
|
EAM - Asset Maintenance Work Orders |
|
EAM - Asset Meter Reading |
|
EAM - Asset Resource Analyzer |
|
EAM - Inventory Aging |
|
EAM - MRO Inventory |
|
Manufacturing - Lot Genealogy |
|
Manufacturing - Material Usage |
|
Manufacturing - Plan To Produce |
|
Manufacturing - Production Cost |
|
Manufacturing - Resource Usage |
|
Manufacturing - Work Order Performance |
|
Kanban |
|
Quality - Process Quality |
|
Quality – Discrete Quality |
|
Supply Chain - Inventory Aging |
|
Supply Chain - Inventory Balance |
|
Supply Chain - Inventory Transactions |
|
Planning |
|