Hello,
I have source data for specimens collected and the corresponding tests that were performed on these specimens. How do we link up the records between the SPECIMEN and MEASUREMENT tables when the only common field is PERSON_ID?
Regards,
Vimala
We had a similar situation and used the fact relationship table.
Hello Don,
Thanks for your reply. Could you provide me an example of how you inserted rows into the FACT_RELATIONSHIP table? Would it look something like this:
Specimen, 1, Measurement, 2, Has measurement = 36, 1, 21, 2, 44818771
Measurement, 2, Specimen, 1, Has specimen source=21,2,36,1, 44818776
And would we have to repeat this for each distinct value pair found in the source data?
Regards,
Vimala
Can you use visit_occurrence or visit_detail?
From the OMOP v5.3 Spec
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 (table), 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).
Field Required Type Description
domain_concept_id_1 The concept representing the domain of fact one, from
which the corresponding table can be inferred.
fact_id_1 The unique identifier in the table corresponding to the domain
of fact one.
domain_concept_id_2 The concept representing the domain of fact two, from
which the corresponding table can be inferred.
fact_id_2 The unique identifier in the table corresponding to the domain
of fact two.
relationship_concept_id A foreign key to a Standard Concept ID of
relationship in the Standardized Vocabularies.
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
Given your example of a specimen to one or more measurement
Specimen to measurement:
domain_concept_id_1 = 36 (Specimen) The Domain_id is ‘Metadata’ and
Concept Class is ‘Domain’
fact_id_1 = the specimen_id
domain_concept_id_2 = 27 (Observation)
fact_id_2 = the observation_id
relationship_concept_id = 2,000,000,001 ( these are custom concepts we
defined it as ‘Specimen to Observation’)
Then the reverse for Measurement to Specimen:
domain_concept_id_1 = 27 (Observation)
fact_id_1 = the observation_id
domain_concept_id_2 = 36 (Specimen)
fact_id_2 = the specimen_id
relationship_concept_id = 2,000,000,002 ( these are custom concepts we
defined it as ‘Observation to Specimen’)
Hello Gowtham,
The VISIT_OCCURRENCE table is not suitable in our case. And we are on an older version of the CDM. So we do not have access to VISIT_DETAIL.
We feel that there is a natural link between SPECIMEN and MEASUREMENT. We are quite surprised that the CDM does not reflect this. Is it possible to submit a change request to include a link between these two tables in the next update of the model?
Regards,
Vimala
The CDM reflects what people need to do analytical use cases. The CDM is not the place to hoard whatever data might be available, but to serve a certain question. So, we need your use case, Vimala.
It certainly is possible and encouraged! Called the CDM Working Group. I realize it might be a little difficult for you to attend, given the timing. But you can definitely submit a proposal. We need a use case and the actual proposal. If you need help, let us know and we make it happen.
You may also join forces with @cukarthik and @bailey, who are proposing a similar desire.