Stephen Clinton — Power BI Portfolio

Calls Waiting Wallboard

Requirement

For cases received on 999 requiring clinical call back, provide a live view of call volumes and wait times split across clinician groups. Audience: Clinical contact centre staff and team leaders, senior operational command staff.

Power BI Solution

Power BI dashboard polling the CAD every 60 seconds to display case counts by response priority in both a numerical and coloured bar visualisation along with bar visualisation of cases being assessed by each group of clinicians. Delivered a near real-time operational view of clinical queue pressure, enabling supervisors to intervene dynamically in queue management and prioritisation.

Data Engineering

Initiation of an unused CAD indicator, with support from the vendor and adjustment to parameterised setting in operations, new multi-condition CASE logic across separate CAD event tables and the optimisation of a query against the live CAD tables (sub 1 second refresh) to prevent interference with CAD operations.

Key Performance Indicator – Outcomes Based

Requirement

Report a significant internal and external KPI with high accuracy showing case outcomes and the nature of the group resolving, across volumes and percentage of all cases. Audience: Clinical contact staff, leadership and senior leadership teams, board and external stakeholders.

Power BI Solution

New Power BI Report showing the percentage and volume over time against headline figures and group level outcome detail. Report uses bookmark functionality for percentage / volume transition and a floating information window.

Data Engineering

Redesigned KPI measurement to use case outcomes via a new reference table, replacing keyword-based calculation methodology. Provided socialisation for stakeholders and database view of all cases and outcomes for reference.

Key Performance Indicator – Time Based

Requirement

Provide a reliable methodology to measure time from case confirmed to clinical assessment, accounting for complex clinical event sequencing in the patient management system. Audience: Clinical leadership, senior operations team, performance stakeholders with summaries to executive team and board.

Power BI Solution

Power BI report built over the resulting dataset showing metrics over time and with filtering by case priorities and the group assessing the case. Drill through for incident detail and a separate help / information / version page.

Data Engineering

Defined a robust start and end indicator for the metric, based on available system timestamps. Complex SQL to correctly attribute the assessment start time to the right set of clinicians from the clinical queue event table. New SQL procedures and table creation to process clinical queue data for accuracy.

Response and Utilisation Information – Staff Group

Requirement

Report on Volunteer Responder activity including times available, utilisation while available and 999 calls responded to. Audience: Responder management team, operations leadership and performance management groups.

Power BI Solution

Power BI report to present visually in a format that suited the responder team including time and event-based views, metrics and field parameter filters.

Data Engineering

Volunteer Responders book available on the response management system in a different way to regular crews making standard reports inaccurate. Clarified undocumented areas of the response management system from the vendor and built new raw data capture processes to reporting servers. Captured event times and calculated availability durations, temporal modelling used to join response tables and capture utilisation activity.

Clinical Call Flow Comparison

Requirement

Report the flow of calls through clinical teams, showing call status on entry to the queue and the outcome type, to identify patterns in how calls are processed across different clinical levels. Audience: Clinical leadership groups to enable identification of repeatable call pathways and bottlenecks, directly influencing operational decision-making.

Power BI Solution

New dashboard with visualisations that aid the viewer in comparing the input and outcome for maximum usability of complex scenarios. Drill through detail pages and a separate page for help, information and version updates.

Data Engineering

Designed a new state-transition model from CAD event data using level-change data and ordered event streams with LEAD/LAG logic to capture time a case spends with each clinician group. Enforced temporal accuracy with no overlaps and no leakage between levels. Created persistent tables to materialise true state changes to avoid later re-processing large datasets.

Patient Assessments Report (Questionnaire Data)

Requirement

Provide visibility of clinician decision-making in remote patient assessments, supporting volume, duration and time-based analysis, patient response associations, outcomes and individual clinician performance. Data based on questionnaire style patient assessments designed to guide the clinician and carried out over the telephone. Audience: Clinicians, team leaders, operational leadership.

Power BI Solution

Multi-page Power BI report showing volume analysis, assessment durations and outcomes with correlations between responses for all activity and by individual clinician. Additional pages show anonymous incident level detail and version and report detail information for the user.

Data Engineering

Designed and built a new episode-based model of questionnaire responses defining the start and end of each episode to enable volumetric counting and sequencing of assessments. Implemented multiple response handling, text cleaning to standardise entries, branch logic for separate pathways in the same questionnaire and a reusable cleaned data set for all questionnaire reporting across multiple clinician streams.

Telephony Report

Requirement

Provide information on telephone activity to and from the 999 clinicians; call distribution, volume, handling and wait times including detail to identify specific calls for review. Audience: Clinical contact centre leadership, clinical group supervisors, operations and performance groups.

Power BI Solution

Multi-page report for volumes, call durations, handling time analysis, wait time analysis and call details for inbound and outbound calls with anonymous call level drill through and a data glossary page.

Data Engineering

Joined previously created call and agent event detail records to capture activity for this group of clinicians.

Operations Report

Requirement

Provide visibility of the call handling and clinical assessment activity from the patient management system. Include case volumes, activity frequency and durations as well as headline metrics, patient demographics, performance for internal and external indicators, and case outcome information. Replace multiple fragmented reports from multiple reporting systems. Audience: KPI pages for assessment times used in all external performance reports to stakeholders and all pages used daily by operations teams as the main source of operational information.

Power BI Solution

Multi-page consolidated operational dashboard with high-level KPI tracking and detailed drill-down analysis. DAX measures for time-based KPIs and aggregation across operational dimensions. Filter page with indicators on each page to show filtering impact.

Data Engineering

Data from the Patient Management System minimum data set provided by Data Engineering with additional metrics derived where not natively available.

Electronic Patient Care Record Report

Requirement

Summarise patient care records captured by the front-line clinical responders, volume analysis, patient demographics, questionnaire and outcome analysis. Audience: Clinical leadership, response team supervisors, clinical audit teams.

Power BI Solution

Large multi-page Power BI report to show all ePCR information including common placement of report-wide filters with metrics to ease filtering to see impact and relationships between patient findings and interventions. Additional specialist reports for Maternity calls and Medicines Management.

Data Engineering

Vendor servers synchronised multiple times daily to organisation data warehouse. Supplemental tables queried for additional information including interaction logic to present relationships between medications administered. Logic built to capture ePCR records with no link to response records.

Hospital Handover Dashboard

Requirement

Capture and report handover details in major receiving hospitals with breakdown by hospital and region. Required for external KPI reporting to stakeholders and liaison with hospitals to improve process. Audience: Response team leadership, hospital liaison team, operational and performance groups.

Power BI Solution

Single page Power BI summary report with drill through information for specific calls, floating window information, help and version page.

Data Engineering

Data extracted from previously unused response management system tables. Required vendor support plus complex transformation logic to construct usable tables from raw unstructured data and align with incident response times. Documented full data process for future maintainability.

Welsh Speakers Heatmap

Requirement

A heatmap visual of the base locations of Welsh speaking staff, for publication in printed and online media. Audience: Internal report readers, Board, Welsh Language Commissioner and other external stakeholders.

Power BI Solution

Significant styling in a single page Power BI report to enable the map to be used in a presentation and printed document.

Data Engineering

Integrated and cleaned human resource system data (national database) and merged with response data for base station locations to create a dataset specifically for geospatial visualisation. Repeatable for future years’ publications.