Oracle® Health Sciences Cohort Explorer Administrator's Guide Release 1.0 E24438-01 |
|
|
PDF · Mobi · ePub |
This chapter describes the different configuration steps required in different cases. It contains the following topics:
The C_LOAD_PARAM table is used to store the seed data needed for loading into the 33 data model tables. The attributes of this table are described in the table below. After the OHSCE installs successfully, this table is populated with default seed data. You must change the value in PARAM_VALUE field according to your requirements.
Table 2-1 C_LOAD_PARAM Table Attributes
Column Name | Column Type | Description |
---|---|---|
PACKAGE_NAME |
VARCHAR2 (300) |
Name of the ODI package that includes the variable, full and incremental interfaces |
PARAM_NAME |
VARCHAR2 (80) |
Name of the ODI parameter used in the interface |
PARAM_VALUE |
VARCHAR2 (4000) |
Actual value of the ODI parameter used in the interface |
INSERT_DT |
DATE |
Date when the record got inserted |
You must update the PARAM_VALUE fields with appropriate code type values.
PACKAGE_NAME records starting with LOAD are configuration records for loading into their respective data model table. For example, in the table above, the package LOAD_CONSENT_DIMENSION is the configuration record required for loading W_EHA_CONSENT_D. You must therefore provide appropriate code type value for loading W_EHA_CONSENT_D in the field PARAM_VALUE for that corresponding package.
For PACKAGE_NAME records containing SEED_NAV_RECORDS and SEED_NA_RECORDS, the PARAM_VALUE fields may or may not be changed. These records are needed to populate NAV and NA records in all dimension tables (%_D).
Following is the list of global parameters applicable for all ETLs. Oracle recommends that you do not make any modification to these parameters.
Table 2-2 ETL Global Parameters in C_LOAD_PARAM Table
PACKAGE_NAME | PARAM_NAME | PARAM_VALUE | DESCRIPTION |
---|---|---|---|
GLOBAL |
HIERARCHY_NAME |
DEFAULT_HIERARCHY |
NULL |
GLOBAL |
SEPERATOR |
~ |
NULL |
SEED_NA_RECORDS |
$NA_VARCHAR2 |
NA |
NULL |
SEED_NA_RECORDS |
$NA_CHAR |
1 |
NULL |
SEED_NA_RECORDS |
$NA_NUMBER |
9999 |
NULL |
SEED_NA_RECORDS |
$NA_WID |
-2 |
NULL |
SEED_NA_RECORDS |
$NA_DESC |
No value |
NULL |
SEED_NA_RECORDS |
$NA_DATE |
1-Jan-00 |
NULL |
SEED_NA_RECORDS |
$NA_CODE |
$NULL_NO_VAL$ |
NULL |
SEED_NAV_RECORDS |
$NAV_VARCHAR2 |
NAV |
NULL |
SEED_NAV_RECORDS |
$NAV_CHAR |
1 |
NULL |
SEED_NAV_RECORDS |
$NAV_NUMBER |
9999 |
NULL |
SEED_NAV_RECORDS |
$NAV_WID |
-1 |
NULL |
SEED_NAV_RECORDS |
$NAV_DESC |
No value is set for exclusion |
NULL |
SEED_NAV_RECORDS |
$NAV_DATE |
1-Jan-00 |
NULL |
SEED_NAV_RECORDS |
$NAV_CODE |
$NULL_NO_VAL_SET_EXCL$ |
NULL |
The C_LOAD_DE_IDENTIFY table is used to store de-identification attributes and its values for the de-identification process. De-identification is the process of removal or masking of identifying information like patient's name, medical record number, birth date, and social security number from medical records, to protect patient privacy.
This table is also loaded with default seed data when the application is installed successfully. You may change the values for the fields DE_IDENTITY_VALUE and DE_IDENTITY_FLG according to your business needs. The default configuration is to move the value of Patient's de-identification attributes as it is from HDWF to CDM. Oracle recommends that you enable masking of these attributes.The attributes of the table are described below.
Table 2-3 C_LOAD_DE_IDENTIFY Table Attributes
Column Name | Column Type | Description |
---|---|---|
PACKAGE_NAME |
VARCHAR2 (300) |
Name of the ODI package that includes the variable, full and incremental interfaces |
TARGET_TABLE_NAME |
VARCHAR2 (80) |
Name of the main target table that is loaded by this package |
PARAM_NAME |
VARCHAR2 (80) |
Name of the parameter used in the interface |
PARAM_DATA_TYPE |
VARCHAR2 (80) |
Data type of the parameter. The supported data types can be varchar, number and date |
DE_IDENTITY_VALUE |
VARCHAR2 (4000) |
De identity value of the parameter used in the interface. This field will contain value when the DE_IDENTITY_FLG is set to 'S' |
DE_IDENTITY_FLG |
VARCHAR2 (1) |
A flag used to represent the type of value supplied to the parameter S represents Standard Value C represents the Custom Value; this will call the PL/SQL function customized by customer. |
INSERT_DT |
DATE |
Date when the record got inserted |
You can choose any one of these three masking options:
Populate HDM value as it is to their corresponding CDM attributes. (no masking)
Populate a standard value to the CDM de-identification attributes. (Masking with constant value)
Populate a calculated value to the CDM de-identification attributes (Masking with a calculated value)
A configuration table called C_LOAD_DE_IDENTIFY is created to choose any one of the above option and three de-identification functions are created for finding the calculated value of each type of value (for example, CHAR, NUMBER, DATE). These are:
CUSTOM_HDM_DATE_DE_IDENTIFY (…) -- user-defined function to encrypt a DATE attribute
CUSTOM_HDM_NUMBER_DE_IDENTIFY (…) -- user-defined function to encrypt a NUMBER attribute
CUSTOM_HDM_STRING_DE_IDENTIFY (…) -- user-defined function to encrypt a CHAR or STRING attribute
You can change the logic of these three functions. Use the DE_IDENTITY_FLG and DE_IDENTITY_VALUE fields in the table C_LOAD_DE_IDENTIFY to choose the option for any de-identification attribute.
Each de-identification attribute has a corresponding record in this table. Provide null, S, or C values for each record in the DE_IDENTITY_FLG field, based on the following criteria:
If you do not want masking for any attribute at any time, leave the null value in this field. In this case, the value from HDWF attribute is populated to corresponding attribute of CDM.
If you want mask the value of attribute, configure the DE_IDENTIFY_FLG as 'S' and configure the value of DE_IDENTIFY_VALUE with the masked value that you would like to populate into it's corresponding CDM attribute.
For example, if you want to mask the value of DIAGNOSIS_ONSET_DT attribute of W_EHA_DX_PATIENT_H to '01-01-1900', configure the record as following.
Table 2-4 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_DX_PATIENT_BRIDGE |
W_EHA_DX_PATIENT_H |
DIAGNOSIS_ONSET_DT |
DATE |
01/01/1900 00:00:00 |
S |
NULL |
Note:
Ensure that the PARAM_DATA_TYPE column in C_LOAD_DE_IDENTIFY is configured to the data type column of attribute to be masked.Ensure the value of PARAM_VALUE is correctly formatted. No quotes should be prefixed or suffixed to the literal configured.
For date fields, it should be MM/DD/YYYY HH24:MI:SS.
For varchar field, it should be a varchar.
For Number, it has to be valid number.
If you want to populate standard values for any attribute, provide value S in this field. Also provide the standard value to be populated in CDM, in the field DE_IDENTITY_VALUE.
If you want to populate calculated values, provide value C in this field. In this case the value from any one of the three functions listed above (depending on the data type of the attribute) is populated.
The C_LOAD_DATES table is called the job control table. During the incremental load, it is used to store the most recent execution details of ODI packages and therefore, this table is loaded automatically during the execution of ODI components. You do not need to update this table.
Table 2-5 C_LOAD_DATES Table Attributes
Column Name | Column Type | Description |
---|---|---|
PACKAGE_NAME |
VARCHAR2 (300) |
Name of the ODI package that includes the variable, full and incremental interfaces |
TARGET_TABLE_NAME |
VARCHAR2 (300) |
Name of the main target table that is loaded by this package |
ETL_PROC_WID |
NUMBER (10) |
Unique identifier that is used to populate for all the records in that table for a particular load |
LAST_MAX_DATE |
DATE |
Last extract timestamp -1 second of the previous extraction |
INSERT_DT |
DATE |
Date when the record got inserted |
The C_COHORT_PROCEDURE_TYPE table is used to store different procedure- types used in Oracle Health Sciences Cohort Explorer reports. The attributes of the table are described below.
Table 2-6 C_COHORT_PROCEDURE_TYPE Table Attributes
Column Name | Column Type | Description |
---|---|---|
COHORT_PROC_TYP_ID |
NUMBER(10,0) |
The Surrogate key for Procedure Type |
COHORT_PROC_TYP_CODE |
VARCHAR2(80 BYTE) |
The Actual (functional) code of Procedure Type |
INSERT_DT |
DATE |
Date when the record got inserted |
UPDATE_DT |
DATE |
The record last updation date |
DELETE_FLG |
CHAR(1 BYTE) |
The soft delete flag of the record |
INTEGRATION_ID |
VARCHAR2(400 BYTE) |
The integration ID of the each procedure type code |
DATASOURCE_NUM_ID |
NUMBER(10,0) |
The data source number ID of the each procedure type code |
The C_COHORT_PROCEDURE_TYPE table contains seed data as shown below. Oracle recommends that you do not modify the seed data in this configuration table as these procedure types are used in Oracle Health Sciences Cohort Explorer reports.
Table 2-7 C_COHORT_PROCEDURE_TYPE Table Seed Data
COHORT_PROC_TYP_ID | COHORT_PROC_TYP_CODE | INSERT_DT | UPDATE_DT | DELETE_FLG | INTEGRATION_ID | DATASOURCE_NUM_ID |
---|---|---|---|---|---|---|
1 |
PROC_CHEMO_CODE |
11-JAN-11 |
11-JAN-11 |
N |
PROC_CHEMO_CODE |
1 |
2 |
PROC_ONCOLOGY_CODE |
11-JAN-11 |
11-JAN-11 |
N |
PROC_ONCOLOGY_CODE |
1 |
3 |
PROC_SURGERY_CODE |
11-JAN-11 |
11-JAN-11 |
N |
PROC_SURGERY_CODE |
1 |
OHSCE supports two hierarchies, one for diagnosis and the other for anatomical site. The data model supports a 15 level hierarchy for both diagnosis, and anatomical site, however for this version of OHSCE we are only using 8 of the 15 levels for diagnosis, and 6 of the 15 levels for anatomical site.
You can use more than the default number of levels by adding new levels to the pre-built hierarchies in the Business Modeling and Mapping (BMM) Layer of the RPD. After you add parent levels to the default hierarchies, you must add members to each additional level. These members should then be moved to the appropriate folder in the Presentation Layer.
Once added to the hierarchy definition and the presentation layer, the additional hierarchy members can be used in custom reports and dashboards.