Definition

The Immunisation data model provides a comprehensive record of patient immunisation information derived from observation data. It captures coded immunisation entries recorded by clinicians, including the associated clinical code, vaccination details, date of administration, and contextual consultation information. This model is essential for understanding a patient’s vaccination history and supporting public health reporting across healthcare services.

Information

The Immunisation data model is a structured representation of immunisation observations recorded against patients in EMIS Web. Each immunisation record is derived from the broader observation data and filtered specifically to entries categorised as immunisations.

Each immunisation record is uniquely identified by a combination of observation_id and organisation, ensuring a distinct entry for every immunisation within a specific healthcare organisation.

This model includes some of the key identifiers:

• observation_id: The unique internal identifier for the observation record.
• observation_guid: GUID for the observation record
• observation_uuid: UUID for the observation record

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

• is_deleted: Indicates whether the immunisation record has been soft-deleted in the source system.
• is_sensitive: Indicates whether the record is flagged as sensitive.
• is_confidential: Indicates whether the record is confidential.
• transform_datetime: The timestamp indicating when the record was last processed in the data warehouse.

Overview

flowchart TB
  subgraph container["Data Collection"]
    n10["Vaccine Code"]
    n11["Administration Dates"]
    n12["Batch & Manufacturer Details"]
  end

  n17["Organisation 1"] --> n7
  n17["Organisation 1"] --> n5
  n18["Organisation 2"] --> n6
  n18["Organisation 2"] --> n8
  n18["Organisation 2"] --> n4

  n7["Patient 123"] --> container
  n5["Patient 98"] --> container
  n6["Patient 456"] --> container
  n4["Patient 20"] --> container
  n8["Patient 47"] --> container

  container --> n13["Gather Immunisation Data"]
  n13 --> n14["ETL"]
  n14 --> n15["Immunisation Data Model"]

  n7["Patient 123"]:::rect
  n5["Patient 98"]:::rect
  n6["Patient 456"]:::rect
  n4["Patient 20"]:::rect

Changes in iPCV v2

1. Removal of Columns

Several columns previously available directly in the immunisation (v1) table have been removed from the immunisation_v2 (v2) table. These include:

Why?

These columns have been removed to streamline the data model and maintain a single source of truth for reference data:

• MKB reference columns: No longer included to reduce complexity and avoid duplication, as this data is now available through dedicated reference tables
• Patient opt flags columns: Removed as these flags are maintained in the patient scope dimension table, providing a single source of truth

Decommissioned Column	Relevant ID / Column	Reason / Extended Data Model to be Referred
other_code	code_id	codeable_concept_v2
other_code_system	code_id	codeable_concept_v2
other_display	code_id	codeable_concept_v2
readv2_code	code_id	codeable_concept_v2
snomed_concept_id	code_id	codeable_concept_v2
snomed_description_id	code_id	codeable_concept_v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
non_regular_and_current_active_flag	patient_id	patient_v2
opt_out_93c1_flag	patient_id	patient_v2
opt_out_9nd19nu09nu4_flag	patient_id	patient_v2
opt_out_9nd19nu0_flag	patient_id	patient_v2
opt_out_9nu0_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	patient_v2
regular_patient_flag	patient_id	patient_v2
sensitive_patient_flag	patient_id	patient_v2
data_filter		Removed as it is deprecated in v2
_record_version		No longer available in v2
_update_date		Changes tracked differently in v2
_update_hour		Changes tracked differently in v2

Customer benefit

• Single source of truth for patient flags through the patient scope dimension table
• Reduced risk of data inconsistencies and synchronization issues
• Improved data lineage tracking with centralized reference tables

Customer action

• Review and update any processes or integrations that relied on the removed columns
• Use dedicated MKB reference tables for code mappings instead of inline columns
• Query the patient scope dimension table for patient opt flags and status information
• Ensure that any necessary mappings or transformations are adjusted accordingly

2. NULL value columns

Several columns in the immunisation_v2 table now consistently return NULL values and have been retained for backward compatibility. These include:

Why?

These columns are retained to maintain backward compatibility while returning NULL values to ensure consistent behavior in downstream processes.

Deprecated Column	Replacement ID / Column	Reason / Extended Data Model to be Referred
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
practitioner_id	authorising_user_in_role_id	user_in_role_v2
registration_ods_code		Always NULL as it is deprecated in v2.
processing_id		Always NULL as it is deprecated in v2

Customer benefit

• User in role information is now standardized through entered_by_user_in_role_id and authorising_user_in_role_id foreign keys joined with user_in_role_v2
• Reduced risk of unexpected behavior in downstream processes
• Clear distinction between deprecated columns and new standardized relationships

Customer action

• Identify any queries or reports that use the deprecated columns
• Update processes to use entered_by_user_in_role_id and authorising_user_in_role_id, joining with user_in_role_v2 to retrieve user in role details

3. Addition of new columns

The v2 immunisation model introduces additional columns to enhance auditability, relationships, and vaccine administration details. These include:

User tracking columns:

• entered_by_user_in_role_id
• authorising_user_in_role_id

Relationship and context columns:

• observation_id
• parent_observation_id
• parent_observation_guid
• parent_observation_uuid
• consultation_id
• problem_observation_id
• problem_observation_guid
• problem_observation_uuid
• problem_end_datetime
• episodicity
• episodicity_description
• observation_organisation_id
• is_parent
• is_abnormal
• associated_text

Numeric and range columns:

• numeric_operator
• numeric_value
• unit_of_measure
• uom_dmd
• ucum_code
• range_minimum_operator
• range_minimum
• range_minimum_text
• range_maximum_operator
• range_maximum
• range_maximum_text
• range_units
• range_qualifier_description
• qualifier_mapping

Why?

These columns have been added to enhance the data model:

• User tracking columns: Enable auditing and lineage tracking by providing standardized foreign keys to the user_in_role_v2 table, replacing the previously nullable GUID columns
• Relationship and context columns: Support hierarchical immunisation structures, link immunisations to consultations and problems, and provide clear organisational context
• Numeric and range columns: Standardize how vaccine doses and measurements are represented with operators, units of measure, and reference ranges for dosing schedules and administration guidelines

Customer benefit

• Improved data consistency through standardized foreign key relationships
• Enhanced ability to track immunisation hierarchies and linked clinical context
• Accurate vaccine dose interpretation with standardized operators and units
• Comprehensive reference range information for vaccination schedules
• Better temporal and encounter-based analysis capabilities

Customer action

• Join user tracking columns with user_in_role_v2 for audit trails
• Use relationship columns to build hierarchical immunisation structures and link to consultations and observations.
• Incorporate vaccine administration columns when analysing doses to ensure correct interpretation
• Utilize range columns for dosing schedules and administration guidelines

Examples

Get all immunisations for a patient

SELECT *
FROM hive.explorer_ipcv_vanilla.immunisation_v2
WHERE patient_id = 123456789
LIMIT 100

Find sensitive or confidential immunisations

SELECT *
FROM hive.explorer_ipcv_vanilla.immunisation_v2
WHERE is_sensitive = TRUE
  OR is_confidential = TRUE
LIMIT 100

Get immunisations with SNOMED mapping for interoperability

SELECT
  i.observation_id,
  i.code_id,
  i.original_term,
  i.batch_number,
  i.manufacturer,
  m.snomed_concept_id,
  m.snomed_description_id
FROM hive.explorer_ipcv_vanilla.immunisation_v2 i
LEFT JOIN hive.explorer_ipcv_vanilla.mkb_mapping_dmdpreparation_v2 m
  ON i.code_id = m.emis_code_id
  AND i.organisation = m.organisation
WHERE m.snomed_concept_id IS NOT NULL
LIMIT 100