Overview

A Northern Ireland–specific, NHSCT-aligned transformation project. I designed and built Aurora Domiciliary Care, a full analytics ecosystem showcasing best practice in operations, workforce, finance, and compliance.
It features realistic runs & rotas, ECM data (GPS, travel, shift types), finance uplift rules, and NHSCT locality models, all with GDPR-by-design principles and role-based security.

🔍 Key Highlights

  • Runs & Rotas: AM/PM/Night visits, primary/secondary carers, ordered sequences, travel-time mapping.
  • ECM Realism: GPS validation, travel minutes, weekend/evening/night uplifts, visit verification.
  • Finance Automation: Claimed vs Paid variance tracking, evening (+5%), night (+10%), weekend (+10%) uplifts, and remittance reconciliation.
  • Compliance & Quality: RQIA inspections with timeline and outcome scores, incident tracking, complaints SLA, and training expiry analytics.
  • Locality Benchmarking: Antrim/Ballymena, Causeway, Mid Ulster, and East Antrim KPI comparisons.

🖥️ Dashboards

Executive KPI Dashboard
Executive KPI Dashboard
Operations Performance Overview
Operations Performance Overview
Finance Claimed vs Paid Analysis
Finance Claimed vs Paid
Compliance: RQIA, Incidents, Complaints
Compliance & Quality
Runs & Rotas Overview
Runs & Rotas (Overview)
Workforce KPIs
Workforce KPIs
Excel KPI Overview
Excel – KPI Overview
Excel Care Packages View
Excel – Care Packages
Excel Clients Table
Excel – Clients
Excel Runs & Rotas
Excel – Runs & Rotas
Excel Remittance Reconciliation
Excel – Remittance
Excel Trust Payments
Excel – Trust Payments

Access Data Model (TBC)

Planned: schema diagram, table validation, audit trail examples.

⚙️ Tech Stack

| Layer | Technology | |:–|:–| | Data Layer | Microsoft Access / SQL – validated tables with audit fields | | ETL / Prep | Power Query, scheduled refresh, DAX measures | | Analytics | Power BI (RLS, field parameters, drillthrough, decomposition tree) | | Ops Toolkit | Excel-based Analyst Toolkit (pivots, slicers, validation charts) |

🔐 Governance & Security

  • UK GDPR / DPA 2018: Fully synthetic dataset; privacy-by-design model.
  • Access Control: Power BI RLS roles — Manager_By_Locality, Staff_Self.
  • Data Quality: Verified %, On-Time SLA %, Missed Visit %, Training Compliance %.
  • Audit & Retention: Full traceability for incidents, complaints, audits, and RQIA outcomes with purge policy demo.

📊 Outcomes

  • Operational Insight: Missed visits <1%, punctuality and travel-time performance measured.
  • Financial Transparency: End-to-end claimed/paid reconciliation and locality variance clarity.
  • Compliance Readiness: Real-time inspection and training readiness dashboards; full audit traceability.
  • Transformational Model: Demonstrates NI-specific digital transformation aligned with NHSCT locality structure.

🧩 Assets

  • Aurora.accdb — Access data model (clients, staff, runs, finance, compliance)
  • Aurora_PowerBI.pbix — Executive dashboards
  • Aurora_Analyst_Toolkit.xlsx — Excel insights pack
  • /img/portfolio/aurora/ — Screenshot gallery
Executive KPI Dashboard
Executive KPI Dashboard
Executive KPI (Mobile)
KPI (Mobile)
Operations Performance Overview
Operations Overview
Operations Dashboard Wide
Operations (Wide)
Finance Claimed vs Paid
Finance – Claimed vs Paid
Finance Mobile
Finance (Mobile)
Quality & Compliance
Quality & Compliance
Quality & Compliance (Mobile)
Compliance (Mobile)
Runs & Rotas Overview
Runs & Rotas
Runs & Rotas (Mobile)
Runs & Rotas (Mobile)
Workforce KPIs
Workforce KPIs
Workforce KPIs (Mobile)
Workforce (Mobile)
Power Query Clients Transform
Power Query – Clients
Power Query Training Records
Power Query – Training Records
Power BI Tables View
Tables View
Power BI Relationship Model
Relationship Model
DAX Measure Example 1
DAX Measure – 1
DAX Measure Example 2
DAX Measure – 2
DAX Measure Example 3
DAX Measure – 3
DAX Measure Example 4
DAX Measure – 4
DAX Measure Example 5
DAX Measure – 5
Measures dashboard
Measures Dashboard
Power Query Editor
Power Query Editor
Excel KPI Overview
Excel – KPI Overview
Excel Care Packages View
Excel – Care Packages
Excel Clients Table
Excel – Clients
Excel Runs & Rotas
Excel – Runs & Rotas
Excel Remittance Reconciliation
Excel – Remittance
Excel Trust Payments
Excel – Trust Payments
Excel Expenses
Excel – Expenses
Excel Mileage
Excel – Mileage
Excel Staff
Excel – Staff
Excel Complaints
Excel – Complaints

Access Data Model (TBC)

Future: schema diagram, table validation, audit trail examples.

↑