OHDSI Home | Forums | Wiki | Github

Representing Orders and Results in the Measurement table

We recently submitted data to N3C and have received feedback that we have "Too many Measurements that don’t have a result” ! For Covid tests in particular, we often have 3 rows in the measurement table per order:

  • Two rows representing the type/source of the order specimen
  • One row representing the result
  • Thus 2/3 of our covid measurements don’t have a result.

In general, we have 2 rows per order in our measurement table -one for the order only and one for the order with the result. However, based on what the data dictionary says, it sounds like:

  1. If an Order has a Result- it should be represented as 1 record in the measurement table not 2 separate records.
  2. If an Order was performed but result is not known, it should be represented in the Measurement table.
  3. If an Order was not performed- it should not be part of the measurement table.

Please advise.
@Christian_Reich @aostropolets @MPhilofsky


Yeah, that’s because we are studying patients, not hospital systems. So, if we have a Measurment including result that’s all that matters to the patient.

You are going to have to find a way to assign test order to test results. Otherwise I wouldn’t know how you can avoid that.

We have to do this as well. Pseudocode:

  • Get all results, that have been signed off on, that have a result value, inner joined to the order record.

If it is missing one of the above, just don’t include it.

Thanks Mark

thanks @Christian_Reich

Hello @PriyaDesai,

There are three areas in Epic where lab data lives.

  1. The lab result table contains records at the component level. So, each individual row represents a component of a lab panel or individual lab and its result.

  2. The “orders” table. Most of these orders are coded with custom Epic codes and have to be custom mapped to standard concepts. The data are generally at the lab panel level, but sometimes a provider orders a single component. These do not contain results.

  3. The billing tables. These are the billing records and are usually coded with CPT4 codes at the lab panel level. These do not contain results.

Lab panel data do not contain results because the test is for multiple different measurements per panel. So, results are only found in the Order Results table.

It’s been a while since I have dug into this source data, but when I was working directly with the data, we were unable to directly connect the billing data tables with the orders or results data. Using the patient identifier and the date to de-duplicate these two tables would be very messy, especially for hospitalized patients since they can have multiple labs the same day, one individual lab component can be contained in multiple panels, lab draws can be duplicated, clotted, re-run, etc. Order Results and Order Proc can be connected, but there are a few “what to do when ___” questions for these data.

Mark suggests doing an inner join between orders and results. But what if 13 out of 14 lab components of one label panel have a result, but 1 component doesn’t have a result? Do you remove the panel or keep it since it was performed, but 1 result remains unknown? What if it’s the opposite and 13 out of 14 lab components aren’t resulted? Also, how do you tell the ETL how many components are in a panel? This metadata is not part of the CPT4 code. I’m sure there are other IF/THEN questions to answer once you look into the data. I’ve also heard of use cases (note- this is not an OHDSI approved use case :slight_smile: ) where researchers want to know which lab panels are ordered and it’s next to impossible to know if you only have the component data since components can be in multiple panels.

So, here’s a suggestion. Instead of de-duplicating, removing records and making your ETL more complex, alter the N3C view. Have one full PHI OMOP CDM with very minimal filters. Only remove clinical events which did not occur or implausible. Then from this master CDM, create different views for different purposes. De-Id, LDS, project specific, etc. The University of Colorado has been successfully using this method for a while. We bring in all our Flowsheet data which are mostly mapped to concept_id = 0, but then filter these data out when delivering data for different OMOP projects since concept_id = 0 has no meaning in the OHDSI world. We also filter out data based on our privacy agreements and other restrictions. It’s much easier to create views for individual projects, than alter the full ETL for every project.

Business rules are the answer; well that and how your EHR tables are set up. How does one know if the lab, in your example, even did the order, if it was not in-house? With our EHR, the inner join will still return results even if the entire order was not completed, therefore it does not break the requirements.

Not knowing the Epic table structure, I kept my pseudocode as simple as possible.

Yes, its a nightmare to process our data, but the concept isn’t complex.

Thanks for that detailed answer @MPhilofsky Melanie- I have to admit that sounds like a lot of work :slight_smile:
We are also starting to populate our Specimen table- From the description, it sounds like perhaps the

  • the Two rows representing the type/source of the order specimen in case of a COVID test should be actually be only one row, but the type/source of the specimen should be in Specimen table [Nasopharyngeal swab]?
    And the connect the measurement table and specimen table using the fact relationship table?

#Specimen @Christian_Reich