A ETL Related Tables

The appendix contains the following topics:

• Mapping of Key System Attributes Between HDWF and CDM

• Configuring Code Types and Codes Used in ETL

• C_LOAD_DE_IDENTIFY Table

Mapping of Key System Attributes Between HDWF and CDM

The following table describes the source HDWF driving entity table for each data model table in OHSCE.

Table A-1 Mapping of Key System Attributes

CDM Table	Key Attributes	HDWF Table(s)
W_USER_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_USER HDM_USER HDM_USER HDM_USER
W_UOM_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY,HDM_CD_SYS,HDM_CD_REPOSITORY_CD_TYP,HDM_CD_TYP HDM_CD_REPOSITORY,HDM_CD_SYS
W_EHA_DIAGTST_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_INTVN_OBSV HDM_INTVN_OBSV HDM_INTVN_OBSV,HDM_INTVN,HDM_OBSV,W_EHA_RESEARCH_PATIENT_D HDM_INTVN_OBSV,HDM_INTVN,HDM_OBSV
W_EHA_DIAGTST_PROC_DHL	Datasource Num ID Integration ID Delete Flag Update Date	HDM_INTVN_OBSV HDM_INTVN_OBSV,HDM_RELTD_INTVN HDM_INTVN_OBSV,HDM_INTVN,HDM_OBSV,HDM_RELTD_INTVNW_EHA_RESEARCH_PATIENT_D HDM_INTVN_OBSV,HDM_INTVN,HDM_OBSV,HDM_RELTD_INTVN
W_EHA_DIAGTST_PROC_DHL	Datasource Num ID Integration ID Delete Flag Update Date	HDM_INTVN_OBSV HDM_INTVN_OBSV,HDM_RELTD_INTVN HDM_INTVN_OBSV,HDM_INTVN,HDM_OBSV,HDM_RELTD_INTVNW_EHA_RESEARCH_PATIENT_D HDM_INTVN_OBSV,HDM_INTVN,HDM_OBSV,HDM_RELTD_INTVN
W_EHA_DIAGTST_SPEC_DHL	Datasource Num ID Integration ID Delete Flag Update Date	HDM_SPCMN HDM_SPCMN HDM_SPCMN,HDM_INTVN,W_EHA_RESEARCH_PATIENT_D HDM_SPCMN,HDM_INTVN
W_EHA_PT_HISTORY_PT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_PT_HX HDM_PT_HX HDM_PT_HX,HDM_SUBST_HX,W_EHA_RESEARCH_PATIENT_D HDM_PT_HX
W_EHA_PROC_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_INTVN HDM_INTVN HDM_INTVN,W_EHA_RESEARCH_PATIENT_D HDM_INTVN
W_EHA_PROCEDURE_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY,HDM_CD_SYS,HDM_CD_REPOSITORY_CD_TYP,HDM_CD_TYP HDM_CD_REPOSITORY,HDM_CD_SYS
W_EHA_CONSENT_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_RACE_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_DIAGNOSTIC_TEST_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_ETHNICITY_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_PATIENT_HISTORY_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_SPECIMEN_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_CONSENT_STATUS_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_DIAGNOSIS_STATUS_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_CONSENT_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CNSNT HDM_CNSNT HDM_CNSNT, HDM_PT, W_EHA_RESEARCH_PATIENT_D HDM_CNSNT
W_EHA_ETHN_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_IND_PRTY_ETHN HDM_IND_PRTY_ETHN HDM_IND_PRTY_ETHN, HDM_PT, W_EHA_RESEARCH_PATIENT_D HDM_IND_PRTY_ETHN
W_EHA_SUBADMN_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_INTVN_SUSBST HDM_INTVN_SUSBST HDM_INTVN_SUSBST, HDM_INTVN, HDM_SUBADMN, HDM_PT, W_EHA_RESEARCH_PATIENT_D HDM_INTVN_SUSBST, HDM_INTVN, HDM_SUBADMN
W_EHA_RESEARCH_PATIENT_F	Datasource Num ID Integration ID Delete Flag Update Date	HDM_PT HDM_PT HDM_PT HDM_PT
W_EHA_PROCEDURE_TYPE_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_TYP HDM_CD_TYP HDM_CD_TYP, C_COHORT_PROCEDURE_TYPE HDM_CD_TYP
W_EHA_PROC_TYPE_PROC_DHL	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY_CD_TYP HDM_CD_REPOSITORY_CD_TYP HDM_CD_TYP, HDM_CD_REPOSITORY_CD_TYP, W_EHA_PROCEDURE_D, C_COHORT_PROCEDURE_TYPE,W_EHA_PROCEDURE_TYPE_D HDM_CD_REPOSITORY
W_EHA_SPECIMEN_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_SPCMN HDM_SPCMN HDM_PT, W_EHA_RESEARCH_PATIENT_D, W_EHA_ANATOMICAL_SITE_D,HDM_SPCMN,W_EHA_SPECIMEN_D HDM_SPCMN
W_EHA_RACE_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_IND_PRTY_RC HDM_IND_PRTY_RC HDM_IND_PRTY_RC, HDM_PT, HDM_CD_REPOSITORY HDM_IND_PRTY_RC
W_EHA_PROC_SUBADMN_DHL	Datasource Num ID Integration ID Delete Flag Update Date	HDM_INTVN_SUBST HDM_INTVN_SUBST, HDM_RELTD_INTVN HDM_RELTD_INTVN, HDM_INTVN_SUBST, W_EHA_MEDICATION_D,HDM_INTVN,W_EHA_PROCEDURE_D,HDM_PT,HDM_SUBADMN HDM_RELTD_INTVN, HDM_INTVN_SUBST, HDM_INTVN,HDM_PT,HDM_SUBADMN
W_EHA_MEDICATION_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS HDM_CD_REPOSITORY, HDM_CD_SYS
W_EHA_RESEARCH_PATIENT_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_PT HDM_PT.PT_ID HDM_PT,HDM_IND_PRTY,HDM_LOC,HDM_PRTY_ADDR HDM_PT,HDM_IND_PRTY,HDM_LOC
W_EHA_DX_PATIENT_H	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CNRN_ANATSITE HDM_CNRN_ANATSITE HDM_CNRN_ANATSITE,HDM_CNRN,W_EHA_RESEARCH_PATIENT_D HDM_CNRN_ANATSITE,HDM_CNRN
W_EHA_DIAGTST_SUBADMN_DHL	Datasource Num ID Integration ID Delete Flag Update Date	HDM_INTVN_SUBST HDM_INTVN_SUBST HDM_RELTD_INTVN,W_EHA_RESEARCH_PATIENT_D,HDM_INTVN,HDM_INTVN_SUBST,HDM_SUBADMN HDM_RELTD_INTVN,HDM_INTVN,HDM_INTVN_SUBST,HDM_SUBADMN
W_EHA_ANATOMICAL_SITE_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS Not Applicable as ETL always runs only in Full Load
W_EHA_DIAGNOSIS_D	Datasource Num ID Integration ID Delete Flag Update Date	HDM_CD_REPOSITORY HDM_CD_REPOSITORY HDM_CD_REPOSITORY, HDM_CD_REPOSITORY_CD_TYP, HDM_CD_TYP, HDM_CD_SYS Not Applicable as ETL always runs only in Full Load

Configuring Code Types and Codes Used in ETL

Following is the list of parameters available to configure different code types:

Table A-2 Configuring Code Types and Codes Used in ETL

PACKAGE_NAME	PARAM_NAME	PARAM_VALUE	DESCRIPTION
LOAD_ANATOMICAL_SITE_DIMENSION	ANATOMICAL_SITE_CODE	ANATOMICAL_SITE_CODE	NULL
LOAD_CONSENT_DIMENSION	CONSENT_TYPE_CODE	CONSENT_TYPE	NULL
LOAD_CONSENT_PATIENT_BRIDGE	CONSENT_TYPE_CODE	CONSENT_TYPE_CODE	NULL
LOAD_CONSENT_PATIENT_BRIDGE	CONSENT_STATUS_CODE	CONSENT_STATUS_CODE	NULL
LOAD_CONSENT_STATUS_DIMENSION	CONSENT_STATUS_CODE	CONSENT_STATUS_CODE	NULL
LOAD_DIAGNOSIS_DIMENSION	DIAGNOSIS_CODE	DIAGNOSIS_CODE	NULL
LOAD_DIAGNOSIS_STATUS_DIMENSION	DIAGNOSIS_STATUS_CODE	DIAGNOSIS_STATUS_CODE	NULL
LOAD_DIAGNOSTIC_TEST_DIMENSION	DIAGNOSTIC_TEST_CODE	DIAGNOSTIC_TEST_CODE	NULL
LOAD_DIAGNOSTIC_TEST_PATIENT_BRIDGE	DIAGNOSTIC_TEST_CODE	DIAGNOSTIC_TEST_CODE	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	RESULTING_CODE	RESULTING	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	PRE_INTERVENTION_DIAGNOSTIC_TEST_CODE	PRE_INTERVENTION_DIAGNOSTIC_TEST	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	INTRA_INTERVENTION_DIAGNOSTIC_TEST_CODE	INTRA_INTERVENTION_DIAGNOSTIC_TEST	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	PROCEDURE_CODE	PROCEDURE_CODE	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	POST_INTERVENTION_DIAGNOSTIC_TEST_CODE	POST_INTERVENTION_DIAGNOSTIC_TEST	NULL
LOAD_DIAGNOSTIC_TEST_SPECIMEN_BRIDGE	DIAGNOSTIC_TEST_CODE	DIAGNOSTIC_TEST_CODE	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	MEDICATION_CODE	MEDICATION_CODE	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	DIAGNOSTIC_TEST_CODE	DIAGNOSTIC_TEST_CODE	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	PRE_INTERVENTION_MEDICATION	PRE_INTERVENTION_MEDICATION	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	INTRA_INTERVENTION_MEDICATION	INTRA_INTERVENTION_MEDICATION	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	POST_INTERVENTION_MEDICATION	POST_INTERVENTION_MEDICATION	NULL
LOAD_DX_PATIENT_BRIDGE	DIAGNOSIS_CODE	'DIAGNOSIS_CODE', 'DIAGNOSIS_CATEGORY'	NULL
LOAD_DX_PATIENT_BRIDGE	DIAGNOSIS_STATUS_CODE	DIAGNOSIS_STATUS_CODE	NULL
LOAD_ETHNICITY_DIMENSION	ETHNICITY_CODE	ETHNICITY_CODE	NULL
LOAD_MEDICATION_DIMENSION	MEDICATION_CODE	MEDICATION_CODE	NULL
LOAD_PATIENT_HISTORY_DIMENSION	PATIENT_HISTORY_CODE	PATIENT_HISTORY_CODE	NULL
LOAD_PROCEDURE_DIMENSION	PROCEDURE_CODE	PROCEDURE_CODE	NULL
LOAD_PROCEDURE_PATIENT_BRIDGE	PROCEDURE_CODE	PROCEDURE_CODE	NULL
LOAD_PROCEDURE_SUBSTANCEADMINISTRATION_BRIDGE	MEDICATION_CODE	MEDICATION_CODE	NULL
LOAD_PROCEDURE_SUBSTANCEADMINISTRATION_BRIDGE	PROCEDURE_CODE	PROCEDURE_CODE	NULL
LOAD_PROCEDURE_SUBSTANCEADMINISTRATION_BRIDGE	MEDICATION_RELSHP_CODE	'PRE_INTERVENTION_MEDICATION','INTRA_INTERVENTION_MEDICATION','POST_INTERVENTION_MEDICATION'	NULL
LOAD_PROCEDURE_TYPE_PROCEDURE_BRIDGE	PROCEDURE_CODE	PROCEDURE_CODE	NULL
LOAD_RACE_DIMENSION	RACE_CODE	RACE_CODE	NULL
LOAD_RESEARCH_PATIENT_D	ADDR_STATUS_CODE	A	NULL
LOAD_RESEARCH_PATIENT_D	HOME_ADDR_TYP	HOME	NULL
LOAD_RESEARCH_PATIENT_D	CITY	CITY	NULL
LOAD_SPECIMEN_DIMENSION	SPECIMEN_TYPE_CODE	SPECIMEN_TYPE_CODE	NULL
LOAD_SUBADMN_PATIENT_BRIDGE	MEDICATION_CODE	MEDICATION_CODE	NULL
LOAD_UOM_DIMENSION	UOM_CODE	'SPECIMEN_QUANTITY_UOM_CODE','FREQUENCY_UOM_CODE','AGE_AT_FIRST_ONSET_UOM_CODE','DOSE_UOM_CODE','OBSERVATION_VALUE_UOM_CODE'	NULL

C_LOAD_DE_IDENTIFY Table

Following table shows the configuration of de-identification:

Table A-3 C_LOAD_DE_IDENTIFY Table Seed Data

PACKAGE_NAME	TARGET_TABLE_NAME	PARAM_NAME	PARAM_DATA_TYPE	DE_IDENTITY_VALUE	DE_IDENTITY_FLG	INSERT_DT
LOAD_CONSENT_PATIENT_BRIDGE	W_EHA_CONSENT_PATIENT_H	CNSNT_EFF_STRT_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_CONSENT_PATIENT_BRIDGE	W_EHA_CONSENT_PATIENT_H	CNSNT_EFF_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGNOSTIC_TEST_PATIENT_BRIDGE	W_EHA_DIAGTST_PATIENT_H	DIAGTST_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	W_EHA_DIAGTST_PROC_DHL	PROCEDURE_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	W_EHA_DIAGTST_PROC_DHL	DIAGTST_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGNOSTIC_TEST_PROCEDURE_BRIDGE	W_EHA_DIAGTST_PROC_DHL	PROCEDURE_START_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGNOSTIC_TEST_SPECIMEN_BRIDGE	W_EHA_DIAGTST_SPEC_DHL	SPECIMEN_COLLECTION_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGNOSTIC_TEST_SPECIMEN_BRIDGE	W_EHA_DIAGTST_SPEC_DHL	DIAGTST_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	W_EHA_DIAGTST_SUBADM_DHL	SUBADMN_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	W_EHA_DIAGTST_SUBADM_DHL	DIAGTST_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DIAGTST_SUBADM_BRIDGE	W_EHA_DIAGTST_SUBADM_DHL	SUBADMN_START_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DX_PATIENT_BRIDGE	W_EHA_DX_PATIENT_H	AGE_AT_FIRST_ONSET	NUMBER	1111	NULL	NULL
LOAD_DX_PATIENT_BRIDGE	W_EHA_DX_PATIENT_H	DIAGNOSIS_REPORTED_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DX_PATIENT_BRIDGE	W_EHA_DX_PATIENT_H	DIAGNOSIS_ONSET_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_DX_PATIENT_BRIDGE	W_EHA_DX_PATIENT_H	DIAGNOSIS_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PATIENT_HISTORY_PATIENT_BRIDGE	W_EHA_PT_HISTORY_PT_H	PATIENT_HISTORY_START_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PATIENT_HISTORY_PATIENT_BRIDGE	W_EHA_PROC_PATIENT_H	PATIENT_HISTORY_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PROCEDURE_PATIENT_BRIDGE	W_EHA_PROC_PATIENT_H	PROCEDURE_START_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PROCEDURE_PATIENT_BRIDGE	W_EHA_PROC_PATIENT_H	PROCEDURE_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PROCEDURE_SUBSTANCEADMINISTRATION_BRIDGE	W_EHA_PROC_SUBADMN_DHL	SUBADMN_START_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PROCEDURE_SUBSTANCEADMINISTRATION_BRIDGE	W_EHA_PROC_SUBADMN_DHL	SUBADMN_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PROCEDURE_SUBSTANCEADMINISTRATION_BRIDGE	W_EHA_PROC_SUBADMN_DHL	PROCEDURE_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_PROCEDURE_SUBSTANCEADMINISTRATION_BRIDGE	W_EHA_PROC_SUBADMN_DHL	PROCEDURE_START_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_RESEARCH_PATIENT_D	W_EHA_RESEARCH_PATIENT_D	DOB_DATE	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_RESEARCH_PATIENT_D	W_EHA_RESEARCH_PATIENT_D	DECEASED_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_RESEARCH_PATIENT_D	W_EHA_RESEARCH_PATIENT_D	CITY	STRING	Alaska	NULL	NULL
LOAD_RESEARCH_PATIENT_D	W_EHA_RESEARCH_PATIENT_D	POSTAL_CODE	STRING	99506	NULL	NULL
LOAD_SPECIMEN_PATIENT_BRIDGE	W_EHA_SPECIMEN_PATIENT_H	SPECIMEN_COLLECTION_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_SUBADMN_PATIENT_BRIDGE	W_EHA_SUBADMN_PATIENT_H	INTVN_STRT_DT	DATE	01/01/1900 00:00:00	NULL	NULL
LOAD_SUBADMN_PATIENT_BRIDGE	W_EHA_SUBADMN_PATIENT_H	INTVN_END_DT	DATE	01/01/1900 00:00:00	NULL	NULL