North West Ambulance Service Business Intelligence Barry Duke Senior

North West Ambulance Service Business Intelligence Barry Duke Senior Consultant, Ascribe

About NWAS The North West Ambulance Service is the biggest ambulance service in the country providing 24 hour, 365 days a year accident and emergency services to those in need of emergency medical treatment and transport. Our highly skilled staff provide life-saving care to patients in the community and take people to hospital or a place of care if needed.

About Ascribe Health & Social Care BI Portals Microsoft Integration to many key clinical and corporate systems 70 customers for BI 75% coverage of NHS on other solutions Public Sector (Health and Local / Regional Government) Output: Consulting Accelerators Hardened product

Purpose BI Challenges in NWAS Methodology Platform What we did and what we produced but first – what they said! http://www.itpro.co.uk/630817/introducing-excel-powerpivot

BI Strategy From From From From From From From From From From From department to enterprise reporting to knowledge submission to harvesting silos to warehouses batch to message desktop to web data sets to enterprise schematics bespoke to re-usable ad hoc to structured divisions to “Corp” reporting software to Microsoft BI stack

Data Warehouse Methodology

Microsoft BI Stack

Issues Business Intelligence helps solve Service design Performance / contract / finance management Capacity planning SLR Forecasting and projection Trend and time-series Service delivery - outcomes not inputs Mergers and acquisitions – e.g. Public Health functions

Data Set % CAT B Responses within/over 19 Mins # CAT C Responses % CAT C Responses in [local target] # AS3 Responses # CTC to Allocation (First Vehicle CAT A) % CTC to Allocation (First Vehicle CAT A) within/over 65 secs # CTC to Allocation (Best Vehicle CAT A) Dimensions # CAT B Responses Ambulance Crew Call Category (A, B, C, AS3) Commissioner (PCT, DHA) Date GPS (location of incident/Ambulance) Hospital Incident Type Location (Analysis Area, Address, Postcode) MPDS Code Organisation (Area Sector) Patient Staff (Call Taker, Dispatcher) Time Vehicle Station Vehicle Type % CAT A Responses within/over 19 Mins Business Matrix % CAT A Responses within/over 8 Mins X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X # Call Pick Up (AS1/2) % Call Pick Up (AS1/2) within/over 5 secs # Location Confirmation (CAT A) % Location Confirmation (CAT A) within/over 30 secs # RI to Allocation (First Vehicle CAT A) % RI to Allocation (First Vehicle CAT A) within/over 30 secs # RI to Allocation (Best Vehicle CAT A) % RI to Allocation (Best Vehicle CAT A) within/over 30 secs X X X X % CTC to Allocation (Best Vehicle CAT A) within/over 65 secs X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X Total Lost Unit hours 20 mins X X X X Days Lost at Hospitals Control Average Hospital Turnaround Demand and Response % Hospital Turnaround within/over 20 mins ProClarity # Hospital Attendances Dundas / RS # CAT A Responses RS Total Demand Sharepoint Portal Datasheet View Measures Hospital Turnaround X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X Drillable Chart Scorecard OLAP Application Data Store Integration Services Report Server MIS Application BING Reporting Services Operational Data Store Cubes Analysis Services

Dimensions Measures # CAT A Conveying Responses % CAT A Conveying Responses within/over 19 Mins # CAT B Conveying Responses % CAT B Conveying Responses within/over 19 Mins # CAT C Responses % CAT C Responses in [local target] # AS3 Responses X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X # On scene under/over 20 mins % On scene under/over 20 mins X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X Unit Hour Supply Unit Hour Utilisation Unit Hour Capacity X X X X X X X X X X X X X X X X X X X X X X Unit Hour Demand X X X X X X X X X X X X X X X X X X X X X X Planned Unit Hours % Job Cycle (CTC Time to Clear) under/over 60 mins Crew Job Cycle Left Scene to Hosp Allocation to Clear # Job Cycle (CTC Time to Clear) under/over 60 mins % Job Cycle (Allocation to Clear) under/over 58 mins % Allocated to At Scene under/over 6 mins X X X X X # Job Cycle (Allocation to Clear) under/over 58 mins # Allocated to At Scene under/over 6 mins Allocation to On Scene % Left Scene to at Hospital under/over 20 mins % Allocation to Mobile under/over 60 secs Hospital Turnaround Hospital Attendances Arrived to Notified Notified to Handover Handover to Clear # Left Scene to at Hospital under/over 20 mins # Allocation to Mobile under/over 60 secs X X X X X X X X X X X X % Acknowledgement to Mobile under/over 30 secs X X X X X X X X X X X X # Acknowledgement to Mobile under/over 30 secs X X X X X X X X X X X X % MDT to Acknowledgement under/over 30 secs X X X X X X X X X X X X X X X X X X X X X X # MDT to Acknowledgement under/over 30 secs X X X X X X X X X X X X X X X X X X X X X X %Allocation to MDT under/over 10 secs X X X X X X X X X X X X X X X X X X X X X X # Allocation to MDT under/over 10 secs Mean average Handover to Clear Time X X X X X X X X X % Handover to Clear Time 10/20/30 mins X X X X X X X X X # Handover to Clear Time 10/20/30 mins X X X X X X X X X X X X Mean Average Notified to Handover Time X X X X X X X X X X X X % Notified to Handover Time under/over 15 mins X X X X X X X X X X X X # Notified to Handover Time under/over 15 mins Mean average Arrived to Notified Time X X X X X X X X X X X X X X X X X X X X X X % Arrived to Notified under/over 10 mins X X X X X X X X X X X X X X X X X X X X X X # Arrived to Notified under/over 10 mins Total Lost Unit hours 20 mins X X X X X Days Lost at Hospitals X X X X X Mean Average Hospital Turnaround (arrived to clear) X X X X X X % Hospital Turnaround within/over 20 mins Control Job Cycle CTC to Location Confirmed CTC to Allocation # Hospital Attendances % CTC to Allocation under/over 120 secs (Best Vehicle) X X X # CTC to Allocation under/over 120 secs (Best Vehicle) % Location Confirmation within/over 30 secs X X X % CTC to Allocation under/over 120 secs (First Vehicle) # Location Confirmation X X X # CTC to Allocation under/over 120 secs (First Vehicle) % Call Pick Up (AS1/2) within/over 5 secs X X X % RI to Allocation (Best Vehicle) within/over 30 secs # Call Pick Up (AS1/2) X X X # RI to Allocation (Best Vehicle) % CTC to Allocation (Best Vehicle) within/over 65 secs X X X % RI to Allocation (First Vehicle) within/over 30 secs # CTC to Allocation (Best Vehicle) Demand and Response Demand and Response # RI to Allocation (First Vehicle) % CTC to Allocation (First Vehicle) within/over 65 secs X X X X X X X X X X X X X X X X X X X X X X # CTC to Allocation (First Vehicle) # Patient Journeys # Patients treated at the scene only # Calls resolved through telephone advice % CAT A Responses within/over 8 Mins Measures Groups # CAT A Responses Age Call Category (Gov) Call Category (Service) Call Sign Chief Complaint Clinic Type Commissioner Date Gender Geography Hospital Incident Type Late Handover Reasons Location Nature Organisation Reason Stop Staff Staff Type Tag Time Vehicle Type # Calls (Total Demand) The matrix! CTC to Clear Resources Resources X X X X X X X X X X X X X X X

Connecting you with your data: Sharepoint

Sharepoint Secure Delineates users by capability / capacity Board to incident record in less than 5 clicks Scorecards for exceptions and assurance Dashboards for drill-down Designed to meet self-service needs If a bus matrix is 100 measures by 20 dimensions then you can answer 2000 questions in seconds with no technical skills

Connecting you with your data: Sharepoint

Connecting you with your data: Excel

Power Pivot In-Memory BI 6m rows in Excel Slicers Sparklines Data mining features Adding in new data sets on the fly Direct interface to structured warehouse Published via Excel (NOT e-MAILED!) For analysts

Power Pivot

Bespoke presentation Formatting and presentation influences consumption as much as content! Gauges and dials don’t help analysts but they help others

Same Platform Generates Operational Clinical Intelligence

Public Health GIS

Public Health GIS

Summary The Microsoft stack A structured methodology Accelerator products and consultancy projects Meeting real business and clinical needs