OHDSI Home | Forums | Wiki | Github

Clarity on what data needs to go in Observation table and Measurement table

Researchers
#1

Hi,

I am trying to put all the medical data collected into CDM tables so that I can run patient level predictions.
So while in the process of writing ETL’s I have ended up in lot of confusions.

So CDM 5 onwards the ‘Measurement’ table was introduced. According to the documentation it says

“The MEASUREMENT table contains both orders and results of such Measurements as laboratory tests, vital signs, quantitative findings from pathology reports”

Incase of Labs which return textual data like - “Pale yellow” or “1-2 ph” where do we have to put these data ?
Is it in Observation or Measurement table ?

Suppose we have data like -
How many cigarettes do you smoke per day ?
Ans: 20 to 30
Does this answer qualify to be put in Measurement table ?

“weight” in SNOMED is considered as an Observation but its measured as part of vitals which is numerical
value. So does this come under Observation or Measurement ??

I am looking for more clarity on what data needs to go in Observation table and Measurement table ?

Kindly help me out.

Thank you for your time,
Kenith

#2

The Measurement table is for tests (etc) which have a numeric value or a set of values which can be mapped to a Concept.

For instance “Pale Yellow” is name for concept 36307582. http://athena.ohdsi.org/search-terms/terms/36307582

This is a standard answer for LOINC LA25958-2, and can be found in the “Meas Value” domain, “LOINC” vocabulary, and “Answer” concept class. So for this test, you’d put 36307582 in the [value_as_concept_id] field and put “Pale Yellow” in the [value_source_value] field.

As for “1-2 ph” , I’m not sure: http://athena.ohdsi.org/search-terms/terms?query=1-2+ph&page=1, but it’s likely 45884526. (same domain, etc)

For “tests” which do not have a standard value, you’d put them in the Observation table, but only if you can’t put them anywhere else.

#3

Thank you @roger.carlson. It was helpful.

Suppose for tests which do not have numeric value and standard value
example: “1-2 ph” or “3-4/ hpf” or some random text value

Do we have to put them in Observation table ?
Or do we have to put them in Measurement table as measurement_source_value and keep value_as_number empty

#4

There’s no single answer.

In some cases, you can map your values to standard concepts. That’s what the source_to_concept_map table is for. There’s also a tool called USAGI (https://github.com/OHDSI/Usagi) which can help with that.

In others, you can leave the value_as_number empty, set value_as_concept_id to zero (0), and put your value in value_source_value (which you should do in any case). I’m not sure how useful that record will be in a research sense, but at least you won’t lose it.

#5

The table to put data is determined by the domain_id of the standard concept_id representing the source code. In general, labs belong in the Measurement table. If the result of the lab is a Float data type, it belongs in the value_as_number field, results that map to a concept_id go in the value_as_concept_id field, a string text value of 50 characters or less goes to the value_source_value field, and if you have a > 50 character result, then put the data in the Note table and link it to the measurement_id via the Fact Relationship table.

The Book of OHDSI is a very useful resource and it’s FREE! It explains the conventions. I highly suggest reading it :slight_smile:

Also, If you have similar questions to this, you should put it in the CDM Builders forum thread. This is the research thread and your questions might go unanswered here since it is a build question.

How to record a lab result where result > 50 character
#6

Thank you @MPhilofsky, @roger.carlson
Your Answers have given me better understanding on how to handle such scenarios

#8

hello every one
@MPhilofsky , we have the same case here, in lab table we have test results as float, short text like (Nonreactive,Yellow,Normaol …) , values like (<1,>=1.0,<1.7) and long text exceeded 50 characters.
how to deal with each one and how to record text exceeded 50 character in note and how to link it to measurement_id via the Fact Relationship table.
thanks

t