documentation:next_cdm:add_survey
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
documentation:next_cdm:add_survey [2017/06/19 14:28] cmkerr [Adding PROM data to CDM] |
documentation:next_cdm:add_survey [2017/08/01 18:08] (current) clairblacketer |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ===== Adding PROM data to CDM ===== | ===== Adding PROM data to CDM ===== | ||
| + | ===Proposals now housed on github=== | ||
| + | [[https://github.com/OHDSI/CommonDataModel/issues/90|link to github]] | ||
| + | |||
| * Requestor: Colin Orr and Catherine Kerr, ICON plc | * Requestor: Colin Orr and Catherine Kerr, ICON plc | ||
| - | * Revising party: Revising party: Joshua Ransom, Anna Corning, Emelly Rusli, Rayhnuma Ahmed, Aaron Stern; SHYFT Analytics | + | * Revising party: Joshua Ransom, Anna Corning, Emelly Rusli, Rayhnuma Ahmed, Aaron Stern; SHYFT Analytics |
| * Discussion: [[http://forums.ohdsi.org/t/linking-patient-survey-responses-together/2413/19|here]] | * Discussion: [[http://forums.ohdsi.org/t/linking-patient-survey-responses-together/2413/19|here]] | ||
| Line 17: | Line 20: | ||
| ==== Proposal ==== | ==== Proposal ==== | ||
| ICON plc is developing a platform to ingest, store and analyse the 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: | ICON plc is developing a platform to ingest, store and analyse the 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 PROM (Patient_reported Outcome Measure) questionnaire that is completed by a patient. 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 observations together in the OBSERVATION table as well as providing additional information about the survey itself. | + | |
| - | - The patient responses are stored as key-value pairs in the OBSERVATION table, but we need to be able to group responses together according to the survey they belong to. We also need to know the timing of the observation in relation to the treatment the patient received - for example, 'baseline', or 'six month followup', so that we can compare outcomes. We propose adding three new columns to the OBSERVATION table, described below. | + | * We need to store data relating to each PROM (Patient_reported Outcome Measure) questionnaire that is completed by a patient. 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 observations together in the RESPONSE table as well as providing additional information about the survey itself. |
| - | - Patient responses will be stored as concepts in the OBSERVATION table. We require an extension to the concept table to include the ICHOM Data Dictionaries for each Standard Set. This requires new concepts for each data point in each Standard Set AND a concept for each allowable value for each data point. The ICHOM vocabulary will grow with each Standard Set that is defined by ICHOM. Each Standard Set is likely to generate 500 to 1,000 concepts and the objective is for 50 Standard Sets, thus requiring in the order of 50,000 new concepts. | + | * The patient responses are stored as key-value pairs in the RESPONSE table, but we need to be able to group responses together according to the survey they belong to. We also need to know the timing of the observation in relation to the treatment the patient received - for example, 'baseline', or 'six month followup', so that we can compare outcomes. We propose adding the SURVEY and RESPONSE tables to fit these requirements. |
| + | * Patient responses will be stored in the RESPONSE table as either: \\ RESPONSE_AS_CONCEPT_ID, RESPONSE_AS_NUMBER, RESPONSE_AS_STRING, and/or RESPONSE_AS_DATETIME. We require an extension to the concept table to include the following domain/vocabulary additions: | ||
| + | |||
| + | ^ Domain ^ Vocabulary ^ | ||
| + | | SURVEY | LOINC (PhenX and EQ-5D are both captured within this existing vocabulary), OMOP Custom (including ICHOM?) \\ *each survey included may require end user license agreements | | ||
| + | | QUESTION | LOINC (PhenX and EQ-5D are both captured within this existing vocabulary), OMOP Custom (including ICHOM?) \\ *each survey included may require end user license agreements | | ||
| + | |||
| + | * QUESTION_CONCEPT_ID and QUESTION_SOURCE_VALUE may be used to record individual questions or groupings of questions that represent a survey Section (these relationships could be managed within the FACT_RELATIONSHIP table as well) | ||
| + | * Extensions to the concept table will include the ICHOM Data Dictionaries for each Standard Set. This requires new concepts for each data point in each Standard Set AND a concept for each allowable value for each data point. The ICHOM vocabulary will grow with each Standard Set that is defined by ICHOM. Each Standard Set is likely to generate 500 to 1,000 concepts and the objective is for 50 Standard Sets, thus requiring in the order of 50,000 new concepts. | ||
| | | ||
| === SURVEY table === | === SURVEY table === | ||
| - | ^ Field ^ Required ^ Type ^ Description ^ | + | (other potential table name options: PRO, PATIENT_REPORTED_OUTCOME, PROM, others?) |
| - | | SURVEY_ID | Yes | integer | Unique identifier for each completed survey | | + | ^ Field \\ //Proposed revision// ^ Required ^ Type ^ Description ^ Field \\ //Reference from |
| - | | SURVEY_CONCEPT_ID | Yes | integer | A foreign key that refers to a survey Concept identifier in the Standardized Vocabularies | | + | original proposal, if revised// ^ |
| - | | PERSON_ID | Yes | integer | A foreign key identifier to the Person in the PERSON table about whom the survey was completed | | + | | SURVEY_ \\ OCCURRENCE_ID | Yes | integer | Unique identifier for each completed survey | SURVEY_ID | |
| - | | VISIT_OCCURRENCE_ID | No | integer | A foreign key to the visit in the visit table during which the the treatment was carried out that relates to this survey | | + | | SURVEY_ \\ CONCEPT_ID | Yes | integer | A foreign key that refers to a survey Concept identifier in the Standardized Vocabularies | | |
| - | | SURVEY_DATE | Yes | date | Date on which the survey was completed | | + | | PERSON_ID | Yes | integer | A foreign key identifier to the Person in the PERSON table about whom the survey was completed | | |
| - | | ASSISTED_IND_CONCEPT_ID | No | integer | A foreign key that refers to a data source Concept identifier in the Standardized Vocabularies | | + | | VISIT_ \\ OCCURRENCE_ID | No | integer | A foreign key to the visit in the visit table during which the the treatment was carried out that relates to this survey | | |
| + | | SURVEY_DATE | Yes | date | Date on which the survey was completed | | | ||
| + | | ASSISTED_ \\ CONCEPT_ID | No | integer | A foreign key that refers to a data source Concept identifier in the Standardized Vocabularies \\ Example: \\ Yes = concept_id 45877994; LOINC concept_code LA33-6 \\ No = concept_id 45878245; LOINC concept_code LA32-8 | ASSISTED_IND \\ _CONCEPT_ID | | ||
| | ASSISTED_IND | No | varchar(10) | ICHOM code representing whether patient required assistance to complete the survey - 1=Completed without assistance, 2=Completed with assistance | | | ASSISTED_IND | No | varchar(10) | ICHOM code representing whether patient required assistance to complete the survey - 1=Completed without assistance, 2=Completed with assistance | | ||
| - | | DATA_SOURCE_CONCEPT_ID | No | integer | A foreign key that refers to a data source Concept identifier in the Standardized Vocabularies | | + | | SURVEY_RECORDER \\ _CONCEPT_ID | No | integer | A foreign key that refers to a data source Concept identifier in the Standardized Vocabularies \\ Example: \\ Research Associate = concept_id 4074477; SNOMED concept_code 224614003 \\ Patient = concept_id 4023409; SNOMED concept_code 116154003 | DATA_SOURCE \\ _CONCEPT_ID | |
| - | | DATA_SOURCE | No | varchar(10) | ICHOM code representing role of person who completed the survey - e.g. 1=Administrative, 2=Clinician, 3=Patient-reported | | + | | SURVEY_RECORDER \\ _SOURCE_VALUE | No | varchar(10) | ICHOM code representing role of person who completed the survey - e.g. 1=Administrative, 2=Clinician, 3=Patient-reported | DATA_SOURCE | |
| - | | TIMING_CONCEPT_ID | No | integer | A foreign key that refers to a timing Concept identifier in the Standardized Vocabularies | | + | | TIMING_ \\ CONCEPT_ID | No | integer | A foreign key that refers to a timing Concept identifier in the Standardized Vocabularies \\ Example: \\ 3 month follow-up = concept_id 44789369; SNOMED obs concept_code 200521000000107 | | |
| - | | TIMING_SOURCE_VALUE | No | varchar(100) | Text string representing the timing of the survey - e.g. 'BASELINE' | | + | | TIMING_ \\ SOURCE_VALUE | No | varchar(100) | Text string representing the timing of the survey - e.g. 'BASELINE' | | |
| - | | COLLECTION_METHOD_CONCEPT_ID | No | varchar(10) | A foreign key that refers to a collection method Concept identifier in the Standardized Vocabularies | | + | | COLLECTION_ \\ METHOD_CONCEPT_ID | No | varchar(10) | A foreign key that refers to a collection method Concept identifier in the Standardized Vocabularies \\ Example: \\ Telephone Reported = concept_id 4084141; obs SNOMED concept_code 281313006 | | |
| - | | COLLECTION_METHOD | No | varchar(10) | ICHOM code representing method of capturing responses in survey - e.g. 1=Paper, 2=Telephone, 3=Electronic Questionnaire | | + | | COLLECTION_METHOD | No | varchar(10) | The collection type as it appears in the source data.This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is stored here for reference. \\ Example: \\ ICHOM code representing method of capturing responses in survey - e.g. 1=Paper, 2=Telephone, 3=Electronic Questionnaire | | |
| - | | DURATION | No | varchar(50) | Time taken to complete survey HH:MM:SS | | + | | DURATION | No | varchar(50) | Time taken to complete survey HH:MM:SS | | |
| - | | SURVEY_SOURCE_VALUE | No | varchar(100) | Unique identifier for each completed survey in source system | | + | | SURVEY_ \\ SOURCE_VALUE | No | varchar(100) | The survey name/title as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is stored here for reference. | SURVEY_NAME | |
| - | | SURVEY_NAME | No | varchar(50) | Name of the survey - e.g. HOOSPS, EQ5D-3L, VR12 | | + | | SURVEY_ \\ SOURCE_IDENTIFIER | No | varchar(100) | Unique identifier for each completed survey in source system | SURVEY_ \\ SOURCE_VALUE | |
| + | | VALIDATED_ \\ SURVEY_ \\ CONCEPT_ID | No | Integer | A foreign key that refers to a data source Concept identifier in the Standardized Vocabularies \\ Example: \\ Yes = concept_id 45877994; LOINC concept_code LA33-6 \\ No = concept_id 45878245; LOINC concept_code LA32-8 | | | ||
| + | | VALIDATED_SURVEY \\ _SOURCE_VALUE | No | varchar(50) | The validated survey identifier as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is stored here for reference. | | | ||
| - | === OBSERVATION table - new columns === | + | === RESPONSE table === |
| - | ^ Field ^ Required ^ Type ^ Description ^ | + | ^ Field ^ Required ^ Type ^ Description ^ |
| - | | SURVEY_ID | No | integer | A foreign key to the survey in the SURVEY table to which the observation belongs | | + | | RESPONSE_OCCURRENCE_ID | Yes | integer | Unique identifier for each response. | |
| - | | TIMING_CONCEPT_ID | No | integer | A foreign key that refers to a timing Concept identifier in the Standardized Vocabularies | | + | | PERSON_ID | Yes | integer | A foreign key identifier to the Person in the PERSON table about whom the response was recorded. | |
| - | | TIMING_SOURCE_VALUE | No | varchar(100) | Text string representing the timing of the survey - e.g. 'BASELINE' | | + | | SURVEY_OCCURRENCE_ID | Yes | integer | A foreign key to SURVEY table about which survey the question and response occurred. | |
| - | + | | QUESTION_CONCEPT_ID | Yes | integer | A foreign key that refers to a question Concept identifier in the Standardized Vocabularies | | |
| - | Note: The TIMING columns are required in the OBSERVATION table as well as the SURVEY table because many observations are collected by clinicians and administrators and do not have an associated SURVEY record. | + | | QUESTION_SOURCE_VALUE | No | varchar(255) | The question as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is, stored here for reference. | |
| + | | RESPONSE_DATE | Yes | date | Date on which the response was recorded | | ||
| + | | RESPONSE_DATETIME | No | datetime | Date and time on which the response was recorded | | ||
| + | | RESPONSE_AS_CONCEPT_ID | Yes | integer | Foreign key that refers to a response Concept identifier in the Standardized Vocabularies | | ||
| + | | RESPONSE _AS_STRING | No | varchar(255) | The response stored as a string. This is applicable to questions where the result is expressed as verbatim text. | | ||
| + | | RESPONSE _AS_NUMBER | No | float | The response stored as a number. This is applicable to questions where the result is expressed as a numeric value. | | ||
| + | | RESPONSE_AS_DATETIME | No | datetime | The response stored as a datetime. This is applicable to questions where the result is expressed as a historical date/time. | | ||
| + | | RESPONSE_RANGE_LOW | No | varchar(50) | The lowest value of the range of responses for the question. | | ||
| + | | RESPONSE_RANGE_HIGH | No | varchar(50) | The highest value of the range of responses for the question | | ||
| ==== Use Cases ==== | ==== Use Cases ==== | ||
| Line 53: | Line 76: | ||
| === CONCEPT table - example === | === CONCEPT table - example === | ||
| - | ^ CONCEPT_ID ^ CONCEPT_NAME ^ DOMAIN_ID ^ VOCABULARY_ID ^ CONCEPT_CLASS_ID ^ STANDARD_CONCEPT ^ CONCEPT_CODE ^ | + | ^ CONCEPT_ID ^ CONCEPT _NAME ^ DOMAIN_ID ^ VOCABULARY_ID ^ CONCEPT_ CLASS_ID ^ STANDARD _CONCEPT ^ CONCEPT _CODE ^ |
| | 2020 | HPS1 | Metadata | Domain | Domain | | ICHOM generated | | | 2020 | HPS1 | Metadata | Domain | Domain | | ICHOM generated | | ||
| | 2021 | None | HPS1 | ICHOM Observation | PRO Measure | S | 0 | | | 2021 | None | HPS1 | ICHOM Observation | PRO Measure | S | 0 | | ||
| Line 66: | Line 89: | ||
| ^ Column ^ Value ^ Comment ^ | ^ Column ^ Value ^ Comment ^ | ||
| - | | SURVEY_ID | 19073 | | | + | | SURVEY_OCCURRENCE_ID | 19073 | | |
| | SURVEY_CONCEPT_ID | 3501 | Concept for HOOSPS survey | | | SURVEY_CONCEPT_ID | 3501 | Concept for HOOSPS survey | | ||
| | PERSON_ID | 21405 | | | | PERSON_ID | 21405 | | | ||
| - | | VISIT_OCCURRENCE_ID | 13403 | ID of visit during which treatment was carried out | | + | | VISIT_OCCURRENCE_ID | 13403 | | |
| | SURVEY_DATE | 2016-07-14 | | | | SURVEY_DATE | 2016-07-14 | | | ||
| - | | ASSISTED_IND_CONCEPT_ID | 3601 | Concept for "Completed without assistance" | | + | | ASSISTANCE_CONCEPT_ID | 3601 | Concept for "Completed without assistance" | |
| - | | ASSISTED_IND | 1 | ICHOM value for "Completed without assistance" | | + | | ASSISTANCE_SOURCE_VALUE | 1 | ICHOM value for "Completed without assistance" | |
| - | | DATA_SOURCE_CONCEPT_ID | 3611 | Concept for "Patient-reported" | | + | | SURVEY_RECORDER_CONCEPT_ID | 3611 | Concept for "Patient-reported" | |
| - | | DATA_SOURCE | 3 | ICHOM value for "Patient-reported" | | + | | SURVEY_RECORDER_SOURCE_VALUE | 3 | ICHOM value for "Patient-reported" | |
| | TIMING_CONCEPT_ID | 3621 | Concept for "BASELINE" timing | | | TIMING_CONCEPT_ID | 3621 | Concept for "BASELINE" timing | | ||
| | TIMING_SOURCE_VALUE | BASELINE | | | | TIMING_SOURCE_VALUE | BASELINE | | | ||
| | COLLECTION_METHOD_CONCEPT_ID | 3631 | Concept for "Electronic questionnaire" | | | COLLECTION_METHOD_CONCEPT_ID | 3631 | Concept for "Electronic questionnaire" | | ||
| - | | COLLECTION_METHOD | 3 | ICHOM value for "Electronic questionnaire" | | + | | COLLECTION_METHOD_SOURCE_VALUE | 3 | ICHOM value for "Electronic questionnaire" | |
| - | | DURATION | 00:07:45 | Patient took 7 minutes and 45 seconds to complete online survey | | + | | DURATION_TIME | 00:07:45 | Patient took 7 minutes and 45 seconds to complete online survey | |
| - | | SURVEY_SOURCE_VALUE | HS001234 | | | + | | SURVEY_SOURCE_VALUE | HOOSPS | | |
| - | | SURVEY_NAME | HOOSPS | | | + | | SURVEY_SOURCE_IDENTIFIER | HS001234 | | |
| + | | VALIDATED_SURVEY_CONCEPT_ID | 3701 | Concept for "Validated survey" | | ||
| + | | VALIDATED_SURVEY_SOURCE_VALUE | 1 | ICHOM value for "Validated Survey" | | ||
| - | === OBSERVATION table - example === | + | === RESPONSE table - example === |
| ^ Column ^ Value ^ Comment ^ | ^ Column ^ Value ^ Comment ^ | ||
| - | | OBSERVATION_ID | 794657 | | | + | | RESPONSE_OCCURRENCE_ID | 794657 | | |
| | PERSON_ID | 21405 | | | | PERSON_ID | 21405 | | | ||
| - | | OBSERVATION_CONCEPT_ID | 2020 | Concept for HPS1 | | + | | SURVEY_OCCURRENCE_ID | 19073 | | |
| - | | OBSERVATION_DATE | 2016-07-14 | | | + | | QUESTION_CONCEPT_ID | 2020 | Concept for HPS1 | |
| - | | OBSERVATION_DATETIME | | | | + | | QUESTION_SOURCE_VALUE | HPS1 | | |
| - | | OBSERVATION_TYPE_CONCEPT_ID | 1001 | Concept for 'PRO Measure' | | + | | RESPONSE_DATE | 2016-07-14 | | |
| - | | VALUE_AS_NUMBER | | | | + | | RESPONSE_DATETIME | | | |
| - | | VALUE_AS_STRING | | | | + | | RESPONSE_AS_CONCEPT_ID | 2023 | Concept for "Moderate" | |
| - | | VALUE_AS_CONCEPT_ID | 2023 | Concept for "Moderate" | | + | | RESPONSE _AS_STRING | | | |
| - | | QUALIFIER_CONCEPT_ID | | | | + | | RESPONSE _AS_NUMBER | | | |
| - | | UNIT_CONCEPT_ID | | | | + | | RESPONSE_AS_DATETIME | | | |
| - | | PROVIDER_ID | | | | + | | RESPONSE_RANGE_LOW | 0 | | |
| - | | VISIT_OCCURRENCE_ID | 13403 | | | + | | RESPONSE_RANGE_HIGH | 4 | | |
| - | | OBSERVATION_SOURCE_VALUE | | | | + | |
| - | | OBSERVATION_SOURCE_CONCEPT_ID | | | | + | |
| - | | UNIT_SOURCE_VALUE | | | | + | |
| - | | QUALIFIER_SOURCE_VALUE | | | | + | |
| - | | SURVEY_ID | 19073 | | | + | |
| - | | TIMING_CONCEPT_ID | 3621 | | | + | |
| - | | TIMING_SOURCE_VALUE | BASELINE | | | + | |
documentation/next_cdm/add_survey.1497882532.txt.gz · Last modified: 2017/06/19 14:28 by cmkerr
Page Tools
