Oracle^® Business Intelligence Applications Installation and Configuration Guide > Oracle Business Analytics Warehouse for Life Sciences Data Considerations > Importing Syndicated Data into Oracle Business Analytics Warehouse >

Loading Syndicated Data into the Oracle Business Analytics Warehouse

The following options are available for loading syndicated data into the Oracle Business Analytics Warehouse:

• Loading From a Flat File Source Using Syndicated Data Staging Tables and Cross-Referencing Tables
• Loading From a Flat File Source Using Syndicated Market Staging Tables

Loading From a Flat File Source Using Syndicated Data Staging Tables and Cross-Referencing Tables

This option is supported in Oracle Business Intelligence Applications Version 7.8.3. To load syndicated data with this option, you have to prepare external data files for syndicated data and cross-reference data as described in tables Table 130 to Table 141 in section Syndicated Data Flat File Formats.

The ETL process will load syndicated data source files as a full load each time, but cross-reference data files will be loaded incrementally. Cross-reference data files must contain only new and updated information each time to support proper incremental loading. When the cross-reference data files are prepared, the data must be already loaded into the transactional database so the proper Siebel row IDs and the corresponding external source keys are resolved and provided in the data files.

Setting the correct alignment type to be used with the syndicated data is critical to loading data through the flat files. The Alignment Type is set in the external file AlignmentType.csv which is installed during the Oracle Business Intelligence Applications installation in the folder. Before running the ETL, the Alignment Type must be set to one of the following options under ALIGN_TYPE field.

• Zipcode
• Account/Contact
• Brick

Then, AlignmentType.csv file should be copied into the SrcFiles folder on Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 7.1.4\Server\SrcFiles.

In previous releases, only one Alignment Type could be set for use with all Syndicated Data Types. In Release 7.9, multiple Alignment Types can be set for different Syndicated Data Types.

For example, Physician Rx data can be loaded with an Account/Contact Alignment Type while Brick level Indirect Sales data can be loaded with a Brick Alignment Type. In addition, the same target Syndicated data table (for example the Physician Rx table) can be loaded with data defined in multiple Alignment Types such as both Account/Contact and Zipcode.

The Syndicated Data Type and Alignment Type rule mappings are set in the AlignmentType.csv file and this data is loaded into the W_ALIGN_TYPE_G table at the start of the ETL process for use in the Syndicated data load. The format of the AlignmentType.csv file is shown below.

NOTE:  Before running the ETL, the values in the "ALIGN_TYPE" column in this file should be updated to reflect the correct Alignment Types to be used with the appropriate Syndicated Data Types listed in the "DATA_SRC_CD" column.

You can change the Alignment Types only when you choose to run the full ETL load process.

Once the syndicated data and cross-reference source files are prepared, then the data can be loaded directly into Oracle Business Analytics Warehouse staging tables. If any invalid data source type is used in the syndicated data files, then the ETL will load as it is. You have to prepare the correct data source type in the data files. Only the data that should not be nullable but is null in data files will be filtered out separately so you can review the rejected data for the next ETL run.

When the syndicated data is loaded from the Oracle Business Analytics Warehouse staging tables to target base tables, such as W_SYNDD_RX_F, the external source keys are replaced with Oracle Business Intelligence keys and the data is loaded in the normalized format.

Then, based on what you defined as a primary alignment type above, the syndicated data in base target tables is replicated into the proper owner position and loaded into the main target tables at the territory level, such as W_SYND_RX_T_F, which contains all base table attributes plus ranking and position information.

Flat File Data Population Rules for Populating TRx Data

This section provides information about various major columns in the W_SYNDD_RX_F table related to populating TRx data.

• INTEGRATION_ID
  • Data type is varchar(30).
  • Value can be any unique number or combination of values coming from the external file source with the appended postfix -##, such as -1, if the data is corresponding to the first month of bulk syndicated data, and -2 if the data is corresponding to the second month of bulk data, and so on.
  • INTEGRATION_ID manipulation is done by LS_EXP_FLATFILE_GENERATE_INTEGRATION_ID transformation. It is required because the incoming INTEGRATION_ID in flat file represents 26 different bulk loads in denormalized format. Therefore, the format must be changed to normalized for the Oracle Business Analytics Warehouse target table.
• DATASOURCE_NUM_ID
  • ETL run specific (a numeric value that indicates the source for the data).
  • Used in standard Siebel mappings to ensure each record carries a value indicating its source.
  • Used in source qualifiers to ensure that lookups on dimension tables when populating fact tables area referencing data that was loaded from a uniform data source.
  • Data source number 1 is reserved for the Siebel transactional database. The external source should use a number other than 1.
  • For syndicated data flat file loads, the external source number (other than 1) is loaded into staging tables. When the data is loaded from the staging tables to target tables and external source keys are matched to Siebel IDs, the data source number is converted as 1 in the target table.
  • Combination of INTEGRATION_ID and DATASOURCE_NUM_ID must be a unique value (mandated by a unique index on both columns).

    This means that the system will not accept the same record from the same source with identical unique IDs.

• DATA_LEVEL_WID
  • Stores a foreign key in the W_LOV_UD table for the record corresponding to that syndicated data source type for the data.
  • Value comes from DATA_SRC_CD in the flat file. For example, in the table W_SYNDD_RX_F, the proper data source type defined in flat file must be one of the following:
    • RXPrf
    • RXZip
    • RXBrk
    • RXSMI
    • RXSMM
  • The standard mapping uses the incoming DATA_SRC_CD code to do a lookup against the W_LOV_UD table to secure the ROW_WID of that data source type and inserts it into the W_SYNDD_RX_F table DATA_LEVEL_WID column.
• PAYER_TYPE_WID
  • Stores a foreign key in the W_INS_PLAN_D table for the record corresponding to that payer type of the data such as Cash, Total.
  • Value comes from PAYER_TYPE_ID in the flat file.
• PERIOD_TYPE_WID
  • Stores a foreign key in the W_LOV_D table for the record corresponding to the period for the data.
  • Value comes from PERIOD_CD in the flat file, such as Month, Week.
  • The standard mapping uses the incoming PERIOD_CD code to do a lookup against the W_LOV_D table to secure the ROW_WID of that period type and inserts it into the W_SYNDD_RX_F table PERIOD_TYPE_WID column
• GEO_WID
  • GEO_WID is a foreign key to the W_GEO_D dimension, which contains a geographical hierarchy based on zip code as the lowest level of detail. It is preconfigured to allow users to roll up a TRx measure to levels such as city, state or province, county, country, and continent.
  • If the flat file source of TRx data load is zip code level, then the ZIPCODE field in the flat file must be provided. Then, the value is performed using the following logic to get loaded in the target GEO_WID:
    • MPLT_GEO_WID_ZIPCODE takes zip code as the incoming value, and the literal NO_DUP_FLG = 'N' ports to do a lookup against W_GEO_D for any record where the ZIPCODE column matches the incoming zip code value and the DUP_ZIPCODE column = 'N'. (Sample data in the W_GEO_D table contains multiple city records per zip code, only one of which is marked as DUP_ZIPCODE = 'N'). The mapping also ensures that even with multiple matches only the first record is retrieved, and with no matches, the data comes back with the appropriate Unspecified code.
• CON_GEO_WID
  • CON_GEO_WID is a foreign key to the W_GEO_D dimension, which contains a geographical hierarchy based on zip code as the lowest level of detail.
  • The following three attributes, city, country, and zip code, are the main keys to determine CON_GEO_WID, and are brought from flat file CON_CITY, CON_COUNTRY, and CON_ZIPCODE fields.
  • If the flat file source of TRx data load is contact level, then CON_CITY, CON_COUNTRY, and CON_ZIPCODE fields in flat file must be provided. Then, the value is performed the following logic to get loaded in target CON_GEO_WID:
    • LS_EXP_FLATFILE_CITY_COUNTRY_ZIP_CHKNULL and LS_MPLT_FLATFILE_GEO_WID use the combination of city, country, and zip code to retrieve the first available record from W_GEO_D even if duplicates exist and regardless of the NO_DUP_FLG.
• ALIGN_WID
  • Depending on your selected alignment item type, the following transformation is performed:
    • When alignment item type is Zipcode:

  IIF(DATA_SRC_CD = 'RXPrf', IN_CON_GEO_WID,

  IIF(DATA_SRC_CD = 'RXBrk' OR DATA_SRC_CD = 'RXSMI' OR DATA_SRC_CD = 'RXSMM' OR DATA_SRC_CD = 'RXZip', IN_GEO_WID,

  ETL_UNSPEC_NUM))

  • When alignment item type is Account/Contact:

  IIF(DATA_SRC_CD = 'RXBrk' OR DATA_SRC_CD = 'RXPrf' OR DATA_SRC_CD = 'RXSMI' OR DATA_SRC_CD = 'RXSMM' OR DATA_SRC_CD = 'RXZip', IN_CONTACT_WID, ETL_UNSPEC_NUM)

  • When alignment item type is Brick:

  IIF(DATA_SRC_CD = 'RXPrf', IN_CON_AREA_WID,

  IIF(DATA_SRC_CD = 'RXBrk' OR DATA_SRC_CD = 'RXSMI' OR DATA_SRC_CD = 'RXSMM' OR DATA_SRC_CD = 'RXZip', IN_AREA_WID,

  ETL_UNSPEC_NUM))

  The resulting value is used to populate the ALIGN_WID column in the fact table, which should also be found in W_ALIGNMT_DH table ALIGN_WID column.

  Based on this ALIGN_WID column, Oracle Business Analytics Warehouse replicates proper territory and populates W_SYND_RX_T_F table as well.

  NOTE:  This section discusses the W_SYNDD_RX_F table as an example only. In the Oracle Business Analytics Warehouse there are six tables used for the different syndicated data sources which are loaded in the same manner: W_SYNDD_DS_F, W_SYNDD_IDS_F, W_SYNDD_RX_F, W_SYNDD_PT_F, W_SYNDD_XPT_F, and W_SYNDD_W_F tables.

Loading From a Flat File Source Using Syndicated Market Staging Tables

The Oracle Business Analytics Warehouse supports loading syndicated market data using flat files. You have to prepare flat files of syndicated market data source and cross-referencing data source files as described in tables Table 130 to Table 141 in section Syndicated Data Flat File Formats.

By default this option is disabled. To use this feature, you must turn on the option manually in the DAC client. Turning on this option allows the flat file syndicated market data to be loaded directly into the Oracle Business Analytics Warehouse staging table W_SYNDM_RX_FS, and then loaded into the target table W_SYNDM_RX_F.

To load syndicated market data using flat files

1. Open the DAC client, and navigate to Design > Task.
2. Enter the following query:

   LS Load into SyndicatedMarket Fact for*

   Eleven tasks are returned.

3. Deactivate the following tasks, which, by default, are turned on:
   • LS Load into SyndicatedMarket Fact for Calculated Exponent Plantrak
   • LS Load into SyndicatedMarket Fact for Calculated Indirect Sales
   • LS Load into SyndicatedMarket Fact for Calculated Plantrak
   • LS Load into SyndicatedMarket Fact for Calculated Rx
   • LS Load into SyndicatedMarket Fact for Calculated Weekly Early View

     NOTE:  Do not deactivate LS Load into SyndicatedMarket Fact for Calculated Direct Sales.

4. Activate the following tasks:
   • LS Load into SyndicatedMarket Fact for Exponent Plantrak
   • LS Load into SyndicatedMarket Fact for Indirect Sales
   • LS Load into SyndicatedMarket Fact for Plantrak
   • LS Load into SyndicatedMarket Fact for Rx
   • LS Load into SyndicatedMarket Fact for Weekly Early View
5. Save your work.