Imms

Background

Imms is a Power BI app designed to show key information for the K'aute health team. It provides an overview of the following data:

• Pasifika people domiciled in the Waikato region who are flagged as under-immunised for specified vaccines in the AIR.
• Specified vaccines are:
  • DTaP
  • MMR
  • Covid
  • Flu
• HSU data is used to also show people not enrolled with a primary care provider. As of 7/2/2024 this now includes the most recent available HSU Data for FY2022/23.
• Domicile and contact information is provided where available.

Data sources

The source data is provided via the National Intelligence team within the National Public Health Service.

Separate CSV files per vaccine are pushed to an sFTP endpoint for pickup, usually on a weekly basis. Contact for this service is Manmeet Kaur.

The current process is manual and requires:

• Pickup of flat files
• Load to Postgres RDS
• Run function imms.fn_imms_merge() to pivot data into a single reporting table
• Refresh Power BI.

Eligible Population

The eligible population is sourced from the consumer table within ImmSOT. The consumer table is constructed from individuals sourced from the NIR, CIR, HSU, and NES.

ImmSOT: Immunisation Source of Trust. This is a database that holds immunisation information uploaded via AIR and PMS systems.

NIR: The National Immunisation Register is a computerised information system that has been developed to hold immunisation details of New Zealand children. The NIR was introduced in 2005 and decommissioned in **December 2023**.

CIR: The Covid Immunisation Register is a computerised information system that was developed to hold Covid and Flu immunisation details** of all New Zealanders. The CIR was decommissioned in December 2023.

HSU: The Health Service User table provides information about the number of people in New Zealand who used health services in the 2022 calendar year (01 Jan 2022 - 31 Dec 2022).

NES: The National Enrolment Service provides national enrolment and identity data.

Immunisation Data Sources

Immunisation data is sourced from ImmSOT.

ImmSOT: Immunisation Source of Trust. This is a database that holds immunisation information uploaded via AIR and PMS systems.

AIR: The Aotearoa Immunisation Register is a computerised information system that has been developed to hold immunisation details of all New Zealanders. AIR fully replaced NIR and CIR in December 2023.

Filters Applied to the Data

• The remainder cohort is limited to individuals under 12 years old.
• The remainder cohort excludes individuals flagged as inactive in the ImmSOT consumer table.
• The remainder cohort is limited to individuals who identify as Māori.

Disclaimer

Data is sourced from **AIR** and may be subject to any **data quality issues** still to be resolved in AIR, which includes, but is not limited to:

• Incorrect vaccination event dates
• Miscoding of vaccination given
• Miscoding of person to vaccination event

Note: These data quality issues are largely attributed to the fact that many fields within AIR are **free text** and are therefore **prone to data entry errors**.

Configuration

The app is published directly to the Health team lead, Clinical Director and GM.

Technical information

Data store is the kpt database, under imms schema. A single table exists for each vaccine file; extract date is used to separate and identify the latest data in each table.

The imms.fn_imms_merge() function executes daily at 1300 UTC via cron and is used to:

• Insert a list of unique identifiers across all new data into a reporting table (imms.imms_all)
• Update each row from new data to flag vaccine status separately for each vaccine, as well as update demographic/contact information.

Rationale for above is that there is often overlap of patient groups between vaccine data sets (especially childhood imms, but less frequently also the Covid data) and we need to see any combination of vaccines a person requires.

This function has a check on it to make sure it only executes when there is new data in underlying tables:

IF (select max(extract_date) from imms.imms_all) = (select max(extract_date) from imms.two_mmr) THEN
		RETURN 'No update needed EXITING'; --stop execution here if test is TRUE
	ELSE
		BEGIN --begin/end block to hold all the merge logic
			RAISE NOTICE 'Doing a merge'; --just for info

ELT Process

Overview of current process shown below:

%%{init: {'theme': 'default', 'flowchart': {'useMaxWidth': false,'htmlLabels': false}, 'logLevel': 'debug', 'securityLevel': 'loose', 'themeVariables': {'fontSize': '10px', 'nodeSpacing': 20, 'rankSpacing': 20}} }%% flowchart TB subgraph EB0["📅 EventBridge"] direction TB EB1["cron(30 18 ? * MON,TUE *)"]:::mono-.-> EB2[Sync_Imms_Files_Weekly]:::mono end subgraph L0["⚙️ Lambda"] direction TB L1[syncImmsFileSftp]:::mono subgraph S30["📁 s3://kpa-imms"] direction TB S31[Check for new files]-.-> S32[Convert to parquet]-.-> S33[Move to folder path <code> parquet/ </code>] end subgraph TW0["🔒 SFTP Endpoint"] direction TB N1[Weekly data produced]-.-> N2[Files added to filepath] end L21[copyImmsData]:::mono end subgraph DB0["🗄️ Database"] DB1[Append new data to target in schema<code> imms </code>] DB2["cron(0 13 * * 2)"]:::mono-.-> DB3["rpt.fn_imms_merge()"]:::mono-.-> DB4[Patient level view of all overdue imms<code> imms.imms_all </code>] end L1<-->TW0 L1<-->S30 TW0<-->S30 EB2-->L0 S33-.PUT Trigger.->L21 L21-.->DB1 classDef mono font-family:Courier New,fill:#393939,color:#ffffff;

SWPICS

We were previously providing a subset of this data for people domiciled in South Waikato to SWPICS.

Relevant data for them can be produced via imms.vw_swpics_imms as needed.

Data Dictionary

Field Name	Description
EXTRACT_DATE	Extract date.
NHI	NHI number (unique identifier for individuals).
FIRST_NAME	First name.
MIDDLE_NAME	Middle name(s).
FAMILY_NAME	Family name.
PHONE_NUMBER	Phone number (sourced from NES, NIBS, CIR).
DATE_OF_BIRTH	Date of birth.
CURRENT_AGE	Age calculated as at previous week end date.
SCHEDULE_AGE	Immunisation schedule age (e.g., 6W, 3M, 5M, 12M, etc.).
PRIORITY_ETHNICITY_LEVEL_1	Prioritised ethnicity (main category).
PRIORITY_ETHNICITY_LEVEL_2	Further granular breakdown of prioritised ethnicity.
ADDRESS_LINE_1	Address line 1 (sourced from NHI Register).
ADDRESS_LINE_2	Address line 2 (sourced from NHI Register).
ADDRESS_LINE_3	Address line 3 (sourced from NHI Register).
ADDRESS_LINE_4	Address line 4 (sourced from NHI Register).
MESHBLOCK_2018	Meshblock geographic unit calculated as at 2018.
LATITUDE	Latitude for the centroid point of the meshblock boundaries (rounded to 4 decimal places).
LONGITUDE	Longitude for the centroid point of the meshblock boundaries (rounded to 4 decimal places).
SA2_2018_CODE	Statistical Area 2 (SA2) geographic unit code as at 2018.
SA2_2018_NAME	Statistical Area 2 (SA2) name as at 2018.
DHB_OF_RESIDENCE	District Health Board of residence.
DEPRIVATION_DECILE_NUM	Deprivation decile number for geographic unit as at 2018.
ENROLLED_WITH_PRACTICE	Flag indicating whether an individual is enrolled with a practice.
OUTREACH_SUB_STATUS	Immunisation outreach sub-status for individual.
DTAP_IPV_HEPB_HIB	Immunisation status for DTaP-IPV-HepB/Hib as at previous week end date.
PCV	Immunisation status for PCV as at previous week end date.
RV	Immunisation status for Rotavirus vaccine as at previous week end date.
MEN_B	Immunisation status for Meningococcal B vaccine as at previous week end date.
MMR	Immunisation status for Measles, Mumps, and Rubella (MMR) vaccine as at previous week end date.
HIB	Immunisation status for Haemophilus influenzae type B (Hib) vaccine as at previous week end date.
VV	Immunisation status for Varicella vaccine as at previous week end date.
DTAP_IPV	Immunisation status for DTaP-IPV as at previous week end date.
OVERALL_STATUS	Overall immunisation status for antigen groups (DTaP-IPV-HepB/Hib, PCV, MMR, HiB, DTaP-IPV, and Tdap). RV, VV, and MenB are excluded for specific reasons.
UNDER_REVIEW	Individual is active and being reviewed by AIR Admin for track and trace or referral allocation.
WITH_OUTREACH	Individual is actively managed with an allocated internal team or external Outreach Immunisation Services (OIS) provider.
UNABLE_TO_CONTACT	Flag used to record an unsuccessful outcome of outreach.
FULLY_IMMUNISED	Individual has received all required immunisations in the National Immunisation Schedule.
NOT_FULLY_IMMUNISED	Individual has not completed any of the required immunisations and has not declined any.
DECLINED	Individual or their parent has declined at least one required immunisation.
PARTIALLY_IMMUNISED	Individual has completed some required immunisations and has not declined any.
PARTIALLY_IMMUNISED_DECLINED	Individual has completed some immunisations but has declined others.
NOT_ELIGIBLE	Individual is not eligible to receive an immunisation under the National Immunisation Schedule.