Definition

The MKB Mapping Attributes model provides consolidated information about mappings between national codes and SNOMED concepts, specifically for language codes and sexual orientation codes.

Information

The MKB Mapping Attributes model provides mappings between national data dictionary codes and their corresponding SNOMED concepts, specifically for language and sexual orientation attributes. This view consolidates information from national code dimension and code dimension tables, creating a unified reference for these specialized mappings.

Each record in this model represents a unique combination of a code and a national data dictionary code, with additional context about the attribute type (language or sexual orientation). The model maintains the deletion status of each mapping, ensuring that obsolete mappings can be appropriately filtered out in downstream applications.

What’s changing in iPCV v2

Schema updates for data maintenance

• The is_deleted column has been added to indicate whether a clinical code has been marked as deleted
• The data_filter column is removed as it is no longer required

Benefit

• The is_deleted column enables more effective data maintenance by allowing customers to identify and manage deleted clinical codes
• The data model is streamlined by removing deprecated columns from iPCV v1 and v2, resulting in a cleaner and more efficient structure

Customer Action

• Update your ETL processes to use the is_deleted column to manage and clean up clinical codes
• Review your queries and remove any references to the deprecated data_filter column to align with the updated schema and avoid confusion

Examples

Active language mappings

SELECT
  code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  organisation,
  transform_datetime
FROM hive.explorer_ipcv.srv_mkb_mapping_attributes
WHERE
  is_deleted = FALSE
  AND attribute_type = 'language'
ORDER BY
  data_dictionary_national_code

Sexual orientation mappings

SELECT
  code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  transform_datetime
FROM hive.explorer_ipcv.srv_mkb_mapping_attributes
WHERE
  attribute_type = 'sex_orientation'
ORDER BY
  is_deleted, -- Show active mappings first
  data_dictionary_national_code

Mapping statistics by type

SELECT
  attribute_type,
  COUNT(*) AS total_mappings,
  SUM(CASE WHEN is_deleted = FALSE THEN 1 ELSE 0 END) AS active_mappings,
  SUM(CASE WHEN snomed_concept_id IS NOT NULL THEN 1 ELSE 0 END) AS mappings_with_snomed,
  MIN(transform_datetime) AS earliest_update,
  MAX(transform_datetime) AS latest_update
FROM hive.explorer_ipcv.srv_mkb_mapping_attributes
GROUP BY
  attribute_type
ORDER BY
  attribute_type