A data warehouse is a central data store of integrated data from one or more disparate sources, used for reporting and data analysis. While operational systems are optimized for the preservation of data integrity and the speed of recording transactions, data warehouses are optimized for the high performance execution of queries.
The typical Extract-Transfer-Load (ETL) based data warehouse uses a staging layer to clean the extracted data and transform it into a homogeneous structure and standardized terminology. During this process, the techniques from Preparing Data for Analytics, such as mapping codes to SNOMED CT, can be used to prepare the data for analytics. The transformed data is then loaded into the data warehouse, and indexed, so that optimized analysis of the data can begin.
The benefits of using a data warehouse include:
Data from multiple heterogeneous sources can be integrated to enable consistent querying over data from all sources
The operational clinical system does not suffer performance degradation when running large analytics queries over historical data
The data quality can be improved by cleaning the data, and mapping non-SNOMED CT codes to SNOMED CT
The data can be restructured to optimize query performance
The following figure illustrates an architecture in which the patient record data is extracted from its operational data store and loaded into a data warehouse for reporting and other analytics purposes.
Commercial data warehousing solutions that support SNOMED CT include Cambio's COSMIC Intelligence, Cerner's PowerInsight Data Warehouse (PIDW) and Cerner's Health Facts Data Warehouse.
Electronic patient record systems typically require high performance, high reliability and no (or limited) downtime. Any operation that effects these key criteria need to be kept to an absolute minimum, so as not to disturb the clinical and documentation activities of busy clinicians.
Many analytics activities require large volumes of data to be processed, which may slow down or even 'lock out' clinical transactions that are being performed at the same time. For this reason, population-based analytics and clinical research is typically not performed directly on patient records in their native clinical system. Instead, analytics directly over 'live' patient records tends to be restricted to point of care analytics activities, such as historical summaries, clinical decision support and point of care reporting. These analytics activities tend to demand the most up to date data possible to ensure its accuracy. They also tend to only require data for a single patient, which can be efficiently accessed using a patient identifier index.
The following figure illustrates a simple architecture in which the data store for patient records is directly used for reporting and analytics purposes.
The increasing volume and variety of data collected by healthcare enterprises is a challenge to traditional relational database management systems. This increase in data is due both to an increase in computerization of health records, and to an increase in the capture of data from other sources, such as medical instruments (e.g. biometric data from home monitoring equipment), imaging data, gene sequencing, administrative information, environmental data and medical knowledge. The proliferation of large volumes of both structured and unstructured data sets has led to the popularity of the term 'Big data' within the healthcare context. Big data refers to any collection of data sets that is so large and complex that it becomes difficult to process them using traditional data processing applications.
Accommodating and analyzing this expanding volume of diverse data (i.e. 'Big Data') requires distributed database technologies. A distributed database is a federation of loosely coupled data stores with separate processing units, which are controlled by a common distributed database management system. It may be stored in multiple computers located in the same physical location, or dispersed over a network of interconnected computers. Distributed databases may be categorized as either:
Homogeneous – A distributed database with identical software and hardware running on all database instances.
Heterogeneous – A distributed database supported by different hardware, operating system, database management systems and even data models.
In both cases, however, the database appears through a single interface as if it were a single database.
Distributed databases are used for Big Data analytics for a number of reasons, including:
Transparency of querying over heterogeneous data stores
Increase in the reliability, availability and protection of data due to data replication
Local autonomy of data (e.g. each department or institution controls their own data)
Distributed query processing can improve performance, as the load can be balanced among the servers
A number of tools are available for the distributed storage and processing of big data, including Apache Hadoop. Apache Hadoop is an open-source software framework, which splits files into large blocks and distributes these blocks amongst the nodes in the cluster. To process the data, Hadoop sends code to the nodes that have the required data, and the nodes then process the data in parallel. Hadoop supports horizontal scaling – that is, as data grows additional servers can be added to distribute the load across them.
Many distributed database solutions use NoSQL (Not Only SQL) systems. NoSQL systems are increasingly being used for big data, as they provide a mechanism for storage and retrieval of data in a variety of structures, including relational, key-value, graph or documents. The Oxford University, in collaboration with Kaiser Permanente are using a NoSQL database (RDFox) to investigate how to perform complex queries efficiently across extremely large numbers of patient records. RDFox is a highly scalable and performant NoSQL database that is readily distributed across parallel processing units.
While the use of SNOMED CT for analytics does not dictate a particular data architecture, there are a few key options to consider. In this section, we describe the major categories of data architecture that may be used to perform analytics over SNOMED CT enabled patient data, including:
Analytics directly over patient records;
Analytics over data exported to a data warehouse;
Analytics over a Virtual Health Record (VHR);
Analytics using distributed storage and processing.
Please note that some of these approaches may be used in combination. For example, data warehouses with large volumes of data may use distributed storage and processing for enhanced performance, and querying directly over disparate patient records could be performed using a Virtual Health Record.
A Virtual Health Record (VHR) provides a virtual view of heterogeneous data sources, using a common data model. In contrast to the data warehousing approach in which heterogeneous data is extracted, transformed and stored in a homogeneous form, the VHR approach does not require clinical data to be extracted from existing data stores. Instead, logical queries are defined in terms of a common data model and then transformed into a set of physical queries which can each be executed locally on an individual data store. The following figure illustrates an architecture which supports querying over a VHR.
The process of transforming the logical query into separate physical queries may involve translating:
The Query Language – from a common query language to the local data store's native query language
Data Model References – from the common data model to the local data model
Terminology References – from the standard terminology to the local code system
For example, if the user poses the following SQL query, written in terms of the VHR's common data model, to select those patients with a diagnosis that is a subtype of 40733004 |infectious disease|:
SELECT patient_id FROM Health_Records
WHERE diagnosis IN (<40733004 |infectious disease|)
This query may be translated into the following 3 queries for local execution on each data store:
Data Store A:
Patient_record/patient_id[@diagnosis=typeOf(INF)]
Data Store B:
SELECT id FROM EHR NATURAL JOIN DSummary
WHERE discharge_diagnosis IN (descendantsOf (40733004)
Data Store C:
SELECT patient FROM record
WHERE diag IN (<40733004)
Similarly, when the query results are returned by each data store, these need to be transformed and mapped into the common data model and then combined for presentation to the user.
The VHR approach provides an alternative architecture to a data warehouse for integrating heterogeneous systems. It is most commonly used when copying clinical data into a data warehouse is not possible (e.g. due to legislative requirements), or when the currency of the data is imperative. The challenges with this approach lie with the potential complexity of the transformations required. The implementation of this approach is considered to be a type of heterogeneous distributed database.
