A Configuring Universal Adapter for CRM Analytics

This appendix provides instructions for Configuring Universal Adapter for CRM Analytics, and contains the following topics:

• Section A.1, "What is Universal Adapter for CRM Analytics?"

• Section A.2, "Data Format Requirements"

• Section A.3, "About the Sample Source Files Provided"

• Section A.4, "How to Create Data Files for Universal Adapter for CRM Analytics"

• Section A.5, "List of Sample Source Files for Universal Adapter for CRM Analytics"

A.1 What is Universal Adapter for CRM Analytics?

Universal Adapter for CRM Analytics enables you to extract data from CRM 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 for CRM Analytics can be used for any source systems as long as the source data can be presented in the specified flat file format.

The CRM source data could also reside in custom applications or legacy systems. Universal Adapter for CRM Analytics is shipped as part of any of the Oracle BI Applications CRM Analytics products and is not separately priced.

A.2 Data Format Requirements

To use Universal Adapter for CRM Analytics, 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.

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:

<OracleBI>\dwrep\Informatica\Sample Universal Source Files

For a list of sample source files, see Section A.5, "List of Sample Source Files for Universal Adapter for CRM Analytics".

The sample source file file_budget.csv is shown in the figure below:

Figure A-1 Sample Universal Adapter Source File

A.4 How to Create Data Files for Universal Adapter for CRM Analytics

To create data files, Oracle recommends that you use the sample source files provided (for a list of sample source files, see Section A.5, "List of Sample Source Files for Universal Adapter for CRM Analytics").

To create data files for Universal Adapter for CRM Analytics:

1. Copy the sample source files (file_xxx.csv) from the <OracleBI>\dwrep\Informatica\Sample Universal Source Files folder to the <Informatica PowerCenter>\Server\SrcFiles folder.

2. Delete the sample data rows from every sample source file.

   You must have all of the empty files in the <Informatica PowerCenter>\Server\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 <Informatica PowerCenter>\Server\SrcFiles folder.

3. Populate the files that you need in the <Informatica PowerCenter>\Server\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.5 List of Sample Source Files for Universal Adapter for CRM Analytics

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

• Section A.5.1, "List of Source Files for Seed Data and Common Dimensions"

• Section A.5.2, "List of Source Files for Specific Subject Areas"

A.5.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 the CRM Universal Adapter.

Table A-1 Seed Data and Common Dimension Source Files

CRM 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.5.2 List of Source Files for Specific Subject Areas

This section lists CRM Subject Areas and their related source files. Populate these source files selectively, depending on your business needs.

Table A-2 Subject Areas and Associated Source Files

CRM Subject Area	Associated Source Files
CRM - Forecasting	file_dtl_fcst_fs file_fcst_ser_ds file_industry_ds file_opty_ds file_source file_sum_fcst_fs file_terr
CRM - 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
CRM - 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
CRM - 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
CRM - 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
CRM - Automotive Vehicle Sales	file_asset_ds file_household_ds file_vehicle_fs
CRM - 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
CRM - Consumer Goods Trade Funds	file_fndhdr_fs file_fndtxn_fs file_fund_ds file_fund_fs file_plan_promo_ds file_terr
CRM - Consumer Goods - Performance	file_performance_fs
CRM - Consumer Goods - Promotion	file_plan_promo_ds file_promotion_fs
CRM - Consumer Goods - Retail Audit	file_invloc_ds file_plan_promo_ds file_rtlaudit_fs
CRM - 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
CRM - 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
CRM - 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
CRM - 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
CRM - 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
CRM - 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
CRM - 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
CRM - Pharma - Plan Prescription	file_affl_aa_hs file_affl_ca_hs file_alignmt_dhs file_area_ds file_syndd_pt_fs
CRM - 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
CRM - 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
CRM - 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
CRM - 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
CRM - Financial Service - Balance History	file_asset_ds file_balhstry_fs file_industry_ds
CRM - Public Sector - Benefit	file_case_ds file_benefit_fs file_bnft_plan_ds
CRM - 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
CRM - Public Sector - Incident	file_case_ds file_incident_ds file_incident_fs file_period_ds
CRM - Public Sector - Lead	file_case_ds file_lead_ds file_lead_fs file_period_ds