Editing 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.<ref>https://www.tewhatuora.govt.nz/our-health-system/digital-health/the-aotearoa-immunisation-register-air/the-aotearoa-immunisation-register-air/</ref>
* Specified vaccines are:
** DTaP
** MMR
** Covid
** Flu
* HSU<ref>https://www.stats.govt.nz/reports/review-of-health-service-user-population-methodology</ref> 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.<ref>At the current time, Monday afternoons.</ref> Contact for this service is [mailto:manmeet.kaur@tewhatuora.govt.nz Manmeet Kaur].

The current process is manual and requires:
* Pickup of flat files
* Load to Postgres RDS
* Run function <code>imms.fn_imms_merge()</code> 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 <code>kpt</code> database, under <code>imms</code> schema. A single table exists for each vaccine file; <code>extract date</code> is used to separate and identify the latest data in each table.

The <code>imms.fn_imms_merge()</code> 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 (<code>imms.imms_all</code>)
* 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:

<syntaxhighlight lang="sql" line>
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
</syntaxhighlight>

===ELT Process===

Overview of current process shown below:

<mermaid>
%%{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;

</mermaid>


===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 <code>imms.vw_swpics_imms</code> as needed.


== Data Dictionary ==

{| class="wikitable"
! 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.
|}


==References==

[[Category:Apps]]