| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19039-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19039-01 |
|
|
PDF · Mobi · ePub |
This section describes how to configure Oracle Pharma Analytics. It contains the following topics:
Section 14.1, "Overview of Oracle Business Analytics Warehouse for Life Sciences"
Section 14.2, "Importing Syndicated Data into Oracle Business Analytics Warehouse"
Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load"
Section 14.5, "Supporting Restartability in Syndicated Data and Market Load"
Section 14.6, "Life Sciences Data Loading Issues with Oracle Business Analytics Warehouse"
Section 14.7, "Incremental Updates in the Oracle Business Analytics Warehouse LS Dimension Tables"
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 direct sales data, indirect sales data, prescription data, plan level Rx data, physician plan level Rx data, and weekly early view 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 and market, Indirect Sales syndicated data, and market 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:
Section 14.2.2, "Data Types Supported in the Oracle Business Analytics Warehouse"
Section 14.2.3, "Loading Syndicated Data into the Oracle Business Analytics Warehouse"
Table 14-1 provides terms and definitions related to syndicated loading.
Table 14-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 14-2.
Table 14-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:
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 14-4 through Table 14-9 and Table 14-15 through Table 14-19 in Section 14.3, "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 located in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles). Before running the ETL, the Alignment Type must be set to one of the following options under ALIGN_TYPE field.
Zipcode
Account/Contact
Brick
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 in Table 14-3.
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 14-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 14-10 through Table 14-14 and Table 14-15 through Table 14-19 in Section 14.3, "Syndicated Data Flat File Formats."
By default this option is disabled. To use this feature, you must turn on the option manually in DAC. 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:
In DAC, go to the Design view, and display the Tasks tab.
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.
Prepare the flat files based on the formats provided in this section and copy the formatted flat files to the SrcFiles directory on the Informatica server computer (for example, INFA_HOME\server\infa_shared\SrcFiles) before starting the ETL run.
This section includes descriptions for the following flat files used in the syndicated data load process:
Section 14.3.1, "Syndicated Data Flat File for Direct Sales Data"
Section 14.3.2, "Syndicated Data Flat File for Indirect Sales Data"
Section 14.3.3, "Syndicated Data Flat File for Prescription Data"
Section 14.3.4, "Syndicated Data Flat File for Plan Level Rx Data"
Section 14.3.5, "Syndicated Data Flat File for Physician Plan Level Rx Data"
Section 14.3.6, "Syndicated Data Flat File for Weekly Early View Data"
Section 14.3.7, "Syndicated Market Flat File for Indirect Sales Data"
Section 14.3.8, "Syndicated Market Flat File for Prescription Data"
Section 14.3.9, "Syndicated Market Flat File for Plan Level Rx Data"
Section 14.3.10, "Syndicated Market Flat File for Physician Plan Level Rx Data"
Section 14.3.11, "Syndicated Market Flat File for Weekly Early View Data"
Section 14.3.12, "Flat File Cross-Reference Data Population Rules"
Table 14-4 provides information on the syndicated data flat file for the Direct Sales data. Save the flat file as LoadSyndData_DS.txt, use the pipe (|) symbol as the field separator.
Table 14-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_M26 |
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_M26 |
NUMBER(22,7) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-5 provides information on the syndicated data flat file for the Indirect Sales data. Save the flat file as LoadSyndData_IDS.txt, use the pipe (|) symbol as the field separator.
Table 14-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_M26 |
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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-6 provides information on the syndicated data flat file for the Prescription data. Save the flat file as LoadSyndData_RX.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-7 provides information about the syndicated data flat file for the Plan Level Rx data. Save the flat file as LoadSyndData_PT.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-8 provides information on the syndicated data flat file for the Physician Plan Level Rx data. Save the flat file as LoadSyndData_XPT.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-9 provides information on the syndicated data flat file for Weekly Early View data. Save the flat file as LoadSyndData_W.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-10 provides information on the syndicated market flat file for Indirect Sales data. Save the flat file as LoadSyndMarket_IDS.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-11 provides information on the syndicated market flat file for Prescription data. Save the flat file as LoadSyndMarket_RX.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-12 provides information on the syndicated market flat file for Plan Level Rx data. Save the flat file as LoadSyndMarket_PT.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-13 provides information about the syndicated market flat file for Physician Plan Level Rx data. Save the flat file as LoadSyndMarket_XPT.txt, use the pipe (|) symbol as the field separator.
Table 14-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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
Table 14-14 provides information on the syndicated market flat file for the Weekly Early View data. Save the flat file as LoadSyndMarket_W.txt, use the pipe (|) symbol as the field separator.
Table 14-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. |
|
MARKET_ID |
VARCHAR2(30) |
- |
External market ID for the product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in the 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: 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_W13 |
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) |
- |
- |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 Use the field to provide the partition key when partition is enabled. Leave this field blank if partition is not used. see Section 14.4, "Supporting Database Partitioning in Syndicated Data and Market Load."
The following tables provide information about flat file cross-reference data population rules. Each file should be prepared with the pipe (|) symbol as the field separator.
Table 14-15 Flat File Cross-Reference Data Population Rules for Area (Brick) Data, LoadXRef_Area.txt
| 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 14-16 Flat File Cross-Reference Data Population Rules for Account and Plan Data, LoadXRef_Account.txt
| 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 14-17 Flat File Cross-Reference Data Population Rules for Payer Type Data, LoadXRef_Payer.txt
| 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 14-18 Flat File Cross-Reference Data Population Rules for Contact Data, LoadXRef_Contact.txt
| 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 14-19 Flat File Cross-Reference Data Population Rules for Product and Market Data, LoadXRef_Product.txt
| 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 section contains the following topics:
Section 14.4.2, "Definitions for Database Partition Components"
Section 14.4.5, "How to Prepare Flat Files for Database Partition"
Section 14.4.6, "How to Enable Database Partition Feature for Syndicated Data and Market Fact"
Section 14.4.7, "How to Disable Database Partition Feature for Syndicated Data and Market Fact"
Oracle Business Analytics Warehouse for Life Sciences is designed to process six different types of syndicated data at high level. During syndicated data loading, ETL always does a full load on syndicated data and market that truncates the existing data and reloads with new data. This is because syndicated data loading does not support incremental load by nature. But there are user requirements to maintain syndicated data on multiple countries or regions and load only one country's or one region's data at a time without affecting the data of other countries or regions in the table. The difficulties faced in the previous releases when doing this type of data loading are addressed in this release.
In Release 7.9.6.3, Oracle Business Analytics Warehouse for Life Sciences supports syndicated data and market on multiple countries or regions by using the database partitioning feature. Without impacting other countries' data in the same target table, you have the flexibility to drop and reload syndicated data for one or more than one country or region within one ETL.
When preparing the syndicated data and market flat files, you must provide values in a new field for the partition key, COUNTRY_REGION_NAME, for the country's or region's name. Based on the values provided in COUNTRY_REGION_NAME, each country's or region's data is loaded into the specified partition respectively.
Note:
Syndicated data partitioning is supported only on the Oracle platform. If you require partition on other database platforms, then customize the partition feature to suite your requirements.Table 14-20 provides terms and definitions related to Database Partition.
Table 14-20 Database Partition Definition for Syndicated Data and Market Load
| Term | Definition |
|---|---|
|
Partition Column [or, Partition Key] |
The key column name that is used for partition in a table. The partition column, COUNTRY_REGION_NAME, is created for each table by default for the syndicated data and market load. |
|
Partition Method |
The way of defining partition. There are RANGE, LIST, HASH, and COMPOSITE partitioning methods. For syndicated data and market load, the LIST partitioning method is used. |
|
Partition Name |
The name to specify each partition for COUNTRY_REGION_NAME. Foot 1 |
|
Partition Value |
The actual value that is used in each partition name and each flat file. Foot 2 |
|
Default Partition |
Each table should have a DEFAULT partition value. Unmatched data found when comparing the flat file with the partition value is stored to the DEFAULT partition value.Foot 3 |
Footnote 1 Do not provide space in a partition name and all characters must be in upper case. The partition name must be equal to the partition value. For example, use partition name such as USA, UNITEDKINGDOM, or FRANCE.
Footnote 2 Do not provide space in partition values and all characters must be in upper case. The partition value must be equal to the partition name. Also, the same value must be used in the COUNTRY_REGION_NAME field in each flat file source. For example, use the partition name such as 'USA', 'UNITEDKINGDOM', or 'FRANCE'.
Footnote 3 Set the partition name as UNSPECIFIED. Set the partition value as DEFAULT for the data with partition key not present in the partition list.
To use the Database Partition feature, decide on the following, depending on the business requirement:
the syndicated data type that requires a partition.
how many partitions you require for the COUNTRY_REGION_NAME.
the partition name that must be used.
the partition value for each partition name that must be used.
In Release 7.9.6.3, there are several important restrictions that must be followed to select a partition name and partition value.
One partition name can have only one partition value defined.
The partition name must be equal to the partition value.
There must be no space in the partition name and value, and all text characters must be in upper case.
For example, if you want to load the syndicated data and market for direct sales data every month, and the data is for three different countries—U.S.A, United Kingdom, and France, and you decide to use the Database Partition for the direct sales data, then use the following partition name and value.
Based on the business requirement in pharmaceutical companies, the countries or regions to be partitioned in syndicated data and market tables can be very different. So, you must rebuild these related tables when the Partition feature is enabled.
The tables, Table 14-22 through Table 14-27 summarizes the fact tables in group for each syndicated data type. For example, to load syndicated data and market for direct sales data as partitioned, all the fact table group listed in Table 14-22 must be recreated as partitioned.
The following steps are recommended to recreate tables with partition:
Create a data warehouse schema in DAC.
Note:
For detailed information on how to create data warehouse tables using DAC, see Oracle Business Intelligence Applications Installation Guide.Decide on whether you want to use Database Partition for the entire syndicated data and market fact tables or selectively use a few syndicated data types.
For example, use the following steps to partition only the direct sales data.
Use any utility such as Oracle SQL Developer to export the DDL script from existing schema in the data warehouse based on the list provided in Table 14-22 for the direct sales data fact tables group.
After the DDL script is exported, to recreate the partition table, modify the script based on the following syntax:
DROP TABLE [table_name]; CREATE TABLE [table_name] ( [column_1],[column_2],…[column_N] ) PARTITION BY LIST ( COUNTRY_REGION_NAME ) ( PARTITION [partition_name_1] VALUES ( [partition_value_1] ), … PARTITION [partition_name_N] VALUES ( [partition_value_N] ),PARTITION UNSPECIFIED VALUES (DEFAULT));
Note:
A partition name can have only one partition value defined.The partition name must be equal to the partition value.
There must be no space in the partition name and value, and the text must be in upper case.
For example, if you want to enable partition on the W_SYNDD_DS_F fact table for three countries—USA, United Kingdom, and France—then use the following SQL statement:
DROP TABLE W_SYNDD_DS_F;
CREATE TABLE W_SYNDD_DS_F
(
ACCNT_AREA_WID NUMBER (10) DEFAULT 0 NOT NULL,
ACCNT_GEO_WID NUMBER (10) DEFAULT 0 NOT NULL,
ACCNT_WID NUMBER (10) DEFAULT 0 NOT NULL,
ALIGN_WID NUMBER (10) DEFAULT 0 NOT NULL,
DATASOURCE_NUM_ID NUMBER (10) DEFAULT 0 NOT NULL,
DATA_LEVEL_WID NUMBER (10) DEFAULT 0 NOT NULL,
ETL_PROC_WID NUMBER (10) DEFAULT 0 NOT NULL,
EXT_INTEGRATION_ID VARCHAR2 (30 CHAR) DEFAULT '0' NOT NULL,
INTEGRATION_ID VARCHAR2 (30 CHAR) DEFAULT '0' NOT NULL,
MONTH_WID NUMBER (10) DEFAULT 0 NOT NULL,
PAYER_TYPE_WID NUMBER (10) DEFAULT 0 NOT NULL,
PERIOD_DAY_WID NUMBER (10) DEFAULT 0 NOT NULL,
PERIOD_TYPE_WID NUMBER (10) DEFAULT 0 NOT NULL,
PROD_WID NUMBER (10) DEFAULT 0 NOT NULL,
ROW_WID NUMBER (10) DEFAULT 0 NOT NULL,
MAT_S_AMT NUMBER (22,7),
MAT_S_UNIT NUMBER (22,7),
S_AMT NUMBER (22,7),
S_UNIT NUMBER (22,7),
U_AMT_DT DATE,
U_SALES_AMT NUMBER (22,7),
U_AMT_CURCY_CD VARCHAR2 (30 CHAR),
ACCNT_ID VARCHAR2 (30 CHAR),
PROD_ID VARCHAR2 (30 CHAR),
PERIOD_DT DATE,
X_CUSTOM VARCHAR2 (10 CHAR),
COUNTRY_REGION_NAME VARCHAR2 (30 CHAR) DEFAULT 'UNSPECIFIED' NOT NULL
)
PARTITION BY LIST (COUNTRY_REGION_NAME)
(PARTITION USA VALUES ('USA'),
PARTITION UNITEDKINGDOM VALUES ('UNITEDKINGDOM'),
PARTITION FRANCE VALUES ('FRANCE'),
PARTITION UNSPECIFIED VALUES (DEFAULT)
);
Repeat step 3 for all fact tables for direct sales group in Table 14-22.
Execute the DDL script to recreate the partitioned tables.
Table 14-22 Syndicated Data and Market Fact Tables Group for Direct Sales Data
| Type | Table Name |
|---|---|
|
Syndicated Data Level |
W_SYNDD_DS_F W_SYND_DS_T_F W_SD_DS_PAR1_A W_SD_DS_PAR2_A W_SD_DS_PAR3_A W_SD_DS_TOP_A |
|
Syndicated Market Level |
W_SYNDM_DS_F W_SYNM_DS_T_F W_SM_DS_PAR1_A W_SM_DS_PAR2_A W_SM_DS_PAR3_A W_SM_DS_TOP_A |
Table 14-23 Syndicated Data and Market Fact Tables Group for Indirect Sales Data
| Type | Table Name |
|---|---|
|
Syndicated Data Level |
W_SYNDD_IDS_F W_SYND_IDS_T_F W_SYND_IDS_D_F W_SD_IDS_PAR1_A W_SD_IDS_PAR2_A W_SD_IDS_PAR3_A W_SD_IDS_TOP_A |
|
Syndicated Market Level |
W_SYNDM_IDS_F W_SYNM_IDS_T_F W_SYNM_IDS_D_F W_SM_IDS_PAR1_A W_SM_IDS_PAR2_A W_SM_IDS_PAR3_A W_SM_IDS_TOP_A |
Table 14-24 Syndicated Data and Market Fact Tables Group for Prescription Data
| Type | Table Name |
|---|---|
|
Syndicated Data Level |
W_SYNDD_RX_F W_SYND_RX_T_F W_SD_RX_PAR1_A W_SD_RX_PAR2_A W_SD_RX_PAR3_A W_SD_RX_TOP_A |
|
Syndicated Market Level |
W_SYNDM_RX_F W_SYNM_RX_T_F W_SM_RX_PAR1_A W_SM_RX_PAR2_A W_SM_RX_PAR3_A W_SM_RX_TOP_A |
Table 14-25 Syndicated Data and Market Fact Tables Group for Physician Plan Level Rx Data
| Type | Table Name |
|---|---|
|
Syndicated Data Level |
W_SYNDD_XPT_F W_SYND_XPT_D_F W_SYND_XPT_T_F W_SD_XPT_PAR1_A W_SD_XPT_PAR2_A W_SD_XPT_PAR3_A W_SD_XPT_TOP_A |
|
Syndicated Market Level |
W_SYNDM_XPT_F W_SYNM_XPT_D_F W_SYNM_XPT_T_F W_SM_XPT_PAR1_A W_SM_XPT_PAR2_A W_SM_XPT_PAR3_A W_SM_XPT_TOP_A |
Table 14-26 Syndicated Data and Market Fact Tables Group for Plan Level Rx Data
| Type | Table Name |
|---|---|
|
Syndicated Data Level |
W_SYNDD_PT_F W_SYND_PT_T_F W_SD_PT_PAR1_A W_SD_PT_PAR2_A W_SD_PT_PAR3_A W_SD_PT_TOP_A |
|
Syndicated Market Level |
W_SYNDM_PT_F W_SYNM_PT_T_F W_SM_PT_PAR1_A W_SM_PT_PAR2_A W_SM_PT_PAR3_A W_SM_PT_TOP_A |
Table 14-27 Syndicated Data and Market Fact Tables Group for Weekly Early View Data
| Type | Table Name |
|---|---|
|
Syndicated Data Level |
W_SYNDD_W_F W_SYND_W_T_F W_SD_W_PAR1_A W_SD_W_PAR2_A W_SD_W_PAR3_A W_SD_W_TOP_A |
|
Syndicated Market Level |
W_SYNDM_W_F W_SYNM_W_T_F W_SM_W_PAR1_A W_SM_W_PAR2_A W_SM_W_PAR3_A W_SM_W_TOP_A |
This section contains the following topics:
Section 14.4.5.1, "Syndicated Data and Market Flat File Format"
Section 14.4.5.2, "Define the Relation Between Top Level Position and Partition Value in Flat File"
The flat file format for syndicated data and market fact data load supports the Database Partition feature. The new field, COUNTRY_REGION_NAME, is added in each syndicated data and market flat file as the LAST position of the field.
If you are not using Database Partition, then in the flat file, the value for the COUNTRY_REGION_NAME field should be empty. If you want to use Database Partition, then the value of the COUNTRY_REGION_NAME field in each flat file must be equal to one of the partition names (and partition values) that are provided in the DDL script of Section 14.4.4, "How to Create Partition Table."
Table 14-28 represents the field definition used in each syndicated data and market flat file.
Note:
For information about the flat file formats, see Section 14.3, "Syndicated Data Flat File Formats."For syndicated data, see Section 14.3.1, "Syndicated Data Flat File for Direct Sales Data" through Section 14.3.6, "Syndicated Data Flat File for Weekly Early View Data."
For syndicated market, see Section 14.3.7, "Syndicated Market Flat File for Indirect Sales Data" through Section 14.3.11, "Syndicated Market Flat File for Weekly Early View Data."
Table 14-28 Field Definition for Partition Column in Syndicated Data and Market Flat File
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
- |
Country or Region name.Foot 1 |
Footnote 1 This field is the partition column and the value for this filed is null if Database Partition is disabled; else, enter the value that is provided as the partition value (and partition name) used in the DDL script. There must be no space in the string and all the characters must be in upper case. For example, text characters to be used in the partition column can in this format: 'USA', 'UNITEDKINGDOM' or 'FRANCE'.
Users logging in to the analytics server for Life Sciences, after Database Partition is enabled, require specific information with regard to the country or region to which they belong. This information is important for the user to access the relevant country or region related partition data. To provide this information to the user, you must prepare the Load_TopPostnPartition.txt flat file based on the format provided in Table 14-29.
In the flat file, provide unique and complete list of relation between the top level sales force position and partition value, use the pipe (|) symbol as the field separator, save the file as Load_TopPostnPartition.txt, and then copy the file into the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles), before starting to load syndicated data and market.
Table 14-29 Flat File Layout for Load_TopPostnPartition.txt
| Field Name | Data Type | Field Required | Field Description |
|---|---|---|---|
|
CURRENT_TOP_LVL_POSTN |
VARCHAR2 (50) |
Not Null |
Current Top Level Position NameFoot 1 |
|
COUNTRY_REGION_NAME |
VARCHAR2 (30) |
Not Null |
Country or Region nameFoot 2 |
Footnote 1 Corresponding W_POSITION_DH. CURRENT_TOP_LVL_POSTN should be provided in the flat file. This field is case sensitive and matched case must be provided.
Footnote 2 The value of COUNTRY_REGION_NAME field must be equal to one of the partition names (and partition values) in the DDL script. There must be no space in the character string and all characters must be upper case. For example, use text like 'USA', 'UNITEDKINGDOM', or 'FRANCE'.
Note:
Follow these rules to prepare the flat file. Failure to adhere to these critical rules when preparing the flat file may result in unexpected behavior when you try to log in to the analytics server configured for Life Sciences.The Current Top Level Position must have a 1:1 relationship with the COUNTRY_REGION_NAME value and the relationship is mandatory for each top level position.
All subordinates to each top level position should have M:1 relationship with the parent positions.
The CURRENT_TOP_LVL_POSTN field is case sensitive. The CURRENT_TOP_LVL_POSTN field value must be equal to the W_POSITION_DH.CURRENT_TOP_LVL_POSTN column value.
The COUNTRY_REGION_NAME field value must be equal to the partition name and partition value defined in the DDL script that is used to create the partition table.
The COUNTRY_REGION_NAME field values must not have spaces. And, the characters in the field must be all upper case.
The flat file data is always loaded as full load into the W_TOP_POSTN_PARTITION_M table. There are no incremental insert or update supported.
After you create the partition tables, see Section 14.4.4, "How to Create Partition Table," you must enable Database Partition before loading the syndicated data and market fact table data. This feature is disabled by default in the Oracle Business Analytics Warehouse for Life Sciences.
Database Partition is dependent on the parameters in DAC. Table 14-30 contains the parent-level parameter list with descriptions to enable or disable Database Partition.
Table 14-30 Parent-Level Parameter for DB Partition
| Parameter Name | Description |
|---|---|
|
$$SYND_DS_PARTITION_ENABLED |
Enable or disable (Y/N) DB Partition of Syndicated Data and Market facts for Direct Sales data. |
|
$$SYND_IDS_PARTITION_ENABLED |
Enable or disable (Y/N) DB Partition of Syndicated Data and Market facts for Indirect Sales data. |
|
$$SYND_RX_PARTITION_ENABLED |
Enable or disable (Y/N) DB Partition of Syndicated Data and Market facts for Prescription data. |
|
$$SYND_XPT_PARTITION_ENABLED |
Enable or disable (Y/N) DB Partition of Syndicated Data and Market facts for Physician Plan Level Rx data. |
|
$$SYND_PT_PARTITION_ENABLED |
Enable or disable (Y/N) DB Partition of Syndicated Data and Market facts for Plan Level Rx data. |
|
$$SYND_W_PARTITION_ENABLED |
Enable or disable (Y/N) DB Partition of syndicated data and market facts for Weekly Early View data. |
Note:
Each parameter in Table 14-30 is shared by both syndicated data and market fact tables. For example, if you set the value 'Y' for the $$SYND_DS_PARTITION_ENABLED parameter, then you have enabled Database Partition of both syndicated data and market fact tables for direct sales data.Using the procedures provided in Section 14.4.3, "Identify Partition Name and Value" and Section 14.4.4, "How to Create Partition Table," and based on your decision to partition the syndicated data type, you can create proper partition tables. However, to activate Database Partition, after creating the partition tables, you must enable proper parameters based on the information provided in Table 14-30. For example, if you want the partition feature enabled for only the direct sales data, then you must set the value 'Y' for the $$SYND_DS_PARTITION_ENABLED parameter. If you decide to have partition for all the syndicated data types, then you must set value 'Y' for all parameters listed in Table 14-30.
To enable Partition in DAC metadata:
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Source System Parameters tab.
In the Name field, query for the $$SYND_*_PARTITION_ENABLED value.
From the query result, select the parameter to which you want to enable Partition.
For example, if you want to enable Partition for all the syndicated data types, then, select the $$SYND_DS_PARTITION_ENABLED parameter, set the Value field to Y, and then click Save.
Repeat the previous step for the other parameters defined in Table 14-30.
$$SYND_IDS_PARTITION_ENABLED
$$SYND_RX_PARTITION_ENABLED
$$SYND_XPT_PARTITION_ENABLED
$$SYND_PT_PARTITION_ENABLED
$$SYND_W_PARTITION_ENABLED
To disable Database Partition in DAC metadata (reverting to default.)
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Source System Parameters tab.
In the Name field, query for the $$SYND_*_PARTITION_ENABLED value.
From the query result, select the $$SYND_DS_PARTITION_ENABLED parameter, set the Value field to N, and then click Save.
Repeat the previous step for the other parameters defined in Table 14-30:
$$SYND_IDS_PARTITION_ENABLED
$$SYND_RX_PARTITION_ENABLED
$$SYND_XPT_PARTITION_ENABLED
$$SYND_PT_PARTITION_ENABLED
$$SYND_W_PARTITION_ENABLED
Database Partition for syndicated data and market supports only the Oracle database platform; however, to use Database Partition for other database platforms, you must customize the current feature accordingly.
The major Database Partition features in Oracle Business Analytics Warehouse for Life Sciences are:
The ability to track the active partition value for the current ETL during run time
The ability to truncate the partition only by active partition value
The ability to create index in partitioned table
The following sections describe the general design logic applied for Database Partition for the Oracle Business Analytics Warehouse for Life Sciences. Use this information to modify Partition for other database Platforms.
The Active Partition (or Partitions) is the partition that is being processed in current ETL. The table W_SYND_PARTITION_TMP is used to process the active partition (or partitions) in the current ETL. This temporary table captures the active partition values from each syndicated data staging table that was already loaded from the flat file source. The information in the table is also applied to the syndicated market level data loading.
Table 14-31 Schema Definition of W_SYND_PARTITION_TMP Table
| Column Name | Data Type | Column Required | Column Description |
|---|---|---|---|
|
SOURCE |
VARCHAR2(30) |
NOT NULL |
Indicates the data sourceFoot 1 |
|
COUNTRY_REGION_NAME |
VARCHAR2(30) |
NOT NULL |
Country or Region name, which is also the Active Partition Value (and Partition Name). |
Footnote 1 Hard-coded values are used, refer Table 14-32 for the actual values.
Source
Indicates the source of the syndicated data staging table and type data.
Table 14-32 Value Definition Used in W_SYND_PARTITION_TMP.SOURCE
| Value of Source | Description |
|---|---|
|
W_SYNDD_DS_FS |
Partition value comes from staging table source of Syndicated Data for Direct Sales data. |
|
W_SYNDD_IDS_FS |
Partition value comes from staging table source of Syndicated Data for Indirect Sales data. |
|
W_SYNDD_RX_FS |
Partition value comes from staging table source of Syndicated Data for Prescription data. |
|
W_SYNDD_PT_FS |
Partition value comes from staging table source of Syndicated Data for Plan Level Rx data. |
|
W_SYNDD_XPT_FS |
Partition value comes from staging table source of Syndicated Data for Physician Plan Level Rx data. |
|
W_SYNDD_W_FS |
Partition value comes from staging table source of Syndicated Data for Weekly Early View data. |
COUNTRY_REGION_NAME
Active and unique partition value (same as partition name) used in syndicated data flat file during the current ETL run.
Depending on the active partition selected for the current ETL run, the SQL text to truncate the partitioned table is dynamically generated for the active partition.
For example, if there are three partitions of the syndicated data and syndicated market for direct sales data: USA, UNITEDKINGDOM, and FRANCE partitions, and for the current ETL run, the data available to load is only for U.S.A. Then, the active partition is USA and the ETL process must truncate the partitioned direct sales tables only for the USA partition.The dynamic SQL text generation is done using the parameters available in DAC. Table 14-33 defines the parent-level parameters that are used for SQL text generation. The parent-level parameters simply call its child-level parameter listed in Table 14-34 respectively.
Table 14-33 Parent-Level Parameter for SQL Generation
| Parent Name | Description |
|---|---|
|
$$SYND_DS_PARTITION_TRUNCATE_SQL |
Call child-level parameter of Syndicated Data and Market facts for Direct Sales data. |
|
$$SYND_IDS_PARTITION_TRUNCATE_SQL |
Call child-level parameter of Syndicated Data and Market facts for Indirect Sales data. |
|
$$SYND_RX_PARTITION_TRUNCATE_SQL |
Call child-level parameter of Syndicated Data and Market facts for Prescription data. |
|
$$SYND_XPT_PARTITION_TRUNCATE_SQL |
Call child-level parameter of Syndicated Data and Market facts for Physician Plan Level Rx data. |
|
$$SYND_PT_PARTITION_TRUNCATE_SQL |
Call child-level parameter of Syndicated Data and Market facts for Plan Level Rx data. |
|
$$SYND_W_PARTITION_TRUNCATE_SQL |
Call child-level parameter of Syndicated Data and Market facts for Weekly Early View data. |
Table 14-34 defines the child-level parameters available for SQL generation. Based on the data captured for the active partition in the W_SYND_PARTITION_TMP table, the parameter generates actual SQL text to truncate the partitioned table for all active partitions as concatenating each SQL statement. In the previous example, if all the partitions for the three countries are active during the current ETL run, then the child-level parameter is dynamically generated as follows:
execute immediate 'ALTER TABLE getTableName() TRUNCATE PARTITION "USA"'; execute immediate 'ALTER TABLE getTableName() TRUNCATE PARTITION "UNITEDKINGDOM"'; execute immediate 'ALTER TABLE getTableName() TRUNCATE PARTITION "FRANCE"';
The child-level parameters are designed to use Oracle specific syntax and are executed only in the Oracle database platform. In other database platforms, the parameters will do nothing.
Table 14-34 Child-Level Parameter for SQL Generation
| Parameter Name | Description |
|---|---|
|
$$SYND_DS_PARTITION_TRUNCATE_SQL_TEXT |
Concatenated SQL text to truncate partitioned Syndicated Data and Market facts for Direct Sales data. The SQL is only for active partitions. |
|
$$SYND_IDS_PARTITION_TRUNCATE_SQL_TEXT |
Concatenated SQL text to truncate partitioned Syndicated Data and Market facts for Indirect Sales data. The SQL is only for active partitions. |
|
$$SYND_RX_PARTITION_TRUNCATE_SQL_TEXT |
Concatenated SQL text to truncate partitioned Syndicated Data and Market facts for Prescription data. The SQL is only for active partitions. |
|
$$SYND_XPT_PARTITION_TRUNCATE_SQL_TEXT |
Concatenated SQL text to truncate partitioned Syndicated Data and Market facts for Physician Plan Level Rx data. The SQL is only for active partitions. |
|
$$SYND_PT_PARTITION_TRUNCATE_SQL_TEXT |
Concatenated SQL text to truncate partitioned Syndicated Data and Market facts for Plan Level Rx data. The SQL is only for active partitions. |
|
$$SYND_W_PARTITION_TRUNCATE_SQL_TEXT |
Concatenated SQL text to truncate partitioned Syndicated Data and Market facts for Weekly Early View data. The SQL is only for active partitions. |
In Release 7.9.6.3 of Oracle Business Analytics Warehouse, the Actions function is provided in DAC. Actions are used to override the existing truncate table function embedded by default in DAC, execute the SQL generated by Section 14.4.8.2, "Dynamically Generate SQL for Active Partition to Truncate Partitioned Table" to truncate partitioned tables.
Since syndicated data and market base fact tables are affected by both Database Partition and Restartability features; the syndicated data and market base fact tables are designed slightly different from the other fact tables. For more details on the Restartability feature, see Section 14.5, "Supporting Restartability in Syndicated Data and Market Load."
Table 14-35 provides information on the Task Action for syndicated data and market base fact tables.
Table 14-35 Task Action for Syndicated Data and Market Base Fact Tables
| Task Action Name | Description |
|---|---|
|
Task Action: LS Syndicated Data Fact_DirectSales Truncate Table |
Task Action to truncate table of Syndicated Data and Market facts for Direct Sales data. |
|
Task Action: LS Syndicated Data Fact_IndirectSales Truncate Table |
Task Action to truncate table of Syndicated Data and Market facts for Indirect Sales data. |
|
Task Action: LS Syndicated Data Fact_Rx Truncate Table |
Task Action to truncate table of Syndicated Data and Market facts for Prescription data. |
|
Task Action: LS Syndicated Data Fact_ExponentPlantrak Truncate Table |
Task Action to truncate table of Syndicated Data and Market facts for Physician Plan Level Rx data. |
|
Task Action: LS Syndicated Data Fact_Plantrak Truncate Table |
Task Action to truncate table of Syndicated Data and Market facts for Plan Level Rx data. |
|
Task Action: LS Syndicated Data Fact_WeeklyEarlyView Truncate Table |
Task Action to truncate table of Syndicated Data and Market facts for Weekly Early View data. |
The main logic of the Task Action is as follows:
In the Oracle Database platform
When Database Partition is enabled, execute the parent-level parameter such as $$SYND_DS_PARTITION_TRUNCATE_SQL listed in Table 14-33 to truncate the partitioned table.
When Database Partition is disabled, execute the normal truncation for the complete table.
In other Database platforms
Execute normal truncation for the complete table.
The Task Action definition can be found in DAC. To view the definition:
In DAC, choose Tools, then Seed Data, then Actions, then Task Actions.
Query the Name field for the Task Action: LS Syndicated Data Fact*Truncate Table value.
From the query result, select the Task Action that you want to review.
Click the Value field to see the definition.
The Task Action is called by each task of syndicated data and market territory or district level and aggregate facts to execute the definition:
Example to review for syndicated data direct sales base fact.
In DAC, go to the Design view, and display the Tasks tab.
Query the Name field, as follows:
When you enable DB Partition but disable Restartability, query the Name field for the LS Load into SyndicatedData Fact for Direct Sales value.
When you enable both DB Partition and Restartability, query the Name field for the LS Load into SyndicatedData Fact for Direct Sales_Restart value.
From the query result, select the Actions tab on the bottom.
Locate the Task Action: LS Syndicated Data Fact_DirectSales Truncate Table value embedded in the Action field.
When you start to run ETL, the task action embedded in the task is executed.
Syndicated data and market territory or district level and aggregate fact tables are not affected by Restartability. Instead of Task Action, Table Action is used to truncate partitioned tables. Table 14-36 represents the Table Action for the syndicated data and market territory or district level and aggregate fact tables and description.
Table 14-36 Table Action for Syndicated Data and Market Territory or District Level and Aggregate Fact Tables
| Table Action Name | Description |
|---|---|
|
Table Action: LS Syndicated Data Fact_DirectSales Truncate Table |
Table Action to truncate table of Syndicated Data and Market facts for Direct Sales data. |
|
Table Action: LS Syndicated Data Fact_IndirectSales Truncate Table |
Table Action to truncate table of Syndicated Data and Market facts for Indirect Sales data. |
|
Table Action: LS Syndicated Data Fact_RX Truncate Table |
Table Action to truncate table of Syndicated Data and Market facts for Prescription data. |
|
Table Action: LS Syndicated Data Fact_ExponentPlantrak Truncate |
Table Action to truncate table of Syndicated Data and Market facts for Physician Plan Level Rx data. |
|
Table Action: LS Syndicated Data Fact_Plantrak Truncate Table |
Table Action to truncate table of Syndicated Data and Market facts for Plan Level Rx data. |
|
Table Action: LS Syndicated Data Fact_WeeklyEarlyView Truncate Table |
Table Action to truncate table of Syndicated Data and Market facts for Weekly Early View data. |
Table Action logic is similar to Task Action logic. For more details on Task Action, see Section 14.4.8.3, "Method to Truncate Partitioned Table for Syndicated Data and Market Base Fact Tables":
In Oracle DB platform
When Database Partition is enabled, execute the parent-level parameter such as $$SYND_DS_PARTITION_TRUNCATE_SQL listed in Table 14-33 to truncate the partitioned table.
When Database Partition is disabled, execute the normal truncation for the entire table.
In other DB platform
Execute normal truncation for entire table.
To find the Table Action definition in DAC:
In DAC, choose Tools, then Seed Data, then Actions, then Table Actions.
Query the Name field for the Table Action: LS Syndicated Data Fact*Truncate Table value.
From the query result, select the Table Action that you want to review.
Click the Value field to see the definition.
The Table Action is called by each table of syndicated data and market territory or district level and aggregate facts to execute the definition:
Example to review for syndicated data direct sales territory level fact.
In DAC, go to the Design view, and then display the Tables tab.
Query the Name field for the W_SYND_DS_T_F value.
From the query result, select the Actions tab on the bottom.
Locate the Table Action: LS Syndicated Data Fact_DirectSales Truncate Table value embedded in the Action field.
Once you start to run ETL, the Table Action embedded in the table is executed.
Two Index Actions (the Local Bitmap and Local Unique indexes) are provided for index creation on the partitioned table. The Local Bitmap index is available within each partition and the Local Unique index includes the COUNTRY_REGION_NAME partition column as the first leading unique index column within each partition.Table 14-37 represents the Index Action for Syndicated Data and Market fact tables and description.
Table 14-37 Index Action for Syndicated Data and Market Fact Tables
| Syndicated Data Type | Index Action Name | Description |
|---|---|---|
|
Syndicated Data and Market facts for Direct Sales data. |
|
|
|
Syndicated Data and Market facts for Indirect Sales data. |
|
|
|
Syndicated Data and Market facts for Prescription data. |
|
|
|
Syndicated Data and Market facts for Physician Plan Level Rx data. |
|
|
|
Syndicated Data and Market facts for Plan Level Rx data. |
|
|
|
Syndicated Data and Market facts for Weekly Early View data. |
|
|
The main logic of the Index Action for the BITMAP index is:
In Oracle database platform
If Database Partition is enabled, then execute Index Action to create the BITMAP index as LOCAL.If Database Partition is disabled, then execute Index Action to create the BITMAP index as normal.
In other database platform
Execute Index Action to create the BITMAP index as normal.
The main logic of the Index Action for UNIQUE index is:
In all database platforms
If Database Partition is enabled, then execute the Index Action to create the UNIQUE index as LOCAL; including COUNTRY_REGION_NAME as the first leading index column value.
If Database Partition is disabled, then execute the Index Action to create the UNIQUE index as normal.
To locate the Index Action definition in DAC:
In DAC, choose Tool, then Seed Data, then Actions, then Index Actions.
Query the Name field, as follows:
For the BITMAP, index as Index Action:LS Syndicated Data Fact*Create BITMAP Index
For the UNIQUE, index as Index Action:LS Syndicated Data Fact*Create UNIQUE Index
From the query result, select the Index Action that you want to review.
Click the Value field to see the definition.
The Index Action is called by each index of syndicated data and market facts to execute the definition:
Example to review for syndicated data direct sales base fact.
In DAC, go to the Design view, and then display the Indices tab.
Query the Table Name field for the W_SYNDD_DS_F value.
From the query result, select the W_SYNDD_DS_F_F1 index and then navigate to the Actions tab on the bottom.
You can find that the Index Action:LS Syndicated Data Fact_DirectSales Create BITMAP Index value is embedded in Action field.
From the query result, select the W_SYNDD_DS_F_U1 index and then navigate to the Actions tab on the bottom.
You can find that the Index Action:LS Syndicated Data Fact_DirectSales Create UNIQUE Index value is embedded in the Action field.
When you start to run ETL, the Index Action embedded in the indexes is executed.
This section contains the following sections:
In Oracle Business Analytics Warehouse for Life Sciences, syndicated data and market always do full load only. This is because syndicated data and market do not come with a key to uniquely track each transaction among ETL runs. In previous releases, if ETL failed in the middle and data was loaded partially, you had to rerun the ETL to load data from the start again. Data that had been loaded before the failed point would be removed as well. This is not very efficient.
In Release 7.9.6.3, instead of loading the data from the very beginning, if you have enabled Restartability, you can resume the ETL and continue from the last failed point. However, Restartability is disabled by default in the DAC metadata. To enable Restartability, you must activate the feature in DAC after installing the Oracle Business Analytics Warehouse.
Note:
Restartability is available when you are loading the syndicated data and market data into the base fact tables. The feature is not available when you are loading data into territory or district level and aggregate fact tables.This section contains the following sections:
Section 14.5.2.1, "How to Enable Restartability Feature in DAC for Syndicated Data Base Fact"
Section 14.5.2.2, "How to Enable Restartability Feature in DAC for Syndicated Market Base Fact"
Section 14.5.2.3, "How to Reassemble Subject Areas and Rebuild Execution Plans"
To enable Restartability for flat file loading method for the syndicated data base facts:
In DAC, go to the Design view, and then display the Tasks tab.
Query the Name field for the LS Load into SyndicatedData Fact* value.
From the query result, select the LS Load into SyndicatedData Fact for Direct Sales task, select Inactive, and then click Save.
Repeat the previous step for the following tasks:
LS Load into SyndicatedData Fact for Indirect Sales
LS Load into SyndicatedData Fact for Exponent Plantrak
LS Load into SyndicatedData Fact for Plantrak
LS Load into SyndicatedData Fact for Rx
LS Load into SyndicatedData Fact for Weekly Early View
Select the LS Load into SyndicatedData Fact for Direct Sales_Restart task, clear Inactive, and then click Save.
Repeat the previous step for the following tasks:
LS Load into SyndicatedData Fact for Indirect Sales_Restart
LS Load into SyndicatedData Fact for Exponent Plantrak_Restart
LS Load into SyndicatedData Fact for Plantrak_Restart
LS Load into SyndicatedData Fact for Rx_Restart
There are two methods to load the syndicated market base fact tables in Oracle Business Analytics Warehouse for Life Sciences.
Calculated Loading Method
Use this method if you have purchased flat files for only syndicated data from the third party vendor. To do Calculated Loading, load the syndicated data with the flat files into the data warehouse for the Life Sciences Analytics, syndicated data is aggregated from product level, calculate the data based on the product-market hierarchy, then load the data as market level data into the syndicated market base facts. See Section 14.2, "Importing Syndicated Data into Oracle Business Analytics Warehouse."
Flat File Loading Method
Use this method if you have purchased flat files for syndicated data and syndicated market from the third party vendor. The flat files are loaded directly into syndicated data and market base facts for Life Sciences Analytics.
Calculated Loading Method for the syndicated market base facts is enabled by default. However, due to difficulty to track the same unique key value in aggregated data, Restartability is not supported for the Calculated Loading Method for syndicated market base facts.
Restartability is supported only for Flat File Loading Method for syndicated market base facts.
Similar to syndicated data base facts, Restartability is disabled by default in DAC metadata. To use Restartability, activate the feature in DAC.
To enable Restartability for Flat File Loading Method for the syndicated market base Facts:
In DAC, go to the Design view, and then display the Tasks tab.
Query the Name field for the LS Load into SyndicatedMarket Fact* value.
From the query result, select the LS Load into SyndicatedMarket Fact for Calculated Exponent Plantrak task, select Inactive, and then click Save.
Note: Calculated Loading Method for syndicated market base fact table is disabled.
Repeat the previous step for the following tasks:
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
Do not change existing settings for the LS Load into SyndicatedMarket Fact for Calculated Direct Sales task. :
There is no flat file available for syndicated market for direct sales data from the third party vendor. Use only Calculated Loading Method to load direct sales data.Select the LS Load into SyndicatedMarket Fact for Exponent Plantrak_Restart task, clear Inactive, and then click Save.
Note:
For the syndicated market base fact, Flat File Loading Method with Restartability is enabled.Repeat the previous step for the following tasks:
LS Load into SyndicatedMarket Fact for Indirect Sales_Restart
LS Load into SyndicatedMarket Fact for Plantrak_Restart
LS Load into SyndicatedMarket Fact for Rx_Restart
LS Load into SyndicatedMarket Fact for Weekly Early View_Restart
Note:
Depending on your business requirement, select the syndicated market type that should have Restartability.For example, if you want to have Restartability only for the Physician Plan Level Rx data, then disable the LS Load into SyndicatedMarket Fact for Calculated Exponent Plantrak task in Step 4 and enable only the LS Load into SyndicatedMarket Fact for Exponent Plantrak_Restart task in Step 6. Do not repeat the procedure for Step 5 and 7 for other syndicated data types.
After enabling Restartability for both the syndicated data and market base facts, reassemble Subject Areas and rebuild Execution Plans to include the changes into the next ETL run. For information about assembling Subject Areas and building Execution Plans, see Oracle Business Intelligence Data Warehouse Administration Console Guide.
Note:
If you enable Restartability only for the direct sales data, then reassemble only the corresponding Subject Areas and Execution Plans.For example, reassemble CRM ePharma–Direct Sales, and CRM ePharma–Objective Subject Areas.
To reassemble Subject Areas:
In DAC, go to the Design view, and then display the Subject Areas tab.
Query the Name field for the CRM ePharma*Sales or CRM ePharma*Prescription or CRM ePharma*Objective values.
Click the Assemble button.
In the pop up window, select All records in the list and then click OK.
For each Subject Area, click Calculate Task List.
A list of tasks that must be assembled is displayed.
Check the task list and click Accept.
After the Subject Areas are assembled for Life Sciences Analytics, check if Restartability is properly included in each Subject Area.
For example, select the CRM ePharma - Direct Sales Subject Area and display the Tasks tab on the bottom.
Query the Name field for the *Restart value.
Note:
If the query returns results, then reassembling the Subject Area is successful.To rebuild Execution Plans:
In DAC, go to the Execute view, and then display the Execution Plans tab.
Select the Execution Plan that you want to run, for example, select the CRM ePharma - Siebel 8.1.1 Execution Plan and click Build.
In the pop up window, select the Selected record only option, and then click OK.
After the Execution Plan is re-built for Life Sciences Analytics, check the Execution Plan.
Select the CRM ePharma - Siebel 8.1.1 Execution Plan, and display the Ordered Tasks tab on the bottom.
Query the Name field for the *Restart value.
Note:
If the query returns result(s), then the Execution Plan is re-built successfully and Restartability is enabled for ETL.This section explains how to disable Restartability for Syndicated Data and Market Base Fact Tables.
To disable Restartability for Flat File Loading Method for syndicated data:
In DAC, go to the Design view, and then display the Tasks tab.
Query the Name field for the LS Load into the SyndicatedData Fact* value.
From the query result, select the LS Load into SyndicatedData Fact for Direct Sales task, clear Inactive, and then click Save.
Repeat the previous step for the following tasks:
LS Load into SyndicatedData Fact for Indirect Sales
LS Load into SyndicatedData Fact for Exponent Plantrak
LS Load into SyndicatedData Fact for Plantrak
LS Load into SyndicatedData Fact for Rx
LS Load into SyndicatedData Fact for Weekly Early View
Select the LS Load into SyndicatedData Fact for Direct Sales_Restart task, select Inactive, and then click Save.
Repeat the previous step for the following tasks:
LS Load into SyndicatedData Fact for Indirect Sales_Restart
LS Load into SyndicatedData Fact for Exponent Plantrak_Restart
LS Load into SyndicatedData Fact for Plantrak_Restart
LS Load into SyndicatedData Fact for Rx_Restart
LS Load into SyndicatedData Fact for Weekly Early View_Restart
To disable Restartability for Flat File Loading Method for syndicated market base facts (reset to default):
In DAC, go to the Design view, and then display the Tasks tab.
Query the Name field for the LS Load into SyndicatedMarket Fact* value.
From the query result, select the LS Load into SyndicatedMarket Fact for Calculated Exponent Plantrak task, clear Inactive, and then click Save.
Note:
For the syndicated market base fact, Calculated Loading Method is enabled.Repeat the previous step for the following tasks:
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: The LS Load into SyndicatedMarket Fact for Calculated Direct Sales task must be active all the time.
Select the LS Load into SyndicatedMarket Fact for Exponent Plantrak_Restart task, select Inactive, and then click Save.
Repeat the previous step for the following tasks:
LS Load into SyndicatedMarket Fact for Indirect Sales_Restart
LS Load into SyndicatedMarket Fact for Plantrak_Restart
LS Load into SyndicatedMarket Fact for Rx_Restart
LS Load into SyndicatedMarket Fact for Weekly Early View_Restart
After disabling Restartability for both syndicated data and market base facts, reassemble Subject Areas and rebuild Execution Plans to include the changes into the next ETL run. For information about assembling Subject Areas and building Execution Plans, see Oracle Business Intelligence Data Warehouse Administration Console Guide.
Note:
If you disabled Restartability for only direct sales data, then reassemble only the corresponding Subject Areas and Execution Plans.For example, reassemble CRM ePharma – Direct Sales, and CRM ePharma – Objective Subject Areas.
To reassemble Subject Areas:
In DAC, go to the Design view, and then display the Subject Areas tab.
Query the Name field for the "CRM ePharma*Sales" or "CRM ePharma*Prescription" or "CRM ePharma*Objective" values.
Click the Assemble button.
In the pop up window, select All records in the list and then click OK.
For each Subject Area, click Calculate Task List.
A list of tasks that must be assembled is displayed.
Check the task list and click Accept.
After the Subject Areas are assembled for Life Sciences Analytics, check if Restartability is removed in each Subject Area.
To rebuild Execution Plans:
In DAC, go to the Execute view, and then display the Execution Plans tab.
Select the Execution Plan that you want to rebuild, for example, select the CRM ePharma - Siebel 8.1.1 Execution Plan and click Build.
In the pop up window, select the Selected record only option, and then click OK.
After the Execution Plan is re-built for Life Sciences Analytics, check the Execution Plan to see if Restartability is removed.
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.
Medical Education Fact. Adds new records to the fact table.
Objective Fact. Adds new records or updates existing records to the fact table.
Profile Ranking Fact. Adds new records or updates existing records to the fact table.
Syndicated Data and Market - 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, District 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. Ensure that the 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 tab in the DAC Design view. 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 must be on a separate row, so the same value must 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, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|