What is observation_event_id and obs_event_field_concept_id?

SELVA_MUTHU_KUMARAN · June 24, 2019, 1:28pm

Hello Everyone,

We have survey data stored in Excel. So, I am currently transforming my question and response pairs as records in observation table.

Can you please help me understand the above two fields (obs_event_id and obs_event_concept_id) from observation table with a simple example?

In addition, I would also like to know whether

Qualifier value - What does this field mean? What value goes into this? Can you please help?
Observation_source_value - I am planning to store our column names (our data is stored in excel so, I make use of the column names to kind of help us further in retrieving the data )

Thanks
Selva

SELVA_MUTHU_KUMARAN · June 24, 2019, 6:23am

@DTorok @MPhilofsky - Can you please help me with this?

DTorok · June 24, 2019, 12:42pm

Not sure where you are getting the column name obs_event_id and Obs_event_concept_id. But for an explanation of the various columns see https://github.com/OHDSI/CommonDataModel/wiki/OBSERVATION

SELVA_MUTHU_KUMARAN · June 24, 2019, 1:29pm

@DTorok - Apologies for not giving the accurate field name in my post. But can I check with you on few things listed below?

Am I right to understand that “observation_event_id” will be same as “visit_occurrence_id” ? I mean patients were questioned when they visited the hospital. So observation_event_id and visit_occurrence_id will be the same. Am I right?
As we have survey data stored in “observation” table, I believe we should be using “survey_conduct” table. In that case, Survey_conduct_id will usually be visit_occurrence_id or procedure_occurrence_id. Am I right?
Can you please help me understand observation_event_field_concept_id with an example? I did refer the link that you sent me, but couldn’t get an idea of what it is

Thanks
Selva

MPhilofsky · June 24, 2019, 2:30pm

@SELVA_MUTHU_KUMARAN,

I have not used the observation_event_field_concept_id field or any of the new columns from v6. I think this addition was proposed by @mgurley and the Oncology WG. @Christian_Reich & @clairblacketer will also know more about this.

Qualifier value - This is not a required field. Example: a person has an allergy recorded in the observation_concept_id = 4048476, allergy to cat dander. qualifier_concept_id = 4087703, severe. Most data don’t have qualifiers.

Observation_source_value - You can put whatever you want in this field. Anything and everything. Whatever helps the persons using the data. Excel sheet name and column name are good. Others put the code and text string as it appears in the source.

mgurley · June 24, 2019, 2:59pm

Sorry, this is not part of the Oncology WG Oncology CDM Extension. None of our structural changes have made it into an official OMOP CDM release yet. I believe these fields were added as part of the support for Survey data. See here:

github.com/OHDSI/CommonDataModel

SURVEY data in OMOP CDM - updated

opened 09:35AM - 23 Nov 17 UTC

closed 07:19PM - 25 Oct 18 UTC

ColinOrr2006

Accepted Proposal

# Adding Patient Reported Outcome data to CDM - Requestor: Colin Orr and Cath…erine Kerr, ICON plc - Revising party: Joshua Ransom, Anna Corning, Emelly Rusli, Rayhnuma Ahmed, Aaron Stern; SHYFT Analytics - Discussion: [here](http://forums.ohdsi.org/t/linking-patient-survey-responses-together/2413/19) ## Background ICON plc is currently engaged in a project with [[http://www.ichom.org/|ICHOM (International Consortium for Health Outcomes Measurement]]. ICHOM's mission is to unlock the potential of value-based healthcare by defining global Standard Sets of outcome measures that really matter to patients for the most relevant medical conditions and by driving adoption and reporting of these measures worldwide. ICHOM brings together patient representatives, clinician leaders, and registry leaders from all over the world to develop Standard Sets, comprehensive yet parsimonious sets of outcomes and case-mix variables for specific medical conditions that ICHOM recommends all providers track. Each Standard Set focuses on patient-centered results, and provides an internationally-agreed upon method for measuring each of these outcomes. ICHOM believes that standardized outcomes measurement will open up new possibilities to compare performance globally, allow clinicians to learn from each other, and rapidly improve the care provided to patients. ICHOM Standard Sets include baseline conditions and risk factors to enable meaningful case-mix adjustment globally, ensuring that comparisons of outcomes will take into account the differences in patient populations across not just providers, but also countries and regions. They also include high-level treatment variables to allow stratification of outcomes by major treatment types. A comprehensive data dictionary, as well as scoring guides for patient-reported outcomes is provided for each Standard Set. ## Proposal ICON plc is developing a platform to ingest, store and analyse the patient outcome measures and is using the OMOP Common Data Model to store the data. The current CDM satisfies many of the requirements, but there are some gaps, specifically: - We need to store data relating to each Patient Reported Outcome (PRO) questionnaire that is completed by a patient. Examples of this type of data are; timestamp of when the questionnaire was completed, did the patient complete it with assistance, role of person who completed the questionnaire, etc. We also need to store the attributes related to the timing of the survey in relation to the treatment the patient received - for example, 'baseline', or 'six month follow-up'. This is additional contextual data that allows us to compare outcomes over time. To store this data, we propose introducing a new SURVEY table. Each row in the table represents an instance of a completed survey and serves to link a number of survey questions and answers together. Individual questions and their answers are stored as name-value pairs in the OBSERVATIONS table. The OBSERVATIONS table requires some additional columns in order to maintain the relationship with the patient questionnaire (SURVEY) as described below. ### SURVEY table The SURVEY table is used to store an instance of a completed survey or questionnaire. It captures details of the individual questionnaire such as who completed it, when it was completed and to which patient treatment or visit it relates to (if any). Each SURVEY has a SURVEY_CONCEPT_ID, a concept in the CONCEPT table identifying the questionnaire e.g. EQ5D, VR12, SF12. Each questionnaire should exist in the CONCEPT table. Each SURVEY can be optionally related to a specific patient visit in order to link it to a specific patient assessment or treatment. Field | Required | Type | Description -- | -- | -- | -- SURVEY_OCCURRENCE_ID | Yes | integer | Unique identifier for each completed survey SURVEY_CONCEPT_ID | Yes | integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of survey. PERSON_ID | Yes | integer | A foreign key identifier to the Person in the PERSON table about whom the survey was completed VISIT_OCCURRENCE_ID | No | integer | A foreign key to the visit_occurrence table during which the survey was completed RESPONSE_TO_VISIT_OCCURRENCE_ID | No | | A foreign key to the visit in the visit_occurrence table during which treatment was carried out that relates to this survey. SURVEY_START_DATE | No | date | Date on which the survey was started SURVEY_START_DATETIME | No | Timestamp | Date and time on which the survey was started SURVEY_END_DATE | Yes | Date | Date on which the survey was completed SURVEY_END_DATETIME | No | Timestamp | Date and time on which the survey was completed ASSISTED_CONCEPT_ID | No | integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies indicating whether the survey was completed with assistance or not (yes / No) ASSISTED__SOURCE_VALUE | No | varchar(100) | Source value representing whether patient required assistance to complete the survey. Example: “Completed without assistance”, ”Completed with assistance”. RESPONDENT_TYPE_ CONCEPT_ID | No | integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the respondent type. Example: Research Associate, Patient RESPONDENT_SOURCE_VALUE | No | varchar(100) | Source code representing role of person who completed the survey. TIMING_CONCEPT_ID | No | integer | A foreign key that refers to a timing Concept identifier in the Standardized Vocabularies Example: 3 month follow-up, 6 month follow-u, … TIMING_SOURCE_VALUE | No | varchar(100) | Text string representing the timing of the survey. Example: Baseline, 6-month follow-up COLLECTION_METHOD_ CONCEPT_ID | No | varchar(10) | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the data collection method (e.g. Paper, Telephone, Electronic Questionnaire) COLLECTION_METHOD_SOURCE_VALUE | No | varchar(100) | The collection method as it appears in the source data. SURVEY_SOURCE_VALUE | No | varchar(100) | The survey name/title as it appears in the source data. SURVEY_SOURCE_IDENTIFIER | No | varchar(100) | Unique identifier for each completed survey in source system VALIDATED_SURVEY_ CONCEPT_ID | No | Integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the validation status of the survey. SURVEY_VERSION_NUMBER | No | Varchar2(20) | Version number of the questionnaire / survey used. PROVIDER_ID | | | ### OBSERVATION table Patient responses to survey questions are stored in the OBSERVATION table. Each record in the OBSERVATION table represents a single question/response pair and is linked to a specific SURVEY / questionnaire in the SURVEY_OCCURRENCE_ID. Each response record is the response to a specific question identified by the OBSERVATION_CONCEPT_ID. This concept ID is a unique question contained in the CONCEPT table. An individual survey question can have multiple responses to a question (e.g. which of these items relate to you, a, b, c ,…?). Each response is stored as a separate record in the OBSERVATION table. The question / answer observation record is linked to the patient questionnaire used for collecting the data using two new fields in the OBSERVATION table; DOMAIN_ID and DOMAIN_OCCURRENCE_ID. DOMAIN_ID for any survey related observations contains the text ‘Survey’ and DOMAIN_OCCURRENCE_ID contains the SURVEY_OCCURRENCE_ID of the specific survey. This domain construct can be used for other observation groupings. The OBSERVATION table can also store survey scoring results. Many validated PRO questionnaires have scoring algorithms (many of which proprietary) that return an overall patient score based on the answers provided.. Survey scores are identified by their OBSERVATION_CONCEPT_ID and are linked back to the scored survey using the same DOMAIN construct described. In the name/value pair model, the name (question) is stored as OBSERVATION_CONCEPT_ID and the value (answer) is stored as OBSERVATION_AS_CONCEPT_ID where the answer is categorical and is defined as a concept in the concept table, OBSERVATION_AS_NUMBER where the answer is numeric, OBSERVATION_AS_STRING where the answer is a free text string or OBSERVATION_AS_DATETIME. **_Amendments required to the OBSERVATION table are as follows_** Change | Field | Required | Type | Description -- | -- | -- | -- | -- New | DOMAIN_OCCURRENCE_ID | No | integer | A foreign key to SURVEY table New | DOMAIN_ID | No | | ‘Survey’ New | VALUE_AS_DATETIME | No | Timestamp | The observation result stored as a datetime value. This is applicable to observations where the result is expressed as a point in time. ### Other Considerations - Extensions to the concept table include the survey and response data that is not currently contained in the standard libraries. All custom extensions to the concept table have been stored in the negative address space so as not to conflict with the currently defined standard. These extensions are not included in the definition of this proposal but should be considered for future work. - There is no formal definition of the relationship between a questionnaire/survey and the questions presented on that survey. There is an implicit relationship created when survey/response data is stored. If an explicit relationship is required, this can be achieved using the FACT_RELATIONSHIP table. ### Use Cases The example below describes the data to be stored for a question on the HOOSPS (Hip Disability and Osteoarthritis Outcome Score) patient questionnaire. The question asks the degree of difficulty in descending stairs due to the patient's hip problem. The patient answers "Moderate". The CONCEPT table contains domain data for the survey HOOSPS, question (HPS1) plus all the potential values that a patient can respond with. ### CONCEPT table – example CONCEPT _ID | CONCEPT _NAME | DOMAIN _ID | VOCABULARY_ID | CONCEPT_ CLASS_ID | STANDARD _CONCEPT | CONCEPT _CODE -- | -- | -- | -- | -- | -- | -- -2020 | HPS1 | Metadata | Domain | Domain | | ICHOM generated -2021 | None | HPS1 | ICHOM Observation | PRO Measure | S | 0 -2022 | Mild | HPS1 | ICHOM Observation | PRO Measure | S | 1 -2023 | Moderate | HPS1 | ICHOM Observation | PRO Measure | S | 2 -2024 | Severe | HPS1 | ICHOM Observation | PRO Measure | S | 3 -2025 | Extreme | HPS1 | ICHOM Observation | PRO Measure | S | 4 -3501 | HOOSPS | Metadata | ICHOM Survey | Domain | | ICHOM generated The patient response is captured as a code 2 (in this instance) in the questionnaire. The CONCEPT_ID is determined by finding a match in the concept table for the code (2) for the specific question (identified by HPS1) in column DOMAIN_ID and the response value (2) in the column CONCEPT_CODE. ### SURVEY table - example Column | Value | Comment -- | -- | -- SURVEY_OCCURRENCE_ID | 19073 | SURVEY_CONCEPT_ID | -3501 | Concept for HOOSPS survey PERSON_ID | 21405 | VISIT_OCCURRENCE_ID | | RESEPONSE_TO_VISIT_OCCURRENCE_ID | 13403 | SURVEY_START_DATE | | SURVEY_END_DATE | 2016-07-14 | ASSISTED_CONCEPT_ID | -3601 | Concept for "Completed without assistance" | | ASSISTED_SOURCE_VALUE | Complete w/o assistance | RESPONDENT_TYPE_CONCEPT_ID | 3611 | Concept for "Patient-reported" RESPONDENT_SOURCE_VALUE | P-REP | Source system value for "Patient-reported" TIMING_CONCEPT_ID | -3621 | Concept for "BASELINE" timing TIMING_SOURCE_VALUE | Baseline | COLLECTION_METHOD_CONCEPT_ID | -3631 | Concept for "Electronic questionnaire" COLLECTION_METHOD_SOURCE_VALUE | E-QUEST | Source system value for "Electronic questionnaire" SURVEY_SOURCE_VALUE | HOOSPS | SURVEY_SOURCE_IDENTIFIER | HS001234 | VALIDATED_SURVEY_CONCEPT_ID | -3701 | Concept for "Validated survey" SURVEY_VERSION_NUMBER | | PROVIDER_ID | | ### OBSERVATION table - example Column | Value | Comment -- | -- | -- OBSERVATION_ID | 794657 | PERSON_ID | 21405 | OBSERVATION_CONCEPT_ID | -2020 | Concept for HPS1 OBSERVATION_DATE | 2016-07-14 | OBSERVATION_DATETIME | | OBSERVATION_TYPE_CONCEPT_ID | XXX | CONCEPT_ID to indicate PRO survey response’ VALUE_AS_CONCEPT_ID | -2023 | Concept for "Moderate" VALUE _AS_STRING | | VALUE _AS_NUMBER | | VALUE_AS_DATETIME | | PROVIDER_ID | | VISIT_OCCURRENCE_ID | | OBSERVATION_SOURCE_VALUE | degree of difficulty in …. | OBSERVATION_SOURCE_CONCEPT_ID | | DOMAIN_ID | Survey | DOMAIN_OCCURRENCE_ID | 19073 |

@ColinOrr2006 might be able to elucidate if I am correct.

clairblacketer · June 24, 2019, 7:24pm

@mgurley is correct here. Initially those columns were added to support the storing of survey responses in the OBSERVATION table. The idea was that the survey itself would be captured in the SURVEY_CONDUCT table and the responses would be captured in the OBSERVATION table. The fields OBS_EVENT_ID and OBS_EVENT_CONCEPT_ID were added in order to link the two records between the two tables. Using the example of a survey, the response in OBSERVATION would have OBS_EVENT_ID = SURVEY_CONDUCT_ID and OBS_EVENT_CONCEPT_ID = 1147833 which is the concept id that codes for SURVEY_CONDUCT.SURVEY_CONDUCT_ID. This then associates the two records in the two different tables using concept ids.

SELVA_MUTHU_KUMARAN · August 28, 2019, 9:55am

Hello @clairblacketer,

Can you please confirm my understanding below?

Survey data is stored in the observation table. Am I right?
Questions are stored as concepts under “observation_concept_id” field and answers are stored under “value_as_concept_id/string/number” fields. Am I right?
Then why do we need Survey_conduct table? What is the use of Survey_conduct table? Is it just store the meta data of survey like when was the survey conducted, what is the survey id (few questionnaires have a standard Id’s) etc? .
If point 3 is right, then “observation.event_id” = “survey_conduct.surbey_conduct_id”. In this case, the ids are nothing but the Survey_ID? For example, EQ5E etc.

Can you please help?