|
|
cohort : Entity
|
|
RESULT SCHEMA TABLE
|
|
|
care_site : Entity
|
|
CARE_SITE
Table Description
The CARE_SITE table contains a list of uniquely identified
institutional (physical or organizational) units where healthcare
delivery is practiced (offices, wards, hospitals, clinics, etc.).
ETL Conventions
Care site is a unique combination of location_id and
place_of_service_source_value. Care site does not take into account
the provider (human) information such a specialty. Many source data do
not make a distinction between individual and institutional providers.
The CARE_SITE table contains the institutional providers. If the
source, instead of uniquely identifying individual Care Sites, only
provides limited information such as Place of Service, generic or
“pooled” Care Site records are listed in the CARE_SITE table. There
can be hierarchical and business relationships between Care Sites. For
example, wards can belong to clinics or departments, which can in turn
belong to hospitals, which in turn can belong to hospital systems,
which in turn can belong to HMOs.The relationships between Care Sites
are defined in the FACT_RELATIONSHIP table.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
care_site_id
|
|
Assign an ID to each combination of a location and nature of the
site - the latter could be the Place of Service, name or another
characteristic in your source data.
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
care_site_name
|
The name of the care_site as it appears in the source data
|
|
varchar(255)
|
No
|
No
|
No
|
|
|
|
place_of_service_concept_id
|
This is a high-level way of characterizing a Care Site. Typically,
however, Care Sites can provide care in multiple settings
(inpatient, outpatient, etc.) and this granularity should be
reflected in the visit.
|
Choose the concept in the visit domain that best represents the
setting in which healthcare is provided in the Care Site. If most
visits in a Care Site are Inpatient, then the
place_of_service_concept_id should represent Inpatient. If
information is present about a unique Care Site (e.g. Pharmacy)
then a Care Site record should be created. Accepted
Concepts.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
location_id
|
The location_id from the LOCATION table representing the physical
location of the care_site.
|
|
integer
|
No
|
No
|
Yes
|
LOCATION
|
|
|
care_site_source_value
|
The identifier of the care_site as it appears in the source data.
This could be an identifier separate from the name of the
care_site.
|
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
place_of_service_source_value
|
|
Put the place of service of the care_site as it appears in the
source data.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
|
|
cdm_source : Entity
|
|
|
|
|
cohort_definition : Entity
|
|
RESULT SCHEMA TABLE
|
|
|
concept : Entity
|
|
CONCEPT
Table Description
The Standardized Vocabularies contains records, or Concepts, that
uniquely identify each fundamental unit of meaning used to express
clinical information in all domain tables of the CDM. Concepts are
derived from vocabularies, which represent clinical information across
a domain (e.g. conditions, drugs, procedures) through the use of codes
and associated descriptions. Some Concepts are designated Standard
Concepts, meaning these Concepts can be used as normative expressions
of a clinical entity within the OMOP Common Data Model and within
standardized analytics. Each Standard Concept belongs to one domain,
which defines the location where the Concept would be expected to
occur within data tables of the CDM.
Concepts can represent broad categories (like ‘Cardiovascular
disease’), detailed clinical elements (‘Myocardial infarction of the
anterolateral wall’) or modifying characteristics and attributes that
define Concepts at various levels of detail (severity of a disease,
associated morphology, etc.).
Records in the Standardized Vocabularies tables are derived from
national or international vocabularies such as SNOMED-CT, RxNorm, and
LOINC, or custom Concepts defined to cover various aspects of
observational data analysis.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
concept_id
|
A unique identifier for each Concept across all domains.
|
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
concept_name
|
An unambiguous, meaningful and descriptive name for the Concept.
|
|
varchar(255)
|
Yes
|
No
|
No
|
|
|
|
domain_id
|
A foreign key to the DOMAIN table
the Concept belongs to.
|
|
varchar(20)
|
Yes
|
No
|
Yes
|
DOMAIN
|
|
|
vocabulary_id
|
A foreign key to the VOCABULARY table
indicating from which source the Concept has been adapted.
|
|
varchar(20)
|
Yes
|
No
|
Yes
|
VOCABULARY
|
|
|
concept_class_id
|
The attribute or concept class of the Concept. Examples are
‘Clinical Drug’, ‘Ingredient’, ‘Clinical Finding’ etc.
|
|
varchar(20)
|
Yes
|
No
|
Yes
|
CONCEPT_CLASS
|
|
|
standard_concept
|
This flag determines where a Concept is a Standard Concept,
i.e. is used in the data, a Classification Concept, or a
non-standard Source Concept. The allowable values are ‘S’
(Standard Concept) and ‘C’ (Classification Concept), otherwise the
content is NULL.
|
|
varchar(1)
|
No
|
No
|
No
|
|
|
|
concept_code
|
The concept code represents the identifier of the Concept in the
source vocabulary, such as SNOMED-CT concept IDs, RxNorm RXCUIs
etc. Note that concept codes are not unique across vocabularies.
|
|
varchar(50)
|
Yes
|
No
|
No
|
|
|
|
valid_start_date
|
The date when the Concept was first recorded. The default value is
1-Jan-1970, meaning, the Concept has no (known) date of inception.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
valid_end_date
|
The date when the Concept became invalid because it was deleted or
superseded (updated) by a new concept. The default value is
31-Dec-2099, meaning, the Concept is valid until it becomes
deprecated.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
invalid_reason
|
Reason the Concept was invalidated. Possible values are D
(deleted), U (replaced with an update) or NULL when valid_end_date
has the default value.
|
|
varchar(1)
|
No
|
No
|
No
|
|
|
|
|
|
concept_ancestor : Entity
|
|
CONCEPT_ANCESTOR
Table Description
The CONCEPT_ANCESTOR table is designed to simplify observational
analysis by providing the complete hierarchical relationships between
Concepts. Only direct parent-child relationships between Concepts are
stored in the CONCEPT_RELATIONSHIP table. To determine higher level
ancestry connections, all individual direct relationships would have
to be navigated at analysis time. The CONCEPT_ANCESTOR table includes
records for all parent-child relationships, as well as
grandparent-grandchild relationships and those of any other level of
lineage. Using the CONCEPT_ANCESTOR table allows for querying for all
descendants of a hierarchical concept. For example, drug ingredients
and drug products are all descendants of a drug class ancestor.
This table is entirely derived from the CONCEPT, CONCEPT_RELATIONSHIP
and RELATIONSHIP tables.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
ancestor_concept_id
|
The Concept Id for the higher-level concept that forms the
ancestor in the relationship.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
descendant_concept_id
|
The Concept Id for the lower-level concept that forms the
descendant in the relationship.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
min_levels_of_separation
|
The minimum separation in number of levels of hierarchy between
ancestor and descendant concepts. This is an attribute that is
used to simplify hierarchic analysis.
|
|
integer
|
Yes
|
No
|
No
|
|
|
|
max_levels_of_separation
|
The maximum separation in number of levels of hierarchy between
ancestor and descendant concepts. This is an attribute that is
used to simplify hierarchic analysis.
|
|
integer
|
Yes
|
No
|
No
|
|
|
|
|
|
concept_class : Entity
|
|
CONCEPT_CLASS
Table Description
The CONCEPT_CLASS table is a reference table, which includes a list of
the classifications used to differentiate Concepts within a given
Vocabulary. This reference table is populated with a single record for
each Concept Class.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
concept_class_id
|
A unique key for each class.
|
|
varchar(20)
|
Yes
|
Yes
|
No
|
|
|
|
concept_class_name
|
The name describing the Concept Class, e.g. Clinical Finding,
Ingredient, etc.
|
|
varchar(255)
|
Yes
|
No
|
No
|
|
|
|
concept_class_concept_id
|
A Concept that represents the Concept Class.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
|
|
concept_relationship : Entity
|
|
CONCEPT_RELATIONSHIP
Table Description
The CONCEPT_RELATIONSHIP table contains records that define direct
relationships between any two Concepts and the nature or type of the
relationship. Each type of a relationship is defined in the
RELATIONSHIP table.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
concept_id_1
|
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
concept_id_2
|
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
relationship_id
|
The relationship between CONCEPT_ID_1 and CONCEPT_ID_2. Please see
the Vocabulary
Conventions. for more information.
|
|
varchar(20)
|
Yes
|
No
|
Yes
|
RELATIONSHIP
|
|
|
valid_start_date
|
The date when the relationship is first recorded.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
valid_end_date
|
The date when the relationship is invalidated.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
invalid_reason
|
Reason the relationship was invalidated. Possible values are ‘D’
(deleted), ‘U’ (updated) or NULL.
|
|
varchar(1)
|
No
|
No
|
No
|
|
|
|
|
|
concept_synonym : Entity
|
|
CONCEPT_SYNONYM
Table Description
The CONCEPT_SYNONYM table is used to store alternate names and
descriptions for Concepts.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
concept_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
concept_synonym_name
|
|
|
varchar(1000)
|
Yes
|
No
|
No
|
|
|
|
language_concept_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
|
|
condition_era : Entity
|
|
CONDITION_ERA
Table Description
A Condition Era is defined as a span of time when the Person is
assumed to have a given condition. Similar to Drug Eras, Condition
Eras are chronological periods of Condition Occurrence. Combining
individual Condition Occurrences into a single Condition Era serves
two purposes:
-
It allows aggregation of chronic conditions that require frequent
ongoing care, instead of treating each Condition Occurrence as an
independent event.
-
It allows aggregation of multiple, closely timed doctor visits for
the same Condition to avoid double-counting the Condition
Occurrences. For example, consider a Person who visits her Primary
Care Physician (PCP) and who is referred to a specialist. At a later
time, the Person visits the specialist, who confirms the PCP’s
original diagnosis and provides the appropriate treatment to resolve
the condition. These two independent doctor visits should be
aggregated into one Condition Era.
ETL Conventions
Each Condition Era corresponds to one or many Condition Occurrence
records that form a continuous interval. The condition_concept_id
field contains Concepts that are identical to those of the
CONDITION_OCCURRENCE table records that make up the Condition Era. In
contrast to Drug Eras, Condition Eras are not aggregated to contain
Conditions of different hierarchical layers. The SQl Script for
generating CONDITION_ERA records can be found here The
Condition Era Start Date is the start date of the first Condition
Occurrence. The Condition Era End Date is the end date of the last
Condition Occurrence. Condition Eras are built with a Persistence
Window of 30 days, meaning, if no occurrence of the same
condition_concept_id happens within 30 days of any one occurrence, it
will be considered the condition_era_end_date.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
condition_era_id
|
|
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
person_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
condition_concept_id
|
The Concept Id representing the Condition.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Condition
|
|
condition_era_start_date
|
The start date for the Condition Era constructed from the
individual instances of Condition Occurrences. It is the start
date of the very first chronologically recorded instance of the
condition with at least 31 days since any prior record of the same
Condition.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
condition_era_end_date
|
The end date for the Condition Era constructed from the individual
instances of Condition Occurrences. It is the end date of the
final continuously recorded instance of the Condition.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
condition_occurrence_count
|
The number of individual Condition Occurrences used to construct
the condition era.
|
|
integer
|
No
|
No
|
No
|
|
|
|
|
|
condition_occurrence : Entity
|
|
CONDITION_OCCURRENCE
Table Description
This table contains records of Events of a Person suggesting the
presence of a disease or medical condition stated as a diagnosis, a
sign, or a symptom, which is either observed by a Provider or reported
by the patient.
User Guide
Conditions are defined by Concepts from the Condition domain, which
form a complex hierarchy. As a result, the same Person with the same
disease may have multiple Condition records, which belong to the same
hierarchical family. Most Condition records are mapped from diagnostic
codes, but recorded signs, symptoms and summary descriptions also
contribute to this table. Rule out diagnoses should not be recorded in
this table, but in reality their negating nature is not always
captured in the source data, and other precautions must be taken when
when identifying Persons who should suffer from the recorded
Condition. Record all conditions as they exist in the source data. Any
decisions about diagnosis/phenotype definitions would be done through
cohort specifications. These cohorts can be housed in the COHORT table.
Conditions span a time interval from start to end, but are typically
recorded as single snapshot records with no end date. The reason is
twofold: (i) At the time of the recording the duration is not known
and later not recorded, and (ii) the Persons typically cease
interacting with the healthcare system when they feel better, which
leads to incomplete capture of resolved Conditions. The CONDITION_ERA table
addresses this issue. Family history and past diagnoses (‘history of’)
are not recorded in this table. Instead, they are listed in the OBSERVATION table.
Codes written in the process of establishing the diagnosis, such as
‘question of’ of and ‘rule out’, should not represented here. Instead,
they should be recorded in the OBSERVATION table,
if they are used for analyses. However, this information is not always
available.
ETL Conventions
Source codes and source text fields mapped to Standard Concepts of the
Condition Domain have to be recorded here.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
condition_occurrence_id
|
The unique key given to a condition record for a person. Refer to
the ETL for how duplicate conditions during the same visit were
handled.
|
Each instance of a condition present in the source data should be
assigned this unique key. In some cases, a person can have
multiple records of the same condition within the same visit. It
is valid to keep these duplicates and assign them individual,
unique, CONDITION_OCCURRENCE_IDs, though it is up to the ETL how
they should be handled.
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
person_id
|
The PERSON_ID of the PERSON for whom the condition is recorded.
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
condition_concept_id
|
The CONDITION_CONCEPT_ID field is recommended for primary use in
analyses, and must be used for network studies. This is the
standard concept mapped from the source value which represents a
condition
|
The CONCEPT_ID that the CONDITION_SOURCE_VALUE maps to. Only
records whose source values map to concepts with a domain of
“Condition” should go in this table. Accepted
Concepts.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Condition
|
|
condition_start_date
|
Use this date to determine the start date of the condition
|
Most often data sources do not have the idea of a start date for a
condition. Rather, if a source only has one date associated with a
condition record it is acceptable to use that date for both the
CONDITION_START_DATE and the CONDITION_END_DATE.
|
date
|
Yes
|
No
|
No
|
|
|
|
condition_start_datetime
|
|
If a source does not specify datetime the convention is to set the
time to midnight (00:00:0000)
|
datetime
|
No
|
No
|
No
|
|
|
|
condition_end_date
|
Use this date to determine the end date of the condition
|
Most often data sources do not have the idea of a start date for a
condition. Rather, if a source only has one date associated with a
condition record it is acceptable to use that date for both the
CONDITION_START_DATE and the CONDITION_END_DATE.
|
date
|
No
|
No
|
No
|
|
|
|
condition_end_datetime
|
|
If a source does not specify datetime the convention is to set the
time to midnight (00:00:0000)
|
datetime
|
No
|
No
|
No
|
|
|
|
condition_type_concept_id
|
This field can be used to determine the provenance of the
Condition record, as in whether the condition was from an EHR
system, insurance claim, registry, or other sources.
|
Choose the CONDITION_TYPE_CONCEPT_ID that best represents the
provenance of the record. Accepted
Concepts. A more detailed explanation of each Type
Concept can be found on the vocabulary
wiki.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Type Concept
|
|
condition_status_concept_id
|
This concept represents the point during the visit the diagnosis
was given (admitting diagnosis, final diagnosis), whether the
diagnosis was determined due to laboratory findings, if the
diagnosis was exclusionary, or if it was a preliminary diagnosis,
among others.
|
Choose the Concept in the Condition Status domain that best
represents the point during the visit when the diagnosis was
given. These can include admitting diagnosis, principal diagnosis,
and secondary diagnosis. Accepted
Concepts.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
Condition Status
|
|
stop_reason
|
The Stop Reason indicates why a Condition is no longer valid with
respect to the purpose within the source data. Note that a Stop
Reason does not necessarily imply that the condition is no longer
occurring.
|
This information is often not populated in source data and it is a
valid etl choice to leave it blank if the information does not
exist.
|
varchar(20)
|
No
|
No
|
No
|
|
|
|
provider_id
|
The provider associated with condition record, e.g. the provider
who made the diagnosis or the provider who recorded the symptom.
|
The ETL may need to make a choice as to which PROVIDER_ID to put
here. Based on what is available this may or may not be different
than the provider associated with the overall VISIT_OCCURRENCE
record, for example the admitting vs attending physician on an EHR
record.
|
integer
|
No
|
No
|
Yes
|
PROVIDER
|
|
|
visit_occurrence_id
|
The visit during which the condition occurred.
|
Depending on the structure of the source data, this may have to be
determined based on dates. If a CONDITION_START_DATE occurs within
the start and end date of a Visit it is a valid ETL choice to
choose the VISIT_OCCURRENCE_ID from the Visit that subsumes it,
even if not explicitly stated in the data. While not required, an
attempt should be made to locate the VISIT_OCCURRENCE_ID of the
CONDITION_OCCURRENCE record.
|
integer
|
No
|
No
|
Yes
|
VISIT_OCCURRENCE
|
|
|
visit_detail_id
|
The VISIT_DETAIL record during which the condition occurred. For
example, if the person was in the ICU at the time of the diagnosis
the VISIT_OCCURRENCE record would reflect the overall hospital
stay and the VISIT_DETAIL record would reflect the ICU stay during
the hospital visit.
|
Same rules apply as for the VISIT_OCCURRENCE_ID.
|
integer
|
No
|
No
|
Yes
|
VISIT_DETAIL
|
|
|
condition_source_value
|
This field houses the verbatim value from the source data
representing the condition that occurred. For example, this could
be an ICD10 or Read code.
|
This code is mapped to a Standard Condition Concept in the
Standardized Vocabularies and the original code is stored here for
reference.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
condition_source_concept_id
|
This is the concept representing the condition source value and
may not necessarily be standard. This field is discouraged from
use in analysis because it is not required to contain Standard
Concepts that are used across the OHDSI community, and should only
be used when Standard Concepts do not adequately represent the
source detail for the Condition necessary for a given analytic use
case. Consider using CONDITION_CONCEPT_ID instead to enable
standardized analytics that can be consistent across the network.
|
If the CONDITION_SOURCE_VALUE is coded in the source data using an
OMOP supported vocabulary put the concept id representing the
source value here.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
condition_status_source_value
|
This field houses the verbatim value from the source data
representing the condition status.
|
This information may be called something different in the source
data but the field is meant to contain a value indicating when and
how a diagnosis was given to a patient. This source value is
mapped to a standard concept which is stored in the
CONDITION_STATUS_CONCEPT_ID field.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
|
|
cost : Entity
|
|
COST
Table Description
The COST table captures records containing the cost of any medical
event recorded in one of the OMOP clinical event tables such as
DRUG_EXPOSURE, PROCEDURE_OCCURRENCE, VISIT_OCCURRENCE, VISIT_DETAIL,
DEVICE_OCCURRENCE, OBSERVATION or MEASUREMENT.
Each record in the cost table account for the amount of money
transacted for the clinical event. So, the COST table may be used to
represent both receivables (charges) and payments (paid), each
transaction type represented by its COST_CONCEPT_ID. The
COST_TYPE_CONCEPT_ID field will use concepts in the Standardized
Vocabularies to designate the source (provenance) of the cost data.
A reference to the health plan information in the PAYER_PLAN_PERIOD
table is stored in the record for information used for the
adjudication system to determine the persons benefit for the
clinical event.
User Guide
When dealing with summary costs, the cost of the goods or services
the provider provides is often not known directly, but derived from
the hospital charges multiplied by an average cost-to-charge ratio.
ETL Conventions
One cost record is generated for each response by a payer. In a
claims databases, the payment and payment terms reported by the
payer for the goods or services billed will generate one cost
record. If the source data has payment information for more than one
payer (i.e. primary insurance and secondary insurance payment for
one entity), then a cost record is created for each reporting payer.
Therefore, it is possible for one procedure to have multiple cost
records for each payer, but typically it contains one or no record
per entity. Payer reimbursement cost records will be identified by
using the PAYER_PLAN_ID field. Drug costs are composed of ingredient
cost (the amount charged by the wholesale distributor or
manufacturer), the dispensing fee (the amount charged by the
pharmacy and the sales tax).
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
cost_id
|
|
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
cost_event_id
|
|
|
integer
|
Yes
|
No
|
No
|
|
|
|
cost_domain_id
|
|
|
varchar(20)
|
Yes
|
No
|
Yes
|
DOMAIN
|
|
|
cost_type_concept_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
currency_concept_id
|
|
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
total_charge
|
|
|
float
|
No
|
No
|
No
|
|
|
|
total_cost
|
|
|
float
|
No
|
No
|
No
|
|
|
|
total_paid
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_by_payer
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_by_patient
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_patient_copay
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_patient_coinsurance
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_patient_deductible
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_by_primary
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_ingredient_cost
|
|
|
float
|
No
|
No
|
No
|
|
|
|
paid_dispensing_fee
|
|
|
float
|
No
|
No
|
No
|
|
|
|
payer_plan_period_id
|
|
|
integer
|
No
|
No
|
No
|
|
|
|
amount_allowed
|
|
|
float
|
No
|
No
|
No
|
|
|
|
revenue_code_concept_id
|
|
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
revenue_code_source_value
|
Revenue codes are a method to charge for a class of procedures
and conditions in the U.S. hospital system.
|
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
drg_concept_id
|
|
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
drg_source_value
|
Diagnosis Related Groups are US codes used to classify hospital
cases into one of approximately 500 groups.
|
|
varchar(3)
|
No
|
No
|
No
|
|
|
|
|
|
death : Entity
|
|
DEATH
Table Description
The death domain contains the clinical event for how and when a Person
dies. A person can have up to one record if the source system contains
evidence about the Death, such as: Condition in an administrative
claim, status of enrollment into a health plan, or explicit record in
EHR data.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
person_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
death_date
|
The date the person was deceased.
|
If the precise date include day or month is not known or not
allowed, December is used as the default month, and the last day
of the month the default day.
|
date
|
Yes
|
No
|
No
|
|
|
|
death_datetime
|
|
If not available set time to midnight (00:00:00)
|
datetime
|
No
|
No
|
No
|
|
|
|
death_type_concept_id
|
This is the provenance of the death record, i.e., where it came
from. It is possible that an administrative claims database would
source death information from a government file so do not assume
the Death Type is the same as the Visit Type, etc.
|
Use the type concept that be reflects the source of the death
record. Accepted
Concepts. A more detailed explanation of each Type
Concept can be found on the vocabulary
wiki.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
Type Concept
|
|
cause_concept_id
|
This is the Standard Concept representing the Person’s cause of
death, if available.
|
There is no specified domain for this concept, just choose the
Standard Concept Id that best represents the person’s cause of
death.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
cause_source_value
|
|
If available, put the source code representing the cause of death
here.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
cause_source_concept_id
|
|
If the cause of death was coded using a Vocabulary present in the
OMOP Vocabularies put the CONCEPT_ID representing the cause of
death here.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
|
|
device_exposure : Entity
|
|
DEVICE_EXPOSURE
Table Description
The Device domain captures information about a person’s exposure to a
foreign physical object or instrument which is used for diagnostic or
therapeutic purposes through a mechanism beyond chemical action.
Devices include implantable objects (e.g. pacemakers, stents,
artificial joints), medical equipment and supplies (e.g. bandages,
crutches, syringes), other instruments used in medical procedures
(e.g. sutures, defibrillators) and material used in clinical care
(e.g. adhesives, body material, dental material, surgical material).
User Guide
The distinction between Devices or supplies and Procedures are
sometimes blurry, but the former are physical objects while the latter
are actions, often to apply a Device or supply.
ETL Conventions
Source codes and source text fields mapped to Standard Concepts of the
Device Domain have to be recorded here.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
device_exposure_id
|
The unique key given to records a person’s exposure to a foreign
physical object or instrument.
|
Each instance of an exposure to a foreign object or device present
in the source data should be assigned this unique key.
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
person_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
device_concept_id
|
The DEVICE_CONCEPT_ID field is recommended for primary use in
analyses, and must be used for network studies. This is the
standard concept mapped from the source concept id which
represents a foreign object or instrument the person was exposed
to.
|
The CONCEPT_ID that the DEVICE_SOURCE_VALUE maps to.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Device
|
|
device_exposure_start_date
|
Use this date to determine the start date of the device record.
|
Valid entries include a start date of a procedure to implant a
device, the date of a prescription for a device, or the date of
device administration.
|
date
|
Yes
|
No
|
No
|
|
|
|
device_exposure_start_datetime
|
|
This is not required, though it is in v6. If a source does not
specify datetime the convention is to set the time to midnight
(00:00:0000)
|
datetime
|
No
|
No
|
No
|
|
|
|
device_exposure_end_date
|
The DEVICE_EXPOSURE_END_DATE denotes the day the device exposure
ended for the patient, if given.
|
Put the end date or discontinuation date as it appears from the
source data or leave blank if unavailable.
|
date
|
No
|
No
|
No
|
|
|
|
device_exposure_end_datetime
|
|
If a source does not specify datetime the convention is to set the
time to midnight (00:00:0000)
|
datetime
|
No
|
No
|
No
|
|
|
|
device_type_concept_id
|
You can use the TYPE_CONCEPT_ID to denote the provenance of the
record, as in whether the record is from administrative claims or
EHR.
|
Choose the drug_type_concept_id that best represents the
provenance of the record, for example whether it came from a
record of a prescription written or physician administered drug. Accepted
Concepts. A more detailed explanation of each Type
Concept can be found on the vocabulary
wiki.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Type Concept
|
|
unique_device_id
|
This is the Unique Device Identification (UDI-DI) number for
devices regulated by the FDA, if given.
|
For medical devices that are regulated by the FDA, a Unique Device
Identification (UDI) is provided if available in the data source
and is recorded in the UNIQUE_DEVICE_ID field.
|
varchar(255)
|
No
|
No
|
No
|
|
|
|
production_id
|
This is the Production Identifier (UDI-PI) portion of the Unique
Device Identification.
|
|
varchar(255)
|
No
|
No
|
No
|
|
|
|
quantity
|
|
|
integer
|
No
|
No
|
No
|
|
|
|
provider_id
|
The Provider associated with device record, e.g. the provider who
wrote the prescription or the provider who implanted the device.
|
The ETL may need to make a choice as to which PROVIDER_ID to put
here. Based on what is available this may or may not be different
than the provider associated with the overall VISIT_OCCURRENCE
record.
|
integer
|
No
|
No
|
Yes
|
PROVIDER
|
|
|
visit_occurrence_id
|
The Visit during which the device was prescribed or given.
|
To populate this field device exposures must be explicitly
initiated in the visit.
|
integer
|
No
|
No
|
Yes
|
VISIT_OCCURRENCE
|
|
|
visit_detail_id
|
The Visit Detail during which the device was prescribed or given.
|
To populate this field device exposures must be explicitly
initiated in the visit detail record.
|
integer
|
No
|
No
|
Yes
|
VISIT_DETAIL
|
|
|
device_source_value
|
This field houses the verbatim value from the source data
representing the device exposure that occurred. For example, this
could be an NDC or Gemscript code.
|
This code is mapped to a Standard Device Concept in the
Standardized Vocabularies and the original code is stored here for
reference.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
device_source_concept_id
|
This is the concept representing the device source value and may
not necessarily be standard. This field is discouraged from use in
analysis because it is not required to contain Standard Concepts
that are used across the OHDSI community, and should only be used
when Standard Concepts do not adequately represent the source
detail for the Device necessary for a given analytic use case.
Consider using DEVICE_CONCEPT_ID instead to enable standardized
analytics that can be consistent across the network.
|
If the DEVICE_SOURCE_VALUE is coded in the source data using an
OMOP supported vocabulary put the concept id representing the
source value here.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
unit_concept_id
|
UNIT_SOURCE_VALUES should be mapped to a Standard Concept in the
Unit domain that best represents the unit as given in the source
data.
|
There is no standardization requirement for units associated with
DEVICE_CONCEPT_IDs, however, it is the responsibility of the ETL
to choose the most plausible unit. If there is no unit associated
with a Device record, set to NULL.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
Unit
|
|
unit_source_value
|
This field houses the verbatim value from the source data
representing the unit of the Device. For example, blood
transfusions are considered devices and can be given in mL
quantities.
|
This code is mapped to a Standard Condition Concept in the
Standardized Vocabularies and the original code is stored here for
reference. Using the blood transfusion example, blood transfusion
is represented by the DEVICE_CONCEPT_ID and the unit (mL) would be
housed in the UNIT_SOURCE_VALUE and mapped to a standard concept
in the unit domain.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
unit_source_concept_id
|
This is the concept representing the UNIT_SOURCE_VALUE and may not
necessarily be standard. This field is discouraged from use in
analysis because it is not required to contain Standard Concepts
that are used across the OHDSI community, and should only be used
when Standard Concepts do not adequately represent the source
detail for the Unit necessary for a given analytic use case.
Consider using UNIT_CONCEPT_ID instead to enable standardized
analytics that can be consistent across the network.
|
If the UNIT_SOURCE_VALUE is coded in the source data using an OMOP
supported vocabulary put the concept id representing the source
value here.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
|
|
domain : Entity
|
|
DOMAIN
Table Description
The DOMAIN table includes a list of OMOP-defined Domains the Concepts
of the Standardized Vocabularies can belong to. A Domain defines the
set of allowable Concepts for the standardized fields in the CDM
tables. For example, the “Condition” Domain contains Concepts that
describe a condition of a patient, and these Concepts can only be
stored in the condition_concept_id field of the CONDITION_OCCURRENCE
and CONDITION_ERA tables. This reference table is populated with a
single record for each Domain and includes a descriptive name for the
Domain.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
domain_id
|
A unique key for each domain.
|
|
varchar(20)
|
Yes
|
Yes
|
No
|
|
|
|
domain_name
|
The name describing the Domain, e.g. Condition, Procedure,
Measurement etc.
|
|
varchar(255)
|
Yes
|
No
|
No
|
|
|
|
domain_concept_id
|
A Concept representing the Domain Concept the DOMAIN record
belongs to.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
|
|
dose_era : Entity
|
|
DOSE_ERA
Table Description
A Dose Era is defined as a span of time when the Person is assumed to
be exposed to a constant dose of a specific active ingredient.
ETL Conventions
Dose Eras will be derived from records in the DRUG_EXPOSURE table and
the Dose information from the DRUG_STRENGTH table using a standardized
algorithm. Dose Form information is not taken into account. So, if the
patient changes between different formulations, or different
manufacturers with the same formulation, the Dose Era is still
spanning the entire time of exposure to the Ingredient.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
dose_era_id
|
|
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
person_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
drug_concept_id
|
The Concept Id representing the specific drug ingredient.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Drug
|
|
unit_concept_id
|
The Concept Id representing the unit of the specific drug
ingredient.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Unit
|
|
dose_value
|
The numeric value of the dosage of the drug_ingredient.
|
|
float
|
Yes
|
No
|
No
|
|
|
|
dose_era_start_date
|
The date the Person started on the specific dosage, with at least
31 days since any prior exposure.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
dose_era_end_date
|
|
The date the Person was no longer exposed to the dosage of the
specific drug ingredient. An era is ended if there are 31 days or
more between dosage records.
|
date
|
Yes
|
No
|
No
|
|
|
|
|
|
drug_era : Entity
|
|
DRUG_ERA
Table Description
A Drug Era is defined as a span of time when the Person is assumed to
be exposed to a particular active ingredient. A Drug Era is not the
same as a Drug Exposure: Exposures are individual records
corresponding to the source when Drug was delivered to the Person,
while successive periods of Drug Exposures are combined under certain
rules to produce continuous Drug Eras.
ETL Conventions
The SQL script for generating DRUG_ERA records can be found here.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
drug_era_id
|
|
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
person_id
|
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
drug_concept_id
|
The Concept Id representing the specific drug ingredient.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Drug
|
|
drug_era_start_date
|
|
The Drug Era Start Date is the start date of the first Drug
Exposure for a given ingredient, with at least 31 days since the
previous exposure.
|
date
|
Yes
|
No
|
No
|
|
|
|
drug_era_end_date
|
|
The Drug Era End Date is the end date of the last Drug Exposure.
The End Date of each Drug Exposure is either taken from the field
drug_exposure_end_date or, as it is typically not available,
inferred using the following rules: For pharmacy prescription
data, the date when the drug was dispensed plus the number of days
of supply are used to extrapolate the End Date for the Drug
Exposure. Depending on the country-specific healthcare system,
this supply information is either explicitly provided in the
day_supply field or inferred from package size or similar
information. For Procedure Drugs, usually the drug is administered
on a single date (i.e., the administration date). A standard
Persistence Window of 30 days (gap, slack) is permitted between
two subsequent such extrapolated DRUG_EXPOSURE records to be
considered to be merged into a single Drug Era.
|
date
|
Yes
|
No
|
No
|
|
|
|
drug_exposure_count
|
|
|
integer
|
No
|
No
|
No
|
|
|
|
gap_days
|
|
The Gap Days determine how many total drug-free days are observed
between all Drug Exposure events that contribute to a DRUG_ERA
record. It is assumed that the drugs are “not stockpiled” by the
patient, i.e. that if a new drug prescription or refill is
observed (a new DRUG_EXPOSURE record is written), the remaining
supply from the previous events is abandoned. The difference
between Persistence Window and Gap Days is that the former is the
maximum drug-free time allowed between two subsequent
DRUG_EXPOSURE records, while the latter is the sum of actual
drug-free days for the given Drug Era under the above assumption
of non-stockpiling.
|
integer
|
No
|
No
|
No
|
|
|
|
|
|
drug_exposure : Entity
|
|
DRUG_EXPOSURE
Table Description
This table captures records about the exposure to a Drug ingested or
otherwise introduced into the body. A Drug is a biochemical substance
formulated in such a way that when administered to a Person it will
exert a certain biochemical effect on the metabolism. Drugs include
prescription and over-the-counter medicines, vaccines, and
large-molecule biologic therapies. Radiological devices ingested or
applied locally do not count as Drugs.
User Guide
The purpose of records in this table is to indicate an exposure to a
certain drug as best as possible. In this context a drug is defined as
an active ingredient. Drug Exposures are defined by Concepts from the
Drug domain, which form a complex hierarchy. As a result, one
DRUG_SOURCE_CONCEPT_ID may map to multiple standard concept ids if it
is a combination product. Records in this table represent
prescriptions written, prescriptions dispensed, and drugs administered
by a provider to name a few. The DRUG_TYPE_CONCEPT_ID can be used to
find and filter on these types. This table includes additional
information about the drug products, the quantity given, and route of
administration.
ETL Conventions
Information about quantity and dose is provided in a variety of
different ways and it is important for the ETL to provide as much
information as possible from the data. Depending on the provenance of
the data fields may be captured differently i.e. quantity for drugs
administered may have a separate meaning from quantity for
prescriptions dispensed. If a patient has multiple records on the same
day for the same drug or procedures the ETL should not de-dupe them
unless there is probable reason to believe the item is a true data
duplicate. Take note on how to handle refills for prescriptions
written.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
drug_exposure_id
|
The unique key given to records of drug dispensings or
administrations for a person. Refer to the ETL for how duplicate
drugs during the same visit were handled.
|
Each instance of a drug dispensing or administration present in
the source data should be assigned this unique key. In some cases,
a person can have multiple records of the same drug within the
same visit. It is valid to keep these duplicates and assign them
individual, unique, DRUG_EXPOSURE_IDs, though it is up to the ETL
how they should be handled.
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
person_id
|
The PERSON_ID of the PERSON for whom the drug dispensing or
administration is recorded. This may be a system generated code.
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
drug_concept_id
|
The DRUG_CONCEPT_ID field is recommended for primary use in
analyses, and must be used for network studies. This is the
standard concept mapped from the source concept id which
represents a drug product or molecule otherwise introduced to the
body. The drug concepts can have a varying degree of information
about drug strength and dose. This information is relevant in the
context of quantity and administration information in the
subsequent fields plus strength information from the DRUG_STRENGTH
table, provided as part of the standard vocabulary download.
|
The CONCEPT_ID that the DRUG_SOURCE_VALUE maps to. The concept id
should be derived either from mapping from the source concept id
or by picking the drug concept representing the most amount of
detail you have. Records whose source values map to standard
concepts with a domain of Drug should go in this table. When the
Drug Source Value of the code cannot be translated into Standard
Drug Concept IDs, a Drug exposure entry is stored with only the
corresponding SOURCE_CONCEPT_ID and DRUG_SOURCE_VALUE and a
DRUG_CONCEPT_ID of 0. The Drug Concept with the most detailed
content of information is preferred during the mapping process.
These are indicated in the CONCEPT_CLASS_ID field of the Concept
and are recorded in the following order of precedence: ‘Branded
Pack’, ‘Clinical Pack’, ‘Branded Drug’, ‘Clinical Drug’, ‘Branded
Drug Component’, ‘Clinical Drug Component’, ‘Branded Drug Form’,
‘Clinical Drug Form’, and only if no other information is
available ‘Ingredient’. Note: If only the drug class is known, the
DRUG_CONCEPT_ID field should contain 0. Accepted
Concepts.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Drug
|
|
drug_exposure_start_date
|
Use this date to determine the start date of the drug record.
|
Valid entries include a start date of a prescription, the date a
prescription was filled, or the date on which a Drug
administration was recorded. It is a valid ETL choice to use the
date the drug was ordered as the DRUG_EXPOSURE_START_DATE.
|
date
|
Yes
|
No
|
No
|
|
|
|
drug_exposure_start_datetime
|
|
This is not required, though it is in v6. If a source does not
specify datetime the convention is to set the time to midnight
(00:00:0000)
|
datetime
|
No
|
No
|
No
|
|
|
|
drug_exposure_end_date
|
The DRUG_EXPOSURE_END_DATE denotes the day the drug exposure ended
for the patient.
|
If this information is not explicitly available in the data, infer
the end date using the following methods:
1. Start first
with duration or days supply using the calculation drug start date
+ days supply -1 day. 2. Use quantity divided by daily dose that
you may obtain from the sig or a source field (or assumed daily
dose of 1) for solid, indivisibile, drug products. If quantity
represents ingredient amount, quantity divided by daily dose *
concentration (from drug_strength) drug concept id tells you the
dose form. 3. If it is an administration record, set drug end date
equal to drug start date. If the record is a written prescription
then set end date to start date + 29. If the record is a
mail-order prescription set end date to start date + 89. The end
date must be equal to or greater than the start date. Ibuprofen
20mg/mL oral solution concept tells us this is oral solution.
Calculate duration as quantity (200 example) * daily dose (5mL)
/concentration (20mg/mL) 200*5/20 = 50 days. Examples
by dose form
|
date
|
Yes
|
No
|
No
|
|
|
|
drug_exposure_end_datetime
|
|
This is not required, though it is in v6. If a source does not
specify datetime the convention is to set the time to midnight
(00:00:0000)
|
datetime
|
No
|
No
|
No
|
|
|
|
verbatim_end_date
|
This is the end date of the drug exposure as it appears in the
source data, if it is given
|
Put the end date or discontinuation date as it appears from the
source data or leave blank if unavailable.
|
date
|
No
|
No
|
No
|
|
|
|
drug_type_concept_id
|
You can use the TYPE_CONCEPT_ID to delineate between prescriptions
written vs. prescriptions dispensed vs. medication history
vs. patient-reported exposure, etc.
|
Choose the drug_type_concept_id that best represents the
provenance of the record, for example whether it came from a
record of a prescription written or physician administered drug. Accepted
Concepts. A more detailed explanation of each Type
Concept can be found on the vocabulary
wiki.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Type Concept
|
|
stop_reason
|
The reason a person stopped a medication as it is represented in
the source. Reasons include regimen completed, changed, removed,
etc. This field will be retired in v6.0.
|
This information is often not populated in source data and it is a
valid etl choice to leave it blank if the information does not
exist.
|
varchar(20)
|
No
|
No
|
No
|
|
|
|
refills
|
This is only filled in when the record is coming from a
prescription written this field is meant to represent intended
refills at time of the prescription.
|
|
integer
|
No
|
No
|
No
|
|
|
|
quantity
|
|
To find the dose form of a drug the RELATIONSHIP table can be used
where the relationship_id is ‘Has dose form’. If liquid, quantity
stands for the total amount dispensed or ordered of ingredient in
the units given by the drug_strength table. If the unit from the
source data does not align with the unit in the DRUG_STRENGTH
table the quantity should be converted to the correct unit given
in DRUG_STRENGTH. For clinical drugs with fixed dose forms
(tablets etc.) the quantity is the number of
units/tablets/capsules prescribed or dispensed (can be partial,
but then only 1/2 or 1/3, not 0.01). Clinical drugs with divisible
dose forms (injections) the quantity is the amount of ingredient
the patient got. For example, if the injection is 2mg/mL but the
patient got 80mL then quantity is reported as 160. Quantified
clinical drugs with divisible dose forms (prefilled syringes), the
quantity is the amount of ingredient similar to clinical drugs.
Please see how
to calculate drug dose for more
information.
|
float
|
No
|
No
|
No
|
|
|
|
days_supply
|
|
Days supply of the drug. This should be the verbatim days_supply
as given on the prescription. If the drug is physician
administered use duration end date if given or set to 1 as default
if duration is not available.
|
integer
|
No
|
No
|
No
|
|
|
|
sig
|
This is the verbatim instruction for the drug as written by the
provider.
|
Put the written out instructions for the drug as it is verbatim in
the source, if available.
|
varchar(MAX)
|
No
|
No
|
No
|
|
|
|
route_concept_id
|
|
The standard CONCEPT_ID that the ROUTE_SOURCE_VALUE maps to in the
route domain.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
Route
|
|
lot_number
|
|
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
provider_id
|
The Provider associated with drug record, e.g. the provider who
wrote the prescription or the provider who administered the drug.
|
The ETL may need to make a choice as to which PROVIDER_ID to put
here. Based on what is available this may or may not be different
than the provider associated with the overall VISIT_OCCURRENCE
record, for example the ordering vs administering physician on an
EHR record.
|
integer
|
No
|
No
|
Yes
|
PROVIDER
|
|
|
visit_occurrence_id
|
The Visit during which the drug was prescribed, administered or
dispensed.
|
To populate this field drug exposures must be explicitly initiated
in the visit.
|
integer
|
No
|
No
|
Yes
|
VISIT_OCCURRENCE
|
|
|
visit_detail_id
|
The VISIT_DETAIL record during which the drug exposure occurred.
For example, if the person was in the ICU at the time of the drug
administration the VISIT_OCCURRENCE record would reflect the
overall hospital stay and the VISIT_DETAIL record would reflect
the ICU stay during the hospital visit.
|
Same rules apply as for the VISIT_OCCURRENCE_ID.
|
integer
|
No
|
No
|
Yes
|
VISIT_DETAIL
|
|
|
drug_source_value
|
This field houses the verbatim value from the source data
representing the drug exposure that occurred. For example, this
could be an NDC or Gemscript code.
|
This code is mapped to a Standard Drug Concept in the Standardized
Vocabularies and the original code is stored here for reference.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
drug_source_concept_id
|
This is the concept representing the drug source value and may not
necessarily be standard. This field is discouraged from use in
analysis because it is not required to contain Standard Concepts
that are used across the OHDSI community, and should only be used
when Standard Concepts do not adequately represent the source
detail for the Drug necessary for a given analytic use case.
Consider using DRUG_CONCEPT_ID instead to enable standardized
analytics that can be consistent across the network.
|
If the DRUG_SOURCE_VALUE is coded in the source data using an OMOP
supported vocabulary put the concept id representing the source
value here.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
route_source_value
|
This field houses the verbatim value from the source data
representing the drug route.
|
This information may be called something different in the source
data but the field is meant to contain a value indicating when and
how a drug was given to a patient. This source value is mapped to
a standard concept which is stored in the ROUTE_CONCEPT_ID field.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
dose_unit_source_value
|
This field houses the verbatim value from the source data
representing the dose unit of the drug given.
|
This information may be called something different in the source
data but the field is meant to contain a value indicating the unit
of dosage of drug given to the patient. This
is an older column and will be deprecated in an upcoming version.
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
|
|
drug_strength : Entity
|
|
DRUG_STRENGTH
Table Description
The DRUG_STRENGTH table contains structured content about the amount
or concentration and associated units of a specific ingredient
contained within a particular drug product. This table is supplemental
information to support standardized analysis of drug utilization.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
drug_concept_id
|
The Concept representing the Branded Drug or Clinical Drug Product.
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
ingredient_concept_id
|
The Concept representing the active ingredient contained within
the drug product.
|
Combination Drugs will have more than one record in this table,
one for each active Ingredient.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
amount_value
|
The numeric value or the amount of active ingredient contained
within the drug product.
|
|
float
|
No
|
No
|
No
|
|
|
|
amount_unit_concept_id
|
The Concept representing the Unit of measure for the amount of
active ingredient contained within the drug product.
|
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
numerator_value
|
The concentration of the active ingredient contained within the
drug product.
|
|
float
|
No
|
No
|
No
|
|
|
|
numerator_unit_concept_id
|
The Concept representing the Unit of measure for the concentration
of active ingredient.
|
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
denominator_value
|
The amount of total liquid (or other divisible product, such as
ointment, gel, spray, etc.).
|
|
float
|
No
|
No
|
No
|
|
|
|
denominator_unit_concept_id
|
The Concept representing the denominator unit for the
concentration of active ingredient.
|
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
box_size
|
The number of units of Clinical Branded Drug or Quantified
Clinical or Branded Drug contained in a box as dispensed to the
patient.
|
|
integer
|
No
|
No
|
No
|
|
|
|
valid_start_date
|
The date when the Concept was first recorded. The default value is
1-Jan-1970.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
valid_end_date
|
The date when then Concept became invalid.
|
|
date
|
Yes
|
No
|
No
|
|
|
|
invalid_reason
|
Reason the concept was invalidated. Possible values are D
(deleted), U (replaced with an update) or NULL when valid_end_date
has the default value.
|
|
varchar(1)
|
No
|
No
|
No
|
|
|
|
|
|
episode : Entity
|
|
EPISODE
Table Description
The EPISODE table aggregates lower-level clinical events
(VISIT_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE,
DEVICE_EXPOSURE) into a higher-level abstraction representing
clinically and analytically relevant disease phases,outcomes and
treatments. The EPISODE_EVENT table connects qualifying clinical
events (VISIT_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE,
DEVICE_EXPOSURE) to the appropriate EPISODE entry. For example cancers
including their development over time, their treatment, and final
resolution.
User Guide
Valid Episode Concepts belong to the ‘Episode’ domain. For cancer
episodes please see [article], for non-cancer episodes please see
[article]. If your source data does not have all episodes that are
relevant to the therapeutic area, write only those you can easily
derive from the data. It is understood that that table is not
currently expected to be comprehensive.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
episode_id
|
A unique identifier for each Episode.
|
|
integer
|
Yes
|
Yes
|
No
|
|
|
|
person_id
|
The PERSON_ID of the PERSON for whom the episode is recorded.
|
|
integer
|
Yes
|
No
|
Yes
|
PERSON
|
|
|
episode_concept_id
|
The EPISODE_CONCEPT_ID represents the kind abstraction related to
the disease phase, outcome or treatment.
|
Choose a concept in the Episode domain that best represents the
ongoing disease phase, outcome, or treatment. Please see [article]
for cancers and [article] for non-cancers describing how these are
defined. Accepted
Concepts
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Episode
|
|
episode_start_date
|
The date when the Episode beings.
|
Please see [article] for how to define an Episode start date.
|
date
|
Yes
|
No
|
No
|
|
|
|
episode_start_datetime
|
The date and time when the Episode begins.
|
|
datetime
|
No
|
No
|
No
|
|
|
|
episode_end_date
|
The date when the instance of the Episode is considered to have
ended.
|
Please see [article] for how to define an Episode end date.
|
date
|
No
|
No
|
No
|
|
|
|
episode_end_datetime
|
The date when the instance of the Episode is considered to have
ended.
|
|
datetime
|
No
|
No
|
No
|
|
|
|
episode_parent_id
|
Use this field to find the Episode that subsumes the given Episode
record. This is used in the case that an Episode are nested into
each other.
|
If there are multiple nested levels to how Episodes are
represented, the EPISODE_PARENT_ID can be used to record this
relationship.
|
integer
|
No
|
No
|
No
|
|
|
|
episode_number
|
For sequences of episodes, this is used to indicate the order the
episodes occurred. For example, lines of treatment could be
indicated here.
|
Please see [article] for the details of how to count episodes.
|
integer
|
No
|
No
|
No
|
|
|
|
episode_object_concept_id
|
A Standard Concept representing the disease phase, outcome, or
other abstraction of which the episode consists. For example, if
the EPISODE_CONCEPT_ID is treatment
regimen then the
EPISODE_OBJECT_CONCEPT_ID should contain the chemotherapy regimen
concept, like Afatinib
monotherapy.
|
Episode entries from the ‘Disease Episode’ concept class should
have an episode_object_concept_id that comes from the Condition
domain. Episode entries from the ‘Treatment Episode’ concept class
should have an episode_object_concept_id that scome from the
‘Procedure’ domain or ‘Regimen’ concept class.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Procedure, Regimen
|
|
episode_type_concept_id
|
This field can be used to determine the provenance of the Episode
record, as in whether the episode was from an EHR system,
insurance claim, registry, or other sources.
|
Choose the EPISODE_TYPE_CONCEPT_ID that best represents the
provenance of the record. Accepted
Concepts. A more detailed explanation of each Type
Concept can be found on the vocabulary
wiki.
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Type Concept
|
|
episode_source_value
|
The source code for the Episdoe as it appears in the source data.
This code is mapped to a Standard Condition Concept in the
Standardized Vocabularies and the original code is stored here for
reference.
|
|
varchar(50)
|
No
|
No
|
No
|
|
|
|
episode_source_concept_id
|
A foreign key to a Episode Concept that refers to the code used in
the source.
|
Given that the Episodes are user-defined it is unlikely that there
will be a Source Concept available. If that is the case then set
this field to zero.
|
integer
|
No
|
No
|
Yes
|
CONCEPT
|
|
|
|
|
episode_event : Entity
|
|
EPISODE_EVENT
Table Description
The EPISODE_EVENT table connects qualifying clinical events (such as
CONDITION_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE,
MEASUREMENT) to the appropriate EPISODE entry. For example, linking
the precise location of the metastasis (cancer modifier in
MEASUREMENT) to the disease episode.
User Guide
This connecting table is used instead of the FACT_RELATIONSHIP table
for linking low-level events to abstracted Episodes.
ETL Conventions
Some episodes may not have links to any underlying clinical events.
For such episodes, the EPISODE_EVENT table is not populated.
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
episode_id
|
Use this field to link the EPISODE_EVENT record to its EPISODE.
|
Put the EPISODE_ID that subsumes the EPISODE_EVENT record here.
|
integer
|
Yes
|
No
|
Yes
|
EPISODE
|
|
|
event_id
|
This field is the primary key of the linked record in the
database. For example, if the Episode Event is a Condition
Occurrence, then the CONDITION_OCCURRENCE_ID of the linked
record goes in this field.
|
Put the primary key of the linked record here.
|
integer
|
Yes
|
No
|
No
|
|
|
|
episode_event_field_concept_id
|
This field is the CONCEPT_ID that identifies which table the
primary key of the linked record came from.
|
Put the CONCEPT_ID that identifies which table and field the
EVENT_ID came from. Accepted
Concepts
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
Metadata
|
|
|
|
fact_relationship : Entity
|
|
FACT_RELATIONSHIP
Table Description
The FACT_RELATIONSHIP table contains records about the relationships
between facts stored as records in any table of the CDM.
Relationships can be defined between facts from the same domain, or
different domains. Examples of Fact Relationships include: Person
relationships (parent-child), care site
relationships (hierarchical organizational structure of facilities
within a health system), indication relationship (between drug
exposures and associated conditions), usage relationships (of
devices during the course of an associated procedure), or facts
derived from one another (measurements derived from an associated
specimen).
ETL Conventions
All relationships are directional, and each relationship is
represented twice symmetrically within the FACT_RELATIONSHIP table.
For example, two persons if person_id = 1 is the mother of person_id
= 2 two records are in the FACT_RELATIONSHIP table (all strings in
fact concept_id records in the Concept table: - Person, 1, Person,
2, parent of - Person, 2, Person, 1, child of
|
CDM Field
|
User Guide
|
ETL Conventions
|
Datatype
|
Required
|
Primary Key
|
Foreign Key
|
FK Table
|
FK Domain
|
|
domain_concept_id_1
|
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
fact_id_1
|
|
|
integer
|
Yes
|
No
|
No
|
|
|
|
domain_concept_id_2
|
|
|
integer
|
Yes
|
No
|
Yes
|
CONCEPT
|
|
|
fact_id_2
|
|
|
integer
|
Yes
|
No
|
No
|
|
|
link