OHDSI Home | Forums | Wiki | Github

MIMIC2 (and possibly MIMIC3) data in OHDSI CDM format

General
#12

If you like this proposal - please comment on MIT github

2 Likes
#13

I’m in the process of doing the mimicIII ETL using this project. I lost my session in the middle of the actual ETL step, but I think it ran to completion. There’s a post test you can run to check your ETL results, but I can’t tell if the issues it reports are critical or not

[dteuser@dtk8scentos02 mimic-omop]$ psql “$MIMIC” -f “etl/check_etl.sql”
psql:etl/check_etl.sql:1: unrecognized value “” for “ECHO”
Available values are: none, errors, queries, all.
Output format is unaligned.
Pager usage is off.
psql:etl/check_etl.sql:15: etl/StandardizedVocabularies/CONCEPT/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:16: etl/StandardizedVocabularies/COHORT_DEFINITION/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:17: etl/StandardizedVocabularies/ATTRIBUTE_DEFINITION/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:18: etl/StandardizedDerivedElements/COHORT_ATTRIBUTE/check_etl.sql: No such file or directory
psql:etl/check_etl.sql:19: etl/StandardizedHealthSystemDataTables/CARE_SITE/check_etl.sql: No such file or directory
1…4
ok 1 - PERSON – number patients match
ok 2 - PERSON – gender distribution matches
ok 3 - PERSON – date of birth year distributions match
ok 4 - PERSON – no births after deaths
1…1
not ok 1 - number of unique patients who die in the database

Failed test 1: “number of unique patients who die in the database”

Results differ beginning at row 1:

have: (15759)

want: (14849)

Looks like you failed 1 test of 1

1…7
ok 1 - Visit_occurrence table – same number admission
ok 2 - number of hospital admissions who die in-hospital match
ok 3 - Visit_occurrence table – same distribution adm
ok 4 - Visit_occurrence table – distribution admit source value
ok 5 - Visit_occurrence table – repartition discharge_to_source_value
ok 6 - Visit_occurrence table – links checker
ok 7 - Visit_occurrence table – start_date > end_date
1…1
ok 1 - OBSERVATION_PERIOD – row count matches visit_occurrence
1…7
not ok 1 - VISIT_DETAIL – test same number transfers

Failed test 1: “VISIT_DETAIL – test same number transfers”

Results differ beginning at row 1:

have: NULL

want: (184142,19)

ok 2 - VISIT_DETAIL – test visit_source_value and visit_source_concept_id match
not ok 3 - VISIT_DETAIL – test admitting_source_concept_id and admitting_source_value match

Failed test 3: “VISIT_DETAIL – test admitting_source_concept_id and admitting_source_value match”

Results differ beginning at row 1:

have: (0)

want: (139516)

not ok 4 - VISIT_DETAIL – test patients number in visit_detail/icustays

Failed test 4: “VISIT_DETAIL – test patients number in visit_detail/icustays”

Results differ beginning at row 1:

have: (0,0)

want: (46476,57786)

not ok 5 - VISIT_DETAIL – check start_datetime < end_datetime

Failed test 5: “VISIT_DETAIL – check start_datetime < end_datetime”

Number of columns or their types differ between the queries:

have: (161)

want: (0)

not ok 6 - VISIT_DETAIL – check start_date < end_date

Failed test 6: “VISIT_DETAIL – check start_date < end_date”

Number of columns or their types differ between the queries:

have: (35)

want: (0)

ok 7 - VISIT_DETAIL – check care site is never null

Looks like you failed 5 tests of 7

1…7
not ok 1 - MEASUREMENT – check distribution of all labs match

Failed test 1: “MEASUREMENT – check distribution of all labs match”

Results differ beginning at row 168:

have: (33,3,49,219)

want: (33,3,50,219)

psql:etl/StandardizedClinicalDataTables/MEASUREMENT/check_etl.sql:67: ERROR: syntax error at or near “AS”
LINE 7: AS org_name
^
QUERY:
SELECT org_name::TEXT, count(1)
FROM
(
SELECT DISTINCT ON
(hadm_id, spec_type_desc, org_name, coalesce(charttime, chartdate))
AS org_name
FROM microbiologyevents
WHERE org_name IS NOT NULL
) tmp
GROUP BY org_name ORDER BY 2, 1 desc;

CONTEXT: PL/pgSQL function results_eq(text,text,text) line 7 at OPEN
ok 2 - MEASUREMENT – there is source concept in measurement not described
ok 3 - MEASUREMENT – check for duplicate primary keys
ok 4 - MEASUREMENT – standard concept checker
not ok 5 - MEASUREMENT – check row counts match

Failed test 5: “MEASUREMENT – check row counts match”

Results differ beginning at row 1:

have: (768,3097)

want: (1,6330)

ok 6 - Measurement pass, w00t!

Looks like you planned 7 tests but ran 6

1…5
ok 1 - PROCEDURE_OCCURRENCE – check all procedureevents_mv rows inserted
ok 2 - PROCEDURE_OCCURRENCE – check label is consistent with source_value
ok 3 - PROCEDURE_OCCURRENCE – check all CPT code rows inserted
ok 4 - PROCEDURE_OCCURRENCE – check CPT subsections mapped correctly
ok 5 - PROCEDURE_OCCURRENCE – check ICD procedure rows inserted
1…1
ok 1 - PROVIDER – check caregivers/providers match
1…8
ok 1 - CONDITION OCCURRENCE – check ICD diagnoses row count matches
ok 2 - CONDITION OCCURRENCE – diagnosis in admission same
ok 3 - CONDITION OCCURRENCE – distrib diagnosis the same
ok 4 - CONDITION OCCURRENCE – there is source concept in measurement not described
ok 5 - CONDITION OCCURRENCE – primary key checker
ok 6 - CONDITION OCCURRENCE – standard concept checker
not ok 7 - CONDITION OCCURRENCE – start_datetime should be > end_datetime

Failed test 7: “CONDITION OCCURRENCE – start_datetime should be > end_datetime”

Results differ beginning at row 1:

have: (585)

want: (0)

ok 8 - CONDITION OCCURRENCE – start_date should be > end_date

Looks like you failed 1 test of 8

1…10
not ok 1 - OBSERVATION – religion distribution matches (concept 4052017)

Failed test 1: “OBSERVATION – religion distribution matches (concept 4052017)”

Results differ beginning at row 15:

have: (OTHER,2696)

want: (JEWISH,5314)

ok 2 - OBSERVATION – language distribution matches (concept 40758030)
ok 3 - OBSERVATION – marital distribution matches (concept 40766231)
ok 4 - OBSERVATION – insurance distribution matches (concept 46235654)
ok 5 - OBSERVATION – ethnicity distribution matches (concept 44803968)
ok 6 - OBSERVATION – source concept described
ok 7 - OBSERVATION – primary key is always unique
not ok 8 - OBSERVATION – datetimeevents number

Failed test 8: “OBSERVATION – datetimeevents number”

Results differ beginning at row 1:

have: (0)

want: (4485342)

not ok 9 - OBSERVATION – Standard concept checker

Failed test 9: “OBSERVATION – Standard concept checker”

Results differ beginning at row 1:

have: (79027)

want: (0)

Looks like you planned 10 tests but ran 9

1…4
ok 1 - DRUG_EXPOSURE – check number of patients with prescription matches
not ok 2 - DRUG_EXPOSURE – check drug_source_value matches source

Failed test 2: “DRUG_EXPOSURE – check drug_source_value matches source”

Results differ beginning at row 1:

have: (76329301205,1)

want: ("zz ",1)

ok 3 - DRUG_EXPOSURE – is concept source id full filled
ok 4 - DRUG_EXPOSURE – Standard concept checker

Looks like you failed 1 test of 4

1…2
ok 1 - visit occurrence nb
ok 2 - check radio nb
1…3
ok 1 - primary key checker
ok 2 - source concept described
ok 3 - source concept described
ok 4 - Note Nlp pass, w00t!

Looks like you planned 3 tests but ran 4

[dteuser@dtk8scentos02 mimic-omop]$

#14

I’ve loaded the MIMIC III data into OMOP using MIT-LCP/mimic-omop.

Any idea how long it might take to run the Achilles R job on the full MIMIC III dataset? It’s been stuck at 99% for hours now. pgAdmin dashboard indicates a fair bit of activity.

> connectionDetails<- createConnectionDetails(dbms=“postgresql”, user = “dteuser”, password = “XXXXX”, server = “DTK8SCENTOS02.qa.navinet.local/mimic”) > save.image("~/mimic_achilles.RData") > achilles(connectionDetails,cdmDatabaseSchema=“omop”,sourceName=“MIMICIII CDM V5 Database”, cdmVersion = “5.3.0”, runHeel = FALSE, conceptHierarchy = FALSE, numThreads = 10) Connecting using PostgreSQL driver Connecting using PostgreSQL driver |===================================================================================| 100% Executing SQL took 0.147 secs Dropping scratch Achilles tables from schema omop Connecting using PostgreSQL driver |===================================================================================| 100% |===================================================================================| 100% Temporary Achilles tables removed from schema omop Executing multiple queries. This could take a while |===================================================================================| 99%

#15

In my MIMIC-OMOP pgTAB results, it reported just like same as yours. In my opinion, this might be caused by MIMIC 3 conversion ETL queries which provided from the MIT github. these reported issues is not your fault .
Achilles R job takes so long time when it processes MIMIC III dataset. I had spend the time for a week to execute the achilles queries on the RAID - SAS drive server. Check that Achilles R process is still running properly with the pg_activities table in a postgresql database or the CPU/RAM usages monitor.

#16

Indexes are very important as well, before starting any processing, apply the recommended indexes on your data, update statistics to get the best query performance, etc.

#17

Here’s what my dashboard looks like. It appears its like it’s still running, but I lost my R session. I was using R studio web. In the future I’ll use the R shell with screen, so I don’t get disconnected. I did create the indexes. Can I update the stats while it’s running, or will that make things worse?

image
image1040×710 56.4 KB

#18

it does not seem to affect the database performance if does not request too much frequently. In addition, you may wait for a long time even if you create the indexes.

#19

The R process seems to have terminated, so I thought it completed. But I started up Atlas and I can’t see any of the data source reports. Does that indicate that Achilles failed? Where would I look to check? I guess I can generate the SQL and run it in smaller chunks?

#20

I’m getting a PostgreSQL error from the achilles job. I’m using postgres 10, is that a problem? I’ll attach the error report. I don’t see what’s wrong, but I’m on a postgres expert.

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near “)”
Position: 377

SQL:
CREATE TEMP TABLE tempResults

AS
SELECT
*
FROM
(
select
t2.count_value*100.0/all_count as statistic_value,
CAST(‘Meas:NoNumValue:Percentage’ AS VARCHAR(100)) as measure_id
from
(
select sum(count_value) as all_count from omop.achilles_results where analysis_id = 1820
) t1 join (select count_value from omop.achilles_results where analysis_id = 1821) t2
) t3

R version:
R version 3.5.1 (2018-07-02)

Platform:
x86_64-redhat-linux-gnu

Attached base packages:

  • stats
  • graphics
  • grDevices
  • utils
  • datasets
  • methods
  • base

Other attached packages:

  • Achilles (1.6.3)
  • DatabaseConnector (2.2.1)
  • SqlRender (1.5.3)
  • usethis (1.4.0)
  • devtools (2.0.1)
#21

That issue has been reported here: https://github.com/OHDSI/Achilles/issues/359 and they are currently working on a fix.

#22

Thanks. Is this specific to multi-threading or heel? I guess I’ll wait on the patch and re-install the achilles package.

#23

No, not related to multi threading, it has something to do with (I think) heel analysis. You might be able to sidestep the error by setting the runHeel = FALSE, however, i’m not totally sure that it will avoid the error, so please forgive me if you try and it does not work.

#24

Physionet has a new web based system and the plan is hopefully to post the converted data on that improved system. See the discussion here. (and comment there to keep the momentum going).

#25

Does anyone know if there are any updates on sharing a complete version of mimiciii in omop on physionet?

Especially now in Covid19 times, I would very much like to work with a cdm at home, as I can’t access my organisation’s cdm. If there isn’t a version available, I will give the ETL a try.

Thankyou

1 Like
#26

No further progress that I’m aware of. We’re part of a proposal to add ICU data to MIMIC III and complete the OMOP ETL so it can be shared in OMOP form. It would make the data available broadly in a similar manner to the current strategy used in PhysioNet. Won’t know about how that turns out till June.

1 Like
#27

I was emailing with Nicolas Paris and Adrien Parrot about this since Aug 2019
Please send direct email (or PM) to me if you are interested in seeing OMOP shaped MIMIC3 data offered within Physionet server. We all need to join a common project. My effort to create one was denied by physionet folks as not coming from the main person who created the ETL.

https://physionet.org/projects

image
image847×939 43.7 KB

You can also upvote a github issue here

(the prior proposal was closed by MIT admins)

1 Like
#28

We would be happy to share an OMOP version of MIMIC-III on PhysioNet. We have received an OMOP-related submission in the past. The main issue with this submission was that the descriptive information was inadequate. Instructions for submitting a project to PhysioNet are at: https://physionet.org/about/publish/#sharing

1 Like
#29

Last week I received guidance from MIT how to proceed with this. This is a call for collaborators. If you want to be invited into the PhysioNet project, please email me or respond here.
see github here Use this ETL as a way to provide MIMIC in OMOP directly on the Physionet website · Issue #52 · MIT-LCP/mimic-omop · GitHub
The project in draft mode is here

image
image985×634 25.2 KB

and invites are like this

image
image916×971 42.7 KB

1 Like
#30

Hi @Vojtech_Huser,

I am interested and am already a registered user of Physionet.

My mail id is selvasathappan36@gmail.com

#31

This thread is very long and goes back way in time.
Please use this new thread to document the 2020 project for conversion - called Argos.

t