ericaVoss commented 7 months ago

MEASUREMENT.VALUE_AS_CONCEPT_ID can be NULL

CDM or THEMIS convention?

CDM

Table or Field level?

Field

Is this a general convention?

MEASUREMENT.VALUE_AS_CONCEPT_ID
- MEASUREMENT.VALUE_AS_NUMBER

Summary of issues

I think the CDM page is combining both a THEMIS rule and CDM rule. I would suggest taking the THEMIS components out.

Summary of answer

Here is the recommendation of the THEMIS rule for your reference: https://github.com/OHDSI/Themis/issues/123

In light of that recommendation, I would make this change on the CDM Page:

ORIGINAL VALUE_AS_CONVEPT_ID ETL CONVENTIONS	SUGGESTED VALUE_AS_CONVEPT_ID ETL CONVENTIONS
If the raw data provides categorial results as well as continuous results for measurements, it is a valid ETL choice to preserve both values. The continuous value should go in the VALUE_AS_NUMBER field and the categorical value should be mapped to a standard concept in the ‘Meas Value’ domain and put in the VALUE_AS_CONCEPT_ID field. This is also the destination for the ‘Maps to value’ relationship. If there’s no categorial result in a source_data, set value_as_concept_id to NULL, if there is a categorial result in a source_data but without mapping, set value_as_concept_id to 0.	If there is no categorial result in the source data, set VALUE_AS_CONCEPT_ID to NULL, if there is a categorial result in a source data but without mapping, set VALUE_AS_CONCEPT_ID to 0, else map to a CONCEPT_ID.

ORIGINAL VALUE_AS_CONVEPT_ID ETL CONVENTIONS

SUGGESTED VALUE_AS_CONVEPT_ID ETL CONVENTIONS

If the raw data provides categorial results as well as continuous results for measurements, it is a valid ETL choice to preserve both values. The continuous value should go in the VALUE_AS_NUMBER field and the categorical value should be mapped to a standard concept in the ‘Meas Value’ domain and put in the VALUE_AS_CONCEPT_ID field. This is also the destination for the ‘Maps to value’ relationship. If there’s no categorial result in a source_data, set value_as_concept_id to NULL, if there is a categorial result in a source_data but without mapping, set value_as_concept_id to 0.

If there is no categorial result in the source data, set VALUE_AS_CONCEPT_ID to NULL, if there is a categorial result in a source data but without mapping, set VALUE_AS_CONCEPT_ID to 0, else map to a CONCEPT_ID.

Also, in light of the recommendation of the THEMIS rule, I would update this from the "ETL Conventions" section of the "Measurement" table as it would be redundant.

ORIGINAL MEASUREMENT ETL CONVENTIONS	SUGGESTED MEASUREMENT ETL CONVENTIONS
Only records where the source value maps to a Concept in the measurement domain should be included in this table. Even though each Measurement always has a result, the fields VALUE_AS_NUMBER and VALUE_AS_CONCEPT_ID are not mandatory as often the result is not given in the source data. When the result is not known, the Measurement record represents just the fact that the corresponding Measurement was carried out, which in itself is already useful information for some use cases. For some Measurement Concepts, the result is included in the test. For example, ICD10 CONCEPT_ID 45548980 ‘Abnormal level of unspecified serum enzyme’ indicates a Measurement and the result (abnormal). In those situations, the CONCEPT_RELATIONSHIP table in addition to the ‘Maps to’ record contains a second record with the relationship_id set to ‘Maps to value’. In this example, the ‘Maps to’ relationship directs to 4046263 ‘Enzyme measurement’ as well as a ‘Maps to value’ record to 4135493 ‘Abnormal’.	Remove text from the CDM site as the THEMIS text will handle this. https://github.com/OHDSI/Themis/issues/123

ORIGINAL MEASUREMENT ETL CONVENTIONS

SUGGESTED MEASUREMENT ETL CONVENTIONS

Only records where the source value maps to a Concept in the measurement domain should be included in this table. Even though each Measurement always has a result, the fields VALUE_AS_NUMBER and VALUE_AS_CONCEPT_ID are not mandatory as often the result is not given in the source data. When the result is not known, the Measurement record represents just the fact that the corresponding Measurement was carried out, which in itself is already useful information for some use cases. For some Measurement Concepts, the result is included in the test. For example, ICD10 CONCEPT_ID 45548980 ‘Abnormal level of unspecified serum enzyme’ indicates a Measurement and the result (abnormal). In those situations, the CONCEPT_RELATIONSHIP table in addition to the ‘Maps to’ record contains a second record with the relationship_id set to ‘Maps to value’. In this example, the ‘Maps to’ relationship directs to 4046263 ‘Enzyme measurement’ as well as a ‘Maps to value’ record to 4135493 ‘Abnormal’.

Remove text from the CDM site as the THEMIS text will handle this. https://github.com/OHDSI/Themis/issues/123

Technically this is a ratified THEMIS idea, however I think it makes sense to be documented in the ETL conventions: CONCEPT_ID (to NULL or not to NULL) #11

Other comments/notes

There is also some open discussion about domains, but I don't feel comfortable documenting that.

clairblacketer commented 7 months ago

Thanks @ericaVoss, this makes sense. I will move to the build column as this is an update to the CDM docs.

clairblacketer commented 6 months ago

As I reviewed, I would like to keep the ETL language in the CDM docs because I think the instruction is helpful. I will put links to the themis repository so that additional context can be given.

OHDSI / CommonDataModel