Oracle® Health Sciences Cohort Explorer Administrator's Guide Release 1.0 E24438-01 |
|
|
PDF · Mobi · ePub |
This chapter describes the architecture of Oracle Health Sciences Cohort Explorer. It contains the following topics:
OHSCE architecture includes the following principal components:
A predefined Cohort data model containing tables of patient identifiable medical information and configuration data.
Pre-built ODI based interfaces that are designed to extract patient related data from Oracle Healthcare Data Warehouse Foundation (HDWF).
Following are the key system attributes and how they influence loading from HDWF tables to data mart tables:
DELETE_FLG - The soft delete flag status determines whether a record in the data mart is deleted or not. Table 2-1 lists the source for each data mart table that determines the soft delete status of a record. If delete flag is set to Y in any of the source tables, the target record is automatically soft deleted.
INTEGRATION_ID - This is populated from the INTEGRATION_ID of the main driving table, the table that decides the grain. Appendix A, "ETL Related Tables" lists the main driving tables, which determine the INTEGRATION_ID. If there is more than one driving tables, the INTEGRATION_IDs of these tables are concatenated with a separator.
DATASOURCE_NUM_ID - This is populated from the DATASOURCE_NUM_ID of the main driving table. Appendix A, "ETL Related Tables" lists the main driving tables.
UPDATE_DT - UPDATE_DT of HDWF tables determines when a record is created or updated in HDWF. If a record is updated after completion of the last ETL load, it is picked up for incremental load. UPDATE_DT of one or more source table associated with data mart table determines if the record should be picked. Appendix A, "ETL Related Tables" lists the source table for each data mart table that determine the updated status of record. When more than one source table exists, the incremental load is performed if the UPDATE_DT of any of the source table records is later than the last load date and time.
CURRENT_FLG - If multiple versions of the record are available in HDWF, only the current version is picked up. Current version is found in HDWF using CURRENT_FLG attribute.
Masking Patient INTEGRATION_ID - To mask patient information, Patient INTEGRATION_ID from HDWF is not loaded into data mart, as it may contain patient identification information. In stead of INTEGRATION_ID, PT_ID from HDWF is populated to data mart.
Populating UOM_WID - If UOM_WID cannot be resolved by ETL while loading data into data mart, the UOM_WID is populated with NAV value. The actual value of NAV is picked up from ETL Global configuration table C_LOAD_PARAM NAV.
Versioning Logic - For each Integration ID in HDWF, there will be a single record in CDM. If there are multiple versions in HDWF, the most current version is loaded in the data mart. The most current version in HDWF is determined using CURRENT_FLG attribute.
This section discusses the OHSCE ODI work repository artifacts.
The work repository contains one project called Oracle Healthcare Analytics.
This project contains two main folders.
Execution Plans
Mappings
Execution plan folder contains ETL artifacts related to packaging and executions of ETLs.
Table 1-1 Execution Plan Folder Contents
Folder | Description |
---|---|
Level1_Load_User_Dimension |
Loads User dimension. |
Level2_Load_All_Other_Dimensions_And_Patient_Fact |
Loads all dimensions in parallel followed by Patient fact and unspecified records for dimensions. |
Level3_Load_All_Bridge_Tables |
Loads all bridge tables in parallel. |
Level4_Load_Unspecified_Records_For_Bridge_Tables |
Loads unspecified records for bridge tables. |
Level5_Load_Protocol_Tables |
Loads protocol related tables. |
Master Execution Plan |
This is the incremental load execution plan. Level1 to Level5 packages are included in this plan. Oracle recommends that you do not run the individual packages. Refer to Chapter 3, "Executing ETLs" for order of execution. |
Initial Setup |
The folder contains three sub folders; Create DBLink, Full_Load_Execution_Plan, and Generate_All_Scenarios. Full_Load_Execution_Plan is the full load execution plan. Refer to Chapter 3, "Executing ETLs" for order of execution. |
Mappings folder contains the interfaces that load data mart tables.
Table 1-2 Mappings Folder Contents
Folder | Description |
---|---|
Dimensions |
Contains ETLs for all dimensions and bridge tables. |
Facts |
Contains ETL for the Patient fact. |
An ODI package is created to populate each data mart table. Each package comprises the following sequence to populate the corresponding data model table:
Refresh all the ODI variables used for that particular package.
Run any one of the interfaces (full load or incremental load) depending on the type of load.
For a comprehensive list of interfaces and their corresponding packages refer to Appendix B, "Work Repository Artifacts"
Figure 1-3 depicts the design on ODI package for the table W_EHA_RACE_PATIENT_H:
Figure 1-3 ODI Package for the Table W_EHA_RACE_PATIENT_H
ETL parameters are passed using ODI variables. The variable values are refreshed during an ETL run from the C_LOAD_PARAM table. For a comprehensive list of Variables refer to Appendix B, "Work Repository Artifacts"
Sequences are used for populating ROW_WID columns in each of the data mart tables. For a comprehensive list of sequences refer to Appendix B, "Work Repository Artifacts"
Knowledge modules are code templates provided by ODI. Following is the list of knowledge modules of OHSCE:
LKM TMP APPS Oracle to Oracle (DBLINK)
CKM oracle
IKM TMP APPS Oracle Control Append
IKM TMP APPS Oracle Incremental Update