Skip Headers
Oracle® Health Sciences Cohort Explorer Administrator's Guide
Release 1.0

Part Number E24438-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 Configuration Steps

This chapter describes the different configuration steps required in different cases. It contains the following topics:

ETL Configuration

Configuring the C_LOAD_PARAM Table

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).

ETL Global Parameters

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


Configuring C_LOAD_DE_IDENTIFY Attributes

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.

Standard Configuration of the C_LOAD_DATES Table

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


Standard Configuration of the C_COHORT_PROCEDURE_TYPE Table

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


Report Configuration

Customizing Reports Hierarchy

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.