| Oracle® Healthcare Data Model Operations Guide 11g Release 2 (11.2) Part Number E18027-02 |
|
|
View PDF |
| Oracle® Healthcare Data Model Operations Guide 11g Release 2 (11.2) Part Number E18027-02 |
|
|
View PDF |
This chapter describes how to populate an Oracle Healthcare Data Model warehouse:
Note:
In general, the instructions in this chapter assume that after doing the fit-gap analysis described in "Performing Fit-Gap Analysis", you have not identified or made any changes to the Oracle Healthcare Data Model logical or physical model. If you have made changes, you need to modify the ETL accordingly.Before you populate your Oracle Healthcare Data Model warehouse for the first time, take the following steps:
Perform all of the post-installation tasks described in Oracle Healthcare Data Model Installation Guide.
Familiarize yourself with the ETL you use to populate an Oracle Healthcare Data Model warehouse as described in "Overview: The ETL for an Oracle Healthcare Data Model Warehouse".
If the generic code names that are hard-coded in the ETL packages used by the KPI_FLW subprocess are not the names that are actually used by your healthcare organization, replace these codes as described in "Modifying the ETL to Change the Code Names".
See:
"Intra-ETL for Oracle Healthcare Data Model" for information on the KPI_FLW subprocess.Determine if you will be doing an initial load of your warehouse or an incremental load. Familiarize yourself with the process of performing which ever type of load you will be performing.
Familiarize yourself with how you can manage any errors that might occur during that initial load as described in "Managing Errors During Intra-ETL Execution".
Estimate the size of your Oracle Healthcare Data Model warehouse. The size of Oracle Healthcare Data Model warehouse largely depends on the number of patients and encounters.
As with any data warehouse, you use Extract, Transform, and Load (ETL) operations to populate an Oracle Healthcare Data Model data warehouse. You use the ETL to perform the initial load of an Oracle Healthcare Data Model data warehouse. After this initial load, you use the ETL to load new data into your Oracle Healthcare Data Model data warehouse regularly so that it can serve its purpose of facilitating business analysis.
These successive loads and transformations must be scheduled and processed in a specific order and will be determined by your business needs. Depending on the success or failure of the operation or parts of it, the result must be tracked and subsequent, alternative processes might be started. You can do a full incremental load of the relational tables and views, OLAP cubes, and the data mining model all at once, or you can refresh the data sequentially.
You perform ETL operations using different types of ETL that you execute in the following order:
Source-ETL that loads the HDM_ tables of the Oracle Healthcare Data Model warehouse. Source-ETL is not provided with the Oracle Healthcare Data Model. Guidelines for writing Source-ETL are provided in "Source-ETL for Oracle Healthcare Data Model".
Intra-ETL that uses the data loaded by the source-ETL to populate the other components of the Oracle Healthcare Data Model. Sample Intra-ETL is provided with the Oracle Healthcare Data Model. The Intra-ETL is introduced in "Intra-ETL for Oracle Healthcare Data Model".
Source-ETL loads the HDM_ tables of the Oracle Healthcare Data Model warehouse.
Source-ETL is not provided with Oracle Healthcare Data Model. You must design and write source-ETL processes yourself.
Keep the following points in mind when designing and writing source-ETL:
You can populate the calendar data by using the calendar population scripts provided with Oracle Healthcare Data Model and described in Oracle Healthcare Data Model Reference.
Analyze all of the tables loaded by the source-ETL process before executing the intra-ETL.
Intra-ETL uses the data in the HDM_ tables that were populated by the source-ETL to populate the other tables, views, and models of the Oracle Healthcare Data Model.
Intra-ETL is provided with the Oracle Healthcare Data Model as the INTRA_ETL_FLW process flow which is designed using process flow of Oracle Warehouse Builder.
Note:
You do not have to use INTRA_ETL_FLW as the the Intra-ETL for Oracle Healthcare Data Model. You can write your own Intra-ETL.INTRA_ETL_FLW is a complete Intra-ETL process composed of subprocess flows to populate the dimensions, derived tables, and KPI fact tables along with two other subprocess flows for Oracle OLAP and Data Mining. This process flow respects the dependency of each individual program and executes the programs in the proper order.
The INTRA_ETL_FLW is the complete Intra ETL process designed using process flow of Oracle Warehouse Builder, and is composed of the following individual subprocesses:
DIM_FLW - This subprocess flow triggers the ETLs for populating dimension tables.
DRVD_FLW - This subprocess flow triggers the ETLs for populating derived fact tables based on the content of the 3NF HDM tables.
KPI_FLW - This subprocess flow triggers the ETLs for populating the fact tables based on the content of the 3NF HDM tables, dimension table and derived fact tables.
OLAP_FLW - This subprocess flow triggers the execution of the OLAP package which loads data from data warehouse to Oracle Healthcare Data Model analytic workspace and prepares the analytic workspace for reporting. It reads ETL parameters from DWC_ETL_PARAM and DWC_OLAP_ETL_PARAM tables which are documented in Oracle Healthcare Data Model Reference.
MINING_FLW - This subprocess flow triggers the data mining model.
You can install Oracle Health Care Data Model Intra-ETL as a project in Oracle Warehouse Builder as described in Oracle Healthcare Data Model Installation Guide. Once installed, you can execute the intra-ETL from Oracle Warehouse Builder.
After you install INTRA_ETL_FLW as a project in Oracle Warehouse Builder as described in Oracle Healthcare Data Model Installation Guide, take the following to populate the Oracle Healthcare Data Model warehouse:
Ensure that the HDM_ tables in the Oracle Healthcare Data Model warehouse are up-to-date. If necessary, execute the source-ETL that you have written using the guidelines given in "Source-ETL for Oracle Healthcare Data Model".
Update the parameters in the DWC_ETL_PARAM and the DWC_OLAP_ETL_PARAM control tables following the guidelines Oracle Healthcare Data Model Reference. (For an initial load, remember to specify C for the value of the BUILD_METHD column of the DWC_OLAP_ETL_PARAM table.)
See:
Oracle Healthcare Data Model Reference for complete information on these control tables.Ensure that the repository user (for example, OHDM_ETL) has the EXECUTE privilege for all the packages that are executed as part of OHDM_INTRA_ETL Process Flow.
These packages are listed on the Transformation node of the data module. Following the examples in this chapter, they are listed on the Transformation node of OHDM_INTRA_ETL.
Within Oracle Warehouse Builder, execute the INTRA_ETL_FLW process.
This topic discusses how you can identify and manage errors during Intra-ETL execution. It contains the following topics:
Two control tables in the ohdm_sys schema, DWC_INTRA_ETL_PROC and DWC_INTRA_ETL_ACTVTY, monitor the execution of the Intra-ETL process. These tables are documented in Oracle Healthcare Data Model Reference.
Each normal run (as opposed to an error-recovery run) of a separate Intra-ETL execution performs the following steps:
Inserts a record into the DWC_INTRA_ETL_PROC table with a monotonically increasing system generated unique process key, SYSDATE as process start time, RUNNING as the process status, and an input date range in the FROM_DT_ETL and TO_DT_ETL columns.
Invokes each of the individual Intra-ETL programs in the appropriate order of dependency. Before the invocation of each program, the procedure inserts a record into the Intra-ETL Activity detail table, DWC_INTRA_ETL_ACTVTY, with a system generated unique activity key in ACTVTY_KEY, the process key value corresponding to the Intra-ETL process in PRCSS_KEY, an individual program name as the ACTVTY_NAME, a suitable activity description in ACTVTY_DESC, SYSDATE as the value of activity start time, and RUNNING as the activity status
Updates the corresponding record in the DWC_INTRA_ETL_ACTVTY table for the activity end time and activity status after the completion of each individual ETL program (either successfully or with errors. For successful completion of the activity, the procedure updates the status as 'COMPLETED-SUCCESS'. When an error occurs, the procedure updates the activity status as 'COMPLETED-ERROR', and also updates the corresponding error detail in the ERR_DTL column.
Updates the record corresponding to the process in the DWC_INTRA_ETL_PROC table for the process end time and status, after the completion of all individual intra-ETL programs. When all the individual programs succeed, the procedure updates the status to 'COMPLETED-SUCCESS', otherwise it updates the status to 'COMPLETED-ERROR'.
You can monitor the execution state of the Intra-ETL, including current process progress, time taken by individual programs, or the complete process, by viewing the contents of the and DWC_INTRA_ETL_ACTVTY tables corresponding to the maximum process key. Monitoring can be done both during and after the execution of the Intra-ETL procedure.
To recover an intra-ETL process:
Identify the errors by looking at the corresponding error details that are tracked against the individual programs in the DWC_INTRA_ETL_ACTVTY table.
See:
Oracle Healthcare Data Model Reference for detailed information on the
DWC_INTRA_ETL_ACTVTY table.Correct the causes of the errors.
Re-run the Intra-ETL.
The Intra_ETL has the intelligence of identifying whether it is a normal run or recovery run by referring the DWC_INTRA_ETL_ACTVTY table. During a recovery run, the Intra_ETL executes only the necessary programs.
In this way, the Intra-ETL error recovery is almost transparent, without involving the Data Warehouse or ETL administrator. The administrator only needs to take correct the causes of the errors and re-invoke the Intra-ETL process once more. The Intra-ETL process identifies and executes the programs that generated errors.
To troubleshoot the Intra-ETL performance:
Check the execution plan as described in "Checking the Execution Plan"
Monitor parallel DML executions as described in "Monitoring PARALLEL DML Executions"
Use SQLDeveloper or other tools to view the package body of the code.
For example, take the following steps to examine HDW_ANL_CLIN_KPI2_MAP.
Copy out the main query statement from code viewer.
To do this, you copy from the beginning to end of the query, which is right above another "COMMIT".
In SQLDeveloper worksheet, issue the following command to turn on the parallel DML:
Alter session enable parallel dml;
Paste the main query statement into another SQLDeveloper worksheet and view the execution plan by clicking F6.
Carefully examine the execution plan to make the mapping runs according to an valid plan.
Check that you are running mapping in parallel mode by executing the following SQL statement to count the executed "Parallel DML/Query" statement
column name format a50 column value format 999,999 SELECT NAME, VALUE FROM GV$SYSSTAT WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%' OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%' ;
If you are running mapping in parallel mode, you should see "DML statements parallelized" increased by 1 every time the mapping was invoked. If not you do not see this increase, then the mapping was not invoked as "parallel DML".
If you see "queries parallelized" increased by 1 (one) instead, then typically it means that the SELECT statement inside of the INSERT was parallelized but that INSERT itself was not.
See also:
"Altering Parallelization"Once the data mining model is created, check the error log in DWC_INTRA_ETL_ACTVTY table which is documented in Oracle Healthcare Data Model Reference.
For example, execute the following code.
set line 160 col ACTVTY_NAME format a30 col ACTVTY_STATUS format a20 col error_dtl format a80 select actvty_name, actvty_status, error_dtl from dwc_intra_etl_actvty;
If all models are created successfully, the actvty_status will be "COMPLETED-SUCCESS". If the actvty_status is "COMPLETED-ERROR" for a certain step, please check the ERR_DTL column, and fix the problem accordingly.
A common error message is shown below.
Message not available ... [Language=ZHS]
'ZHS' is a code for a language. The language name it relates to can appear as different name depending on the database environment. This error happens when DWC_MESSAGE.LANG does not contain messages for the current language.
Check the values in the DWC_MESSAGE table and, if required, update to the language code specified by the Oracle session variable USERENV('lang').
Some generic code names are hard-coded in the ETL packages used by the KPI_FLW subprocess. You can replace these codes with the code names actually used in your healthcare organization by changing the values of the names in the HDM_CD_REPOSITORY table.
To make these changes in an ETL package, use any edit tool to open package and then, change the code name as illustrated by Example 3-1, "Changing Code Names". This examples changes names in HDW_ANL_CLIN_KPI1_MAP. The places in the code where you would put in your code names are marked by the comment ----Change to your own code.
Example 3-1 Changing Code Names
CREATE OR REPLACE PACKAGE HDW_ANL_CLIN_KPI1_MAP
AS
PROCEDURE LOADFACT(p_etl_strt_date in DATE default null,p_etl_end_date in DATE default null,p_etl_strt_date_id NUMBER default null,p_etl_end_date_id NUMBER default null);
FUNCTION LOADETL (p_etl_strt_date in DATE default null,p_etl_end_date in DATE default null,p_etl_activity_name IN VARCHAR2,p_etl_process_no IN NUMBER DEFAULT NULL) RETURN VARCHAR2;
END HDW_ANL_CLIN_KPI1_MAP;
/
create or replace PACKAGE BODY HDW_ANL_CLIN_KPI1_MAP
AS
. . .
where
DWD_Enc.ENC_END_DATE_ID between p_etl_strt_date_id and p_etl_end_date_id
And DWD_Enc.Enc_End_DT between p_etl_strt_date and p_etl_end_date
),
flt_enc as (
select /*+materialize */ enc_id from (
select enc_id from enc where
Enc_Strt_Dt - interval '18' year >= Prty_Bir_Dt
and
ENC_Cd_Nm in (
'Evaluation and Management New Outpatient Visit', --Change to your own code
'Evaluation and Management Established Outpatient Visit', --Change to your own code
'Initial Nursing Facility Care', --Change to your own code
'Subsequent Nursing Facility Care', --Change to your own code
'An Assisted Living Facility or Other Domicile', --Change to your own code
'Home services ?new patient', --Change to your own code
'Home services - established patient' ) --Change to your own code
minus
select /*+ ordered */
Enc.Enc_Id
from enc Enc
Inner join DWD_ENC_ORD enc_ord
On enc_ord.Enc_Id=Enc.Enc_Id
--And Enc.ENC_END_DATE_ID between p_etl_strt_date_id and p_etl_end_date_id
--And Enc.Enc_End_DT between p_etl_strt_date and p_etl_end_date
Left outer join HDM_Subadmn_ord_subst saos
On saos.ord_id=enc_ord.ord_id
Left outer join DWX_Code_Rep_XREF Saos_CR_Xref
On Saos_CR_XREF.Cd_Id = SAOS.Subst_Cd_id
And Saos_CR_XREF.Cd_Typ_Cd = '1032' --Change to your own code
--and Saos_CR_XREF.Cd_Typ_Nm = 'Substance Code' --Change to your own code
Left outer join HDM_Ord_Rsn ord_rsn
On ord_rsn.ord_id=enc_ord.ord_id
Left outer join DWX_Code_Rep_XREF OrdRsn_CR_Xref
On OrdRsn_CR_Xref.Cd_Id = Ord_Rsn.Ord_Rsn_Cd_Id
and OrdRsn_CR_Xref.Cd_Typ_Cd = '1030'--Change to your own code
-- and OrdRsn_CR_Xref.Cd_Typ_Nm = 'Order Reason Code'--Change to your own code
Where
Enc_Ord.Ord_Subtyp_Nm in ('Substance Administration Order' ) --Change to your own code
And Enc_Ord.Ord_StCd_Nm in ('Not Prescribed') --Change to your own code
And
(
Saos_CR_Xref.Cd_Desc in ('Beta Blocker') --Change to your own code
OR
Enc_Ord.Ord_Cd_desc in ('Beta Blocker') --Change to your own code
)
AND OrdRsn_CR_Xref.Cd_Nm in ('Not Eligible for Beta Blocker Therapy') --Change to your own code
)
),
Numerator as
(
select /*+materialize */ enc_id from
(
SELECT enc21.enc_id
From enc Enc21
Inner Join DWD_ENC_ORD EO21
On Enc21.Enc_Id = EO21.Enc_Id
Left outer join HDM_Subadmn_Ord_Subst saos21
On saos21.ord_id=EO21.ord_id
Left outer join DWX_Code_Rep_XREF Saos_CR_Xref21
On Saos_CR_XREF21.Cd_Id = SAOS21.Subst_Cd_id
And Saos_CR_XREF21.Cd_Typ_Cd = '1032'--Change to your own code
--and Saos_CR_XREF21.Cd_Typ_Nm = 'Substance Code'
WHERE
(
EO21.Ord_Subtyp_Nm in ('Substance Administration Order' ) --Change to your own code
AND EO21.Ord_StCd_Nm in ('Prescribed') --Change to your own code
AND
(
Saos_CR_Xref21.Cd_Desc in ('Beta Blocker') --Change to your own code
OR
EO21.Ord_Cd_Desc in ('Beta Blocker') --Change to your own code
)
)
union
SELECT EI22.enc_id
From DWD_ENC_INTVN EI22
Inner Join HDM_SUBADMN Sa22
On EI22.Intvn_Id=Sa22.Intvn_Id
Inner join DWX_Code_Rep_XREF Sa_CR_Xref22
On Sa_CR_XREF22.Cd_Id = SA22.SUBST_TYP_cd_ID
And Sa_CR_XREF22.Cd_Typ_Cd = '1038'--Change to your own code
--and Saos_CR_XREF21.Cd_Typ_Nm = 'Substance Type'--Change to your own code
WHERE
EI22.Intvn_Subtyp_Nm in ('Substance Administration') --Change to your own code
AND EI22.Intvn_Cd_Desc in ('Beta Blocker' ) --Change to your own code
AND Sa_CR_XREF22.Cd_Nm in ('Medication') --Change to your own code
union
SELECT enc23.enc_id
FROM enc Enc23
Inner Join HDM_PT_HX Pt_Hx23
On Enc23.Pt_Id = Pt_Hx23.Pt_Id
Inner Join DWX_CODE_Rep_XREF Hxsub_CR_Xref23
On Hxsub_CR_Xref23.Cd_Id = Pt_Hx23.Pt_Hx_Subtyp_Id
and Hxsub_CR_Xref23.Cd_Typ_Cd = '1046'--Change to your own code
--and Hxsub_CR_Xref23.Cd_Typ_Nm = 'Patient History Subtype'--Change to your own code
Inner Join DWX_CODE_Rep_XREF Hx_CR_Xref23
On Hx_CR_XREF23.Cd_Id = Pt_Hx23.Pt_Hx_Cd_Id
and Hx_CR_XREF23.Cd_Typ_Cd = '1047'--Change to your own code
--and Hx_CR_XREF23.Cd_Typ_Nm = 'Patient History Code'--Change to your own code
Inner Join HDM_Subst_Hx Sub_Hx23
On Sub_Hx23.Pt_Hx_Id = Pt_Hx23.Pt_Hx_Id
Inner Join DWX_CODE_Rep_XREF SubstHx_CR_Xref23
On SubstHx_CR_Xref23.Cd_Id = Sub_Hx23.Subst_Catg_Id
and SubstHx_CR_Xref23.Cd_Typ_Cd = '1082'--Change to your own code
--and SubstHx_CR_Xref23.Cd_Typ_Nm = 'Substance Category Code'--Change to your own code
WHERE
Hxsub_CR_Xref23.Cd_Nm in ('Substance History') --Change to your own code
AND SubstHx_CR_Xref23.Cd_Desc in ('Beta Blocker') --Change to your own code
AND Hx_CR_XREF23.Cd_Nm in ('Current Medication') --Change to your own code
)
)
SELECT
Distinct
Denominator.Enc_End_DATE_ID,
Denominator.Enc_End_TOD_ID,
Denominator.Fac_Mbr_Id,
Denominator.Org_Mbr_Id,
Denominator.AgeBnd_Id,
Denominator.Shft_Id,
Denominator.Pt_ID,
Denominator.SvcprvPrct_Id,
Denominator.Enc_Id,
1 as KPI_DENM,
Case WHEN Numerator.Enc_ID is not NULL then 1 else 0 end as KPI_NUMN,
NULL as KPI_DENM_VAL,
NULL as KPI_NUMN_VAL,
1 as KPI_ID,
UID AS CREATED_BY_ID,
UID AS CHANGED_BY_ID,
SYSDATE AS INSERT_DT,
SYSDATE AS UPDATE_DT,
-1 AS TENANT_ID
From
--Denominator:
(
Select /*+ordered */
Distinct
DWD_Enc.Enc_End_DATE_ID,
DWD_Enc.Enc_End_TOD_ID,
DWD_Enc.Fac_Mbr_Id,
DWD_Enc.Org_Mbr_Id,
DWD_Enc.AgeBnd_Id,
DWD_Enc.Shft_Id,
DWD_Enc.Pt_ID,
DWD_Enc.SvcprvPrct_Id,
DWD_Enc.Enc_Id
FROM
enc DWD_Enc
Inner join DWD_ENC_CNRN Enc_Cnrn
On DWD_Enc.Enc_Id=Enc_Cnrn.Enc_Id
And DWD_Enc.Enc_Strt_Dt - interval '18' year >= DWD_Enc.Prty_Bir_Dt
Inner join DWD_ENC_OBSV Enc_Obsv
On DWD_Enc.Enc_Id=Enc_Obsv.Enc_Id
Where Enc_Cnrn.Cnrn_Subtyp_Nm in ('Diagnosis') --Change to your own code
And Enc_Cnrn.Cnrn_Cd_Nm in ('Heart Failure') --Change to your own code
AND Enc_Cnrn.Cnrn_Cd in ('425.2','425.3') --Change to your own code
And Enc_Obsv.Obsv_Cd_Nm in ('LVEF') --Change to your own code
And
(
(
Enc_Obsv.Obsv_Val_Typ_Nm in ('Numeric' )
And Enc_Obsv.Obsv_Val_Nmeric < 40
And Enc_Obsv.Obsv_Val_UOMCd_Nm in ('%')
)
Or
(
Enc_Obsv.Obsv_Val_Typ_Nm in ('Code')
And Enc_Obsv.Obsv_Val_Cd_Nm in ('Moderately or Severely Depressed LVF') --Change to your own code
)
Or
(
Enc_Obsv.Obsv_Val_Typ_Nm in ('Code')
And Enc_Obsv.Obsv_Val_Cd_Nm in ('Depressed LVF') --Change to your own code
And Enc_Obsv.Obsv_Sevrty_Cd_Nm in ('Moderate','Severe') --Change to your own code
)
)
-- Denominator Exclusion Criteria
AND DWD_Enc.Enc_Id in
( select enc_id from flt_enc
)
) Denominator -- Denom query alias
--Numerator Query:
LEFT OUTER JOIN Numerator
ON Denominator.Enc_Id= Numerator.Enc_Id
;
COMMIT;
. . .
IF l_error_dtl IS NULL THEN l_status := 'SUCCESS';
ELSE l_status := 'ERROR';
END IF;
RETURN l_status;
END LOADETL;
End HDW_ANL_CLIN_KPI1_MAP;
/
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|