We have a question about the procedure table wherein there are multiple providers with different tasks such as main surgeon, assisting surgeon, anesthesiologist, scrubbed nurse, etc.
We would appreciate it if someone could advise us what would be the best way to link each team member to a specific procedure in the OMOP CDM.
The first question is always do you have a reason, ‘use case’, to link each team member to a specific procedure. Assuming that you do have a use case, use the fact_relationship table. In one direction, domain_id_1 will be ‘Procedure’, fact_id_1 the procedure record id, domain_id_2 will be ‘Provider’ and fact_id_2 the provider_id. Create a record for each procedure provider. You will probably have to create a custom concept for the relationship_concept_id. Custom concepts are added to the concept table with a concept_id greater then two billion.
As for the provider_id in the procedure records, decide on a scheme to determine the ‘primary’ provider. From your example it would probably be the main surgeon.
Great answer @DTorok !
I also have a specific question if I may:
I noticed that there is no such Id for the domain “provider” in the DOMAIN table. There is only a single row I found:
domain_name: provider specialty
Now let’s say that procedure domain id is 99.
If one’s wish to link 3 providers (with provider Id’s 1,2,3) in a procedure with id 456 (let’s say this procedure is some kind of complex surgery), flesh and blood, to this procedure via FACT RELATIONSHIP table (let’s also say relationship id 7 that says “is associated with” or some sort), so we can add 3 rows to that table (as csv format)
fact_1_id, fact_1_domain_id, fact_2_id fact_2_domain_id, relationship_concept_id
456, 99, 1, 33, 7
456, 99, 2, 33, 7
456, 99, 3, 33, 7
But domain ID 33 is only for provider specialty and not for tye provider_id (because there is no provider_concept_id).
In that case, what would be the right domain_id for those provider Ids in those rows in FACT RELATIONSHIP.
I do not know the answer. I think there is a realization that ‘Domain’ is overloaded with meanings. As you point out Domain id ‘Provider’ is the domain for provider specialty, but it is also used as a flag to indicate the Provider table. Currently I would use domain id 33, Provider, to indicate fact 2 id references the Provider table. However, there may be a plan to use something other than ‘Domain’ to identify a CDM table. Current vocabulary has concepts with Domain = ‘Metadata’, Vocabulary_id = ‘CDM’ and Concept Class id = ‘Table’. So there is now a concept that specifically represents the Provider table. However, I have not seen an ‘Official’ announcement that these concepts should be used to identify the tables in Fact_Relationship.
The Fact_Relationship table is really meant to link facts not dimensions (data warehouse term). Provider is not a fact but a dimension. This is probably why there is no Provider domain. @gil.frenkel I see two ways to resolve your issue:
1). Use Fact_Relationship table as @DTorok suggested. Since there is no Provider domain concept, you may want use Provider.provider_id field concept Id. There are two of them.
1147201 for CDM version 5.3 and before
1147815 for CDM version 6.0 and above
However, use field concept Id instead of domain Id does not conform to the conventional way of how this table is used. But I do think going forward, field concept Id should be used in Fact_Relationship table instead of domain Id so that it is consistent with Measurement, Observation and other tables where field concept Id is used to link to other event tables. I will make a separate suggestion in the forum later on.
2). Use Observation table in CDM v6.0. Observation table is a catch-all bucket and can link to other event (fact) tables via following 2 columns:
observation_event_id: put Procedure_Occurrence. procedure_occurrence_id here
obs_event_field_concept_id: put field concept id for Procedure_Occurrence.procedure_occurrence_id here (1147810)
For observation_concept_id, you may want to use one of the following:
4269007 Has provider
761929 Under care of multiple providers
Then put the Provider.provider_id into value_as_number column, and Provider.provider_name (or NPI) into value_as_string column.
Hope this helps.
Hi @QI_omop and thank you for your answer.
One thing that still keeps me somewhat buffled, is the following problem:
Let’s say I’m going for the 2nd option you have suggested.
Plus, let’s say a researcher wants to count all of the procedures that a specific provider (let’s assume that his/her provider_id and NPI are 12345 and 6789, respectively) in one hospital has been directly involved with (as a surgeon in one procedure or as an assisting surgeon in other procedure etc).
How will that researcher know that the value in value_as _number that is equal to 12345 is referring to a provider_id and not something else?
The same goes for value_as_string - if I have NPI for example, how will that researcher know that the value 789 is reffering to an NPI and not something else?
Will the researcher know the answer solely based on the observation_concept_id?
I.e., if the observation_concept_id equals to 4269007 or 761929 then that’s enough?
In addition, the OBSERVATION table already includes the field PROVIDER_ID.
Why not creating a single row for every provider that took part in a specific procedure, and put each of the providers’ IDs in the OBSERVATION.PROVIDER_ID field?
the OBSERVATION table already includes the field PROVIDER_ID
Good point! Observation.provider_id is probably more appropriate than value_as_number.
Why not creating a single row for every provider that took part in a specific procedure
That is what I meant. If there are 5 providers for a specific procedure, there should be 5 Observation records.