MIMIC in the OMOP Common Data Model

1.1.1 Structural Mapping

The structural mapping aims at moving the MIMIC data into the right place in OMOP with some data transformations. It parts into three phases: conception, implementation and evaluation.

The conception phase consists of looping over each MIMIC table and choosing an equivalent location in OMOP for each column. In general both projects were appropriately documented but in several cases, we needed some clarification from MIMIC contributors on the dedicated MIMIC git repository⁴, or from the OMOP community forum⁵. Some trickier choices have been discussed in the MIMIC-OMOP git repository⁶ and can be tracked in the commit logs.

The implementation is done by an Extract-Transform-Load (ETL) process which is composed of Postgres scripts, extracting information from the source or concept mapping tables, then transforming and loading an OMOP target table. The scripts are managed sequentially through a main program. In last resort some modification of the structural model of OMOP have been made. A dedicated script recaps all of them. It contains columns name modifications, new columns, columns type modifications or database indexing modification. In particular, each source table has been added a global unique sequence incremented from 0 that serves as the primary key and links in the OMOP target tables. As a result every record is uniquely identified allowing to chain the information with OMOP while simplifying the primary/foreign key maintenance.

Although evaluating a structural model is difficult [22], several articles have attempted to assess the quality of the CDM [7, 20]. The criteria developed by Khan et al [23], which refer to the Moody and Shanks metrics [22], have been adapted to assess the quality of the data transformation (table 1).

Beside Moody and Shanks, we provide a set of controls to guaranty a correct transformation. In order to compare overall statistics, some SQL queries have been setup to compare MIMIC and MIMIC-OMOP and we provide basic populations characterizations. All tables have been covered and tested through simple counts, aggregate counts or distribution checks. We estimated the loss of information during the ETL process by measuring the percentage of both columns and rows lost in the process as other previous studies have done [15]. This is important to note we have chosen not to keep irrelevant informations: for example some rows are known to be invalid in MIMIC or some informations are redundant. Each ETL script has been tested using pgTAP, a unit test framework for Postgres. Each unit test script checked whether a particular OMOP target table was loaded correctly. Integrity constraints (primary keys, foreign keys, non-nullable columns) have been included to apply integrity checks at ETL runtime. The last axe of the structural evaluation is Achilles Sofware. It is an open-source analysis software produced by OHDSI⁷. Like many previous authors, we used the Achilles to assess data quality [24]. This tool is used for data characterization, data quality assessment (Achilles’ heel) and health observation data visualization. It has been common practice to perform Achilles tests and use it as a quality assessment in related studies. All the resulting tables are presented in the results section.

1.1.2 Conceptual Mapping

The conceptual mapping aims at aligning the MIMIC local terminologies to the OMOP’s standard ones. It consists of three phases: integration, alignment and evaluation.

The integration phase is about loading both kind of terminologies into the OMOP vocabulary tables. The OMOP terminologies are provided by the Athena tool and were loaded with the associated programs. We have used an export with all terminologies without licensing limitations. The local terminologies have been extracted from the multiple MIMIC tables and loaded in the OMOP concept table. When possible, relevant informations from the original MIMIC tables have been concatenated in the concept_name column. MIMIC local concepts were loaded with a concept_id identifier starting from 2 billion (lower numbers are reserved for OMOP terminologies⁸). In the OMOP concept table, MIMIC concepts can be distinguished with the vocabulary_id identifier equal to “MIMIC code” and a domain_id identifier targeting the OMOP table in which the corresponding data is stored. This domain information is used in the ETL to send the information in the proper table. We want to call this method “concept-driven dispatching”. OMOP documentation explains that conceptual mapping has to be done before the structural mapping because the nature of the OMOP standard concepts guides in which table (domain) the information should be stored. The concept-driven dispatching methodology enable changing the concept mapping after the transformation without modifying the underlying ETL code because the latter is dynamically based on the concept table content.

The alignment phase to standardizing local MIMIC codes into OMOP standard codes, had four distinct cases. In the first case, some MIMIC data is by chance already coded according to OMOP standard terminologies (e.g. LOINC laboratory results) and, therefore, the standard and local concepts are the same. In the second case, MIMIC data is not coded in the standard OMOP terminologies, but the mapping is already provided by OMOP (ex: ICD9/SNOMEDCT), so the domain tables have been loaded accordingly. In the third case, terminology mapping is not provided, but it is small enough to be done manually in a few hours (such as demographic status, signs and symptoms). In the fourth case, terminology mapping is not provided and consists of a large set of local terms (admission diagnosis, drugs). Then, only a subset of the most represented codes was manually mapped. When a manual terminology mapping concept is required, a mapping csv file has been built. This solution can be adapted to medical users who do not have training in database engineering. The spreadsheet has several columns such as local/standard labels, ids and also comments, evaluation metrics and a script loads them into the Postgres when completed. We have chosen to use simple SQL queries that are flexible enough to be queried on demand or to generate a pre-filled csv with the best matches. It uses Postgres full-text ranking features and links local and standard candidates with a rating function based on their labels. This work was performed under the control of an intensivist.

The evaluation phase was both quantitative and qualitative. The quantitative evaluation measures the completeness of our work: the percent of concepts that are mapped to a standard. The qualitative evaluation evaluates the correctness. For newly generated mappings it has consisted of manually tagging each mapping with a score between 0 and 1 and eventually write a commentary on each mapped concept. In case the mapping of was provided by OMOP - automatic OMOP terminologies mapping -, the evaluation was performed on a subset of concepts manually picked within each terminology.

2.1.1 Structural Mapping

The result of the Structural Mapping are presented in the table 2. Among of the 37 OMOP tables, the one related to hospital costs were not applicable, some related to derived data were not populated and some tables related to vocabulary were pre-loaded with terminology informations. The 26 tables of MIMIC have been dispatched into 19 OMOP tables. The reduced number of tables results from the differences in design of both models. OMOP stores all the terminologies into one table whereas MIMIC has one table for each terminology and the same applies for facts data that are grouped by nature in OMOP while MIMIC tables are more specialized and respects the source EHR’s design. For example the measurement gather measured information and combines 4 source tables resulting in 365 181 104 rows which is 20% more than the largest MIMIC table. To some extends this is a regression in terms of performances. Two important tables are provided by OMOP to represent the relationship between the data: concept_relationship and fact_relationship. We used them to bind the drugs into a solution, for microbiology / antibiograms and for visit_detail / caresite links. The following SQL query (listing 1) shows how a microorganism is linked to its susceptibility test by a fact_relationship and illustrates the flexibility of the model. However this flexibility affects the simplicity and the performances of the model by increasing the number of joins within SQL queries.

• Download figure
• Open in new tab

• Download figure
• Open in new tab

Listing 1.

Original table microbiology SQL query

The table 3 presents the basic characterization of the MIMIC-OMOP population and assesses the overall quality of structural mapping. Fortunately most statistics remain similar between the two versions with still few differences. The table 3 shows MIMIC contains 61,532 intensive care stays while OMOP contains 71 576 intensive care stays. This represents a 16% increase in stays. By desigh MIMIC aggregates information from various systems. Thus the transfer information is divided into several tables, such as admissions, transfers and also icustays. Rather OMOP centralizes this information in the detail of the visit_detail. We also added emergency stays as a normal location for patients throughout their hospital stay (unlike what had been done by MIMIC). The Icustays MIMIC table has not been transformed because it derives from the transfer table¹⁴ and we decided to assign a new visit_detail row for each ICU stay (based on the transfer table) while MIMIC preferred to assign a new ICU stay if a new admission occurs > 24h after the end of the previous stay.

This table also shows an increase of the number of laboratory measurements per admission. This is because MIMIC-OMOP gathers laboratory data from both the MIMIC dedicated laboratory table and the chartevents table which is usually not considered for this purpose. For laboratory tests we put a specimen (i.e. a blood sample) for many laboratory results (because one blood sample can be used for several tests), we decided to create as many rows of samples as laboratory tests because the information is not present in MIMIC. The same was true when date information was not provided (start /end_datetime for drug_exposure).

As mentioned in the table 4, from 20% to 80% of the source columns has not been kept. Almost all were redundant with others or provided derived information. The main concern is the loss of some timestamps. For example, the MIMIC chartevents tables provides the storetime and charttime columns, but OMOP only provides one column to store timestamp. Thus, MIMIC storetime column was eliminated during the ETL which has been considered the less valuable.

As mentioned in the methods the incorrect entries are not kept in the process. According to the tables 4, four MIMIC tables (inputevents_mv, chartevents, procedureevents_mv, note) have deleted rows in the ETL process. All of them were tagged in MIMIC as erroneous or cancelled.

A set of minor modifications of the OMOP tables structure was made in order to fit the data. All character columns with limited length have been modified to unlimited length since it could cause unpredictable truncation of content, while having no negative impact on Postgres storage size or performance. The visit_occurrence and the visit_detail tables have been corrected accordingly to some discussions on the OHDSI forum. The nlp_note table has been extended with fields mentioned in online documentation but forgotten in the scripts. In addition the offset column has been divided into two integer type columns because the offset term is a SQL reserved word and it makes sense to fill the resulting offset_begin and offset_end resulting columns with integer values.

All the PgTAP unit tests passed. Moreover OMOP had a 100% match of the integrity constraints and the foreign key relationships of the data models. After 18 hours of computations Achilles Heel issued 15 errors, 18 warnings and 8 notifications. This result is good compared to other studies [24].

2.1.2 Conceptual Mapping

The results of the Conceptual Mapping’s completeness are presented in the table 5.

We have often mapped many source concepts to a unique standard concept_id because MIMIC provides a large number of equivalent concepts. For example MIMIC provides 6 distinct concepts for body temperature: Temperature C, Temperature C (calc), Temperature F, Temperature F (calc), Temperature Fahrenheit and Temperature Celsius. All of them have been mapped to the LOINC “Body temperature” and numerical values have been normalized.

OMOP’s terminology coverage has already been rated as excellent [20]. We used the OMOP terminology mappings - NDC-RxNorm, ICD9-SNOMED, CPT4-SNOMED - to standardize a consequent set of MIMIC non-standard terminologies.

The automatic OMOP terminologies mapping was evaluated by an intensivist. This results are in favor of a good integration of the model. We checked 100 elements for each mapping used (NDC, ICD9 and CPT4). ICD9 and CPT4 are correctly mapped to SNOMED (100%). But only 85% of NDCs are linked to a correct RxNorm code. This is partly due to an incorrect NDC drug code (from MIMIC), partly because only 78% of NDC codes are mapped to Rxnorm. Moreover, even if this does not seem to have affected our ETL we know that some of ICD-9-CM codes can have a one-to-several match with SNOMED¹⁵ (28%).

In several cases, OMOP had no suitable concepts for the ICU specific cases. In particular, the visit_detail table does not yet introduce relevant information and duplicates information from visit_occurrence table. Therefore we extended the concepts to track bed transfers and room transfers thought admitting_concept_id, discharge_to_concept_id or visit_type_concept_id columns. These added concepts have been introduced with concept_id between 2 billion and 2.001B to distinguish them with OMOP concepts (0 to 2B) and MIMIC locals (>2.001B).

Some local concepts could not mapped to standard ones. This unmapped concepts are linked with the concept_id = 0 and appear in different cases. In the first case, the local concept has no equivalent in the standard concept set. In the second case, it has not yet been mapped and may have a standard equivalent. In the third case, the value is missing and cannot be mapped. In our opinion, although not all of these cases can be used for standard queries, they should have a different concept identifier in order to be treated differently (not only concept_id = 0). Some of the domains_id do not match the table name, it makes sense because the observation domain can be measurement table and vice versa. Although various types of information are stored in the measurement table, the dedicated OMOP concepts for the measurement_type_concept_id column were not sufficient to distinguish them. Therfore we added some (Labs - Chemistry, Labs – Culture Organisms etc).