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

Part Number E35272-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

A Configuring Universal Adapter

This appendix provides instructions for loading data using Universal Adapter.

It contains the following topics:

A.1 What is Universal Adapter for CRM Analytics?

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.

A.2 Data Format Requirements

To use Universal Adapter, source data should be presented in flat files according to the following specifications:

A.3 About the Sample Source Files Provided

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.

Figure A-1 Sample Universal Adapter Source File

This screenshot or diagram is described in surrounding text.

A.4 Configuring Universal Adapter for Oracle Price Analytics

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


A.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 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.

A.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.

A.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.

A.5 How to Create Data Files for Universal Adapter

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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.

A.6 List of Sample Source Files for Universal Adapter

This section lists sample source files for Universal Adapter, and contains the following topics:

A.6.1 List of Source Files for Seed Data and Common Dimensions

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

  • file_codes_common

  • file_costlst_g

  • file_dual_g

  • file_exch_rate

  • file_lov_d

  • file_lst_of_val_g

Common Dimension

  • file_employee

  • file_geo_ds

  • file_int_org

  • file_int_org_dh

  • file_party_org_ds

  • file_party_per_ds

  • file_position_ds

  • file_product

  • file_user


A.6.2 List of Source Files for Specific Subject Areas

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

  • file_asset_ds

  • file_household_ds

  • file_vehicle_fs

Consumer Goods - Performance

  • file_performance_fs

Consumer Goods - Promotion

  • file_plan_promo_ds

  • file_promotion_fs

Consumer Goods - Retail Audit

  • file_invloc_ds

  • file_plan_promo_ds

  • file_rtlaudit_fs

Consumer Goods Trade Funds

  • file_fndhdr_fs

  • file_fndtxn_fs

  • file_fund_ds

  • file_fund_fs

  • file_plan_promo_ds

  • file_terr

Forecasting

  • file_dtl_fcst_fs

  • file_fcst_ser_ds

  • file_industry_ds

  • file_opty_ds

  • file_source

  • file_sum_fcst_fs

  • file_terr

Loyalty

  • file_loy_accrual_item_fs.csv

  • file_loy_activity_fs.csv

  • file_loy_cost_fs.csv

  • file_loy_member_ds.csv

  • file_loy_member_marketing_fs.csv

  • file_loy_member_status_hist_fs.csv

  • file_loy_member_tier_hist_fs.csv

  • file_loy_member_tier_move_a_tmp.csv

  • file_loy_partner_point_block_fs.csv

  • file_loy_point_block_ds.csv

  • file_loy_point_type_ds.csv

  • file_loy_program_ds.csv

  • file_loy_promotion_ds.csv

  • file_loy_promotion_enrollee_fs.csv

  • file_loy_promotion_member_fs.csv

  • file_loy_promotion_product_fs.csv

  • file_loy_redemption_item_fs.csv

  • file_loy_referral_ds.csv

  • file_loy_revenue_fs.csv

  • file_loy_statement_fs.csv

  • file_loy_tier_tiercls_ds.csv

  • file_loy_transaction_id_ds.csv

  • file_loy_voucher_ds.csv

Marketing

  • file_camp_hist_fs

  • file_camp_opty_fs

  • file_geo_region_ds

  • file_household_ds

  • file_household_fs

  • file_industry_ds

  • file_ld_wave_ds

  • file_mktg_cost_fs

  • file_mktg_goal_fs

  • file_mktg_lead_fs

  • file_mktg_lead_opty_h

  • file_mktg_lead_ds

  • file_offer_ds

  • file_ofr_prod_fs

  • file_opty_ds

  • file_period_ds

  • file_person_fs

  • file_regn_ds

  • file_resp_prod_fs

  • file_response_ds

  • file_response_fs

  • file_segment_ds

  • file_source

  • file_terr

  • file_vendor_ds

  • file_wave_ds

CRM - Price

  • file_order_ds.csv

  • file_orderitem_fs.csv

  • file_ordit_wtr_log_fs.csv

  • file_pri_segment_ds.csv

  • file_pri_strategy_ds.csv

  • file_pwf_element.csv

  • file_qteit_wtr_log_fs.csv

  • file_quote_ds.csv

  • file_quoteitem_fs.csv

Sales

  • file_agree_ds

  • file_entlmnt_ds

  • file_geo_region_ds

  • file_industry_ds

  • file_invoice_fs

  • file_offer_ds

  • file_oiprdattr_ds

  • file_opty_cmpt_fs

  • file_opty_con_fs

  • file_opty_ds

  • file_opty_stg_fs

  • file_order_ds

  • file_orderitem_fs

  • file_person_fs

  • file_prdattrnm_ds

  • file_qiprdattr_ds

  • file_quote_ds

  • file_quoteitem_fs

  • file_regn_ds

  • file_revn_fs

  • file_segment_ds

  • file_source

  • file_srvreq_ds

  • file_sstage_ds

  • file_terr

Service

  • file_activity_fs

  • file_agree_ds

  • file_agreeitem_fs

  • file_assess_ds

  • file_asset_ds

  • file_asset_fs

  • file_claimelmnt_ds

  • file_entlmnt_ds

  • file_opty_ds

  • file_quote_ds

  • file_region_ds

  • file_source

  • file_srvreq_ds

  • file_srvreq_fs

  • file_survey_fs

Telecom

  • file_agree_ds

  • file_entlmnt_ds

  • file_industry_ds

  • file_offer_ds

  • file_oiprdattr_ds

  • file_opty_ds

  • file_order_ds

  • file_orderitem_fs

  • file_prilstitem_ds

  • file_qiprdattr_ds

  • file_quote_ds

  • file_quoteitem_fs

  • file_regn_ds

  • file_segment_ds

  • file_source

  • file_srvreq_ds

  • file_sstage_ds

  • file_terr

Pharma - Account Call

  • file_accnt_rnk_ds

  • file_affl_aa_hs

  • file_affl_ac_hs

  • file_affl_ca_hs

  • file_affl_cc_hs

  • file_area_ds

  • file_call_accnt_fs

  • file_call_attnd_fs

  • file_con_rnk_ds

  • file_postn_con_ds

Pharma - Contact Call

  • file_affl_ac_hs

  • file_affl_cc_hs

  • file_area_ds

  • file_call_con_fs

  • file_con_rnk_ds

  • file_postn_con_ds

Pharma - Direct Sales

  • file_accnt_rnk_ds

  • file_affl_aa_hs

  • file_affl_ca_hs

  • file_alignmt_dhs

  • file_area_ds

  • file_ins_plan_ds

  • file_syndd_ds_fs

Pharma - Indirect Sales

  • file_accnt_rnk_ds

  • file_affl_aa_hs

  • file_affl_ca_hs

  • file_alignmt_dhs

  • file_area_ds

  • file_ins_plan_ds

  • file_syndd_ids_fs

Pharma - Medical Education

  • file_area_ds

  • file_con_rnk_ds

  • file_med_att_tmp

  • file_med_cst_tmp

  • file_med_ed_fs

  • file_med_evt_ds

  • file_med_plan_ds

  • file_postn_con_ds

Pharma - Objective

  • file_accnt_rnk_ds

  • file_affl_aa_hs

  • file_affl_ac_hs

  • file_affl_ca_hs

  • file_affl_cc_hs

  • file_alignmt_dhs

  • file_area_ds

  • file_call_accnt_fs

  • file_call_con_fs

  • file_con_rnk_ds

  • file_ins_plan_ds

  • file_objective_fs

  • file_plan_promo_ds

  • file_postn_con_ds

  • file_syndd_ds_fs

  • file_syndd_ids_fs

  • file_syndd_rx_fs

CRM - Pharma - Physician Plan Prescription

  • file_affl_aa_hs

  • file_affl_ac_hs

  • file_affl_ca_hs

  • file_affl_cc_hs

  • file_alignmt_dhs

  • file_area_ds

  • file_con_rnk_ds

  • file_postn_con_ds

  • file_syndd_xpt_fs

Pharma - Physician Prescription

  • file_affl_ac_hs

  • file_affl_cc_hs

  • file_alignmt_dhs

  • file_area_ds

  • file_con_rnk_ds

  • file_ins_plan_ds

  • file_postn_con_ds

  • file_syndd_rx_fs

Pharma - Plan Prescription

  • file_affl_aa_hs

  • file_affl_ca_hs

  • file_alignmt_dhs

  • file_area_ds

  • file_syndd_pt_fs

Pharma - Weekly Physician Prescription

  • file_affl_ac_hs

  • file_affl_cc_hs

  • file_alignmt_dhs

  • file_area_ds

  • file_con_rnk_ds

  • file_ins_plan_ds

  • file_postn_con_ds

  • file_syndd_w_fs

Pharma - Profile Rank

  • file_accnt_rnk_ds

  • file_affl_aa_hs

  • file_affl_ac_hs

  • file_affl_ca_hs

  • file_affl_cc_hs

  • file_area_ds

  • file_con_rnk_ds

  • file_per_rank_fs

  • file_postn_con_ds

Financial Service - Accounts and Application

  • file_agree_ds

  • file_asset_ds

  • file_asset_fs

  • file_balhstry_fs

  • file_fn_holding_ds

  • file_fn_holding_fs

  • file_industry_ds

  • file_ins_claim_ds

  • file_offer_ds

  • file_opty_ds

  • file_region_ds

  • file_regn_ds

  • file_revn_fs

  • file_segment_ds

  • file_source

  • file_src_offr_hs

  • file_srvreq_ds

  • file_srvreq_fs

  • file_sstage_ds

  • file_terr

Financial Service - Accounts and Insurances

  • file_asset_ds

  • file_asset_fs

  • file_claimelmnt_ds

  • file_industry_ds

  • file_ins_claim_ds

  • file_ins_claim_fs

  • file_terr

Financial Service - Balance History

  • file_asset_ds

  • file_balhstry_fs

  • file_industry_ds

Public Sector - Benefit

  • file_case_ds

  • file_benefit_fs

  • file_bnft_plan_ds

Public Sector - Case

  • file_activity_fs

  • file_case_ds

  • file_case_fs

  • file_incident_ds

  • file_lead_ds

  • file_period_ds

  • file_region_ds

  • file_srvreq_ds

  • file_srvreq_fs

Public Sector - Incident

  • file_case_ds

  • file_incident_ds

  • file_incident_fs

  • file_period_ds

Public Sector - Lead

  • file_case_ds

  • file_lead_ds

  • file_lead_fs

  • file_period_ds


A.7 List of Source Files for Oracle Enterprise Asset Management Analytics and Oracle Manufacturing Analytics

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

  • file_eam_asset_ds.csv

  • file_eam_location_ds.csv

  • file_eam_cost_fs.csv

  • file_eam_workorder_fs.csv

  • file_mfg_operations_ds.csv

  • file_mfg_seiban_ds.csv

  • file_workorder_ds.csv

EAM - Asset History

  • file_eam_asset_fs.csv

  • file_eam_asset_ds.csv

  • file_eam_location_ds.csv

EAM - Asset Maintenance Cost

  • file_eam_cost_fs.csv

  • file_eam_asset_ds.csv

  • file_eam_location_ds.csv

  • file_mfg_operations_ds.csv

  • file_mfg_seiban_ds.csv

  • file_workorder_ds.csv

EAM - Asset Maintenance Transaction

  • file_eam_material_usage_fs.csv

  • file_eam_asset_ds.csv

  • file_eam_location_ds.csv

  • file_mfg_operations_ds.csv

  • file_mfg_seiban_ds.csv

  • file_workorder_ds.csv

EAM - Asset Maintenance Work Orders

  • file_eam_asset_ds.csv

  • file_eam_location_ds.csv

  • file_eam_workorder_fs.csv

  • file_mfg_seiban_ds.csv

  • file_workorder_ds.csv

EAM - Asset Meter Reading

  • file_eam_asset_ds.csv

  • file_eam_location_ds.csv

  • file_eam_meter_ds.csv

  • file_eam_meter_reading_fs.csv

  • file_mfg_seiban_ds.csv

  • file_workorder_ds.csv

EAM - Asset Resource Analyzer

  • file_eam_asset_ds.csv

  • file_eam_location_ds.csv

  • file_eam_rsrc_act_fs.csv

  • file_eam_rsrc_std_fs.csv

  • file_mfg_operations_ds.csv

  • file_mfg_seiban_ds.csv

  • file_mfg_res_capacity_fs.csv

  • file_workorder_ds.csv

EAM - Inventory Aging

  • file_inv_aging_fs.csv

EAM - MRO Inventory

  • file_inventory_daily_bal_fs.csv

  • file_inv_lot_ds.csv

  • file_inv_lot_daily_bal_fs.csv

Manufacturing - Lot Genealogy

  • file_inv_lot_ds.csv

  • file_mfg_lot_genealogy_fs.csv

  • file_workorder_ds.csv

Manufacturing - Material Usage

  • file_mfg_operations_ds.csv

  • file_cst_cost_org_book_ds.csv

  • file_cst_val_unit_ds.csv

  • file_mfg_material_usage_fs.csv

  • file_mfg_seiban_ds.csv

  • file_workorder_ds.csv

Manufacturing - Plan To Produce

  • file_mfg_seiban_ds.csv

  • file_mfg_plan_ds.csv

  • file_mfg_planned_production_fs.csv

  • file_cst_cost_org_book_ds.csv

  • file_cst_val_unit_ds.csv

Manufacturing - Production Cost

  • file_cst_cost_org_book_ds.csv

  • file_cst_val_unit_ds.csv

  • file_mfg_seiban_ds.csv

  • file_mfg_prod_cost_fs.csv

  • file_workorder_ds.csv

Manufacturing - Resource Usage

  • file_mfg_operations_ds.csv

  • file_mfg_res_capacity_fs.csv

  • file_mfg_seiban_ds.csv

  • file_mfg_res_xact_fs.csv

  • file_mfg_res_usage_plan_fs.csv

  • file_workorder_ds.csv

Manufacturing - Work Order Performance

  • file_mfg_operations_ds.csv

  • file_mfg_seiban_ds.csv

  • file_mfg_operation_detail_fs.csv

  • file_workorder_ds.csv

Kanban

  • file_kanban_card_ds.csv

  • file_kanban_replen_cycle_fs.csv

  • file_workorder_ds.csv

Quality - Process Quality

  • file_inv_lot_ds.csv

  • file_qa_sample_ds.csv

  • file_qa_spec_ds_prc.csv

  • file_qa_test_ds.csv

  • file_qa_spec_test_ds.csv

  • file_qa_spec_result_fs.csv

  • file_qa_char_ds.csv

  • file_qa_spec_ds.csv

  • file_workorder_ds.csv

Quality – Discrete Quality

  • file_eam_asset_ds.csv

  • file_qa_spec_ds_prc.csv

  • file_qa_test_ds.csv

  • file_qa_char_ds.csv

  • file_qa_plan_ds.csv

  • file_qa_results_fs.csv

  • file_qa_spec_ds.csv

  • file_workorder_ds.csv

Supply Chain - Inventory Aging

  • file_inv_aging_fs.csv

Supply Chain - Inventory Balance

  • file_inventory_daily_bal_fs.csv

  • file_inv_lot_ds.csv

  • file_inv_lot_daily_bal_fs.csv

Supply Chain - Inventory Transactions

  • file_inv_lot_ds.csv

  • file_mfg_seiban_ds.csv

  • file_inv_prod_lot_txn_fs.csv

  • file_workorder_ds.csv

Planning

  • file_mfg_plan_ds.csv

  • file_plan_pegging_details_fs.csv

  • file_workorder_ds.csv