Oracle Health Sciences Cohort Explorer Implementation Scripts Guide

1 Sample Implementation Scripts

Oracle Health Sciences Cohort Explorer 1.0 provides the following implementation scripts:

• Overview
• cohort_hdwf_sample_data_load_v1.0.sql
• cohort_hdwf_sample_data_delete_v1.0.sql
• create_cxe_user_example.sql

Top

1.1 Overview

cohort_hdwf_sample_data_load_v1.0.sql script populates a minimum number of HDWF tables with a few sample data rows to exercise the ETL mappings delivered with Oracle Health Sciences Cohort Explorer (OHSCE) 1.0. HDWF supports a versioning architecture that lets almost every table maintain multiple time based versions of every persisted business entity. Most HDWF tables have a compound key that consists of a surrogate prefix (For example, PT_ID) logically concatenated with a version number suffix (for example, PT_VN). The versions are managed by standard columns EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, and CURRENT_FLG.

For example, a Patient entity might have any number of time based versions starting with an original version and ending with a current version with any number of intermediate versions between. By definition, versions cannot overlap and the last or current version always carries an Effective To Date of 12/31/9999. OHSCE does not currently utilize HDWF versioning and therefore this test data set does not include such data.

All HDWF sample data has a single version (Version Number/VN=0). In most sample queries, the version number suffix is ignored. Also, the EFFECTIVE_FROM_DT is set to an arbitrary 'start of time' value that is 01/01/1970. In an actual HDWF implementation employing versioning, it would be important to match the EFFECTIVE_FROM_DT for the original and any subsequent versions to the actual source system timestamps. Refer to Oracle Health Sciences Healthcare Data Warehouse Foundation Programmer's Guide for more details.

Both HDWF and OHSCE are fully dependent on alternate unique identifier (unique keys) derived from the source system. This information is persisted in a compound key that consists of DATASOURCE_NUM_ID and INTEGRATION_ID. The INTEGRATION_ID contains a single or concatenated key derived from the source system that logically binds or links HDWF data with the source system. In some cases the source system key might be a natural key, in others it might be a local surrogate key.

There are complicated scenarios in which multiple source systems deliver data to HDWF and the DATASOURCE_NUM_ID can contain a ‘tie breaker’ value, which allows for the rare situation where a business key from multiple source systems might randomly have the same value.

In the sample data a single DATASOURCE_NUM_ID=0 is used. In an actual deployment it may be necessary to identify and utilize multiple values.

After you load data into empty target tables, you see the following:

   TABLE                      RECS  Logical Entity Name (from HDWF Logical Data Model) and description
    ------------------------ ----  ----------------------------------------------
  1 HDM_CD_SYS                    1  "Code System" - describes a 'vocabulary' of codes maintained by a single entity (customer, Oracle, outside standards body/vendor).
 2 HDM_CD_TYP                    41  "Code Type" - describes the logical subsets of codes grouped for purposes of validation and selection.
 3 HDM_CD_REPOSITORY            184  "Code Repository" - contains codes used in HDM.
 4 HDM_CD_REPOSITORY_CD_TYP     269  "Code Repository Code Type" – an intersection or associative entity that allows codes to be reused in many "Code Types".
 5 HDM_CD_HIER                   48  "Code Hierarchy" – a self-associative entity that allows codes to be optionally arranged into parent-child hierarchies.
 6 HDM_PRTY                      10  "Party" – a generic entity for individual or organizational entities.
 7 HDM_IND_PRTY                  10  "Individual Party" -  a logical subtype of Party which supports the Individual variant of the generic Party.
 8 HDM_IND_PRTY_RC               12  "Individual Party Race" – a child entity of the Individual Party that allows zero, one or more Race codes to be associated to the Individual Party.
 9 HDM_IND_PRTY_ETHN             11  "Individual Party Ethnicity" – a child entity of the Individual party that allows zero, one or more Ethnicity codes to be associated to the Individual Party.
10 HDM_LOC                       10  "Location" – a generic Location (aka Address) utility entity.
11 HDM_PRTY_ADDR                 10  "Party Address" – an intersection entity that allows a Party to be associated with multiple Locations (and vice versa).
12 HDM_PT                        10  "Patient" - the Patient specific 'Role' of Party (has an optional 1:1 relationship with Party).
13 HDM_CNSNT                     11  "Consent" - contains Consent information for a Patient.
14 HDM_SPCMN                     11  "Specimen" - contains Specimen information for a Patient.
15 HDM_PT_HX                     11  "Patient History" - contains Patient History information related to a Patient.
16 HDM_SUBST_HX                  11  "Substance History" – a logical subtype of Patient History which supports the Substance specific variant of the generic Patient History.
17 HDM_CNRN                      11  "Concern" - contains Concern information for a Patient (note that the most typical 'Concern' is a Diagnosis).
18 HDM_CNRN_ANATSITE             12  "Concern Anatomical Site" – a child entity of Concern that allows zero, one or more Anatomical Site Codes to be associated with the Concern.
19 HDM_INTVN                     44  "Intervention" - contains Intervention information for a Patient. There are many different subtypes and types of the generic 'Intervention'.
20 HDM_SUBADMN                   17  "Substance Administration" – a logical subtype of Intervention which is specific to 'Substance Administration'.
21 HDM_INTVN_SUBST               18  "Intervention Substance" – a child entity of Intervention which supports associating zero, one or more Substances to the Intervention.
22 HDM_OBSV                      17  "Observation" contains Observation information for a Patient. Note that Observations can be used to model results of Medical Tests.
23 HDM_INTVN_OBSV                17  "Intervention Observation" – an intersection entity that allows an Intervention to associate with multiple Observations (and less commonly, vice versa).
24 HDM_RELTD_INTVN               27  "Related Intervention" – a self-referential intersection entity for Intervention that allows various Interventions to be related together.

1.2 Loading Data From HDWF to OHSCE

Data is loaded from HDWF to OHSCE in three phases:

• Code Repository related data load
• Patient-related data (built on Party) load
• Transactional data load

Code Repository related data load

This is a prerequisite for loading subsequent Patient and Transactional data. The Code Repository is the primary source for the following OHSCE tables:

• W_EHA_ANATOMICAL_SITE_D
• W_EHA_CONSENT_D
• W_EHA_CONSENT_STATUS_D
• W_EHA_DIAGNOSIS_D
• W_EHA_DIAGNOSIS_STATUS_D
• W_EHA_DIAGNOSTIC_TEST_D
• W_EHA_ETHNICITY_D
• W_EHA_MEDICATION_D
• W_EHA_PATIENT_HISTORY_D
• W_EHA_PROCEDURE_D
• W_EHA_PROCEDURE_TYPE_D
• W_EHA_RACE_D
• W_EHA_SPECIMEN_D

Following are the details of this data load:

1. The Code Repository is loaded with codes from only one sample Code System with code value EHA_CORE. In an actual implementation, codes would probably be sourced from multiple Code Systems (For example, ICD-9-CM, NDC, and so on.).
   
   Command:
   
   select sys_id, sys_cd, sys_nm, sys_version, integration_id from hdm_cd_sys;
   
2. The test data specifies 41 Code Types into which 184 codes (Code Repository) are associated through 269 Code Repository Code Type intersection entities.
   Some codes are (re)used in multiple Code Types.
   
   Commands:
   
   select typ_id, typ_cd, typ_nm, integration_id from hdm_cd_typ order by typ_cd;
select cd_id, cd, cd_nm, sys_id, integration_id from hdm_cd_repository order by integration_id;
select cd_repository_cd_typ_id, cd_id, typ_id, integration_id from hdm_cd_repository_cd_typ order by integration_id;
   
3. Two code hierarchies are demonstrated for Diagnosis Codes and Anatomical Site Codes. Note that in the sample data each hierarchy consists of codes in two Code Types. One contains leaf node codes and the other contains non-leaf node codes (that is to say. the hierarchy). You can also place all the codes into a single Code Type.
   
   Command:
   
   select t.typ_cd, lpad(' ',(level-1)*4,' ')||c.cd cd, lpad(' ',(level-1)*4,' ')||c.cd_nm cd_nm, c.cd_id, h.prnt_cd_id
from hdm_cd_repository c
inner join hdm_cd_hier h on h.cd_id=c.cd_id
inner join hdm_cd_repository_cd_typ rt on c.cd_id=rt.cd_id
inner join hdm_cd_typ t on rt.typ_id=t.typ_id
where t.typ_cd in ('DIAGNOSIS_CODE', 'DIAGNOSIS_CATEGORY', 'ANATOMICAL_SITE_CODE', 'ANATOMICAL_SITE_CATEGORY')
start with h.prnt_cd_id=0
connect by prior h.cd_id=h.prnt_cd_id;

Top

Patient-related data load

Patient-related data (built on Party) is loaded in this phase. The Patient-Party is the primary source for OHSCE tables:

• W_EHA_RESEARCH_PATIENT_F
• W_EHA_RESEARCH_PATIENT_D
• W_EHA_RACE_PATIENT_H
• W_EHA_ETHN_PATIENT_H

Following are the details of this load:

1. The sample data includes ten Individual Parties used as patients. An Individual Party (representing a person and not an organization) consists of the elements contained in two entities called Party and Individual Party joined in a 1:1 relationship. Note that this structure results from the fact that Party has two mutually exclusive subtypes. The other is called Organization Party.
   
   Commands:
   
   select prty_id, prty_nbr, prty_nm, integration_id from hdm_prty;
select prty_id, bir_dt, decd_dt, frst_nm, lst_nm, integration_id from hdm_ind_prty;
   
2. In addition, the Party has multiple child outrigger entities used to represent values that may have a one-to-many relationship with the Party (or Individual Party). In this case OHSCE only utilizes Race and Ethnicity. The sample data has a few corner cases where a Party-Patient has more than one Race or Ethnicity.
   
   Commands:
   
   select ind_prty_rc_id, prty_id, integration_id from hdm_ind_prty_rc;
select ind_prty_ethn_id, prty_id, integration_id from hdm_ind_prty_ethn;

3. A Party may be associated with any number of Locations (Addresses). Theoretically, a Location can be shared with multiple Parties. OHSCE requires that a Party Address have an Address Type code representing a 'home address'. The Party Address intersection entity lets Locations be associated with Parties. The sample data provided has a simple configuration in which each Party has its own Location (address)..
   
   Commands:
   
   select loc_id, cit, pstl_cd, st_cd_id, integration_id from hdm_loc;
select prty_addr_id, prty_id, loc_id, addr_typ_id, addr_stcd_id, integration_id from hdm_prty_addr;

4. A Party may be associated with any number of Locations (Addresses). Theoretically, a Location can be shared with multiple Parties.OHSCE requires that a Party Address have an Address Type code representing a 'home address'. The Party Address intersection entity lets Locations be associated with Parties. The sample data provided has a simple configuration in which each Party has its own Location (address).
   
   Command:
   
   select pt_id, prty_id, pt_nbr, integration_id from hdm_pt;

Top

Transactional data load

Transactional data (for example, Consents, Specimens, History, Diagnoses, Interventions, and Observations) is loaded in this phase. These typically have a one-to-many relationship from Patients.

Following are the details of this load:

1. Patients can have any number of associated Consents. Consents can be of various Types and have several Status values. Consent is the primary source for the OHSCE table, W_EHA_CONSENT_PATIENT_H.
   
   Command:
   
   select cnsnt_id, pt_id, cnsnt_typ_id, cnsnt_stcd_id, cnsnt_nbr, cnsnt_eff_strt_dt, cnsnt_eff_end_dt, integration_id from hdm_cnsnt;
   
2. Patients can have any number of associated Specimens. Specimens can be of various Subtypes and Types and have several Status values. Specimen is the primary source for OHSCE tables W_EHA_SPECIMEN_PATIENT_H and W_EHA_DIAGTST_SPEC_DHL.
   
   Command:
   
   select spcmn_id, pt_id, intvn_id, spcmn_subtyp_id, spcmn_typ_cd_id, targ_anatsite_cd_id, spcmn_nbr, spcmn_qty, spcmn_qty_uomcd_id, integration_id from hdm_spcmn;
   
3. Patients can have any number of Patient History records. However for Cohort Explorer only the Substance History subtype of Patient History is applicable. The Patient History and Substance History are the primary sources for the OHSCE table, W_EHA_PT_HISTORY_PT_H.
   
   Commands:
   
   select pt_hx_id, pt_id, pt_hx_subtyp_id, pt_hx_cd_id, pt_hx_nbr, pt_hx_eff_strt_dt, pt_hx_eff_end_dt, integration_id from hdm_pt_hx;
select pt_hx_id, dos, dos_uomcd_id, freq, freq_uomcd_id, integration_id from hdm_subst_hx;
   
4. Patients can have any number of related Concern records. However, for OHSCE only the Diagnosis subtype of Concern is applicable. Concern and Concern Anatomical Site are the primary sources for the the OHSCE table, W_EHA_DX_PATIENT_H.
   
   Commands:
   
   select cnrn_id, pt_id, cnrn_subtyp_id, cnrn_cd_id, cnrn_stcd_id, cnrn_nbr, cnrn_recd_dt, cnrn_onst_dt, cnrn_reslutn_dt, integration_id from hdm_cnrn;
select cnrn_anatsite_id, cnrn_id, anatsite_cd_id, integration_id from hdm_cnrn_anatsite;
   
5. Patients can have any number of Interventions that represent Procedures in OHSCE. Interventions representing Procedures are signalled by having an Intervention Type (code) that is a member of the Code Type PROCEDURE_CODE. Intervention subtypes are not required to provide input into OHSCE. The Intervention (subset representing Procedures) is the primary source for OHSCE table, W_EHA_PROC_PATIENT_H.
   
   Command:
   
   select intvn_id, pt_id, intvn_subtyp_id, intvn_cd_id, intvn_nbr, strt_dt, end_dt, integration_id from hdm_intvn where intvn_nbr like 'PN%'; -- Procedures only
   
6. Patients can have any number of Interventions that represent Substance Adminstrations in OHSCE. Interventions representing Substance Administrations are signalled by a Generic Name Code, which is a member of the Code Type MEDICATION_CODE. The Substance Administration subtype of Intervention contributes several attributes to OHSCE. The Intervention and subtype Substance Administration in addition to Intervention Substance are the primary sources for the OHSCE table, W_EHA_SUBADMN_PATIENT_H.
   
   Commands:
   
   select intvn_id, pt_id, intvn_subtyp_id, intvn_cd_id, intvn_nbr, strt_dt, end_dt, integration_id from hdm_intvn where intvn_nbr like 'SA%'; -- Substance Administration only
select intvn_id, dos, dos_uomcd_id, integration_id from hdm_subadmn;
select intvn_subst_id, intvn_id, genrc_nm_cd_id, integration_id from hdm_intvn_subst;
   
7. Patients can have any number of Interventions that represent Diagnostic Tests; each Intervention can have multiple Observations representing the results of Diagnostic Tests. The Intervention (subset representing Diagnostic Tests) in addition to Observations, is the primary source for theOHSCE table,W_EHA_DIAGTST_PATIENT_H.
   
   Command:
   
   select intvn_id, pt_id, intvn_subtyp_id, intvn_cd_id, intvn_nbr, strt_dt, end_dt, integration_id from hdm_intvn where intvn_nbr like 'DG%'; -- Diagnostic Test only
select obsv_id, pt_id, obsv_subtyp_id, obsv_val_cd_id, obsv_val_nmeric, obsv_val_uomcd_id, obsv_nbr, integration_id from hdm_obsv;
select intvn_obsv_id, intvn_id, obsv_id, intvn_obsv_rlshptyp_id, integration_id from hdm_intvn_obsv;
   
8. Interventions (representing Procedures) can be related to any number of Interventions (representing Diagnostic Tests) and vice versa. The Related Intervention (subset relating Procedures to Diagnostic Tests is the primary source for the OHSCE table, W_EHA_DIAGTST_PROC_DHL.
   
   Command:
   
   select reltd_intvn_id, src_intvn_id, targ_intvn_id, intvn_rlshptyp_id, integration_id from hdm_reltd_intvn where integration_id like '%PN%DG%';
   
9. Interventions (representing Procedures) can be related to any number of Interventions (representing Substance Administrations) and vice versa. The Related Intervention (subset relating Procedures to Substance Administrations is the primary source for the OHSCE table, W_EHA_PROC_SUBADMN_DHL.
   
   Commands:
   
   select reltd_intvn_id, src_intvn_id, targ_intvn_id, intvn_rlshptyp_id, integration_id from hdm_reltd_intvn where integration_id like '%PN%SA%';
   
10. Interventions (representing Diagnostic Tests) can be related to any number of Interventions (representing Substance Administrations) and vice versa. The Related Intervention (subset relating Diagnostic Test to Substance Administration) is the primary source for the Cohort Explorer table, W_EHA_DIAGTST_SUBADM_DHL.
    
    Commands:
    
    select reltd_intvn_id, src_intvn_id, targ_intvn_id, intvn_rlshptyp_id, integration_id from hdm_reltd_intvn where integration_id like '%DG%SA%';

Top

1.2 cohort_hdwf_sample_data_load_v1.0.sql

This script populates a minimum number of HDWF tables with a few sample data rows to exercise the ETL mappings delivered with Oracle Health Sciences Cohort Explorer (OHSCE) 1.0. This script does not delete data from the target tables. If you want to reload or perform a fresh load of the data, run the associated delete script. This script does not COMMIT by default. If you want to commit INSERT DML statements, run the script, and then issue the COMMIT command explicitly. Click cohort_hdwf_sample_data_load_v1.0.sql to see the script contents.

Top

1.3 cohort_hdwf_sample_data_delete_v1.0.sql

This script deletes all the data from multiple HDWF tables. You must run this script before running the associated insert script. If you want to commit DELETE DML statements, issue the COMMIT command explicitly after running the Delete script. Click cohort_hdwf_sample_data_delete_v1.0.sql to see the script contents.

Top

1.4 create_cxe_user_example.sql

This script creates OHSCE data mart schema. When executed, it will create a cdm database schema and grant privileges required for subsequent installation and use of OHSCE. DBA privileges are required to execute this script. Click create_cxe_user_example.sql to see the script contents.

Top