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.

1 PERSON
2 OBSERVATION_PERIOD
3 Concept Mapping Strategy
4 VISIT_OCCURRENCE
5 OBSERVATION
6 MEASUREMENT
7 NOTE
8 LOCATION
9 CONDITION_OCCURRENCE

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'

Entity Mapping from OpenMRS to OMOP

PERSON

OBSERVATION_PERIOD

Why visit.date_started for observation period start?

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

Additional Considerations:

Concept Mapping Strategy

Initial Approach

Improved Approach

Mapping Table Structure

Current Mapping Strategy

VISIT_OCCURRENCE

OBSERVATION

Filtering Notes:

MEASUREMENT

Filtering Notes:

NOTE

Filtering Notes:

LOCATION

Filtering Notes:

CONDITION_OCCURRENCE

Filtering Notes:

Related content

Why `visit.date_started` for observation period start?

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