ID in Rabbit In a Hat STEM?

ericaVoss · June 5, 2017, 8:19pm

@schuemie - do you remember the use of ID in the STEM?

OHDSI/WhiteRabbit/blob/master/src/org/ohdsi/rabbitInAHat/dataModel/StemTableDefaultMappingV5.0.1.csv#L5


SOURCE_TABLE,SOURCE_FIELD,TARGET_TABLE,TARGET_FIELD
STEM_TABLE,PERSON_ID,CONDITION_OCCURRENCE,PERSON_ID
STEM_TABLE,VISIT_OCCURRENCE_ID,CONDITION_OCCURRENCE,VISIT_OCCURRENCE_ID
STEM_TABLE,PROVIDER_ID,CONDITION_OCCURRENCE,PROVIDER_ID
STEM_TABLE,ID,CONDITION_OCCURRENCE,CONDITION_OCCURRENCE_ID
STEM_TABLE,CONCEPT_ID,CONDITION_OCCURRENCE,CONDITION_CONCEPT_ID
STEM_TABLE,SOURCE_VALUE,CONDITION_OCCURRENCE,CONDITION_SOURCE_VALUE
STEM_TABLE,SOURCE_CONCEPT_ID,CONDITION_OCCURRENCE,CONDITION_SOURCE_CONCEPT_ID
STEM_TABLE,TYPE_CONCEPT_ID,CONDITION_OCCURRENCE,CONDITION_TYPE_CONCEPT_ID
STEM_TABLE,START_DATE,CONDITION_OCCURRENCE,CONDITION_START_DATE
STEM_TABLE,END_DATE,CONDITION_OCCURRENCE,CONDITION_END_DATE
STEM_TABLE,STOP_REASON,CONDITION_OCCURRENCE,STOP_REASON
STEM_TABLE,PERSON_ID,DRUG_EXPOSURE,PERSON_ID
STEM_TABLE,ID,DRUG_EXPOSURE,DRUG_EXPOSURE_ID
STEM_TABLE,CONCEPT_ID,DRUG_EXPOSURE,DRUG_CONCEPT_ID

Should we actually have a map connected to each domain table that leverages STEM (because right now we don’t)?

At first @clairblacketer and I were using as an ID to help us track things, like a unique identifier from the source that would help us later in a build. But then when we noticed it in CONDITION_OCCURRENCE we shifted gears thinking it really was supposed to be more for a CONDITION_OCCURRENCE_ID. But then getting to PROCEDURE_OCCURRENCE it isn’t there.

schuemie · June 6, 2017, 6:13am

Yes, it seems the omissions are just by accident. Feel free to add the additional mappings.

DTorok · November 26, 2019, 6:25pm

Is there an description of how the Stem table is meant to be used?

ericaVoss · January 26, 2020, 4:09pm

No.

The idea of the STEM table is that it contains all the types of columns that you need regardless of the CDM table the data ultimately ends up in. There is a pre-specified map from STEM to all CDM tables. So you map to STEM and RIAH knows where STEM goes into the CDM. The idea is that the Vocabulary controls where a row ultimately goes, so if you map to this generic place, that place can then map out to where it needs to go. Otherwise if you assume a specific record of “procedure data” goes into PROCEDURE_OCCURRENCE but the Vocabulary tells you one of the procedure records is really a condition, problematically you need to be able to pull that procedure record into the condition table. Adds a lot more overhead. Our CDM Builders kind of organically came to the idea of STEM once the Vocabulary started control destination and thus Martijn added it to RIAH.

Capture this issue here:

MaximMoinat · January 27, 2020, 9:04pm

Thanks for raising this! Was on our to do list for some time, but prioritized the development until now.

I started a PR on the Github, including stem table documentation. I used your description above as a starting point.

Edit: your comments are more than welcome! Direct link to the stem table docs section: https://github.com/thehyve/OHDSI-WhiteRabbit/blob/docs/docs/RabbitInAHat.md#stem-table