Rosetta can output data in accordance with the Observational Medical Outcomes Partnership (OMOP) Common Data Model format. The OMOP model standardizes the structure and content of observational data, enabling you to answer the most frequent analytical questions.
Contents
Rosetta’s OMOP conversion is useful for any application that prefers tabular data (e.g., separate, linked data tables) over the nested FHIR JSON/XML format. You can easily ingest the data into a structured database or other off-the-shelf tools, getting you up and running quickly with your own analytics environment. OMOP data is also designed for analytics, presenting data in an easily consumable format with a standard vocabulary for each domain (e.g., all Conditions use SNOMED).
Rosetta uses v5.4 of the OMOP Common Data Model (CDM) specification, with a few exceptions noted in the Convert API.
The Convert API will convert a FHIR R4 bundle into OMOP format.
You can use this in conjunction with other convert APIs, allowing data to flow from one conversion to another. For example:
OHDSI has links and tutorials for numerous off-the-shelf software tools that can process OMOP data.
The CSV data can also be readily imported to an SQL database. You can use the scripts provided by OMOP to help you set up the database.
Patient/35b77437-425d-419c-90b5-af4bc433ebe9
becomes person 1
, and person Patient/33a9fde8-20fa-48c1-bffe-592c4e35efef
becomes person 2
, etc.
Once the data is imported, you can run standard SQL queries to analyze the data. Some examples are given below.
The output of the OMOP conversion is a ZIP archive containing multiple CSV files, one for each supported OMOP data table.
Rosetta’s OMOP output includes the following data tables:
Below are some example SQL queries to get you started with common use cases.
Build a list of conditions from the CONDITION_OCCURRENCE table matching diagnosis codes in a value set based on the SNOMED hierarchy. In this example: all descendants of ‘201826 Type 2 diabetes mellitus’.
select * from condition_occurrence co
join concept c on c.concept_id = co.condition_concept_id
join concept_ancestor ca on ca.descendant_concept_id = c.concept_id and ca.ancestor_concept_id = 201826
-- 201826 Type 2 diabetes mellitus
select * from condition_occurrence co
join concept c on c.concept_id = co.condition_concept_id
join concept_ancestor ca on ca.descendant_concept_id = c.concept_id and ca.ancestor_concept_id = 201826
-- 201826 Type 2 diabetes mellitus
Build a list of patients who have a a CONDITION_OCCURRENCE
matching conditions in a value set. In this example: ‘201826 Type 2 diabetes mellitus’.
select p.person_id, c.concept_code, c.concept_name, c.vocabulary_id, count(distinct co.condition_occurrence_id) from person p
join condition_occurrence co on co.person_id = p.person_id
join concept c on c.concept_id = co.condition_concept_id
join concept_ancestor ca on ca.descendant_concept_id = c.concept_id and ca.ancestor_concept_id = 201826
-- 201826 Type 2 diabetes mellitus
group by p.person_id, c.concept_code, c.concept_name, c.vocabulary_id
select p.person_id, c.concept_code, c.concept_name, c.vocabulary_id, count(distinct co.condition_occurrence_id) from person p
join condition_occurrence co on co.person_id = p.person_id
join concept c on c.concept_id = co.condition_concept_id
join concept_ancestor ca on ca.descendant_concept_id = c.concept_id and ca.ancestor_concept_id = 201826
-- 201826 Type 2 diabetes mellitus
group by p.person_id, c.concept_code, c.concept_name, c.vocabulary_id
Build a list of lab measurements (from the MEASUREMENT
table) by LOINC component (e.g., A1C tests).
select * from measurement m
join concept c on m.measurement_concept_id = c.concept_id
join concept_relationship cr on c.concept_id = cr.concept_id_1
join concept vsc on vsc.concept_id = cr.concept_id_2 and cr.relationship_id = 'Has component'
and vsc.concept_class_id = 'LOINC Component' and vsc.concept_name in ('Hemoglobin A1c/Hemoglobin.total')
select * from measurement m
join concept c on m.measurement_concept_id = c.concept_id
join concept_relationship cr on c.concept_id = cr.concept_id_1
join concept vsc on vsc.concept_id = cr.concept_id_2 and cr.relationship_id = 'Has component'
and vsc.concept_class_id = 'LOINC Component' and vsc.concept_name in ('Hemoglobin A1c/Hemoglobin.total')
Build a list of medications organized by frequency of use (per ingredient) based on the DRUG_EXPOSURE
table.
select vsc.concept_name,count(distinct d.person_id) as countPersons, count(distinct d.drug_exposure_id) as countMeds from drug_exposure d
join concept_relationship cr on cr.concept_id_2 = d.drug_concept_id and cr.relationship_id = 'RxNorm ing of'
join concept vsc on cr.concept_id_1 = vsc.concept_id and vsc.concept_class_id = 'Ingredient'
group by vsc.concept_name
order by countPersons desc
select vsc.concept_name,count(distinct d.person_id) as countPersons, count(distinct d.drug_exposure_id) as countMeds from drug_exposure d
join concept_relationship cr on cr.concept_id_2 = d.drug_concept_id and cr.relationship_id = 'RxNorm ing of'
join concept vsc on cr.concept_id_1 = vsc.concept_id and vsc.concept_class_id = 'Ingredient'
group by vsc.concept_name
order by countPersons desc
There are several ways to query for the members of a value set. This example filters by LOINC component via the CONCEPT_RELATIONSHIP
table.
select * from concept vsc
join concept_relationship cr on cr.concept_id_1 = vsc.concept_id and cr.relationship_id = 'Component of'
join concept c on cr.concept_id_2 = c.concept_id
where vsc.concept_name = 'Hemoglobin A1c/Hemoglobin.total' and vsc.vocabulary_id = 'LOINC' and vsc.concept_class_id = 'LOINC Component'
--vsc.concept_name = 'Hemoglobin A1c/Hemoglobin.total'
--vsc.concept_name = 'SARS-CoV-2 (COVID-19) Ag'
select * from concept vsc
join concept_relationship cr on cr.concept_id_1 = vsc.concept_id and cr.relationship_id = 'Component of'
join concept c on cr.concept_id_2 = c.concept_id
where vsc.concept_name = 'Hemoglobin A1c/Hemoglobin.total' and vsc.vocabulary_id = 'LOINC' and vsc.concept_class_id = 'LOINC Component'
--vsc.concept_name = 'Hemoglobin A1c/Hemoglobin.total'
--vsc.concept_name = 'SARS-CoV-2 (COVID-19) Ag'
This example filters by RxNorm Ingredient via the CONCEPT_RELATIONSHIP
table.
select * from concept vsc
join concept_relationship cr on cr.concept_id_1 = vsc.concept_id and cr.relationship_id = 'RxNorm ing of'
join concept c on cr.concept_id_2 = c.concept_id
where vsc.concept_class_id = 'Ingredient' and vsc.concept_name ilike '%caduet%'
select * from concept vsc
join concept_relationship cr on cr.concept_id_1 = vsc.concept_id and cr.relationship_id = 'RxNorm ing of'
join concept c on cr.concept_id_2 = c.concept_id
where vsc.concept_class_id = 'Ingredient' and vsc.concept_name ilike '%caduet%'
This example uses SNOMED hierarchical relationships. However, note that it is not recursive and only returns immediate children.
select * from concept c
join concept_relationship cr on cr.concept_id_1 = 201826 and cr.relationship_id = 'Subsumes'
-- 201826 Type 2 diabetes mellitus
select * from concept c
join concept_relationship cr on cr.concept_id_1 = 201826 and cr.relationship_id = 'Subsumes'
-- 201826 Type 2 diabetes mellitus
OMOP vocabulary tables also provide a way to get all descendants within the SNOMED hierarchy using the CONCEPT_ANCESTOR
table. You are even able to specify the number of generations to include if desired.
select * from concept c
join concept_ancestor ca on ca.descendant_concept_id = c.concept_id and ca.ancestor_concept_id = 201826
-- 201826 Type 2 diabetes mellitus
select * from concept c
join concept_ancestor ca on ca.descendant_concept_id = c.concept_id and ca.ancestor_concept_id = 201826
-- 201826 Type 2 diabetes mellitus