| 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 |
In general, you manage an Oracle Healthcare Data Model data warehouse in much the same way that you manage any other data warehouse. For example, after the initial data load, you perform incremental data loading.
Data loading is discussed in Chapter 3, "Populating an Oracle Healthcare Data Model Warehouse". This chapter discusses aspects of working with a data warehouse that are specific to an Oracle Healthcare Data Model data warehouse.
This chapter includes the following topics:
Customizing the Reports Delivered with Oracle Healthcare Data Model
Working with the Oracle Healthcare Data Model Data Mining Model
Sample reports and dashboards are delivered with Oracle Healthcare Data Model. These sample reports illustrate the analytic capabilities provided with Oracle Healthcare Data Model -- including the OLAP and data mining capabilities.
The sample reports are based on Oracle Business Intelligence Suite Enterprise Edition which is a comprehensive suite of enterprise BI products that delivers a full range of analysis and reporting capabilities. Thus, the reports also illustrate the ease with which you can use Oracle Business Intelligence Suite Enterprise Edition Answers and Dashboard presentation tools to create useful reports.
You use Oracle Business Intelligence Suite Enterprise Edition tools to customize and troubleshoot the execution of reports, as described in:
Note:
The sample reports are based on Oracle Business Intelligence Suite Enterprise Edition 10g (10.1.3.4.1). and will work with Oracle Business Intelligence Suite Enterprise Edition 10g, or higher within the 10g release.Note:
The reports and dashboards that are used in examples and delivered with Oracle Healthcare Data Model are provided only for demonstration purposes. They are not supported by Oracle.You can use Oracle Business Intelligence Suite Enterprise Edition Answers and Dashboard presentation tools to customize the predefined sample dashboard reports:
Oracle BI Answers. Provides end user ad hoc capabilities in a pure Web architecture. Users interact with a logical view of the information -- completely hidden from data structure complexity while simultaneously preventing runaway queries. Users can easily create charts, pivot tables, reports, and visually appealing dashboards.
Oracle BI Interactive Dashboards. Provide any knowledge worker with intuitive, interactive access to information. The end user can be working with live reports, prompts, charts, tables, pivot tables, graphics, and tickers. The user has full capability for drilling, navigating, modifying, and interacting with these results.
Take the following actions to identify problems generating a report created using Oracle Business Intelligence Suite Enterprise Edition:
In the (Online) Oracle BI Administrator Tool, select Manage, then Security, then Users, and then ohdm.
Ensure that the value for Logging level is 7.
Open the Oracle Healthcare Data Model Repository, select Manage, and then Cache.
In the right-hand pane of the Cache Manager window, select all of the records, then right-click and select Purge.
Run the report or query that you want to track the SQL log.
Open the query log file (NQQuery.log) under OracleBI\server\Log.
The last query SQL is the log of the report you have just run. If an error was returned in your last accessed report, there will be an error at the end of this log.
For example:
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)SQL Issued: SELECT "Organization Dimension".Enterprise saw_0, "Facility Dimension"."Caresite L2" saw_1, "Time Encounter Dimension"."Month" saw_2, "Time Encounter Dimension".Quarter saw_3, "Kpi Dimension".KPI saw_4, "Encounter Kpi Cube"."Represent the correct calculation needed for each of the Kpis"*100 saw_5, AGGREGATE(saw_5 BY saw_1, saw_2, saw_3, saw_4), AGGREGATE(saw_5 BY saw_0, saw_2, saw_3, saw_4) FROM OHDM WHERE ("Time Encounter Dimension"."Year" = 'CY 2010') AND ("Kpi Dimension".KPI = 'Heart Failure: Beta-Blocker Therapy for Left Ventricular Systolic Dysfunction') ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5
Meaning: This error occurs when you did not create OLAP report view in DB
Action: Execute "ohdm_olap_report_view.sql" under $ORACLE_HOME/ohdm/pdm/relational/ddl
The ohdm_sys schema defines the relational tables and views in Oracle Healthcare Data Model. You can use any SQL reporting tool to query and report on these tables and views.
See:
Oracle Healthcare Data Model Reference for more information on Oracle Healthcare Data Model relational tables and views.Oracle Healthcare Data Model also supports On Line Analytic Processing (OLAP) reporting through the use of OLAP cubes defined in the ohdm_sys schema. You can query and write reports on OLAP cubes by using SQL tools to query the views that are defined for the cubes or by using OLAP tools to directly query the OLAP components.
See:
Oracle Healthcare Data Model Referencefor more information on Oracle Healthcare Data Model OLAP cubes and the relational views by which you can access them, and Oracle OLAP User's Guide for information on how to create queries and reports on OLAP cube data.Example 4-1 Creating a Relational Query
EXAMPLE:
Assume that you want to determine the number of inpatient encounters starting in March 2010 where the patient status is discharged and which had observations of 'Catheter in Place'.
The following SQL executes this query. (Note the comments which begin with "--".)
SELECT *
FROM
--InPatient Encounters
HDM_ENC Encounter
INNER JOIN HDM_CD_REPOSITORY EncSubTyp_Repos ON Encounter.ENC_SUBTYP_ID = EncSubTyp_Repos.CD_ID
INNER JOIN HDM_CD_REPOSITORY_CD_TYP EncSubTyp_ReposTyp ON EncSubTyp_Repos.CD_ID = EncSubTyp_ReposTyp.CD_ID
INNER JOIN HDM_CD_TYP EncSubTyp_Typ ON EncSubTyp_ReposTyp.TYP_ID = EncSubTyp_Typ.TYP_ID
-- AND Patients discharged status = discharged
INNER JOIN HDM_CD_REPOSITORY EncPtCod_Repos ON Encounter.PT_DC_STCD_ID = EncPtCod_Repos.CD_ID
INNER JOIN HDM_CD_REPOSITORY_CD_TYP EncPtCod_ReposTyp ON EncPtCod_Repos.CD_ID = EncPtCod_ReposTyp.CD_ID
INNER JOIN HDM_CD_TYP EncPtCod_Typ ON EncPtCod_ReposTyp.TYP_ID = EncPtCod_Typ.TYP_ID
--Corresponding Observations for the above Encounters
INNER JOIN HDM_ENC_OBSV EncObsv ON Encounter.ENC_ID = EncObsv.ENC_ID
INNER JOIN HDM_OBSV Obsv ON EncObsv.OBSV_ID = Obsv.OBSV_ID
--Above Observations with code = 'Catheter in Place'
INNER JOIN HDM_CD_REPOSITORY ObsvCd_Repos ON Obsv.OBSV_CD_ID = ObsvCd_Repos.CD_ID
INNER JOIN HDM_CD_REPOSITORY_CD_TYP ObsvCd_ReposTyp ON ObsvCd_Repos.CD_ID = ObsvCd_ReposTyp.CD_ID
INNER JOIN HDM_CD_TYP ObsvCd_Typ ON ObsvCd_ReposTyp.TYP_ID = ObsvCd_Typ.TYP_ID
WHERE
EncSubTyp_Typ.TYP_NM = 'Encounter Subtype' AND EncSubTyp_Repos.CD_NM = 'Inpatient'
AND EncPtCod_Typ.TYP_NM = 'Patient Discharge Status Code' AND EncPtCod_Repos.CD_NM In ('Discharged to Home' ,'Discharged/Transferred to Another Inpatient Care' ,'Discharged/Transferred to Federal Health Care Facility' ,'Discharged/Transferred to Hospice')
AND to_date(to_char(Encounter.STRT_DT,'MON-YY'),'MON-YY') = to_date ('MAR-10', 'MON-YY')
AND ObsvCd_Typ.TYP_NM = 'Observation Code' AND ObsvCd_Repos.CD_NM = 'Catheter in Place'
Example 4-2 Median Time From Hospital Arrival to PCI
Assume that you want to know the median time from hospital arrival to primary percutaneous coronary intervention (PCI) in acute myocardial infarction (AMI) patients with ST-segment elevation or left bundle branch block (LBBB) on the electrocardiogram (ECG) performed closest to hospital arrival time.
The functional query logic and report query to answer this question follows.
Functional Query Logic
1. Identify distinct Encounters using the following query logic:
Where the Encounter End Date and Time is within the reporting period and
Encounter Subtype = Inpatient
Where (Encounter.Start Date and Time in years – Patient (Individual Party)
birth date and time in years >= 18 years) and
Where Concern Subtype = 'DIAGNOSIS' and
Concern Code = 'ACUTE MYOCARDIAL INFARCTION'
AND all of the following:
* Observation Subtype ='DIAGNOSTIC TEST RESULT' and
* Observation Code= 'ECG' and
* Observation Value Note= 'ST-SEGMENT ELEVATION' OR
* Observation Value Note= 'LEFT BUNDLE BRANCH BLOCK'
AND Where Encounter Start Date and Time -Observation Date and Time <= 24 hours
and Where Intervention Subtype = 'PROCEDURE'
and Intervention Code= 'PERCUTANEOUS TRANSLUMINAL CORONARY ANGIOPLASTY'
EXCLUDE the Encounter if ANY of the following is present:
* Where Encounter. Actual Length of Stay >120 and Encounter.Actual Length of
Stay UoM= 'DAYS'
* Where Patient. Clinical Trials Status= 'Enrolled'
* Where Encounter.Source of Admission Code = 'TRANSFER'
* Encounter Intervention relationship exists with
Intervention Subtype = 'SUBSTANCE ADMINISTRATION' and
Substance Type Code = 'MEDICATION' and
Intervention Code = 'FIBRINOLYTIC AGENTS' and
Where Substance Administration Status Code= 'ADMINISTERED' or
'GIVEN' AND Intervention Start Date and Time is <= CASE EVENT DATE and TIME
Where Intervention Subtype= 'PROCEDURE' and
Intervention Code= 'PERCUTANEOUS TRANSLUMINAL CORONARY ANGIOPLASTY'
* Where the Encounter Intervention relationship exists and
Where Intervention Subtype = 'PROCEDURE' and
Where Intervention Code= 'PERCUTANEOUS TRANSLUMINAL CORONARY ANGIOPLASTY' and
Where Intervention Reason.Intervention Reason Subtype=
Intervention Delay Reason
2. THEN Where Encounter Intervention intersecting entity exists,
Select first CASE EVENT DATE and TIME among the identified Encounters
Where Intervention Subtype= 'PROCEDURE' and
Intervention Code= 'PERCUTANEOUS TRANSLUMINAL CORONARY ANGIOPLASTY' and
Case Event Type= 'REPERFUSION EVENT'
Report Query
Within the selected Encounters, report Median (Door to Primary PCI) as a numeric value with minutes UoM where Door to Primary PCI = Case Event Date and Time minus Encounter Start Date and Time.
You create new OLAP cubes in Oracle Healthcare Data Model in much the same way as you would in any other data warehouse. See Oracle OLAP User's Guide for more information.
Working with data mining model involves:
Oracle does not support modified or new data models. Consequently, do not change the data model that is defined and delivered with Oracle Healthcare Data Model. If changes are required to meet your organization requirements, create a copy of the delivered Oracle Healthcare Data Model where you can make your changes.
For information on the data mining model supplied with Oracle Healthcare Data Model, see Oracle Healthcare Data Model Reference.
Over time, the Oracle Healthcare Data Model database information and behaviors may change. Therefore, you may want to refresh the trained mining model based on the latest stored usage data. For more information about the Oracle Mining training and Scoring (applying) process, see Oracle Data Mining Concepts.
To refresh the mining model based on latest data, call the procedure pkg_ohdm_mining.refresh_mining_source. This procedure performs the following tasks:
Refreshes the mining source table dwd_advr_evt_fall.
Creates a predictive model: advr_fall_mod and also deciphers the fall factors for each patient.
If you find that the performance of your Oracle Healthcare Data Model data warehouse is not as good as you would like, you can tune your Oracle Healthcare Data Model data warehouse as you would any Oracle database as described in Oracle Database Performance Tuning Guide.
In particular, consider:
Oracle Healthcare Data Model provides following B-tree indexes out of the box:
A primary key index and unique index on composite natural key for each table.
All non-code foreign keys of potentially large volume tables are indexed.
Special consideration given to code foundation tables and appropriate indexes are created on those tables.
You can create additional B-tree indexes as appropriate to optimize query and ETL performance. You can leverage the SQL access advisor to further fine tune indexing.
Tip:
Bitmap indexes are not recommended for 3NF EDW. They are typically effective in star schema model to achieve star transformation where ad-hoc queries containing a number of disparate AND and OR conditions on low cardinality columns are anticipated.See:
For more information on:Indexes in the Oracle Healthcare Data Model, see Oracle Healthcare Data Model Reference.
B-tree indexing, see the b-tree indexing section in Oracle Database Performance Tuning Guide.
SQL Access Advisor, see the discussion on the Advisor in Oracle Database Performance Tuning Guide.
Oracle Healthcare Data Model leverages the parallel query and parallel DML feature of Oracle Database. Parallel operations speed up DML statement execution by dividing the work among multiple child processes. Each child process executes its portion of the work under its own parallel process transaction.
If you have a bigger machine, (for example 16 CPUs), then you can alter the degree of parallelization for the entire session as descried in "Enabling Parallel Execution for a Session" or for certain DML operations as described in "Enabling Parallel Execution of DML Operations". Regardless at which level you enable parallelism, the setting of parallelism for a table influences the optimizer. Consequently, when using parallel query, also enable parallelism at the table level as described in "Enabling Parallel Execution at the Table Level".
Parallel query is the most commonly used of Oracle's parallel execution features. Parallel execution can significantly reduce the elapsed time for large queries. To enable parallelization for an entire session, execute the following statement.
alter session enable parallel query;
Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE can be parallelized by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments. To enable parallelization of DML statements, execute the following statement.
alter session enable parallel dml;
When you issue a DML statement such as an INSERT, UPDATE, or DELETE, Oracle applies a set of rules to determine whether that statement can be parallelized. The rules vary depending on whether or not the statement is a DML INSERT statement, or a DML UPDATE or DELETE statement.
Rules for Parallelizing DML UPDATE and DELETE statements
The following rules apply when determining how to parallelize DML UPDATE and DELETE statements:
Oracle can parallelize UPDATE and DELETE statements on partitioned tables, but only when multiple partitions are involved.
You cannot parallelize UPDATE or DELETE operations on a nonpartitioned table or when such operations affect only a single partition.
Rules for Parallelizing DML INSERT statements
The following rules apply when determining how to parallelize DML INSERT statements:
Standard INSERT statements using a VALUES clause cannot be parallelized.
Oracle can parallelize only INSERT . . . SELECT . . . FROM statements.
The setting of parallelism for a table influences the optimizer. Consequently, when using parallel query, also enable parallelism at the table level by issuing the following statement.
alter table <table_name> parallel 32;
The partitioning strategy for Oracle Healthcare Data Model is primarily driven by following aspects:
Improved query performance through partition pruning technique
Performance improvement by enabling parallel execution for DML and DDL
Allow database to scale for large datasets
Facilitate information lifecycle management including data retention/purging
Oracle Healthcare Data Model comes with some partitioned tables out of the box based on following criterion:
Identify potential large volume tables.
Identify partitioning key for each table, (typically the key date field in each table) based on anticipation of the queries going against that table.
Create range partitions that are based on date field identified in previous step – one partition for each period (month/year and so on.)
Create hash partitions for tables where appropriate partition key is a unique ID (surrogate key) rather than the date field.
Leverage interval partitioning feature to extend range partitioning to simplify manageability by automatically creating the new partitions as needed by data.
Create local partitioned indexes inheriting the partitioning strategy from respective partitioned table. All local partitioned indexes are local prefixed indexes. (In other words, the index column is same as the column on which index is partitioned.).
You can custom partition additional tables as appropriate depending upon the data volumes applicable for specific implementation. You can also change the tablespaces and partitions used by Oracle Healthcare Data Model tables.
See:
For more information on:On the partitions defined by Oracle Healthcare Data Model, see Oracle Healthcare Data Model Reference.
Changing the tablespaces and partitions used by Oracle Healthcare Data Model tables, see "Changing the Tablespaces and Partitions Used by Tables".
You can change the tablespace and partitions used by tables. What you do depends on whether or not the Oracle Healthcare Data Model table has partitions:
For tables that do not have partitions (that is, code tables and reference tables), you can change the existing tablespace for a table as described in "Diverting Partitions into New Tablespaces".
For tables that have partitions (that is, base and derived tables), you can specify that new interval partitions be generated into new tablespaces as described in "Changing an Existing Tablespace".
By default, Oracle Healthcare Data Model defines the partitioned tables as interval partitioning, which means the partitions are created only when new data arrives.
Consequently, for Oracle Healthcare Data Model tables that have partitions (that is, base and derived tables), if you want the new interval partitions to be generated in new tablespaces rather than current ones, please issue the following statements.
ALTER TABLE table_name MODIFY DEFAULT ATTRIBUTES TABLESPACE new_tablespace_name;
When new data is inserted in the table specified by table_name, a new partition is automatically created in the tablespace specified by tablespace new_tablespace_name.
For Oracle Healthcare Data Model tables that do not have partitions (that is, code tables and reference tables), if you want to change the existing tablespace for a table then issue the following statement.
ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;
By using a compression algorithm specifically designed for relational data, Oracle is able to compress data much more effectively than standard compression techniques. More significantly, unlike other compression techniques, Oracle incurs virtually no performance penalty for SQL queries accessing compressed tables.
Oracle Healthcare Data Model leverages the compress feature for all base and derived tables which reduces the amount of data being stored, reduces memory usage (more data per memory block), and increases query performance.
You can specify table compression by using the COMPRESS clause of the CREATE TABLE statement or you can enable compression for an existing table by using ALTER TABLE statement as shown below.
alter table <tablename> move compress;
Installing the Oracle Healthcare Data Model component creates the OHDM_SYS account. Installing the Oracle Healthcare Data Model sample reports create the OHDM_SAMPLE_SYS account. Please make sure you unlock those two accounts with new password.
See:
Oracle Healthcare Data Model Installation Guide for information on installing Oracle Healthcare Data Model and for unlocking the OHDM_SYS account.The OHDM_SYS account is for ohdm_sys which is the schema for Oracle Healthcare Data Model. This schema contains all components for the Oracle Healthcare Data Model.
The installation process grants the necessary privileges required for users of the accounts. Once you have installed the product, you only need to consider user privileges in the following situations:
The Intra-ETL programs are executed inside the ohdm_sys schema, therefore, they require the full access to the that schema.
By default, the Oracle Healthcare Data Model sample reports connect to the ohdm_sys schema directly. For security reason, you may want to grant only select privileges to those reporting users. To do this, take the steps outlined inOracle Healthcare Data Model Installation Guide.
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|