| Oracle® Healthcare Data Model Reference 11g Release 2 (11.2) Part Number E18026-02 |
|
|
View PDF |
| Oracle® Healthcare Data Model Reference 11g Release 2 (11.2) Part Number E18026-02 |
|
|
View PDF |
Some tables are defined with a DWC_ prefix in the ohdm_sys schema. You use the control tables when processing the model. For example when loading data or when monitoring errors. In addition to the DWC_ tables, the DWX_FAC_SHFT_XREF table is an install and configuration control table.
This appendix includes the following sections:
Table A-1 lists the control tables.
Table A-1 Control Tables for Oracle Healthcare Data Model
| Table Name | Description |
|---|---|
|
DWC_ETL_HDM_DMN_CD_SYS |
|
|
DWC_ETL_PARAM |
|
|
DWC_INTRA_ETL_ACTVTY |
|
|
DWC_INTRA_ETL_PROC |
|
|
DWC_MESSAGE |
|
|
DWC_OLAP_ETL_PARAM |
|
|
DWX_FAC_SHFT_XREF |
The ETL Install and configuration tables include the following steps:
Set up the Oracle Healthcare Data Model Domain Code System Control Table as part of the Analytical Layer configuration. This table contains the Oracle Healthcare Data Model Code System for each Domain/Subject Area. This information is provided to set up the ETL to use the Code Repository tables appropriately.
Table A-2 provides sample data for the DWC_ETL_HDM_DMN_CD_SYS table.
Table A-2 DWC_ETL_HDM_DMN_CD_SYS Control Table (Values Included for a Logical Example)
| Code Type Details | Oracle Healthcare Data Model Domain/Subject Area | Code System Details |
|---|---|---|
|
1001 (Encounter Subtype) |
ENCOUNTER |
1 - Custom (version 1) |
|
1002 (Encounter Code) |
ENCOUNTER |
1 - Custom (version 1) |
|
1002 (Encounter Code) |
ENCOUNTER |
2 - CPT (version 1.1) |
|
1002 (Encounter Code) |
ENCOUNTER |
7 - CPT Code (version 1) |
|
1003 (Encounter Status Code) |
ENCOUNTER |
1 - Custom (version 1) |
|
1004 (Source of Admission Code) |
ENCOUNTER |
1 - Custom (version 1) |
|
1005 (Party Subtype) |
PARTY |
1 - Custom (version 1) |
|
1006 (Ethnicity Code) |
PARTY |
1 - Custom (version 1) |
|
1007 (Race Code) |
PARTY |
1 - Custom (version 1) |
|
1008 (Gender Code) |
PARTY |
1 - Custom (version 1) |
|
1009 (Clinical Trial Status Code) |
PATIENT |
1 - Custom (version 1) |
|
1010 (Concern Subtype) |
CONCERN |
1 - Custom (version 1) |
|
1011 (Concern Code) |
CONCERN |
3 - ICD9 (version 1.1) |
|
1012 (Encounter Concern Relationship Type) |
ENCOUNTER |
1 - Custom (version 1) |
|
1013 (Service Provider Type) |
SERVICE PROVIDER |
1 - Custom (version 1) |
|
1014 (Observation Subtype) |
OBSERVATION |
1 - Custom (version 1) |
|
1015 (Observation Code) |
OBSERVATION |
1 - Custom (version 1) |
|
1015 (Observation Code) |
OBSERVATION |
2 - CPT (version 1.1) |
|
1015 (Observation Code) |
OBSERVATION |
5 - HCPCS (version 1.1) |
|
1015 (Observation Code) |
OBSERVATION |
6 - SNOMED (version 1.1) |
Shifts are defined in the Analytical Layer Shift Table (DWR_SHFT). This table is used to define a set of Shifts encompassing a day (24 hour Period).
There are two types of shifts pre-defined in Oracle Healthcare Data Model:
7am - 2pm, 2pm - 10pm and 10pm - 7am
7am - 11am, 11am - 2pm, 2pm - 6pm, 6pm - 11pm and 11pm - 7am
Each Facility (HDM_FAC) can follow a Shift definition from the Shift Table DWR_SHFT (that is, can be associated with a set of shifts selected from the above listing).
Set up the Facility Shift Cross Reference Table as part of the Analytical Layer configuration. The Configuration Table, Facility Shift Cross Reference Table, indicates the Shifts used by a Facility (HDM_FAC).
Table A-3 provides sample data for the DWX_FAC_SHFT_XREF table.
Table A-3 DWX_FAC_SHFT_XREF Control Table (Values Included for a Logical Example)
| Facility | Shift |
|---|---|
|
1 (100-1) |
1 (07:00 hrs - 14:00 hrs) |
|
1 (100-1) |
2 (14:00 hrs - 22:00 hrs) |
|
1 (100-1) |
3 (22:00 hrs - 07:00 hrs) |
|
1 (100-1) |
4 (22:00 hrs - 07:00 hrs) |
|
11 (A2) |
5 (07:00 hrs - 11:00 hrs) |
|
11 (A2) |
6 (11:00 hrs - 14:00 hrs) |
|
11 (A2) |
7 (14:00 hrs - 18:00 hrs) |
|
11 (A2) |
8 (18:00 hrs - 23:00 hrs) |
|
11 (A2) |
9 (23:00 hrs - 07:00 hrs) |
|
11 (A2) |
10 (23:00 hrs - 07:00 hrs) |
|
24 (Office Z) |
1 (07:00 hrs - 14:00 hrs) |
|
24 (Office Z) |
2 (14:00 hrs - 22:00 hrs) |
|
24 (Office Z) |
3 (22:00 hrs - 07:00 hrs) |
|
24 (Office Z) |
4 (22:00 hrs - 07:00 hrs) |
Use the ohdm_execute_wf.sh program to manually execute the Intra-ETL. Before you run the Intra-ETL, for an incremental load, you need to update the Oracle Healthcare Data Model Relational ETL parameters in DWC_ETL_PARAM table so that this information can be used when loading the relational data. This program prompts for several environment parameter values and reads ETL parameters from DWC_ETL_PARAM table, as shown in Table A-4, and DWC_OLAP_ETL_PARAM table, as shown in Table A-5.
The PKG_DWD_*_MAP loads data from Oracle Healthcare Data Model base tables into the Oracle Healthcare Data Model derived tables. These packages read relational ETL parameters from the DWC_ETL_PARAM table.
You update the parameters in DWC_ETL_PARAM control table in the ohdm_sys schema so that this information can be used when loading the derived and aggregate tables and views.
Table A-4 describes the valid values for the DWC_ETL_PARAM table.
Table A-4 DWC_ETL_PARAM Table
| Column | Description |
|---|---|
|
PRCSS_NM |
Two possible values:
|
|
ETL_STRT_DT_TM |
The start date and time of ETL Period. |
|
ETL_END_DT_TM |
The end date and time of ETL Period. |
|
LOAD_DT |
The date when this record are populated. |
|
LST_UPDT_DT |
The date when this record are last updated |
|
LST_UPDT_BY |
The user who last updated this record |
The OLAP_FLW mapping that loads OLAP cube data invokes the analytic workspace build function from the PKG_OHDM_OLAP_ETL_AW_LOAD package. This package loads data from Oracle Healthcare Data Model Dimension and KPI Fact Views into the Oracle Healthcare Data Model analytical workspace (OHDM AW) and prepares the OHDM AW for Reporting. The PKG_OHDM_OLAP_ETL_AW_LOAD reads OLAP ETL parameters from the DWC_OLAP_ETL_PARAM table.
Note:
The OHDM DWV_*_FCT OLAP KPI Fact source views are defined using ETL parameters from the DWC_ETL_PARAM table as well as using OLAP ETL parameters from the DWC_OLAP_ETL_PARAM table.Table A-5 describes the valid values for the DWC_OLAP_ETL_PARAM table. You update the Oracle Healthcare Data Model OLAP ETL parameters in DWC_OLAP_ETL_PARAM control table in the ohdm_sys schema so that this information can be used when loading the OLAP cube data. For more information on the values to specify when performing an initial load of OLAP cube data or when refreshing the OLAP cubes after an initial load, see Oracle Healthcare Data Model Operations Guide.
Table A-5 ETL Parameters in the DWC_OLAP_ETL_PARAM Table
| Column Name | Description |
|---|---|
|
PRCSS_NM |
There are two process names for this column:
|
|
BULD_METHD |
Cube build/refresh method specified by one of the following values:
|
|
CUBENAME |
Specifies the cubes you want to build:
|
|
MAXJOBQUEUES |
A decimal value that specifies the number of parallel processes to allocate to this job. (Default value is Note that the number of parallel processes actually allocated by a build is controlled by the smallest of these factors:
|
|
ILM_STRT_DT |
The start date of the data age. This is the Start Date of the Information Lifecycle Management (ILM) Date Range that the cubes need to maintain. Data outside the ILM Date Range would be dropped off, truncated, from Cube storage. |
|
ILM_END_DT |
The end date of the data age. End Date of the Information Lifecycle Management (ILM) Date Range that the cubes need to maintain. Data outside the ILM Date Range would be dropped off, truncated, from Cube storage. |
|
OTHER1 |
Reserved for future use. (Default value is |
|
OTHER2 |
Reserved for future use. (Default value is |
The control tables in the ohdm_sys schema, DWC_INTRA_ETL_PROC and DWC_INTRA_ETL_ACTVTY, monitor the execution of the Intra-ETL process.
Table A-6 contains column name information for DWC_INTRA_ETL_PROC. Table A-7 contains column name information for DWC_INTRA_ETL_ACTVTY.
Table A-6 DWC_INTRA_ETL_PROC Columns
| Column Name | Data Type | Not Null | Remarks |
|---|---|---|---|
|
PRCSS_KEY |
NUMBER(30) |
Yes |
Primary Key, System Generated Unique Identifier |
|
PRCSS_TYP |
VARCHAR2(20) |
Yes |
Process Type. Typically, NORMAL |
|
PRCSS_STRT_TM |
DATE |
Yes |
ETL Process Start Date and Time |
|
PRCSS_END_TM |
DATE |
No |
ETL Process End Date and Time |
|
PRCSS_STUS |
VARCHAR2(30) |
Yes |
Current status of the process |
|
OLD_PRCSS_KEY |
NUMBER(22) |
No |
Old Process Key (if applicable) |
|
FROM_DT_ETL |
DATE |
No |
Start Date (ETL) - From Date of the ETL date range |
|
TO_DT_ETL |
DATE |
No |
End Date (ETL) - To Date of the ETL date range |
|
LOAD_DT |
DATE |
Record Load Date - Audit Field |
|
|
LST_UPDT_DT |
DATE |
Last Update Date and Time - Audit Field |
|
|
LST_UPDT_BY |
VARCHAR2(30) |
Last Update By - Audit Field |
Table A-7 DWC_INTRA_ETL_ACTVTY Columns
| Column Name | Data Type | Not Null | Remarks |
|---|---|---|---|
|
ACTVTY_KEY |
NUMBER(30) |
Yes |
ETL Activity Key. Primary Key, System Generated Unique Identifier. |
|
PRCSS_KEY |
NUMBER(30) |
Yes |
ETL Process Key. Foreign Key to DWC_INTRA_ETL_PROC table. |
|
ACTVTY_NM |
VARCHAR2(50) |
Yes |
ETL Activity Name or Intra ETL Program Name |
|
ACTVTY_DESC |
VARCHAR2(500) |
No |
Activity description |
|
ACTVTY_STRT_TM |
DATE |
Yes |
Indicates the ETL Activity Start Date and Time |
|
ACTVTY_END_TM |
DATE |
No |
Indicates the ETL Activity End Date and Time. |
|
ACTVTY_STUS |
VARCHAR2(30) |
Yes |
Activity Status |
|
COPIED_REC_IND |
CHAR(1) |
No |
|
|
ERR_DTL |
VARCHAR2(2000) |
No |
Error details if any |
|
LOAD_DT |
DATE |
Record Load Date - Audit Field |
|
|
LST_UPDT_DT |
NUMBER(30) |
Last Update Date and Time - Audit Field |
|
|
LST_UPDT_BY |
VARCHAR2(30) |
Last Update By - Audit Field |
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
