ETL-specific form for those who perform ETL or have an OMOP CDM instance at their disposal. Please fill it for each data source you have as we will use this information to prioritize the vocabularies we work on.
Filling the forms should take you around 10 minutes. We are asking about the vocabularies you use, what you think about their quality, challenges you encountered and what we can do to improve the content and the process of distribution and update.
A link to the video from the recent Community Call for more detail:
The survey is due February 23rd. The results of the survey are absolutely crucial for further Vocabularies development.
Please give us your thoughts so we can together create more robust, transparent and sustainable Vocabularies!
Here’s the query for question
“Which tables and fields do you populate with the non-zero concepts from the Vocabularies (check all that apply)?”
from the ETL specific form
with fields as (
select top 1 'person.race_concept_id' as col_name from person where race_concept_id !=0 union all
select top 1 'person.ethnicity_concept_id' from person where ethnicity_concept_id !=0 union all
select top 1 'visit_occurrence.visit_type_concept_id' from visit_occurrence where visit_occurrence.visit_type_concept_id !=0 union all
select top 1 'condition_occcurrence.condition_concept_id' from condition_occurrence where condition_occurrence.condition_concept_id !=0 union all
select top 1 'condition_occcurrence.condition_status_concept_id' from condition_occurrence where condition_occurrence.condition_status_concept_id !=0 union all
select top 1 'drug_exposure.drug_concept_id' from drug_exposure where drug_exposure.drug_concept_id !=0 union all
select top 1 'drug_exposure.route_concept_id' from drug_exposure where drug_exposure.route_concept_id !=0 union all
select top 1 'device_exposure.device_concept_id' from device_exposure where device_exposure.device_concept_id !=0 union all
select top 1 'measurement.measurement_concept_id' from measurement where measurement.measurement_concept_id !=0 union all
select top 1 'measurement.unit_concept_id' from measurement where measurement.unit_concept_id !=0 union all
select top 1 'procedure_occurrence.procedure_concept_id' from procedure_occurrence where procedure_occurrence.procedure_concept_id !=0 union all
select top 1 'procedure_occurrence.modifier_concept_id' from procedure_occurrence where procedure_occurrence.modifier_concept_id !=0 union all
select top 1 'observation.observation_concept_id' from observation where observation.observation_concept_id !=0 union all
select top 1 'note.note_type_concept_id' from note where note.note_type_concept_id !=0 union all
select top 1 'note.note_class_concept_id' from note where note.note_class_concept_id !=0 union all
select top 1 'note.language_concept_id' from note where note.language_concept_id !=0 union all
select top 1 'specimen.specimen_concept_id' from specimen where specimen.specimen_concept_id !=0 union all
select top 1 'specimen.anatomic_site_concept_id' from specimen where specimen.anatomic_site_concept_id !=0 union all
select top 1 'specimen.disease_status_concept_id' from specimen where specimen.disease_status_concept_id !=0 union all
--select top 1 'survey_conduct.survey_concept_id' from survey_conduct where survey_conduct.survey_concept_id !=0 union all
select top 1 'care_site.place_of_service_concept_id' from care_site where care_site.place_of_service_concept_id !=0 union all
select top 1 'provider.specialty_concept_id' from provider where provider.specialty_concept_id !=0 union all
select top 1 'payer_plan_period.payer_concept_id' from payer_plan_period where payer_plan_period.payer_concept_id !=0
--select top 1 'cost.cost_concept_id' from cost where cost.cost_concept_id !=0
),
anna_list as (
select 'person.race_concept_id' as col_name, '1' as rnk union all
select 'person.ethnicity_concept_id' as col_name, '2' as rnk union all
select 'visit_occurrence.visit_type_concept_id' as col_name, '3' as rnk union all
select 'condition_occcurrence.condition_concept_id' as col_name, '4' as rnk union all
select 'condition_occcurrence.condition_status_concept_id' as col_name, '5' as rnk union all
select 'drug_exposure.drug_concept_id' as col_name, '6' as rnk union all
select 'drug_exposure.route_concept_id' as col_name, '7' as rnk union all
select 'device_exposure.device_concept_id' as col_name, '8' as rnk union all
select 'measurement.measurement_concept_id' as col_name, '9' as rnk union all
select 'measurement.unit_concept_id' as col_name, '10' as rnk union all
select 'procedure_occurrence.procedure_concept_id' as col_name, '11' as rnk union all
select 'procedure_occurrence.modifier_concept_id' as col_name, '12' as rnk union all
select 'observation.observation_concept_id' as col_name, '13' as rnk union all
select 'note.note_type_concept_id' as col_name, '14' as rnk union all
select 'note.note_class_concept_id' as col_name, '15' as rnk union all
select 'note.language_concept_id' as col_name, '16' as rnk union all
select 'specimen.specimen_concept_id' as col_name, '17' as rnk union all
select 'specimen.anatomic_site_concept_id' as col_name, '18' as rnk union all
select 'specimen.disease_status_concept_id' as col_name, '19' as rnk union all
select 'survey_conduct.survey_concept_id' as col_name, '20' as rnk union all
select 'care_site.place_of_service_concept_id' as col_name, '21' as rnk union all
select 'provider.specialty_concept_id' as col_name, '22' as rnk union all
select 'payer_plan_period.payer_concept_id' as col_name, '23' as rnk union all
select 'cost.cost_concept_id' as col_name, '24' as rnk
)
select * from anna_list a
left join fields b on a.col_name =b.col_name
order by rnk::int
