OLIDS_MASKED: Raw Concept ID fields not populated as expected

[EddieDavison92]

Raw Concept ID Fields Not Populated with Expected Codes

Summary

Raw concept ID fields across OLIDS_MASKED schema tables are either null or contain unexpected GUID values instead of expected medical coding standards.

Current State

• ALLERGY_INTOLERANCE.allergy_intolerance_raw_concept_id: All NULL
• EPISODE_OF_CARE.episode_type_raw_concept_id: All NULL
• EPISODE_OF_CARE.episode_status_raw_concept_id: All NULL
• OBSERVATION.observation_raw_concept_id: All NULL
• REFERRAL_REQUEST.referral_request_raw_concept_id: All NULL
• DIAGNOSTIC_ORDER.diagnostic_order_raw_concept_id: Contains GUID strings

Question

I had expected the raw concept ID fields to contain either EMIS Local Codes, Read codes or SNOMED-CT codes; not a GUID string.

Are these fields intended to contain clinical codes, or what is the purpose for the GUID values in DIAGNOSTIC_ORDER?

Impact

Unable to access original clinical coding information to check if concepts have a materially different mapping to the clinician's original intent.

Database

• Database: Data_Store_OLIDS_UAT
• Schema: OLIDS_MASKED

Query

-- ALLERGY_INTOLERANCE table
SELECT DISTINCT ai."allergy_intolerance_raw_concept_id"
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.ALLERGY_INTOLERANCE ai;

-- DIAGNOSTIC_ORDER table
SELECT DISTINCT do."diagnostic_order_raw_concept_id"
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.DIAGNOSTIC_ORDER do;

-- EPISODE_OF_CARE table - episode_type_raw_concept_id
SELECT DISTINCT eoc."episode_type_raw_concept_id"
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.EPISODE_OF_CARE eoc;

-- EPISODE_OF_CARE table - episode_status_raw_concept_id
SELECT DISTINCT eoc."episode_status_raw_concept_id"
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.EPISODE_OF_CARE eoc;

-- OBSERVATION table
SELECT DISTINCT o."observation_raw_concept_id"
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.OBSERVATION o;

-- REFERRAL_REQUEST table
SELECT DISTINCT rr."referral_request_raw_concept_id"
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.REFERRAL_REQUEST rr;

[Rkw20067]

From @DanielNuttall-AD:

The GUIDs that can be seen will be linked to the concepts table, so the queries just need to join to the "OLIDS_TERMINOLOGY"."CONCEPT" table on the id column.

The following is an example from one of his other queries:

LEFT JOIN
"Data_Store_OLIDS_Dummy"."OLIDS_TERMINOLOGY"."CONCEPT_MAP" cm ON src."address_type_concept_id" = cm."source_code_id"
LEFT JOIN
"Data_Store_OLIDS_Dummy"."OLIDS_TERMINOLOGY"."CONCEPT" c ON cm."target_code_id" = c."id"

[EddieDavison92]

Great, thank you

[BillWoodNHS]

fix added in release 157.

[EddieDavison92]

Test Summary

4/6 Fields are now properly populated

Resolved:

• ALLERGY_INTOLERANCE.allergy_intolerance_source_concept_id: 100% Populated
• DIAGNOSTIC_ORDER.diagnostic_order_source_concept_id: 100% Populated
• OBSERVATION.observation_source_concept_id: 100% populated
• REFERRAL_REQUEST.referral_request_raw_concept_id: 100% populated

Outstanding:

EPISODE_OF_CARE.episode_type_raw_concept_id: All NULL (field not yet renamed to source_concept_id)
EPISODE_OF_CARE.episode_status_raw_concept_id: All NULL (field not yet renamed to source_concept_id)

-- ALLERGY_INTOLERANCE table
SELECT count(*)
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.ALLERGY_INTOLERANCE ai
WHERE ai."allergy_intolerance_source_concept_id" IS NULL;

-- DIAGNOSTIC_ORDER table
SELECT count(*) 
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.DIAGNOSTIC_ORDER do
WHERE do."diagnostic_order_source_concept_id" IS NULL;

-- EPISODE_OF_CARE table - episode_type_raw_concept_id
SELECT count(*)
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.EPISODE_OF_CARE eoc
WHERE eoc."episode_type_raw_concept_id" IS NULL;

-- EPISODE_OF_CARE table - episode_status_raw_concept_id
SELECT DISTINCT eoc."episode_status_raw_concept_id"
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.EPISODE_OF_CARE eoc;

-- OBSERVATION table
SELECT count(*)
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.OBSERVATION o
WHERE o."observation_source_concept_id" IS NULL;

-- REFERRAL_REQUEST table
SELECT count(*) 
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.REFERRAL_REQUEST rr
WHERE rr."referral_request_source_concept_id" IS NULL;