Entity Mapping from OpenMRS to OMOP

This document provides a detailed reference for the entity-level and field-level mappings used to transform data from the OpenMRS data model into the OMOP Common Data Model (CDM). The transformation was implemented using SQLMesh, and follows OMOP's standardization principles to ensure interoperability across healthcare systems.

Each section describes the mapping for a specific OMOP entity, including:

  • Source fields from OpenMRS

  • Transformations or logic applied

  • Hardcoded or default values (where applicable)

  • Notes on vocabulary alignment and filtering rules

A special focus has been placed on concept mapping, which aligns OpenMRS concepts with standard OMOP vocabulary concepts using the CIEL SAME-AS relationship, to ensure the resulting data can be meaningfully analyzed across OMOP-compliant platforms.

This reference is intended to support implementers, data engineers, and analysts in understanding how OpenMRS data has been normalized and structured for OMOP-based analytical environments.

Feedback

Feedback and suggestions are welcome! If you notice any issues, inconsistencies, or areas for improvement, please reach out or open a discussion thread. Your input helps improve this mapping for everyone.

PERSON

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

person_id

patient.patient_id

Direct mapping

Primary key from OpenMRS patient table

gender_concept_id

person.gender

'M' → 8507, 'F' → 8532, else 0

Uses standard OMOP gender concept IDs

year_of_birth

person.birthdate

Extract YEAR()

month_of_birth

person.birthdate

Extract MONTH()

day_of_birth

person.birthdate

Extract DAY()

birth_datetime

person.birthdate

Direct mapping

race_concept_id

Hardcoded to 0

Race not tracked in OpenMRS by default

ethnicity_concept_id

Hardcoded to 0

Ethnicity not tracked in OpenMRS

location_id

Hardcoded to 0

Needs lookup

provider_id

Hardcoded to 0

care_site_id

Hardcoded to 0

person_source_value

Hardcoded to empty string

Can be used to store original person reference

gender_source_value

person.gender

Direct mapping

Keeps original source value of gender

gender_source_concept_id

Hardcoded to 0

Optional

race_source_value

Hardcoded to empty string

Not tracked in OpenMRS

race_source_concept_id

Hardcoded to 0

ethnicity_source_value

Hardcoded to empty string

ethnicity_source_concept_id

Hardcoded to 0

OBSERVATION_PERIOD

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

observation_period_id

ROW_NUMBER() OVER (ORDER BY MIN(v.date_started))

Synthetic primary key per patient

person_id

visit.patient_id

Direct mapping

Links to the person_id in the PERSON table

observation_period_start_date

visit.date_started

DATE(MIN(v.date_started))

First known activity date per patient

observation_period_end_date

visit.date_stopped, encounter.encounter_datetime

DATE(GREATEST(MAX(v.date_stopped), MAX(e.encounter_datetime)))

Last known activity, taking both visit and encounter into account

period_type_concept_id

Hardcoded to 44814724

OMOP concept ID for EHR record

Why visit.date_started for observation period start?

  • The visit table in OpenMRS represents high-level patient interactions with the healthcare system.

  • date_started gives the earliest recorded point of care, often more reliable than scattered observations.

  • Using the earliest date_started per patient ensures we mark the beginning of valid, intentional medical data.

Why GREATEST(visit.date_stopped, encounter.encounter_datetime) for observation period end?

  • Some visits may not have a date_stopped, especially if not explicitly closed in OpenMRS.

  • Encounters within a visit may occur after the visit’s date_stopped (due to data entry lag or configuration).

  • To ensure we capture the latest available data, we take the greater of:

    • MAX(visit.date_stopped): expected end of clinical engagement

    • MAX(encounter.encounter_datetime): actual last recorded interaction

Additional Considerations:

  • Patients may have long gaps between visits, but for simplicity and consistency, only a single continuous observation period is generated per patient in this model.

Concept Mapping Strategy

One of the key challenges in translating entities from OpenMRS to OMOP was handling concepts effectively.

Initial Approach

The initial approach involved converting existing OpenMRS concepts into the OMOP CONCEPT table and using those concept IDs during the observation transformation. However, this method resulted in OpenMRS-specific data, which reduced interoperability and contradicted OMOP’s goal of standardized, system-agnostic data representation.

Improved Approach

To ensure universal compatibility, standardized OMOP concept IDs were used during the transformation process. This allows the converted data to remain consistent and interoperable across different health systems, not just OpenMRS.

  • The official OMOP CONCEPT.csv file was downloaded from the OMOP vocabulary repository.

  • This file includes over 2 million standard concepts (2,059,343 entries).

  • A custom mapping table was created using:

    • OpenMRS concept table

    • OpenMRS concept_reference_map table

    • The downloaded OMOP concept definitions

Mapping Table Structure

omrs_concept_id

omop_concept_id

relationship_id

source_vocabulary

description

omrs_concept_id

omop_concept_id

relationship_id

source_vocabulary

description

5

45919733

SAME-AS

CIEL

Tetra brand of tetracycline

5

1836948

SAME-AS

RxNorm

tetracycline

5

4187581

NARROWER-THAN

SNOMED

Tetracycline

5

4281393

SAME-AS

SNOMED

Tetracycline-containing product

6

45919413

SAME-AS

CIEL

ETH-Oxydose

6

1124957

SAME-AS

RxNorm

oxycodone

Current Mapping Strategy

The current conversion uses CIEL vocabulary concepts with a SAME-AS relationship to map OpenMRS concepts to OMOP. However, the structure supports alternative vocabularies such as RxNorm or SNOMED, which can be utilized in future iterations or based on specific use cases.

This mapping strategy ensures that clinical data remains aligned with OMOP standards, enabling better data analysis, interoperability, and integration with other OMOP-compliant systems.

VISIT_OCCURRENCE

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

visit_occurrence_id

visit.visit_id

Direct mapping

Primary key in OpenMRS

person_id

visit.patient_id

Direct mapping

patient_id in OpenMRS maps to person_id in OMOP

visit_concept_id

Hardcoded to 0

Needs concept mapping if available

visit_start_date

visit.date_started

DATE() extraction

Standard OMOP format

visit_start_datetime

visit.date_started

Direct mapping

visit_end_date

visit.date_stopped

DATE() extraction

NULL for ongoing visits

visit_end_datetime

visit.date_stopped

Direct mapping

NULL for ongoing visits

visit_type_concept_id

visit.visit_type_id

Direct mapping (needs lookup)

Might require mapping to OMOP concepts

provider_id

Hardcoded to 0

Optional in OMOP

care_site_id

visit.location_id

Direct mapping

Assumes location_id maps to care site

visit_source_value

Hardcoded to empty string

Optional; can populate with source value

visit_source_concept_id

Hardcoded to 0

Optional

admitted_from_concept_id

Hardcoded to 0

Optional; not tracked in OpenMRS by default

admitted_from_source_value

Hardcoded to empty string

discharged_to_concept_id

Hardcoded to 0

discharged_to_source_value

Hardcoded to empty string

preceding_visit_occurrence_id

Hardcoded to 0

Could be populated if visit chains exist

OBSERVATION

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

observation_id

obs.obs_id

Direct mapping

Unique identifier from the OpenMRS obs table

person_id

obs.person_id

Direct mapping

Links to person_id in OMOP PERSON table

observation_concept_id

OMRS_TO_OMOP_CONCEPT.omop_concept_id

Mapped using CIEL SAME-AS relationships

Concept mapping to standard OMOP concepts

observation_date

obs.obs_datetime

DATE() extraction

Extracts just the date portion

observation_datetime

obs.obs_datetime

Direct mapping

observation_type_concept_id

Hardcoded to 32827

OMOP concept for EHR encounter record

value_as_number

obs.value_numeric

Direct mapping

Numeric value of the observation, if present

value_as_string

obs.value_text

LEFT(obs.value_text, 60)

Truncated to 60 characters as there’s a character limit

value_as_concept_id

obs.value_coded

Direct mapping

Used if the observation has a coded value

qualifier_concept_id

NULL

Not used in current mapping

unit_concept_id

NULL

Not used in current mapping

provider_id

NULL

Optional; not mapped

visit_occurrence_id

obs.encounter_id

Direct mapping

Assumes encounter maps to a visit occurrence

visit_detail_id

NULL

Not used

observation_source_value

Hardcoded to empty string

Optional; can be used to track original values

observation_source_concept_id

OMRS_TO_OMOP_CONCEPT.omrs_concept_id

Direct mapping

Keeps original OpenMRS concept ID

unit_source_value

Hardcoded to empty string

Optional

qualifier_source_value

Hardcoded to empty string

Optional

value_source_value

Hardcoded to empty string

Optional

observation_event_id

NULL

Not used

obs_event_field_concept_id

NULL

Not used

Filtering Notes:

  • Only observations that are not voided are included (o.voided = 0).

  • Observations from encounter types 5 (vitals), 8 (notes), and 11 (lab results) are excluded.

  • Concept mapping uses the OMRS_TO_OMOP_CONCEPT table where relationship_id = 'SAME-AS' and vocabulary_id = 'CIEL'.

MEASUREMENT

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

measurement_id

obs.obs_id

Direct mapping

Unique identifier for each measurement

person_id

obs.person_id

Direct mapping

Links to person_id in OMOP PERSON table

measurement_concept_id

OMRS_TO_OMOP_CONCEPT.omop_concept_id

Mapped using CIEL SAME-AS relationships

Uses standardized OMOP concept ID

measurement_date

obs.obs_datetime

DATE() extraction

measurement_datetime

obs.obs_datetime

Direct mapping

measurement_time

obs.obs_datetime

DATE_FORMAT(obs_datetime, '%H:%i:%s')

Extracts time component in HH:MM:SS format

measurement_type_concept_id

Hardcoded to 44818701

OMOP standard for EHR-derived measurement

operator_concept_id

NULL

Not used

value_as_number

obs.value_numeric

Direct mapping

Numeric measurement value

value_as_concept_id

obs.value_coded

Direct mapping

Used if the measurement has a coded value

unit_concept_id

NULL

Optional

range_low

concept_numeric.low_normal

Direct mapping

Lower normal range if available

range_high

concept_numeric.hi_normal

Direct mapping

Upper normal range if available

provider_id

NULL

Not used

visit_occurrence_id

obs.encounter_id

Direct mapping

Links measurement to associated visit

visit_detail_id

NULL

Optional

measurement_source_value

Hardcoded to empty string

Optional

measurement_source_concept_id

OMRS_TO_OMOP_CONCEPT.omrs_concept_id

Direct mapping

Keeps original OpenMRS concept ID

unit_source_value

concept_numeric.units

Direct mapping

Source unit text (e.g., 'mmHg')

unit_source_concept_id

NULL

Optional

value_source_value

obs.value_numeric

Direct mapping

Same as value_as_number; kept for OMOP compatibility

measurement_event_id

NULL

Not used

meas_event_field_concept_id

NULL

Not used

Filtering Notes:

  • Only non-voided observations are included (o.voided = 0).

  • Only vitals (encounter_type_id = 5) and lab results (encounter_type_id = 11) are included.

  • Concept mapping uses the OMRS_TO_OMOP_CONCEPT table where:

    • relationship_id = 'SAME-AS'

    • vocabulary_id = 'CIEL'

NOTE

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field(s)

Transformation / Logic

Notes

note_id

obs.obs_id

Direct mapping

Unique identifier for each note

person_id

obs.person_id

Direct mapping

Links to person_id in OMOP PERSON table

note_date

obs.obs_datetime

DATE() extraction

Date of the observation

note_datetime

obs.obs_datetime

Direct mapping

Full timestamp

note_type_concept_id

Hardcoded to 44814645

OMOP concept ID for Note

note_class_concept_id

Hardcoded to 44814645

Also set to Note

note_title

Hardcoded to empty string

Title not available in OpenMRS

note_text

obs.value_text

Direct mapping

The actual note content

encoding_concept_id

Hardcoded to 4180186

OMOP concept ID for UTF-8

language_concept_id

Hardcoded to 0

Language unknown or not specified

provider_id

NULL

Not mapped

visit_occurrence_id

obs.encounter_id

Direct mapping

Links note to associated visit

visit_detail_id

NULL

Optional

note_source_value

Hardcoded to empty string

Optional

note_event_id

NULL

Not used

note_event_field_concept_id

NULL

Not used

Filtering Notes:

  • Only non-voided observations are included (o.voided = 0).

  • Filters for encounter type ID = 8, which corresponds to Visit Notes in OpenMRS.

  • Only observations with non-null value_text are included.

LOCATION

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

location_id

location.location_id

Direct mapping

Unique identifier for the location

address_1

location.address1

Direct mapping

Primary address line

address_2

location.address2

Direct mapping

Secondary address line

city

location.city_village

Direct mapping

Name of the city or village

state

location.state_province

LEFT(state_province, 2)

Truncated to first 2 characters

zip

location.postal_code

LEFT(postal_code, 9)

Truncated to first 9 characters

county

location.county_district

LEFT(county_district, 20)

Truncated to first 20 characters

location_source_value

location.name

Direct mapping

Original name of the location

country_concept_id

NULL

Not mapped; can be added if a country concept exists

country_source_value

location.country

Direct mapping

Country name as stored in OpenMRS

latitude

location.latitude

NULLIF(latitude, '')

Avoids storing empty string as a valid value

longitude

location.longitude

NULLIF(longitude, '')

Same as above

Filtering Notes:

  • Only non-retired locations are included (l.retired = 0).

CONDITION_OCCURRENCE

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

OMOP Field Name

OpenMRS Source Field

Transformation / Logic

Notes

condition_occurrence_id

conditions.condition_id

Direct mapping

Unique identifier for the condition record

person_id

conditions.patient_id

Direct mapping

Links to the patient in the OMOP PERSON table

condition_concept_id

OMRS_TO_OMOP_CONCEPT.omop_concept_id

Mapped using SAME-AS in CIEL vocabulary

Uses standardized OMOP concept ID

condition_start_date

conditions.onset_date

DATE() extraction

condition_start_datetime

conditions.onset_date

Direct mapping

condition_end_date

conditions.end_date

DATE() extraction

May be NULL if condition is ongoing

condition_end_datetime

conditions.end_date

Direct mapping

condition_type_concept_id

Hardcoded to 0

Optional; can be mapped to OMOP concept types in future

condition_status_concept_id

Hardcoded to 0

Optional

stop_reason

conditions.void_reason

COALESCE(void_reason, '')

Reason for stopping the condition

provider_id

NULL

Not mapped currently

visit_occurrence_id

NULL

Could be linked if encounter/visit info is available

visit_detail_id

NULL

Not used

condition_source_value

Hardcoded to empty string

Optional; can include original value from source if needed

condition_source_concept_id

OMRS_TO_OMOP_CONCEPT.omrs_concept_id

Direct mapping

Keeps original OpenMRS concept ID

condition_status_source_value

conditions.verification_status

COALESCE(verification_status, '')

Captures status like "confirmed", "presumed", etc. if available

Filtering Notes:

  • Only non-voided conditions are included (c.voided = 0).

  • Concept mapping uses the OMRS_TO_OMOP_CONCEPT table where:

    • relationship_id = 'SAME-AS'

    • vocabulary_id = 'CIEL'

Related content