Hello everyone!I’ve got problems about Fact Relationship table.
1.I can’t fully understand every field’s meaning,like “domain_concept_id_1” and “fact_id_1”.Is measurement.measurement_concept_id a domain_concept_id?What is fact_id and how can I find its concpt id?
2.What is “relationship_concept_id”?How can I find its concpt id?
3.Could you give me an example about how to use Fact Relationship table?
Appreciate your help in advance!
Hello @Vines,
First, a brief description of fields:
- domain_concept_id_1 — this field indicates which CDM domain the 1st record (fact) belongs to. As this is a concept_id, you’ll have to look it up in Concept table. These concepts have domain_id=“Metadata” (because they basically refer to CDM’s own structure) and vocabulary_id=“Domain”. Here’s an example.
- fact_id_1 — this is not a concept, but a unique record ID in another CDM table. For example, if the 1st fact is from Measurement table, its measurement_id value will go here.
- domain_concept_id_2 — similar to domain_concept_id_1, this is a concept which indicates the CDM domain of the 2nd fact.
- fact_id_2 — again, a unique ID of 2nd fact in another table. This another table may be the same as for the 1st fact, or a different one.
- relationship_concept_id — a concept which provides more context on how/why these two facts are connected/relate to each other. For example, if the 1st fact is myeloma (Condition) and the 2nd is lab test used to define myeloma progression (Measurement), the concept “Condition relevant to” gives a description of the relationship.
Usage example: let’s say we have 1st fact of “Bacteria identified in Urine by Culture” (Measurement) and 2nd fact of “Nitrofurantoin [Susceptibility]” (also Measurement). For simplicity, let’s say their measurement_id values are 1 and 2, respectively.
| domain_concept_id_1 | fact_id_1 | domain_concept_id_2 | fact_id_2 | relationship_concept_id |
|---|---|---|---|---|
| 21 | 1 | 21 | 2 | 581437 |
Here, both domain_id are 21 “Measurement”, and relationship_concept_id=581437 stands for “Child to Parent Measurement”.
Bear in mind that relationships are directional (see convention #1 here), so a “reverse” record is also needed:
| domain_concept_id_1 | fact_id_1 | domain_concept_id_2 | fact_id_2 | relationship_concept_id |
|---|---|---|---|---|
| 21 | 2 | 21 | 1 | 581436 |
relationship_concept_id=581436 is “Parent to Child Measurement”.
How to find relatinoship_concept_id: domain=“Metadata”, concept_class=“Relatinship”.
1 Like
You are a star!I’ve got a lot from you.
@ rookie_crewkie
Hello Rookie! I have a problem about how to use event_id and event_field_concept_id.For example,there is a record in Contion Occurrence table(concept name is “elation”,concept id is “4146515”). There is another record in the Observation table(concept name is “past”,concept id is “4132507”).I want to use the observation_event_id and obs_event_field_concept_id to link the “past” to the “elation”,which means the patient’s elation is the past symptom.If the record number of “elation” in the Contion Occurrence table is 123,so I put into observation_event_id,and put 1147129condition_occurrence.condition_concept_id into obs_event_field_concept_id.
Am I right?
Hello @Vines,
These fields were introduced in CDM v6.0, and unfortunately I lack experience with it as I mostly work with v5.3.X. Sorry, but you’ll have to seek help on these fields from someone else.
However, there’s a simpler approach to represent patient’s history in CDM — just use ‘History of’ concept for observation_type_concept_id and put the medical concept of the medical fact (‘Elation’, in your example) in observation_concept_id. This is particularly useful when you don’t have the exact date for some medical fact from the patient’s history — you just know that it happened “some time ago”.
Real-life example — patient states “I used to have alcohol-related epilepsy in the past”. You cannot create a Condition record for that (because date fields are required), so instead create a single ‘History of’ Observation record.
As I’ve said, I don’t have enough experience with v6.0, so better check with someone else if this approach is still considered valid.
From my understanding, History of concept(http://athena.ohdsi.org/search-terms/terms/4188893) can not be observation_type_concept_id, because every table’s type_concept_id are limited.For example, NLP derived can be a observation_type_concept_id.There are totally 15 observation_type_concept_id which can be selected.You can select the domain Type Concept and class Observation Type in the Athena,and you will get all the observation_type_concept_id.Other table’s type_concept_id can be selected as the same way.
So, I don’t think your advice gonna be work.Thank you very much for replying.And I will ask someone else just as you said.
You are absolutely right, ‘History of’ should go to observation_concept_id, and the fact concept is for value_as_concept_id. Sorry for the misleading comment above. Need more coffee in the morning 
Friends:
I think this has gone awry a little. Couple points:
- You cannot use FACT_RELATIONSHIP in order to declare facts like in normal language. So, you cannot combine some fact with a concept “Past” in order to indicate that this fact happened in the past. I mean, you can, but no analytic will ever be able to use it. It’s a lost fact.
- If you want to indicate something in the past then record that fact with a past time stamp. If you don’t have an exact time in the past use the “history of” concept in OBSERVATION; and the fact put in value_as_concept.
- Observation Types have nothing to do with it. These types are indications of provenance, i.e. where you got the information from, in what kind of record was it stored. For example, was it from a claim, or an EHR patient reported record, etc.