Definition

The MKB Drug Pack model provides standardised pharmaceutical packaging information for medications used within the healthcare system. It combines drug preparation data with commercial pack configurations, including pricing, quantities, unit of measure, and supplier details.

Information

The MKB Drug Pack model links drug preparations to their available commercial pack forms, covering only medications for which pack-level data exists. It provides a consistent view of drug packaging metadata across all participating organisations, enabling analysis of pricing, pack sizes, and supplier variation.

The model is designed to be used alongside the Drug Code model. The preparation_code_id field in this model corresponds to code_id in the Drug Code model, allowing consumers to enrich pack-level records with drug metadata. The dmd_product_code_id field additionally supports joins to the dm+d Preparation model for broader mapping across coding systems.

Grain and Scope

Each row is a unique drug pack record and each record is uniquely identified by preparation_pack_id column.

This model includes the following key identifiers:

• preparation_pack_id: The unique EMIS identifier for the drug pack.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether the drug pack record has been deleted at source.

• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

• Drug Pack Mapping Updates: Information related to drug pack can change over time; EMIS MKB is updated monthly and may include relevant updates.

Examples

Detailed packaging information for a specific drug

SELECT
  drug.term AS drug_name,
  drug.dmd_product_code_id,
  pack.pack_description,
  pack.units_per_pack,
  pack.unit_of_measure,
  pack.pack_price,
  pack.price_per_unit
FROM
  hive.explorer_ipcv_hermes.drug_code AS drug
  JOIN hive.explorer_ipcv_hermes.mkb_drug_pack AS pack ON drug.code_id = pack.preparation_code_id
WHERE
  drug.term LIKE '%metformin%'
ORDER BY
  drug.term,
  pack.units_per_pack;

All packaging options for a specific medication

SELECT
  term AS drug_name,
  supplier_name,
  pack_description,
  units_per_pack,
  unit_of_measure,
  pack_price,
  price_per_unit
FROM
  hive.explorer_ipcv_hermes.mkb_drug_pack
WHERE
  term LIKE '%Amoxicillin%'
  AND is_deleted = FALSE
ORDER BY
  price_per_unit;

Medications with the most packaging options

SELECT
  term AS drug_name,
  COUNT(DISTINCT preparation_pack_id) AS pack_options_count,
  COUNT(DISTINCT supplier_name) AS supplier_count,
  MIN(price_per_unit) AS min_price_per_unit,
  MAX(price_per_unit) AS max_price_per_unit,
  AVG(price_per_unit) AS avg_price_per_unit
FROM
  hive.explorer_ipcv_hermes.mkb_drug_pack
WHERE
  is_deleted = FALSE
  AND price_per_unit > 0
GROUP BY
  term
HAVING
  COUNT(DISTINCT preparation_pack_id) > 3
ORDER BY
  pack_options_count DESC,
  supplier_count DESC
LIMIT
  20;