Emergency Department Flow & Bed Occupancy Report
An operational Power BI report for a mid-size NHS trust tracking the 4-hour ED standard, waits, ambulance handovers and general and acute bed occupancy. Oracle PAS/EDIS data and Excel bed-state returns land in a Fabric lakehouse medallion, served through a Direct Lake star schema.
Audience: Chief Operating Officer, divisional directors, ED clinical leads, site and patient flow managers. Generated by Blueprint BI on 2026-11-19 and shown exactly as generated.
What was entered
These are the only inputs. Everything below was generated from them.
- Industry
- Healthcare
- Report type
- Operations
- BI tool
- Power BI
- Currency
- GBP (£)
- Data sources
- Oracle, Excel / CSV
- Columns
- None given (Claude designed the model)
- KPIs
- None given (Claude proposed them)
- Notes
- Emergency department flow for a mid-size NHS trust. Focus on 4-hour target and bed occupancy.
- Style and pages
- Corporate Clean, 2 pages
Scope
Purpose, default period and where each input column lands in the model.
- Purpose
- Are we meeting the 4-hour ED standard, where do delays occur, and is bed occupancy constraining flow?
- Audience
- Chief Operating Officer, divisional directors, ED clinical leads, site and patient flow managers
- Default period
- Last 13 complete weeks, refreshed daily by 06:00
Business rules
- 4-hour performance. Attendance meets standard when DepartureDateTime minus ArrivalDateTime is 240 minutes or less; all ED types included. Calculated in: Silver layer, every load (Within4HoursFlag)
- 12-hour wait from arrival. Attendance with total time in department over 720 minutes. Calculated in: Silver layer, every load (Over12HoursFlag)
- G&A bed occupancy. Occupied general and acute beds / available G&A beds at midnight census; excludes critical care, maternity and paediatric beds. Calculated in: Gold layer daily snapshot; ratio in semantic model
- Ambulance handover delay. Handover over 30 minutes from ambulance arrival to clinical handover. Calculated in: Silver layer, every load (HandoverOver30Flag)
1. Architecture
From source systems to the semantic model and the people who use it.
Data flow detail
- Ingestion
- Copy activities land Oracle extracts and SharePoint files into Bronze daily.
- Bronze
- Raw Oracle extracts and files, append-only with load timestamp.
- Silver
- Cleansed, pseudonymised attendances with derived wait flags and conformed codes.
- Gold
- Facts and conformed dimensions optimised with V-Order for Direct Lake.
- Semantic model
- Single star-schema model with measures table, RLS by site and certified for reuse.
- Caveat: Calculated columns and views force DirectQuery fallback; build derived columns in Gold.
- Caveat: Guardrails on row counts per table by capacity SKU; monitor attendance fact growth.
- Caveat: RLS defined in the model can trigger DirectQuery fallback if SQL endpoint security is used instead.
Governance & security
- RLS by site using DimSite[SiteCode] mapped to Entra ID groups.
- No patient identifiers in Gold; NHS number pseudonymised in Silver per IG policy.
- Data quality checks: departure before arrival, null timestamps and duplicate attendances quarantined.
- Refresh monitoring via Fabric Monitoring hub with alerts to BI support.
- Measures reconciled monthly to the national ECDS submission.
Source detail
- ED attendances: Patient-level ED attendance events with arrival, triage and departure timestamps.
- Bed state census: Midnight occupied and available beds per ward.
- Ward and specialty reference: Ward bed type, division and site mapping maintained by information team.
- Operational targets: Monthly 4-hour and occupancy trajectories by site.
| Refresh and alerts | Ready by 06:00 daily for the 08:00 bed meeting; failures alert the BI support Teams channel and on-call analyst |
|---|---|
| Storage mode | Direct Lake |
| History | 3 years plus current year to support year-on-year comparison |
| Environments | Dev, Test and Prod Fabric workspaces |
| Deployment | Fabric deployment pipelines with data source rules |
| Source control | Notebooks, semantic model and report stored as PBIP in Azure DevOps Git |
2. Data model
Star schema with grain, keys and relationships.
Star schema (galaxy with two facts)
3 fact · 4 dimension tables · 8 relationships · measures on _Measures
- DateKeyINT
- DateDATE
- WeekEndingDATE
- MonthNumberINT
- MonthYearNVARCHAR(10)sort by MonthYearSort
- MonthYearSortINT
- DayOfWeekNameNVARCHAR(10)sort by DayOfWeekNumber
- DayOfWeekNumberINT
- FinancialYearNVARCHAR(7)
- WardKeyINT
- WardCodeNVARCHAR(10)Business key
- WardNameNVARCHAR(100)
- DivisionNVARCHAR(50)
- BedTypeNVARCHAR(30)
- ValidFromDATE
- ValidToDATE
- AttendanceIDNVARCHAR(20)Degenerate dimension
- ArrivalDateKeyINT→ DimDate[DateKey]
- SiteKeyINT→ DimSite[SiteKey]
- ArrivalModeKeyINT→ DimArrivalMode[ArrivalModeKey]
- ArrivalHourINT
- TimeInDeptMinutesINT
- HandoverMinutesINT
- Within4HoursFlagINT
- Over12HoursFlagINT
- HandoverOver30FlagINT
- AdmittedFlagINT
- CensusDateKeyINT→ DimDate[DateKey]
- WardKeyINT→ DimWard[WardKey]
- SiteKeyINT→ DimSite[SiteKey]
- AvailableBedsINT
- OccupiedBedsINT
- NoCriteriaToResideBedsINT
- MonthStartDateKeyINT→ DimDate[DateKey]
- SiteKeyINT→ DimSite[SiteKey]
- FourHourTargetDECIMAL(5,4)
- OccupancyTargetDECIMAL(5,4)
- SiteKeyINT
- SiteCodeNVARCHAR(10)Business key
- SiteNameNVARCHAR(100)
- EDTypeNVARCHAR(30)
- ArrivalModeKeyINT
- ArrivalModeCodeNVARCHAR(10)Business key
- ArrivalModeNVARCHAR(30)
- AcuityBandNVARCHAR(30)sort by AcuitySort
- AcuitySortINT
Grain note. FactEDAttendance has no relationship to DimWard, so its measures are only meaningful by the other dimensions.
Grain note. FactBedCensus has no relationship to DimArrivalMode, so its measures are only meaningful by the other dimensions.
Grain note. FactSiteTarget has no relationship to DimWard, DimArrivalMode, so its measures are only meaningful by the other dimensions.
| From (many) | To (one) | Cardinality | Filter direction | Active |
|---|---|---|---|---|
| FactEDAttendance.ArrivalDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactEDAttendance.SiteKey | DimSite.SiteKey | many-to-one | Single | Yes |
| FactEDAttendance.ArrivalModeKey | DimArrivalMode.ArrivalModeKey | many-to-one | Single | Yes |
| FactBedCensus.CensusDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactBedCensus.WardKey | DimWard.WardKey | many-to-one | Single | Yes |
| FactBedCensus.SiteKey | DimSite.SiteKey | many-to-one | Single | Yes |
| FactSiteTarget.MonthStartDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactSiteTarget.SiteKey | DimSite.SiteKey | many-to-one | Single | Yes |
3. DAX measures
An excerpt: the first 6 of 30 measures. Download the .dax file for all of them.
ED Attendances =
COUNTROWS ( FactEDAttendance )Attendances Within 4 Hours =
SUM ( FactEDAttendance[Within4HoursFlag] )4 Hour Performance % =
DIVIDE ( [Attendances Within 4 Hours], [ED Attendances] )4 Hour Performance % PY =
CALCULATE ( [4 Hour Performance %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )4 Hour Performance YoY pts =
VAR Cur = [4 Hour Performance %] VAR PY = [4 Hour Performance % PY] RETURN IF ( NOT ISBLANK ( Cur ) && NOT ISBLANK ( PY ), Cur - PY )4 Hour Target =
VAR T = SUMX ( FactSiteTarget, FactSiteTarget[FourHourTarget] * CALCULATE ( [ED Attendances] ) ) RETURN DIVIDE ( T, CALCULATE ( [ED Attendances], TREATAS ( VALUES ( FactSiteTarget[SiteKey] ), DimSite[SiteKey] ) ) )4. KPIs and RAG rules
| KPI | Target | Direction | Green (1) | Amber (2) | Red (3) | Example |
|---|---|---|---|---|---|---|
4-Hour Performance Attendances within 4 hours / total ED attendances. [4 Hour Performance %] RAG: [4 Hour Performance RAG] | 78% | Higher | ≥ 78.0% | ≥ 73.0% | < 73.0% | 74.6% +2.1 pts Amber |
G&A Bed Occupancy Occupied G&A beds / available G&A beds at last midnight census. [G&A Bed Occupancy %] RAG: [G&A Bed Occupancy RAG] | 92% | Lower | ≤ 92.0% | ≤ 95.0% | > 95.0% | 96.3% +1.4 pts Red |
12-Hour Waits Attendances over 12 hours from arrival / total attendances. [12 Hour Waits %] RAG: [12 Hour Waits RAG] | 2% | Lower | ≤ 2.0% | ≤ 4.0% | > 4.0% | 3.1% -0.6 pts Amber |
Ambulance Handovers Over 30 Minutes Handovers over 30 minutes / all recorded ambulance handovers. [Handovers Over 30 Min %] RAG: [Handovers Over 30 Min RAG] | 5% | Lower | ≤ 5.0% | ≤ 10.0% | > 10.0% | 4.2% -1.8 pts Green |
Average Time in Department Mean minutes from arrival to departure. [Average Time in Dept (mins)] RAG: [Average Time in Dept (mins) RAG] | 240 minutes | Lower | ≤ 240 | ≤ 300 | > 300 | 228 -12 Green |
How RAG is shown
Each KPI has a RAG measure returning 1, 2 or 3 (BLANK when there is no value), so the thresholds live in one place. In Power BI, use conditional formatting with Format style "Rules" based on the RAG measure: 1 green, 2 amber, 3 red, and an icon set on the same rule. Status is never shown by colour alone: every colour is paired with its icon shape or word.
5. Report pages
Page layouts with the theme applied. Charts show placeholder shapes; the KPI cards use Claude's sample values.
Page 1: ED Flow Summary
- Purpose
- Executive view of 4-hour performance, long waits and bed pressure.
- Audience
- COO, executive team and divisional directors
- Default filter
- Last 13 complete weeks, all sites
- Slicers
- DimDate[Date] (date range): Defaults to last 13 complete weeks. · DimSite[SiteName] (dropdown): Shows only RLS-permitted sites.
- Title label
- [Selected Period Label]
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Headline Flow KPIs | kpi strip | values: [4 Hour Performance %], [G&A Bed Occupancy %], [12 Hour Waits %], [Handovers Over 30 Min %], [Average Time in Dept (mins)] |
| Are we meeting the key ED and bed standards? |
| Weekly 4-Hour Performance vs Trajectory | line | axis: DimDate[WeekEnding] values: [4 Hour Performance %], [4 Hour Target] tooltip: [Breaches] |
| Is 4-hour performance improving toward trajectory? |
| 4-Hour Performance by Site | bar | axis: DimSite[SiteName] values: [4 Hour Performance %] tooltip: [4 Hour Variance pts] |
| Which sites are furthest from the standard? |
| Daily G&A Bed Occupancy | line | axis: DimDate[Date] values: [G&A Bed Occupancy %] tooltip: [No Criteria to Reside] |
| Is occupancy sustained above the safe ceiling? |
| 12-Hour Waits by Day of Week | column | axis: DimDate[DayOfWeekName] values: [12 Hour Waits] tooltip: [12 Hour Waits %] |
| Which days generate the longest waits? |
Page 2: Bed Occupancy & Flow Detail
- Purpose
- Operational detail on ward occupancy, arrivals and handovers for the bed meeting.
- Audience
- Site managers, patient flow team and ED clinical leads
- Default filter
- Last 13 complete weeks, selected site
- Slicers
- DimWard[Division] (buttons) · DimArrivalMode[ArrivalMode] (buttons): Affects ED visuals only; bed census not related.
- Drill-through from
- ED Flow Summary
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| Ward 10 Acute Medicine | £1393K | 101.7% |
| Ward 12 Respiratory | £1206K | 88.0% |
| Ward 14 Care of the Elderly | £1161K | 84.7% |
| Ward 7 General Surgery | £1392K | 101.6% |
| AMU | £1208K | 88.2% |
| Ward 21 Orthopaedics | £1173K | 85.7% |
- Ambulance
- Walk-in
- Other
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Ward Occupancy at Last Census | matrix | rows: DimWard[Division], DimWard[WardName] values: [Available Beds], [Occupied Beds], [G&A Bed Occupancy %], [No Criteria to Reside] |
| Which wards are full and holding patients without criteria to reside? |
| Attendances by Arrival Mode | donut | legend: DimArrivalMode[ArrivalMode] values: [ED Attendances] |
| What share of demand arrives by ambulance? |
| Arrivals and Performance by Hour | column | axis: FactEDAttendance[ArrivalHour] y: [4 Hour Performance %] values: [ED Attendances] |
| When do arrival peaks drive breaches? |
| 4-Hour Performance by Acuity | bar | axis: DimArrivalMode[AcuityBand] values: [4 Hour Performance %] tooltip: [Admission Rate %] |
| Which triage categories breach most? |
6. AI-designed page
Page 1 as drawn by the AI Design Studio (a Pro feature), unedited.
Assumptions and open questions
| # | Assumption or question | Impact if different |
|---|---|---|
| 1 | 4-hour performance includes all ED types (Type 1 and UTCs) per the national standard. | Add an EDType filter to 4-hour measures and split trajectories by type. |
| 2 | Year-on-year uses calendar dates; the NHS financial year only labels periods. | Add financial-period columns and rebase PY measures on them. |
| 3 | Bed occupancy is G&A only at the midnight census, not an 08:00 snapshot. | Change census grain and the BedType filter in bed measures. |
| 4 | Targets are monthly by site, so target measures are not sliced by ward, acuity or hour. | Extend FactSiteTarget grain and relate it to further dimensions. |
| 5 | Site RLS mapping comes from an Entra ID group per site maintained by IT. | Add a security mapping table sourced from the workforce system. |
| 6 | Ward division changes are tracked as SCD2 so history stays with the original division. | Switch DimWard to SCD1 and restate history to current divisions. |
Recommendations
- Partition the attendance Delta table by month and use incremental Oracle extracts to keep Direct Lake framing fast.
- Build all wait flags in Silver to avoid calculated columns triggering DirectQuery fallback.
- Reconcile 4-hour figures to monthly ECDS submissions and publish a sign-off note.
- Document measures with descriptions and display folders and certify the semantic model.
- Pin the summary page to the 08:00 bed meeting Teams channel to drive adoption.
- Replace the Excel targets file with a governed SharePoint list with validation to reduce load failures.