| Oracle® Business Intelligence Applications Installation Guide for Informatica PowerCenter Users Version 7.9.5.1 Part Number E13765-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Installation Guide for Informatica PowerCenter Users Version 7.9.5.1 Part Number E13765-01 |
|
|
View PDF |
This chapter contains the following topics:
Oracle Business Analytics Warehouse for Life Sciences Overview
Importing Syndicated Data into Oracle Business Analytics Warehouse
Life Sciences Data Loading Issues with Oracle Business Analytics Warehouse
Incremental Updates in the Oracle Business Analytics Warehouse LS Dimension Tables
This section describes the new features for Pharma Analytic, Version 7.9.
New support for loading Syndicated Data with multiple Alignment Types.
Syndicated data can now be loaded with different Alignment Types to support the requirements of each Syndicated data type being loaded.
Location of Alignment Version flag in the DAC has been changed.
The creation of historical Alignment Dimension hierarchy versions is controlled using a flag set within the DAC console. In Pharma Analytics 7.9 the location of this Alignment Version flag has moved to the Source System Parameters screen within the DAC's Design area.
This section describes the key features and architecture of the Oracle Business Analytics Warehouse for Life Sciences Syndicated and Call Activity data.
ETL for direct load of syndicated data.
Syndicated data can be directly loaded into the Oracle Business Analytics Warehouse.
A set of prebuilt processes against common external syndicated data types allows direct load of syndicated data, such as physician plan level Rx data, physician level Rx data, indirect sales data, and weekly Rx data.
Syndicated data staging architecture.
Syndicated data is loaded against a staging architecture. The staging table architecture uses a denormalized time dimension that improves data loading by a factor of how many periods are loaded.
A set of cross-reference files for external key matching are supplied to resolve cross referencing and loading external syndicated data where the accounts, products, territories and contacts IDs are different from the Siebel IDs.
Syndicated data and Call Activity data single grain fact tables.
Single grain syndicated data star schemas for power users and operational users.
Operational syndicated data star schemas for operational reporting.
Single grain call activity fact tables - account calls, account-attendee calls, and contact calls.
Syndicated data Moving Annual Total (MAT) metrics calculated on ETL.
Calculation of rolling period calculations takes advantage of the syndicated data staging architecture to populate common pharmaceutical time aggregate metrics within the Oracle Business Analytics Warehouse.
ETL for syndicated data market aggregation.
ETL process to load the Syndicated Data market fact tables based on aggregations on the product-market hierarchy. Minimizes the requirement to purchase pre-aggregated data by market.
Set of prebuilt aggregation tables for high performance reporting.
Sales level aggregation tables for product-based syndicated data facts.
Sales level aggregation tables for market-based syndicated data facts.
Call activity aggregation tables for reports requiring top-level sales level activities.
De-duplication tables to de-duplicate data when the same contact is assigned to multiple territories for the district level for Physician Plan Level Rx syndicated data only.
Mini dimensions.
Account and contact rank mini dimensions.
Representative specialty LOV dimensions.
W_POSTN_CON_D used only for contact primary address.
Pharmaceutical companies purchase weekly and monthly sales and prescription data, known as syndicated data, from third-party vendors such as IMS, NDC, and Cegedim. Syndicated data vendors acquire data from drug wholesalers and retailers on a daily, weekly, and monthly basis and compile a master file of customers (wholesalers, pharmacies, hospitals, and doctors) and sales or prescription transactions for customers. Measures include indirect sales, indirect units, and prescriptions, and differ by vendor and periodicity. It is used for sales force analysis reporting and customer targeting.
The data is derived from panels of physicians, pharmacies, and so on, and projected nationally. Since the panels may change on a monthly basis, syndicated data suppliers tend to change their projections of sources on a monthly basis leading to full restatements of historical data. Thus, pharmaceutical companies are required to refresh fully the data in their data warehouses. In addition, weekly data requires incremental loading.
After it is delivered by the vendor, the syndicated data must be fully reloaded into the Oracle Business Analytics Warehouse in a timely manner and made available to users in order for them to make use of sales force analysis reporting.
This section includes the following topics:
Data Types Supported in the Oracle Business Analytics Warehouse
Loading Syndicated Data into the Oracle Business Analytics Warehouse
Table F-1 provides terms and definitions related to syndicated loading.
Table F-1 Syndicated Loading Definitions
| Term | Definition |
|---|---|
|
Syndicated data |
Third-party data from vendors that shows sales and prescription results for client defined markets. |
|
Brick |
Micro sales geographic area defined by vendor that contains prescription and sales trends for clients' product groupings or markets. Bricks do not exist in the US where the micro sales geographic area is commonly the postal code or zip code. |
|
NRx |
Abbreviation of new prescriptions. A new prescription is defined as dispensed prescriptions given a new number by the pharmacy, not necessarily new therapy for the patient. |
|
TRx |
Abbreviation of total prescriptions. TRx = NRx + Refills. After the first time a prescription is filled, when it is refilled, the pharmacy refers back to the previous drug ID number and notes this as a refill. |
|
Indirect sales |
Total drug wholesalers product sales values to drug retailers (pharmacies) by brick or zip code. Sales values are calculated as units multiplied by client-selected price, where the price is the warehouse acquisition cost unless the client has chosen to have a different price applied to its direct sales or government depot sales. |
|
Indirect units |
Total drug wholesalers product sales units to drug retailers (pharmacies) by brick or zip code. Sales unit quantity is determined by multiplying package factor to obtain unit value (for example, one carton x 24 bottles = 24 units). |
The Oracle Business Analytics Warehouse supports multiple data types defined by data source type and periodicity, as described in Table F-2.
Table F-2 Data Types Supported in Oracle Business Analytics Warehouse
| Data Type | Description |
|---|---|
|
Prescription data by contact |
Monthly NRx and TRx data for client defined markets that include competitor products data by physician. |
|
Indirect sales brick level |
Monthly wholesaler sales and unit values data for client defined markets that include competitor products aggregated by brick. |
|
Indirect sales account level |
Monthly wholesaler sales and unit values for company products by pharmacy. |
|
Indirect sales zip level |
Monthly wholesaler sales and unit values for client defined markets that include competitor products aggregated by postal code. |
|
Direct sales account level |
Monthly direct factory sales data and unit values to wholesalers for company's products by wholesaler. |
|
Direct sales brick level |
Monthly direct factory sales data and unit values to wholesalers for company's products by brick. |
|
Direct sales zip level |
Monthly direct factory sales data and unit values to wholesalers for company's products by postal code. |
|
Weekly prescription data |
Weekly NRx and TRx data for client defined markets that include competitor products by physician. |
|
Plan level prescription data |
Monthly prescription data by managed care plan for client defined markets that includes competitor products. |
|
Sales market |
Incentives. Monthly incentive compensation data sourced from internal incentive compensation systems and loaded as a new data type in the Oracle Business Analytics Warehouse. |
|
Sales market |
Modified. Monthly incentive compensation data sourced from internal incentive compensation systems and loaded as a new data type in the Oracle Business Analytics Warehouse. |
|
Physician plan level Rx data |
Monthly prescription data for physicians associated with a managed care plan. |
|
Prescription data by zip code |
Monthly prescription data for client defined markets that includes competitor products aggregated by postal code. |
|
Prescription data by brick |
Monthly prescription data for client defined markets that include competitor products aggregated by brick. |
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
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 F-4 to Table F-19 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 BI 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.Table F-3 AlignmentType.csv Mapping File
| ROW_WID | DATA_SRC_CD | ALIGN_TYPE |
|---|---|---|
|
1 |
RXBrk |
Brick |
|
2 |
RXEVM |
Zipcode |
|
3 |
RXPT |
Account/Contact |
|
4 |
RXPrf |
Account/Contact |
|
5 |
RXSMI |
Zipcode |
|
6 |
RXSMM |
Zipcode |
|
7 |
RXXPT |
Account/Contact |
|
8 |
RXZip |
Zipcode |
|
9 |
SlsDirAct |
Account/Contact |
|
10 |
SlsIndAct |
Account/Contact |
|
11 |
SlsIndBrk |
Brick |
|
12 |
SlsIndZip |
Zipcode |
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.
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.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 F-4 to Table F-19 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
Open the DAC Client, and navigate to Design, then Task.
Enter the following query:
LS Load into SyndicatedMarket Fact for*
Eleven tasks are returned.
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.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
Save your work.
This section includes descriptions of the following flat files used in the syndicated data load process:
Table F-4 provides information about the syndicated data flat file for Direct Sales data.
Table F-4 Syndicated Data Flat File - Direct Sales Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: SlsDirAct |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
AMT_DT |
VARCHAR2(15) |
Exchange date for currency code. Date format YYYYMMDD. |
|
|
ACCNT_ID |
VARCHAR2(30) |
External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_AREA_ID |
VARCHAR2(30) |
External brick ID of account's primary address for account level. Note: Corresponding Siebel S_REGION_EXT.ROW_ID (S_CON_ADDR.BRICK_ID) should be provided in cross-reference file. |
|
|
ACCNT_CITY |
VARCHAR2(50) |
Account city. Initial capital format; for example, Short Hills. |
|
|
ACCNT_COUNTRY |
VARCHAR2(30) |
Account country. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
ACCNT_ZIPCODE |
VARCHAR2(30) |
Account zip code. |
|
|
AMT_CURCY_CD |
VARCHAR2(30) |
Currency code for Amount. |
|
|
PAYER_TYPE_ID |
VARCHAR2(30) |
External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
PROD_ID |
VARCHAR2(30) |
External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
S_AMT_M01 |
NUMBER(22,7) |
Stores sales amounts of current month data. |
|
|
S_AMT_M02 |
NUMBER(22,7) |
Stores sales amounts of previous month data. |
|
|
S_AMT_M03 - S_AMT_M06 |
NUMBER(22,7) |
||
|
S_UNIT_M01 |
NUMBER(22,7) |
Stores sales units of current month data (for example, 20050301 data). |
|
|
S_UNIT_M02 |
NUMBER(22,7) |
Stores sales units of previous month data (for example, 20050201 data). |
|
|
S_UNIT_M03 |
NUMBER(22,7) |
Stores sales units of 20050101 data. |
|
|
S_UNIT_M04 - S_UNIT_M026 |
NUMBER(22,7) |
Table F-5 provides information about the syndicated data flat file for Indirect Sales data.
Table F-5 Syndicated Data Flat File - Indirect Sales Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: SlsIndAct (Indirect sales account level) SlsIndZip (Indirect sales zip code level) SlsIndBrk (Indirect sales brick level) |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
AMT_DT |
VARCHAR2(15) |
Exchange date for currency code. Date format YYYYMMDD. |
|
|
ACCNT_ID |
VARCHAR2(30) |
External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_AREA_ID |
VARCHAR2(30) |
External brick ID of account's primary address for account level. Note: Corresponding Siebel S_REGION_EXT.ROW_ID (S_CON_ADDR.BRICK_ID) should be provided in cross-reference file. |
|
|
ACCNT_CITY |
VARCHAR2(50) |
Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills. |
|
|
ACCNT_COUNTRY |
VARCHAR2(30) |
Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
ACCNT_ZIPCODE |
VARCHAR2(30) |
Account zip code. Enter value if DATA_SRC_CD is account level. |
|
|
AMT_CURCY_CD |
VARCHAR2(30) |
Applies to syndicated data only. Currency code for Amount. |
|
|
AREA_ID |
VARCHAR2(30) |
External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
PAYER_TYPE_ID |
VARCHAR2(30) |
External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
PROD_ID |
VARCHAR2(30) |
Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
ZIPCODE |
VARCHAR2(30) |
Syndicate zip code. Enter value if DATA_SRC_CD is zip code level. |
|
|
S_AMT_M01 |
NUMBER(22,7) |
Stores sales amounts of current month data. |
|
|
S_AMT_M02 |
NUMBER(22,7) |
Stores sales amounts of previous month data. |
|
|
S_AMT_M03 |
NUMBER(22,7) |
Stores sales amounts of 20050101 data. |
|
|
S_AMT_M04 - S_AMT_M026 |
NUMBER(22,7) |
||
|
S_UNIT_M01 |
NUMBER(22,7) |
Stores sales units of current month data. |
|
|
S_UNIT_M02 |
NUMBER(22,7) |
Stores sales units of previous month data. |
|
|
S_UNIT_M03 |
NUMBER(22,7) |
Stores sales units of 20050101 data. |
|
|
S_UNIT_M04 - S_UNIT_M26 |
NUMBER(22,7) |
Table F-6 provides information about the syndicated data flat file for Prescription data.
Table F-6 Syndicated Data Flat File - Prescription Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
RXPrf (Prescription data by Contact) RXZip (Prescription data by Zip Code) RXBrk (Prescription data by Brick) RXSMI (Sales Market – Incentives) RXSMM (Sales Market – Modified) |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
AREA_ID |
VARCHAR2(30) |
External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CONTACT_ID |
VARCHAR2(30) |
External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file. |
|
|
CON_AREA_ID |
VARCHAR2(30) |
External syndicate brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CON_CITY |
VARCHAR2(50) |
Contact city. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills |
|
|
CON_COUNTRY |
VARCHAR2(30) |
Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
CON_ZIPCODE |
VARCHAR2(30) |
Contact zip code. Enter value if DATA_SRC_CD is contact level. |
|
|
PAYER_TYPE_ID |
VARCHAR2(30) |
External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
PROD_ID |
VARCHAR2(30) |
External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
ZIPCODE |
VARCHAR2(30) |
Syndicate zip code. Enter value if DATA_SRC_CD is zip code level. |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
NRX_M01 |
NUMBER(22,7) |
Stores New Rx of current month data. |
|
|
NRX_M02 |
NUMBER(22,7) |
Stores New Rx of previous month data. |
|
|
NRX_M03 |
NUMBER(22,7) |
Stores New Rx of 20050101 data. |
|
|
NRX_M04 - NRX_M26 |
NUMBER(22,7) |
||
|
TRX_M01 |
NUMBER(22,7) |
Stores Total Rx of current month data. |
|
|
TRX_M02 |
NUMBER(22,7) |
Stores Total Rx of previous month data. |
|
|
TRX_M03 |
NUMBER(22,7) |
Stores Total Rx of 20050101 data. |
|
|
TRX_M04 - TRX_M26 |
NUMBER(22,7) |
Table F-7 provides information about the syndicated data flat file for Plan Level Rx data.
Table F-7 Syndicated Data Flat File - Plan Level Rx Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: RXPT |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
ACCNT_ID |
VARCHAR2(30) |
External account and plan ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_AREA_ID |
VARCHAR2(30) |
External brick ID of account's primary address. Enter value if DATA_SRC_CD is account level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_CITY |
VARCHAR2(50) |
Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills |
|
|
ACCNT_COUNTRY |
VARCHAR2(30) |
Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
ACCNT_ZIPCODE |
VARCHAR2(30) |
Account zip code. Enter value if DATA_SRC_CD is account level. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
PROD_ID |
VARCHAR2(30) |
Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
NRX_M01 |
NUMBER(22,7) |
Stores New Rx of current month data. |
|
|
NRX_M02 |
NUMBER(22,7) |
Stores New Rx of previous month data. |
|
|
NRX_M03 |
NUMBER(22,7) |
Stores New Rx of 20050101 data. |
|
|
NRX_M04 - NRX_M26 |
NUMBER(22,7) |
||
|
TRX_M01 |
NUMBER(22,7) |
Stores Total Rx of current month data. |
|
|
TRX_M02 |
NUMBER(22,7) |
Stores Total Rx of previous month data. |
|
|
TRX_M03 |
NUMBER(22,7) |
Stores Total Rx of 20050101 data. |
|
|
TRX_M04 - TRX_M26 |
NUMBER(22,7) |
Table F-8 provides information about the syndicated data flat file for Physician Plan Level Rx data.
Table F-8 Syndicated Data Flat File - Physician Plan Level Rx Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: RXXPT |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
ACCNT_ID |
VARCHAR2(30) |
External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_AREA_ID |
VARCHAR2(30) |
External brick ID of account's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_CITY |
VARCHAR2(50) |
Account city. Enter a value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills |
|
|
ACCNT_COUNTRY |
VARCHAR2(30) |
Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
ACCNT_ZIPCODE |
VARCHAR2(30) |
Account zip code. Enter value if DATA_SRC_CD is account level. |
|
|
CONTACT_ID |
VARCHAR2(30) |
External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file. |
|
|
CON_AREA_ID |
VARCHAR2(30) |
External brick ID of contact's primary address. Enter value if DATA_SRC_CD is contact level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CON_CITY |
VARCHAR2(50) |
Contact city. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills |
|
|
CON_COUNTRY |
VARCHAR2(30) |
Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
CON_ZIPCODE |
VARCHAR2(30) |
Contact zip code. Enter value if DATA_SRC_CD is contact level. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
PROD_ID |
VARCHAR2(30) |
Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
NRX_M01 |
NUMBER(22,7) |
Stores New Rx of current month data. |
|
|
NRX_M02 |
NUMBER(22,7) |
Stores New Rx of previous month data. |
|
|
NRX_M03 |
NUMBER(22,7) |
Stores New Rx of 20050101 data. |
|
|
NRX_M04 - NRX_M26 |
NUMBER(22,7) |
||
|
TRX_M01 |
NUMBER(22,7) |
Stores Total Rx of current month data. |
|
|
TRX_M02 |
NUMBER(22,7) |
Stores Total Rx s of previous month data. |
|
|
TRX_M03 |
NUMBER(22,7) |
Stores Total Rx of 20050101 data. |
|
|
TRX_M04 - TRX_M26 |
NUMBER(22,7) |
Table F-9 provides information about the syndicated data flat file for Weekly Early View data.
Table F-9 Syndicated Data Flat File - Weekly Early View Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: RXEVM |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
CONTACT_ID |
VARCHAR2(30) |
External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file. |
|
|
CON_AREA_ID |
VARCHAR2(30) |
External brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CON_CITY |
VARCHAR2(50) |
Contact city. Enter a value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills |
|
|
CON_COUNTRY |
VARCHAR2(30) |
Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
CON_ZIPCODE |
VARCHAR2(30) |
Contact zip code. Enter value if DATA_SRC_CD is contact level. |
|
|
PAYER_TYPE_ID |
VARCHAR2(30) |
External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Week |
|
|
PROD_ID |
VARCHAR2(30) |
Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
NRX_W01 |
NUMBER(22,7) |
Stores New Rx of current week data. |
|
|
NRX_W02 |
NUMBER(22,7) |
Stores New Rx of previous week data. |
|
|
NRX_W03 |
NUMBER(22,7) |
Stores New Rx of 20050101 data. |
|
|
NRX_W04 - NRX_W13 |
NUMBER(22,7) |
||
|
TRX_W01 |
NUMBER(22,7) |
Stores Total Rx of current week data. |
|
|
TRX_W02 |
NUMBER(22,7) |
Stores Total Rx of previous week data. |
|
|
TRX_W03 |
NUMBER(22,7) |
Stores Total Rx of 20050101 data. |
|
|
TRX_W04 - TRX_W13 |
NUMBER(22,7) |
Table F-10 provides information about the syndicated market flat file for Indirect Sales data.
Table F-10 Syndicated Market Flat File - Indirect Sales Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: SlsIndAct (Indirect sales account level) SlsIndZip (Indirect sales zip code level) SlsIndBrk (Indirect sales brick level) |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
MKT_AMT_DT |
VARCHAR2(15) |
Exchange date for currency code. Date format YYYYMMDD. |
|
|
ACCNT_ID |
VARCHAR2(30) |
External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_AREA_ID |
VARCHAR2(30) |
External brick ID of account's primary address for account level. Note: Corresponding Siebel S_REGION_EXT.ROW_ID (S_CON_ADDR.BRICK_ID) should be provided in cross-reference file. |
|
|
ACCNT_CITY |
VARCHAR2(50) |
Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills. |
|
|
ACCNT_COUNTRY |
VARCHAR2(30) |
Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
ACCNT_ZIPCODE |
VARCHAR2(30) |
Account zip code. Enter value if DATA_SRC_CD is account level. |
|
|
AREA_ID |
VARCHAR2(30) |
External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
MARKET_ID |
VARCHAR2(30) |
External market ID for the product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
MKT_AMT_CURCY_CD |
VARCHAR2(30) |
Currency code for Amount. |
|
|
PAYER_TYPE_ID |
VARCHAR2(30) |
External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
ZIPCODE |
VARCHAR2(30) |
Syndicate zip code. Enter value if DATA_SRC_CD is zip code level. |
|
|
MKT_S_AMT_M01 |
NUMBER(22,7) |
Stores sales amounts of current month data. |
|
|
MKT_S_AMT_M02 |
NUMBER(22,7) |
Stores sales amounts of previous month data. |
|
|
MKT_S_AMT_M03 |
NUMBER(22,7) |
Stores sales amounts of 20050101 data. |
|
|
MKT_S_AMT_M04 - MKT_S_AMT_M26 |
NUMBER(22,7) |
||
|
MKT_S_UNIT_M01 |
NUMBER(22,7) |
Stores sales units of current month data. |
|
|
MKT_S_UNIT_M02 |
NUMBER(22,7) |
Stores sales units of previous month data. |
|
|
MKT_S_UNIT_M03 |
NUMBER(22,7) |
Stores sales units of 20050101 data. |
|
|
MKT_S_UNIT_M04 - MKT_S_UNIT_M26 |
NUMBER(22,7) |
Table F-11 provides information about the syndicated market flat file for Prescription data.
Table F-11 Syndicated Market Flat File - Prescription Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
RXPrf (Prescription data by Contact) RXZip (Prescription data by Zip Code) RXBrk (Prescription data by Brick) RXSMI (Sales Market – Incentives) RXSMM (Sales Market – Modified) |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
AREA_ID |
VARCHAR2(30) |
External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CONTACT_ID |
VARCHAR2(30) |
External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file. |
|
|
CON_AREA_ID |
VARCHAR2(30) |
External syndicate brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CON_CITY |
VARCHAR2(50) |
Contact city. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills |
|
|
CON_COUNTRY |
VARCHAR2(30) |
Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
CON_ZIPCODE |
VARCHAR2(30) |
Contact zip code. Enter value if DATA_SRC_CD is contact level. |
|
|
MARKET_ID |
VARCHAR2(30) |
External market ID for the product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
PAYER_TYPE_ID |
VARCHAR2(30) |
External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
ZIPCODE |
VARCHAR2(30) |
Syndicate zip code. Enter value if DATA_SRC_CD is zip code level. |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
MKT_NRX_M01 |
NUMBER(22,7) |
Stores New Rx of current month data. |
|
|
MKT_NRX_M02 |
NUMBER(22,7) |
Stores New Rx of previous month data. |
|
|
MKT_NRX_M03 |
NUMBER(22,7) |
Stores New Rx of 20050101 data. |
|
|
MKT_NRX_M04 - MKT_NRX_M26 |
NUMBER(22,7) |
||
|
MKT_TRX_M01 |
NUMBER(22,7) |
Stores Total Rx of current month data. |
|
|
MKT_TRX_M02 |
NUMBER(22,7) |
Stores Total Rx of previous month data. |
|
|
MKT_TRX_M03 |
NUMBER(22,7) |
Stores Total Rx of 20050101 data. |
|
|
MKT_TRX_M04 - MKT_TRX_M26 |
NUMBER(22,7) |
Table F-12 provides information about the syndicated market flat file for Plan Level Rx data.
Table F-12 Syndicated Market Flat File - Plan Level Rx Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: RXPT |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
ACCNT_ID |
VARCHAR2(30) |
External account and plan ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_AREA_ID |
VARCHAR2(30) |
External brick ID of account's primary address. Enter value if DATA_SRC_CD is account level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_CITY |
VARCHAR2(50) |
Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills |
|
|
ACCNT_COUNTRY |
VARCHAR2(30) |
Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
ACCNT_ZIPCODE |
VARCHAR2(30) |
Account zip code. Enter value if DATA_SRC_CD is account level. |
|
|
MARKET_ID |
VARCHAR2(30) |
External market ID for the product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
MKT_NRX_M01 |
NUMBER(22,7) |
Stores New Rx of current month data (for example, 20050301 data). |
|
|
MKT_NRX_M02 |
NUMBER(22,7) |
Stores New Rx of previous month data (for example, 20050201 data). |
|
|
MKT_NRX_M03 |
NUMBER(22,7) |
Stores New Rxof 20050101 data. |
|
|
MKT_NRX_M04 - MKT_NRX_M26 |
NUMBER(22,7) |
||
|
MKT_TRX_M01 |
NUMBER(22,7) |
Stores Total Rx of current month data (for example, 20050301 data). |
|
|
MKT_TRX_M02 |
NUMBER(22,7) |
Stores Total Rx of previous month data (for example, 20050301 data). |
|
|
MKT_TRX_M03 |
NUMBER(22,7) |
Stores Total Rx of 20050101 data. |
|
|
MKT_TRX_M04 - MKT_TRX_M26 |
NUMBER(22,7) |
Table F-13 provides information about the syndicated market flat file for Physician Plan Level Rx data.
Table F-13 Syndicated Market Flat File - Physician Plan Level Rx Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: RXXPT |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
ACCNT_ID |
VARCHAR2(30) |
External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_AREA_ID |
VARCHAR2(30) |
External brick ID of account's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
ACCNT_CITY |
VARCHAR2(50) |
Account city. Enter a value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills |
|
|
ACCNT_COUNTRY |
VARCHAR2(30) |
Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
ACCNT_ZIPCODE |
VARCHAR2(30) |
Account zip code. Enter value if DATA_SRC_CD is account level. |
|
|
CONTACT_ID |
VARCHAR2(30) |
External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file. |
|
|
CON_AREA_ID |
VARCHAR2(30) |
External brick ID of contact's primary address. Enter value if DATA_SRC_CD is contact level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CON_CITY |
VARCHAR2(50) |
Contact city. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills |
|
|
CON_COUNTRY |
VARCHAR2(30) |
Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
CON_ZIPCODE |
VARCHAR2(30) |
Contact zip code. Enter value if DATA_SRC_CD is contact level. |
|
|
MARKET_ID |
VARCHAR2(30) |
External market ID for product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Month |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
MKT_NRX_M01 |
NUMBER(22,7) |
Stores New Rx of current month data (for example, 20050301 data). |
|
|
MKT_NRX_M02 |
NUMBER(22,7) |
Stores New Rx of previous month data (for example, 20050201 data). |
|
|
MKT_NRX_M03 |
NUMBER(22,7) |
Stores New Rx of 20050101 data (for example, 20050301 data). |
|
|
MKT_NRX_M04 - MKT_NRX_M26 |
NUMBER(22,7) |
||
|
MKT_TRX_M01 |
NUMBER(22,7) |
Stores Total Rx of current month data. |
|
|
MKT_TRX_M02 |
NUMBER(22,7) |
Stores Total Rx of previous month data. |
|
|
MKT_TRX_M03 |
NUMBER(22,7) |
Stores Total Rx of 20050101 data. |
|
|
MKT_TRX_M04 - MKT_TRX_M26 |
NUMBER(22,7) |
Table F-14 provides information about the syndicated market flat file for Weekly Early View data.
Table F-14 Syndicated Market Flat File - Weekly Early View Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
Enter the following Siebel data source type code: RXEVM |
|
INTEGRATION_ID |
VARCHAR2(25) |
NOT NULL |
External integration ID. |
|
START_DT |
VARCHAR2(15) |
NOT NULL |
Syndicate data date format YYYYMMDD. |
|
CONTACT_ID |
VARCHAR2(30) |
External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file. |
|
|
CON_AREA_ID |
VARCHAR2(30) |
External brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file. |
|
|
CON_CITY |
VARCHAR2(50) |
Contact city. Enter a value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills |
|
|
CON_COUNTRY |
VARCHAR2(30) |
Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA. |
|
|
CON_ZIPCODE |
VARCHAR2(30) |
Contact zip code. Enter value if DATA_SRC_CD is contact level. |
|
|
PAYER_TYPE_ID |
VARCHAR2(30) |
External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file. |
|
|
PERIOD_CD |
VARCHAR2(30) |
Enter the following Siebel period code: Week |
|
|
CONV_FACTOR |
NUMBER(22,7) |
Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume. |
|
|
MKT_NRX_W01 |
NUMBER(22,7) |
Stores New Rx of current data. |
|
|
MKT_NRX_W02 |
NUMBER(22,7) |
Stores New Rx of previous data. |
|
|
MKT_NRX_W03 |
NUMBER(22,7) |
Stores New Rx of 20050101 data. |
|
|
MKT_NRX_W04 - MKT_NRX_W14 |
NUMBER(22,7) |
||
|
MKT_TRX_W01 |
NUMBER(22,7) |
Stores Total Rx of current week data. |
|
|
MKT_TRX_W02 |
NUMBER(22,7) |
Stores Total Rx of previous week data. |
|
|
MKT_TRX_W03 |
NUMBER(22,7) |
Stores Total Rx of 20050214 data. |
|
|
MKT_TRX_W04 - MKT_TRX_W13 |
NUMBER(22,7) |
The following tables provide information about flat file cross-reference data population rules:
Table F-15, "Flat File Cross-Reference Data Population Rules for Area (Brick) Data"
Table F-16, "Flat File Cross-Reference Data Population Rules for Account and Plan Data"
Table F-17, "Flat File Cross-Reference Data Population Rules for Payer Type Data"
Table F-18, "Flat File Cross-Reference Data Population Rules for Contact Data"
Table F-19, "Flat File Cross-Reference Data Population Rules for Product and Market Data"
Table F-15 Flat File Cross-Reference Data Population Rules for Area (Brick) Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
EXT_DATASRC_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
|
EXT_INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
External area (brick) ID. Note: This same ID should be used in ACCNT_AREA_ID, AREA_ID, AND CON_AREA_ID syndicated data and syndicated market flat files. |
|
EXT_AREA_NAME |
VARCHAR2(50) |
Area name. Note: The same value should already be loaded in Siebel S_REGION.NAME. |
|
|
EXT_PROVINCE |
VARCHAR2(50) |
Area province. Note: The same value should already be loaded in Siebel S_REGION.PROVINCE. |
|
|
EXT_STATE |
VARCHAR2(50) |
Area state. Note: The same value should already be loaded in Siebel S_REGION.STATE. Use the format all capitals and abbreviated, such as NJ. |
|
|
EXT_COUNTRY |
VARCHAR2(30) |
Area country. Note: The same value should already be loaded in Siebel S_REGION.COUNTRY. Use the initial capital format; for example, France. |
|
|
INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
Siebel area (brick) ID. Note: This value should come from Siebel S_REGION.ROW_ID. |
Table F-16 Flat File Cross-Reference Data Population Rules for Account and Plan Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
EXT_DATASRC_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files. |
|
EXT_INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
External account and plan ID. Note: The same ID should be used in ACCNT_AREA_ID, AREA_ID, AND CON_AREA_ID syndicated data and syndicated market flat files. |
|
EXT_ACCNT_NAME |
VARCHAR2(100) |
Account and plan name. Note: The same value should already be loaded in Siebel S_ORG_EXT.NAME. |
|
|
EXT_ACCNT_NUM |
VARCHAR2(30) |
Account outlet number. |
|
|
INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
Siebel account ID. Note: This value should come from Siebel S_REGION.ROW_ID. |
Table F-17 Flat File Cross-Reference Data Population Rules for Payer Type Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
EXT_DATASRC_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files. |
|
EXT_INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
External payer ID. Note: The same value should be used in the PAYER_TYPE_ID field of the syndicated data and syndicated market flat files. |
|
EXT_PLAN_TYPE |
VARCHAR2(30) |
Plan type, such as Payer, Plan, and so on. Note: The same value should already be loaded in Siebel S_INS_PLAN.PLAN_TYPE. |
|
|
EXT_PAYER_NAME |
VARCHAR2(100) |
Payer type, such as Cash, Government, Third Party, Total, and so on. Note: The same value should already be loaded in Siebel S_INS_PLAN.NAME. |
|
|
INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
Siebel payer ID. Note: This value should come from Siebel S_REGION.ROW_ID. |
Table F-18 Flat File Cross-Reference Data Population Rules for Contact Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
EXT_DATASRC_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files. |
|
EXT_INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
External contact ID. Note: The same value should be used in the CONTACT_ID field of the syndicated data and syndicated market flat files. |
|
EXT_CON_FULL_NAME |
VARCHAR2(102) |
Contact name. Note: The same value should already be loaded in Siebel S_CONTACT.LAST_NAME and S_CONTACT.FST_NAME. |
|
|
EXT_ME_NUM |
VARCHAR2(50) |
Contact ME number. Note: The same value should already be loaded in Siebel S_CONTACT.CSN. |
|
|
INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
Siebel contact ID. Note: This value should come from Siebel S_CONTACT.ROW_ID. |
Table F-19 Flat File Cross-Reference Data Population Rules for Product and Market Data
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
EXT_DATASRC_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files. |
|
EXT_INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
External product and market ID. Note: The same value should be used in the PAYER_TYPE_ID field of the syndicated data and syndicated market flat files. |
|
EXT_PROD_TYPE |
VARCHAR2(30) |
Product and market type. Note: The same value should already be loaded in Siebel S_PROD_INT. |
|
|
EXT_PROD_NAME |
VARCHAR2(50) |
Product and market name. Note: The same value should already be loaded in Siebel PROD_INT.NAME. |
|
|
INTEGRATION_ID |
VARCHAR2(30) |
NOT NULL |
Siebel product ID. Note: This value should come from Siebel S_PROD_INT.ROW_ID. |
This issue is specific to Analytics for Life Sciences and does not affect other products.
The ETL process updates the Oracle Business Analytics Warehouse for Life Sciences with a full refresh or using incremental updates. In DAC, you can run a full load by selecting Tools, then ETL Management, then Reset Data Warehouse. This procedure is usually only used for the initial build. If you run the same execution plan without confirming the Reset Data Warehouse again, then the ETL incrementally updates the Oracle Business Analytics Warehouse.
Running a full refresh load by confirming the Reset Data Warehouse feature deletes all existing information stored in the fact and dimension tables. The following types of incremental update rules are supported on the fact tables.
Account Call Fact. Adds new records (call activity for account) to the fact table.
Attendee Call Fact. Adds new records (call activity for attendee) to the fact table.
Contact Call Fact. Adds new records (call activity for contact) to the fact table
Syndicated Data - Base Direct Sales Fact, Territory Direct Sales Fact, Base Indirect Sales Fact, Territory Indirect Sales Fact, District Indirect Sales Fact, Base Physician Rx Fact, Territory Physician Rx Fact, Base Rx Plan Fact, Territory Rx Plan Fact, Base Weekly Physician Rx Fact, Territory Weekly Physician Rx Fact, Base Physician Rx Plan Fact, Territory Physician Rx Plan Fact. ETL does not support incremental updates. When running the full refresh ETL, all records in the fact and dimension tables are deleted. To maintain a history in the dimension tables (such as multiple alignments), use the incremental ETL. If you need to incrementally update the syndicates data fact tables for incremental syndicated data loading, use one of the following strategies:
For incremental insert. Prepare flat file source data that has new data with the new INTEGRATION_ID. Load the data directly into the staging table and modify the session not to truncate the fact tables. Then use the existing ETL to load data into fact tables.
For incremental update. Create new mappings that do a lookup against the fact tables and run the update. Make sure that INTEGRATION_ID used in the flat file source and in the target tables are not identical. Because the incoming syndicated data in the flat file going to the target tables is in the normalized format, the INTEGRATION_ID must be manipulated properly. However, the constant rule is applied when the INTEGRATION_ID is manipulated during the ETL load. The syndicated data during the first bulk load has the 'original in-coming INTEGRATION_ID' || '-1'; the 26th bulk load has the 'original in-coming INTEGRATION_ID' || '-26'.
The following are known issues with creation of aggregate measure columns in the Syndicated Data fact tables.
With large volumes of syndicated data, the creation of aggregate measures in the Oracle Business Analytics Warehouse can take four times the amount of time needed to load the fact table. This may be unacceptable under circumstances when there are large amounts of syndicated data.
Incremental Updates in the Oracle Business Analytics Warehouse LS Dimension Tables.
MAT aggregate measures are handled by the ETL process and not by metadata. All other aggregate measures are handled by metadata.
This issue is specific to Oracle Business Analytics Warehouse for Life Sciences and does not affect other products. In the LS dimension tables the following incremental updates are supported. Some dimensions have more than one set of attributes.
Base Dimensional Hierarchy Table.
Attribute. Zip code, Brick, or Account/Contact Hierarchy depends on what alignment item type is selected.
Note:
In release 7.9, all Zip Code, Brick and Account/Contact hierarchy alignment can be loaded.Description. Contains the current version of alignment only.
Slowly Changing Dimension Type.
Attribute. Historical Zip code, Brick, or Account/Contact Hierarchy depends on what alignment item type is selected.
Note:
In release 7.9, all Zip Code, Brick and Account/Contact hierarchy alignment can be loaded.Description. Tracks historical alignments. Note the following:
Depending on selected alignment type in AlignmentType.csv, only the selected alignment rules are extracted and loaded into the Oracle Business Analytics Warehouse. You can change the alignment type only when you run a full refresh ETL.
Note:
In release 7.9, all Zip Code, Brick and Account/Contact alignment types can be extracted and loaded.When the alignment rule is loaded into the Oracle Business Analytics Warehouse, the current alignment rule is stored in the W_ALIGNMT_DH base table and the existing historical alignment rules are stored in W_ALIGNVER_DH table.
The creation of Alignment Versions is controlled though the setting of the Alignment Version flag in the DAC console. In Pharma Analytics 7.9 this flag is now set in the Source System Parameters screen within the DAC's Design area. The parameter name is '$$ALIGNMENT_VER' and takes a value of either Y or N.
If the $$ALIGNMENT_VER parameter is set to N, then the ETL makes changes to existing alignments if there is a change on an assignment rule.
If the $$ALIGNMENT_VER parameter is set to Y, it creates a new alignment version. Any existing alignment moves to the W_ALIGNVER_DH table as history. The W_ALIGNVER_DH table is used only when the $$ALIGNMENT_VER parameter is set to Y.
The dimension has these characteristics:
The first alignment after Full load is 1.
Incremental Updates in the Oracle Business Analytics Warehouse LS Dimension Tables
A new version is created when a new assignment rule (new position or position relationship) or an assignment criteria is modified (change in postal code, brick, contact, or account) if the Alignment Version flag is set to Y.
Assignment criteria:
Contact ZIP Code or Account ZIP Code cannot use ZIP Code ranges. Each ZIP Code assigned to a territory needs to be on a separate row, so the same value needs to be entered for both ZIP Code Low and ZIP Code High. For example, if ZIP Code 09654 is assigned to a territory, the value for both ZIP Code High and ZIP Code Low should be 09654. Also, the same ZIP Code should not be assigned twice to the same territory and a ZIP Code should be unique to a territory when using assignment criteria for Contact ZIP Code or Account ZIP Code.
Contact Brick or Account Brick require unique bricks assigned to a territory.
Every new version increases the counter by one and is a sequential number. Any pre-existing history version alignment data moves to W_ALIGNVER_DH historical alignment table whenever a new version is created. That is, the W_ALIGNMT_DH table always maintains the latest version while the W_ALIGNVER_DH table maintains the rest of the historical version if the $$ALIGNMENT_VER parameter is set to Y. So, when the historical alignment dimension table gets too large, your administrator can create a SQL query to delete all attributes for version numbers from the W_ALIGNVER_DH table.
Effective date of the alignment is assignment activation date.
|
 Copyright © 2009, Oracle. All rights reserved. Legal Notices |
|