New Features Guide
Release 7.2.1
F11020-01
October 2018
This guide explains the new features added to Oracle Healthcare Foundation (OHF) release 7.2.1.
The following interface tables have been added to the data model:
The following interface tables have been modified:
HDI_ARC
HDI_CHRTFLD_COMB
HDI_FSCL_PD
HDI_JRNL
HDI_JRNL_LN
HDI_LED_BAL
HDI_RX
HDI_VCHR
HDI_VCHR_PAYMT
End-to-end data lineage report (from HDI to HDM to HCD to OBIEE Reports) in Self-Service Analytics to track the data transformation/data flow from Interface Tables to BI report.
Usage tracking for Self-Service Analytics supports tracking of who, what, when and how reports/dashboards are accessed.
Source systems may use different patient identifiers to refer to the same patient. The Golden Patient Record or Master Person Index (MPI) maps all identifiers that belong to the same patient to a common unique identifier. This Golden/MPI identifier is then used to track each patient's records across all the different source systems, enabling CDM to support a single patient record that represents the most accurate and current patient information and to view all the clinical data associated with that patient.
This section explains how to:
For additional information, refer to the Administrator's Guide.
The GOLDEN_RECORD_PT_RLSHPTYP_CODE parameter has been added to the Cohort Data Mart C_LOAD_PARAM table to enable or disable the extraction of Golden Patient records into the patient dimension (W_EHA_RESEARCH_PATIENT_D). By default, the feature is disabled (the parameter is set to NULL).
Table 3 C_LOAD_PARAM Settings for Enabling and Disabling Golden Patient Record
| Column Name | Column Value |
|---|---|
|
ENTITY_NM |
W_EHA_RESEARCH_PATIENT_D |
|
PARAM_DESC |
This parameter enables or disables the Golden Patient Record functionality in the application. To enable this functionality, provide a valid code value corresponding to HDM's Related Patient.Patient Relationship Type attribute (HDM_RELTD_PT.PT_RLSHPTYP_ID). To disable this functionality keep this column Null. By default the value of this parameter is Null, indicating that the functionality is disabled. |
|
PARAM_NM |
GOLDEN_RECORD_PT_RLSHPTYP_CODE |
|
PARAM_VAL |
NULL |
Note:
The Golden Patient Record is a one-time initial configuration and must remain consistent during incremental loads. If you decide to change the Golden Patient Record configuration between data loads, reload the CDM by truncating the entire data mart.The HDI_RELTD_PT table has been added to the HDI schema to support data acquisition of non-golden (source) to golden (target) patient ID mappings.
One-to-many mappings - If a non-golden (source) record identifier is mapped to multiple golden (target) record identifiers, the mappings will be considered invalid and logged in the C_ETL_RELATED_PT_EXCPTN exception table during CDM load.
Target patient attribute contains non-golden patient identifier - If a non-golden patient identifier is mapped to another non-golden patient identifier, the mapping is considered invalid and logged in the C_ETL_RELATED_PT_EXCPTN exception table during CDM load.
Golden patient identifier mapped to another golden patient identifier - If a golden patient identifier is mapped to another golden patient identifier, the mapping is considered invalid and logged in the C_ETL_RELATED_PT_EXCPTN exception table during CDM load.
Switching a golden record to a non-golden record - Switching a golden patient record to a non-golden patient record is invalid.
The new HDM_RELTD_PT table has been added to the HDM schema to support non-golden to golden patient ID mappings.
Use the new wf_SIL_DI_HDI_RELTD_PT ETL to load data from HDI_RELTD_PT to HDM_RELTD_PT.
The C_ETL_RELATED_PT intermediate table has been added to the HDM schema to support processing of the mapping records in HDM_RELTD_PT, before loading the CDM dimension and bridge tables.
The C_ETL_RELATED_PT_EXCPTN exception table has been added to the HDM schema to log invalid mapping records.
Use the new wf_SIL_CDM_C_ETL_RELATED_PT ETL to load the data from HDM_RELTD_PT to C_ETL_RELATED_PT, which will then be referred in CDM datamart ETLs to resolve the non-golden to golden patient records. The C_ETL_RELATED_PT table must be loaded before the W_EHA_RESEARCH_PATIENT_D dimension, as it is a driving entity.
The following CDM ETLs have been updated:
| Table Name | ETLs |
|---|---|
|
W_EHA_RESEARCH_PATIENT_D |
wf_SIL_CDM_W_EHA_RESEARCH_PATIENT_D_FULL wf_SIL_CDM_W_EHA_RESEARCH_PATIENT_D |
|
W_EHA_SUBADMN_PATIENT_H |
wf_SIL_CDM_W_EHA_SUBADMN_PATIENT_H_FULL wf_SIL_CDM_W_EHA_SUBADMN_PATIENT_H |
|
W_EHA_PT_GRP_PATIENT_H |
wf_SIL_CDM_W_EHA_PT_GRP_PATIENT_H_FULL wf_SIL_CDM_W_EHA_PT_GRP_PATIENT_H |
|
W_EHA_OBSV_PATIENT_H |
wf_SIL_CDM_W_EHA_ORDER_PATIENT_H_FULL wf_SIL_CDM_W_EHA_ORDER_PATIENT_H |
|
W_EHA_PT_FAM_RLSHP_DHL |
wf_SIL_CDM_W_EHA_FAMBR_RLSHP_TYPE_D_FULL wf_SIL_CDM_W_EHA_FAMBR_RLSHP_TYPE_D |
|
W_EHA_SPECIMEN_PATIENT_H |
wf_SIL_CDM_W_EHA_SPECIMEN_PATIENT_H_FULL wf_SIL_CDM_W_EHA_SPECIMEN_PATIENT_H |
|
W_EHA_PT_HISTORY_PT_H |
wf_SIL_CDM_W_EHA_PT_HISTORY_PT_H_FULL wf_SIL_CDM_W_EHA_PT_HISTORY_PT_H |
|
W_EHA_SUBJECT_D |
wf_SIL_CDM_W_EHA_SUBJECT_D_FULL wf_SIL_CDM_W_EHA_SUBJECT_D |
|
W_EHA_ORDER_PATIENT_H |
wf_SIL_CDM_W_EHA_ORDER_PATIENT_H_FULL wf_SIL_CDM_W_EHA_ORDER_PATIENT_H |
|
W_EHA_APPT_PATIENT_H |
wf_SIL_CDM_W_EHA_APPT_PATIENT_H_FULL wf_SIL_CDM_W_EHA_APPT_PATIENT_H |
|
W_EHA_CONSENT_PATIENT_H |
wf_SIL_CDM_W_EHA_CONSENT_PATIENT_H_FULL wf_SIL_CDM_W_EHA_CONSENT_PATIENT_H |
|
W_EHA_ENC_PATIENT_H |
wf_SIL_CDM_W_EHA_ENC_PATIENT_H_FULL wf_SIL_CDM_W_EHA_ENC_PATIENT_H |
|
W_EHA_DX_PATIENT_H |
wf_SIL_CDM_W_EHA_DX_PATIENT_H_FULL wf_SIL_CDM_W_EHA_DX_PATIENT_H |
Note:
Demographic attributes and entities are populated based on the values associated in HDM with the golden patient record for the following CDM entities:W_EHA_RESEARCH_PATIENT_D
W_EHA_ETHN_PATIENT_H
W_EHA_RACE_PATIENT_H
Soft Deletion of Golden Patient Record
If a golden patient record in HDM_RELTD_PT is soft deleted, then the corresponding patient record in W_EHA_RESEARCH_PATIENT_D is soft deleted and all clinical records associated with the corresponding patient will be soft deleted in CDM.
Soft Deletion of Mapping Record
If a non-golden patient record mapping in HDM_RELTD_PT is soft deleted and not linked to any other golden record, then CDM clinical records which were earlier linked to the golden patient record will be soft deleted.
If a non-golden patient record mapping is updated to a different golden patient record in HDM_RELTD_PT, the mapping update is cascaded to all CDM clinical records to associate clinical records to the new golden patient.
Non-Golden Patient Identifier not Mapped to Golden Patient Identifier
If a non-golden patient identifier is not mapped to any golden patient identifier, then clinical records associated with the non-golden patient identifier in HDM will not be loaded to CDM.
ODB Linkage of Soft-Deleted and Re-Mapped Records
When a patient specimen record is soft-deleted in the <CDM_schema>.W_EHA_SPECIMEN_PATIENT_H table, all linked ODB specimens are also soft-deleted in the <ODB_schema>.W_EHA_SPEC_PATIENT table. When a new specimen is created in CDM by the ETL process, the system attempts to find a corresponding ODB specimen and re-map it using the specimen number and vendor number values. The patient aggregates are updated automatically to reflect new and removed specimen data in ODB.
Permanently Remove Soft-Deleted Records from CDM
If golden record re-mappings occur often and the number of soft-deleted records starts to impact performance or storage capacity, you may choose to enable a purge job as follows:
Configure the cdm_keep_deleted_prcnt parameter in the <Enterprise_schema>.W_EHA_CONFIG_PARAMETER table. This parameter determines the percentage of soft-deleted rows in a bridge table the will trigger the purge job to clean up the table. For example, a value of 5 means that when more than 5 percent of the rows have delete_flg = 'Y' in any given bridge table, all those records will be permanently deleted by the purge job.
Configure the cdm_cleanup_DOP parameter in the <Enterprise_schema>.W_EHA_CONFIG_PARAMETER table. This parameter determines the degree of parallelism used by the purge job. You may choose a value that is lower or equal to the max_parallel_degree parameter.
Schedule or run the job on-demand as follows:
BEGIN
CLEANUP_UTIL.cleanup_job;
END;
The following relationships have been added to the CDM data model to support cancer registry data analysis:
A patient diagnosis record (W_EHA_DX_PATIENT_H) can be associated with multiple patient observation records (W_EHA_OBSV_PATIENT_H) via the new W_EHA_OBSV_PATIENT_H.DX_PATIENT_WID column.
To select the patient diagnosis record linked to a patient observation in HDM, the CDM ETL for W_EHA_OBSV_PATIENT_H uses a parameterized filter on the Observation Concern Relationship Type attribute in the HDM Observation Concern intersection table (HDM_OBSV_CNRN.OBSV_CNRN_RLSHPTYP_ID). If this parameter is set to null (default), the ETL selects all the associations between the patient diagnosis and the patient observation in HDM.
After applying the above filter, if the CDM ETL finds a patient observation that is linked to multiple patient diagnoses in HDM, it replicates the corresponding number of records in W_EHA_OBSV_PATIENT_H with different DX_PATIENT_WID values.
A patient diagnosis record (W_EHA_DX_PATIENT_H) can be associated with multiple patient procedure records (W_EHA_PROC_PATIENT_H) via the new W_EHA_PROC_PATIENT_H.DX_PATIENT_WID column.
To select the patient diagnosis record linked to a patient procedure in HDM, the CDM ETL for W_EHA_PROC_PATIENT_H uses a parameterized filter on the Intervention Concern Relationship Type attribute in the HDM Intervention Concern intersection table (HDM_INTVN_CNRN.INTVN_CNRN_RLSHPTYP_ID). If this parameter is set to null (default), the ETL selects all the associations between the patient diagnosis and the patient procedure in HDM.
After applying the above filter, if the CDM ETL finds a patient procedure that is linked to multiple patient diagnoses in HDM, it replicates the corresponding number of records in W_EHA_PROC_PATIENT_H with different DX_PATIENT_WID values.
A patient diagnosis record (W_EHA_DX_PATIENT_H) can be associated with multiple patient substance administration records (W_EHA_SUBADMN_PATIENT_H) via the new W_EHA_SUBADMN_PATIENT_H.DX_PATIENT_WID column.
To select the patient diagnosis record linked to a patient substance administration in HDM, the CDM ETL for W_EHA_SUBADMN_PATIENT_H uses a parameterized filter on the Intervention Concern Relationship Type attribute in the HDM Intervention Concern intersection table (HDM_INTVN_CNRN.INTVN_CNRN_RLSHPTYP_ID). If this parameter is set to null (default), the ETL selects all the associations between the patient diagnosis and the patient substance administration in HDM.
After applying the above filter, if the CDM ETL finds a patient substance administration that is linked to multiple patient diagnoses in HDM, it replicates the corresponding number of records in W_EHA_ SUBADMN _PATIENT_H with different DX_PATIENT_WID values.
A patient diagnosis record (W_EHA_DX_PATIENT_H) can be associated with multiple patient specimen records (W_EHA_SPECIMEN_PATIENT_H) via the new Patient Specimen Diagnosis association W_EHA_SPECIMEN_DX_PATIENT_H. This table supports associations between one patient specimen record and multiple patient diagnosis records, and vice versa.
To populate W_EHA_SPECIMEN_DX_PATIENT_H, its CDM ETL sources records from the HDM Specimen Concern intersection table (HDM_SPCMN_CNRN). The ETL provides an optional parameterized filter on the HDM attribute Specimen Concern Relationship Type (HDM_SPCMN_CNRN.SPCMN_CNRN_RLSHPTYP_ID). If the parameter for this filter is set to Null (default), the ETL selects all the associations between the patient specimen and the patient diagnosis in HDM.
A patient specimen record (W_EHA_SPECIMEN_PATIENT_H) can be associated with multiple patient observation records (W_EHA_OBSV_PATIENT_H) via the new W_EHA_OBSV_PATIENT_H.SPECIMEN_PATIENT_WID column.
To select the patient specimen record linked to a patient observation in HDM, the CDM ETL for W_EHA_OBSV_PATIENT_H uses a parameterized filter on the Observation Specimen Relationship Type attribute in the HDM Observation Specimen intersection table (HDM_OBSV_SPCMN.OBSV_SPCMN _RLSHPTYP_ID). If this parameter is set to null (default), the ETL selects all the associations between the patient specimen and the patient observation in HDM.
After applying the above filter, if the CDM ETL finds a patient observation that is linked to multiple patient specimens in HDM, it replicates the corresponding number of records in W_EHA_OBSV_PATIENT_H with different SPECIMEN_PATIENT_WID values.
The VCF loader has been enhanced to load the sequence variant data in VCF 4.2 format. The loader is backward compatible and still works with the VCF 4.1 format.
The omics data access can now be controlled through applications like OHTR as the data access control policies have been upgraded to restrict or provide access to genomics data residing in the ODB schema. You can create policies where you can define if you grant access or restrict access to genomic data at patient or subject level. You can also define the access control expiration date for this genomic data.
The RNA-seq data model and loader have been upgraded to support exon, gene and transcript based normalization. Both the model and loader also support loading RPKM/FPKM/FPKM_UQ/TPM quantification types.
Omics specimens loaded in OHF are linked by default to the clinical specimens in CDM based on their Specimen Number (loaded in Omics data files) and Specimen Vendor Number (provided as a loader input parameter).
The linking process has been enhanced to also support specimen identification based on the combination between the Specimen Alias and Issuing Service Provided, both of which are loaded into the HDM interface tables:
The loader first attempts to use Specimen Number/Specimen Vendor Number to link Omics specimens to CDM specimens. If successful, the process stops here.
If a matching Specimen Number/Specimen Vendor number combination is not found in CDM, the loader switches to a lookup by Specimen Alias. In this second pass, the loader tries to match the Specimen Identifier from a result file with the Specimen Alias and the input value of the Specimen Vendor Number with the Issuing Service Provider name.
If a matching record is found in the CDM alias tables, the loader verifies if the corresponding CDM specimen has been previously linked with an Omics specimen.
If an Omics specimen has been linked previously, the loader reuses its record.
If an Omics specimen hasn't been linked previously, the loader creates a new Omics specimen record.
The association of specimen aliases to the loaded genomic files is tracked in the Omics database.
You no longer have to manually update CDM references for OMICS specimens when the CDM schema is reloaded with a full refresh ETL. The CDM references are updated automatically after each ETL to ensure the consistency of ODB - CDM specimen links.
You must still update OMICS study references if the CDM is reloaded with a full load ETL. To do this, in the ODB schema, execute the following SQL statement:
update w_eha_rslt_study rs set external_study_wid = (
select row_wid from cdm.w_eha_study_d sd
where rs.result_study_name = sd. study_name
);
The protein effect for frameshift mutations is now calculated using the short format HGVS recommendations. For example, p.N123fs indicates: the prefix reference sequence (protein), the first amino acid changed (nitrogen), its position (123), and the type of mutation (frameshift).
You can now query mutations based on their proximity to the intron/exon boundary.
The IS_SPLICING attribute has been added to the W_EHA_VARIANT_EFFECT table. The value of this attribute is set to "Y" for variants in close proximity to intron/exon border.
Two system parameters define the length of the splice regions on the intron and exon sides of the border:
splice_region_length_intron - length of the intronic portion of the splice region (default 2).
splice_region_length_exon - length of the exonic portion of the splice region (default 2).
You can control the initialization parameters using the PL/SQL API:
SQL> select config_util.get_config_parameter (i_name=>'splice_region_length_exon') as splice_region_length_exon from dual;
SPLICE_REGION_LENGTH_EXON
--------------------------------------------------------------------------------
2
SQL> select config_util.get_config_parameter (i_name=>'splice_region_length_intron') as splice_region_length_intron from dual;
SPLICE_REGION_LENGTH_INTRON
--------------------------------------------------------------------------------
2
SQL> exec config_util.set_config_parameter (i_name=>'splice_region_length_exon',i_value=>3);
PL/SQL procedure successfully completed.
SQL> exec config_util.set_config_parameter (i_name=>'splice_region_length_intron',i_value=>8);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Reload the Contents of the W_EHA_VARIANT_EFFECT Table
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'VARIANT_EFFECT_FULL_REFRESH',
job_type => 'PLSQL_BLOCK',
job_action => q'[BEGIN odb_var_effect_util.process_var_effect(i_full_refresh=>'Y'); END;]',
enabled => true,
comments => 'Fully refresh variant_effect table contents');
END;
/
The product documentation is available at the following locations:
My Oracle Support: https://support.oracle.com — What's New, What's Fixed, and Known Issues.
Oracle Help Center: https://docs.oracle.com/en/industries/health-sciences/oracle-healthcare-foundation/index.html — The most current documentation set.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
Oracle Healthcare Foundation New Features Guide, Release 7.2.1
F11020-01
Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.