Overview#
Connect Power BI or Tableau directly to a purpose-built ambulance analytics warehouse and answer board-level performance questions without re-entering data or waiting on an engineering team.
Operational electronic patient care record (ePCR) systems are optimised for capturing one patient encounter at a time, not for analysing thousands of them at once. This capability provides a dedicated star-schema (dimensional) data warehouse, modelled on Kimball methodology, that sits alongside the live ePCR store and is kept current by an incremental change-data-capture pipeline. Analysts query pre-built aggregations over encounters, vital signs, medications, clinical guideline decisions, and hospital handovers, all without touching the operational database that crews depend on during a shift.
The result is a short path from frontline ePCR data to clinical governance reporting. Quality leads track guideline compliance, monitor hospital handover delays, and review sepsis, STEMI, and stroke alert incidence across any date range, each within their own tenant boundary. Patient data is de-identified at the warehouse layer so that performance analytics never expose individual identities.
Key Features#
-
Dimensional Star-Schema Model. A Kimball-style warehouse separates measurable facts (encounters, vital-sign readings, medication administrations, guideline decisions, handovers, mass-casualty triage tags, patient telemetry, telemedicine sessions, app launches, and attachments) from descriptive dimensions, so aggregate questions resolve quickly without scanning operational tables.
-
Slowly-Changing Dimensions. Patient, practitioner, vehicle, and hospital dimensions are versioned using SCD Type 2 history, with valid-from and valid-to ranges and a current-record flag. Historical reports remain accurate even after a practitioner moves station or a vehicle is reassigned.
-
De-Identified Patient Layer. Date of birth is reduced to a five-year age band and Eircode is truncated to its routing key before any record lands in the warehouse, giving analysts demographic and geographic breakdowns without exposing individual patients.
-
Incremental Sync with Full-Refresh Fallback. A change-data-capture pipeline moves only new and changed rows from the operational store into the warehouse on a schedule, with a full rebuild available on demand. Every run is logged with row counts, timing, and status for operational assurance.
-
Pre-Built Clinical Aggregations. The read-only query layer ships ready-made measures: encounters by care model or disposition, vital-sign percentile distributions, guideline usage and override ranking, formulary administration ranking, handover latency statistics, per-practitioner compliance, and a sepsis, STEMI, and stroke alert summary.
-
Vital-Signs Percentile Distributions. For any captured vital (heart rate, blood pressure, respiratory rate, oxygen saturation, end-tidal carbon dioxide, blood glucose, temperature, or early-warning score) the layer returns count, minimum, 25th percentile, median, 75th percentile, maximum, and mean across a chosen range.
-
Handover Performance Tracking. Handover latency statistics report mean delay, median delay, and the late-handover rate by receiving hospital, making delays at specific facilities visible and comparable over time.
-
Direct ODBC Connectivity. A connection-information endpoint surfaces read-replica host, port, database, SSL requirement, and driver guidance so business intelligence tools consume live warehouse data as a familiar PostgreSQL source, with no bespoke connector.
Use Cases#
Clinical Governance and Quality#
Clinical governance teams review guideline usage and override ranking to find guidelines that crews frequently set aside, then drill into per-practitioner compliance to identify where additional training would have most impact. Sepsis, STEMI, and stroke alert summaries quantify time-critical alert incidence across the service for any reporting period.
Operations and Performance#
Clinical operations directors monitor hospital handover delays and late-handover rates by facility, track scene and journey times through the encounter fact table, and compare encounter volumes by care model or disposition to understand demand patterns and resourcing pressures.
Analytics and Reporting Teams#
Ambulance service analytics managers and EMS quality leads connect Power BI or Tableau straight to the read-replica and build their own dashboards over the pre-built measures, shortening the route from raw operational data to a board pack without engineering involvement.
Formulary and Medicines Oversight#
Pharmacy and medicines-management leads use the formulary administration ranking, keyed by BNF code, to see which drugs are administered most often, at what mean dose, and within which drug class, supporting stock planning and clinical audit of medicines use.
Integration#
The capability is reached through REST metric endpoints under /api/v1/bi/metrics/, each returning aggregated results scoped to the caller's organisation and protected by a dedicated business intelligence read permission. A parallel GraphQL layer, built with the Strawberry framework, exposes the same measures for read access alongside administrative operations such as triggering a sync run.
A connection-information endpoint at /api/v1/bi/odbc-endpoint-info returns read-replica details (host, port, database, SSL requirement, and a connection-string template) so Power BI and Tableau attach to the warehouse over standard ODBC as a PostgreSQL data source. Because the warehouse is fed by the operational ePCR store, the figures a customer sees in their reporting tool reflect recent frontline activity, not a stale export.
All access is authenticated with OAuth 2.0 bearer tokens in JSON Web Token format, consistent with the wider platform identity layer. The warehouse normalises ePCR data into a stable set of facts and dimensions, so customers plug standard analytics tools into a familiar schema and gain durable, version-safe reporting without modelling the data themselves. Saved queries can be parameterised and reused, with the caller's tenant identity always injected on the server so a published query cannot reach across organisations.
Open Standards#
- BNF (British National Formulary). The drug dimension is keyed by BNF code, enabling interoperable formulary analytics and consistent medicines reporting across systems that recognise the same coding.
- NEWS2 (National Early Warning Score 2). Early-warning scores are stored per vital-sign reading and aggregated per encounter, so deterioration trends can be analysed using a recognised national scoring scale.
- PHECC (Pre-Hospital Emergency Care Council). Practitioner certification level is carried as an attribute on the practitioner dimension, allowing activity and compliance to be broken down by recognised clinical grade.
- SNOMED CT. Discharge diagnosis is captured as a SNOMED CT code in the encounter outcome fact, supporting interoperable diagnosis coding and onward linkage with hospital and national datasets.
- SCD Type 2 (Kimball slowly-changing dimension methodology). Applied to the patient, practitioner, vehicle, and hospital dimensions to preserve full historical context for time-accurate reporting.
- HIPAA and GDPR de-identification. Age is stored as a five-year band rather than date of birth, and Eircode is truncated to its routing key at the warehouse layer, reducing identifiability while preserving analytical value.
- EDF and PESCO data sovereignty. Multi-tenant isolation is enforced by an organisation identifier on every fact table and tenant-specific dimension, so data residency and sovereignty obligations are upheld at the schema level.
Security and Compliance#
Every warehouse query is scoped to the requesting organisation. Cross-tenant aggregation is structurally prevented: facts and tenant dimensions carry an organisation identifier, the read-only query layer always filters on the caller's tenant, and saved queries have the caller's tenant identity force-injected on the server. Read access requires a dedicated business intelligence permission, and sync operations are restricted to administrators.
Patient data is de-identified before it reaches the warehouse, so analytics consumers and connected reporting tools work with age bands and truncated Eircodes rather than identifiable records. Business intelligence tools connect to a read-only replica over an SSL-required connection, keeping reporting traffic off the operational database and ensuring published views cannot return data outside the connecting organisation.
Last Reviewed: 2026-05-26 / Last Updated: 2026-05-26