All samples
Sample · Healthcare · Operations

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.

1Sources
ED attendances
Oracle (EDIS / PAS) · Daily 02:00
Bed state census
Oracle (PAS ward module) · Daily 01:00
Ward and specialty reference
Excel / CSV · Weekly
Operational targets
Excel / CSV · Monthly
2Ingestion
Microsoft Fabric Data Factory pipelines
Incremental load on Oracle LastModified watermark; full reload of reference files
3Storage & transform
Bronze
Fabric Lakehouse (Delta)
Silver
Fabric Lakehouse with Spark notebooks
Gold
Fabric Lakehouse (Delta star schema)
4Semantic model
Power BI semantic model (Fabric)
Direct Lake
5Consumers
Executive team
Power BI App
Site and flow managers
Power BI App on mobile at bed meetings
Divisional directors
subscription email weekly

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 alertsReady by 06:00 daily for the 08:00 bed meeting; failures alert the BI support Teams channel and on-call analyst
Storage modeDirect Lake
History3 years plus current year to support year-on-year comparison
EnvironmentsDev, Test and Prod Fabric workspaces
DeploymentFabric deployment pipelines with data source rules
Source controlNotebooks, 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

Key Business key Foreign key Degenerate Numeric Hidden Sort by
DimDateDate tabledimension
Grain: One row per calendar day
  • DateKeyINT
  • DateDATE
  • WeekEndingDATE
  • MonthNumberINT
  • MonthYearNVARCHAR(10)
    sort by MonthYearSort
  • MonthYearSortINT
  • DayOfWeekNameNVARCHAR(10)
    sort by DayOfWeekNumber
  • DayOfWeekNumberINT
  • FinancialYearNVARCHAR(7)
DimWardSCD type 2dimension
Grain: Inpatient ward
  • WardKeyINT
  • WardCodeNVARCHAR(10)
    Business key
  • WardNameNVARCHAR(100)
  • DivisionNVARCHAR(50)
  • BedTypeNVARCHAR(30)
  • ValidFromDATE
  • ValidToDATE
FactEDAttendancefact
Grain: One row per ED attendance
  • AttendanceIDNVARCHAR(20)
    Degenerate dimension
  • ArrivalDateKeyINT
    → DimDate[DateKey]
  • SiteKeyINT
    → DimSite[SiteKey]
  • ArrivalModeKeyINT
    → DimArrivalMode[ArrivalModeKey]
  • ArrivalHourINT
  • TimeInDeptMinutesINT
  • HandoverMinutesINT
  • Within4HoursFlagINT
  • Over12HoursFlagINT
  • HandoverOver30FlagINT
  • AdmittedFlagINT
FactBedCensusfact
Grain: One row per ward per day at midnight census
  • CensusDateKeyINT
    → DimDate[DateKey]
  • WardKeyINT
    → DimWard[WardKey]
  • SiteKeyINT
    → DimSite[SiteKey]
  • AvailableBedsINT
  • OccupiedBedsINT
  • NoCriteriaToResideBedsINT
FactSiteTargetfact
Grain: One row per site per month
  • MonthStartDateKeyINT
    → DimDate[DateKey]
  • SiteKeyINT
    → DimSite[SiteKey]
  • FourHourTargetDECIMAL(5,4)
  • OccupancyTargetDECIMAL(5,4)
DimSiteSCD type 1dimension
Grain: Hospital site
  • SiteKeyINT
  • SiteCodeNVARCHAR(10)
    Business key
  • SiteNameNVARCHAR(100)
  • EDTypeNVARCHAR(30)
DimArrivalModeSCD type 1dimension
Grain: Arrival mode and acuity combination
  • 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.

Relationships
From (many)To (one)CardinalityFilter directionActive
FactEDAttendance.ArrivalDateKeyDimDate.DateKeymany-to-oneSingleYes
FactEDAttendance.SiteKeyDimSite.SiteKeymany-to-oneSingleYes
FactEDAttendance.ArrivalModeKeyDimArrivalMode.ArrivalModeKeymany-to-oneSingleYes
FactBedCensus.CensusDateKeyDimDate.DateKeymany-to-oneSingleYes
FactBedCensus.WardKeyDimWard.WardKeymany-to-oneSingleYes
FactBedCensus.SiteKeyDimSite.SiteKeymany-to-oneSingleYes
FactSiteTarget.MonthStartDateKeyDimDate.DateKeymany-to-oneSingleYes
FactSiteTarget.SiteKeyDimSite.SiteKeymany-to-oneSingleYes

3. DAX measures

An excerpt: the first 6 of 30 measures. Download the .dax file for all of them.

Validated: 30 measures, 0 errors, 3 warnings
ED Attendances
Number of ED attendances.
ED Activity
ED Attendances =
COUNTROWS ( FactEDAttendance )
Attendances Within 4 Hours
Attendances completed within 240 minutes.
ED Activity
Attendances Within 4 Hours =
SUM ( FactEDAttendance[Within4HoursFlag] )
4 Hour Performance %
Share of attendances within 4 hours.
4 Hour Standard
4 Hour Performance % =
DIVIDE ( [Attendances Within 4 Hours], [ED Attendances] )
4 Hour Performance % PY
4-hour performance for the same period last year.
Time Intelligence
4 Hour Performance % PY =
CALCULATE ( [4 Hour Performance %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
4 Hour Performance YoY pts
Percentage-point change in 4-hour performance year on year.
Time Intelligence
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
Activity-weighted monthly 4-hour trajectory from targets file.
4 Hour Standard
4 Hour Target =
VAR T = SUMX ( FactSiteTarget, FactSiteTarget[FourHourTarget] * CALCULATE ( [ED Attendances] ) ) RETURN DIVIDE ( T, CALCULATE ( [ED Attendances], TREATAS ( VALUES ( FactSiteTarget[SiteKey] ), DimSite[SiteKey] ) ) )
Download all 30 measures

4. KPIs and RAG rules

KPITargetDirectionGreen (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> 300228 -12
Green

How RAG is shown

1 Green#15803D2 Amber#B453093 Red#B91C1C

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]
Emergency Department Flow & Bed Occupancy Report
ED Flow Summary
Last 13 complete weeks, all sites
4-Hour Performance Amber
74.6%
+2.1 ptsTarget 78%
G&A Bed Occupancy Red
96.3%
+1.4 ptsTarget 92%
12-Hour Waits Amber
3.1%
-0.6 ptsTarget 2%
Ambulance Handovers Over 30 Minutes Green
4.2%
-1.8 ptsTarget 5%
Average Time in Department Green
228
-12Target 240 minutes
Weekly 4-Hour Performance vs Trajectory
06 Apr20 Apr04 May18 May
4-Hour Performance by Site
Royal County Hospital Type 1 ED
114%
St Mary's UTC
100%
Northgate Minor Injuries Unit
93%
Riverside Community UTC
84%
Daily G&A Bed Occupancy
01 May03 May05 May07 May
12-Hour Waits by Day of Week
MonTueWedThuFriSatSun
Visual specification: ED Flow Summary
VisualTypeFieldsFormatting and interactionAnswers
Headline Flow KPIskpi strip
values: [4 Hour Performance %], [G&A Bed Occupancy %], [12 Hour Waits %], [Handovers Over 30 Min %], [Average Time in Dept (mins)]
  • Status icon and word from each KPI RAG measure
  • Variance vs target and YoY shown beneath values
Are we meeting the key ED and bed standards?
Weekly 4-Hour Performance vs Trajectoryline
axis: DimDate[WeekEnding]
values: [4 Hour Performance %], [4 Hour Target]
tooltip: [Breaches]
  • Constant line at 78% national standard
  • Target as dashed series
Is 4-hour performance improving toward trajectory?
4-Hour Performance by Sitebar
axis: DimSite[SiteName]
values: [4 Hour Performance %]
tooltip: [4 Hour Variance pts]
  • Bar colour from [4 Hour Performance RAG] with status word in data label
  • Sorted ascending
  • Drill-through to Bed Occupancy & Flow Detail
Which sites are furthest from the standard?
Daily G&A Bed Occupancyline
axis: DimDate[Date]
values: [G&A Bed Occupancy %]
tooltip: [No Criteria to Reside]
  • Constant lines at 92% and 95%
  • Data label on last point
Is occupancy sustained above the safe ceiling?
12-Hour Waits by Day of Weekcolumn
axis: DimDate[DayOfWeekName]
values: [12 Hour Waits]
tooltip: [12 Hour Waits %]
  • Data labels on
  • Sorted by day of week
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]
Emergency Department Flow & Bed Occupancy Report
Bed Occupancy & Flow Detail
Last 13 complete weeks, selected site
Ward Occupancy at Last Census
NameActualvs Target
Ward 10 Acute Medicine£1393K101.7%
Ward 12 Respiratory£1206K88.0%
Ward 14 Care of the Elderly£1161K84.7%
Ward 7 General Surgery£1392K101.6%
AMU£1208K88.2%
Ward 21 Orthopaedics£1173K85.7%
Attendances by Arrival Mode
35%
  • Ambulance
  • Walk-in
  • Other
Arrivals and Performance by Hour
08101214161820
4-Hour Performance by Acuity
1 Immediate
107%
2 Very urgent
102%
3 Urgent
85%
4 Standard
78%
5 Non-urgent
76%
Visual specification: Bed Occupancy & Flow Detail
VisualTypeFieldsFormatting and interactionAnswers
Ward Occupancy at Last Censusmatrix
rows: DimWard[Division], DimWard[WardName]
values: [Available Beds], [Occupied Beds], [G&A Bed Occupancy %], [No Criteria to Reside]
  • Occupancy icon and word from [G&A Bed Occupancy RAG]
  • Sorted by occupancy descending
  • Filter: DimWard[BedType] is G&A
Which wards are full and holding patients without criteria to reside?
Attendances by Arrival Modedonut
legend: DimArrivalMode[ArrivalMode]
values: [ED Attendances]
  • Percentage data labels
What share of demand arrives by ambulance?
Arrivals and Performance by Hourcolumn
axis: FactEDAttendance[ArrivalHour]
y: [4 Hour Performance %]
values: [ED Attendances]
  • 4-hour % as line on secondary axis
When do arrival peaks drive breaches?
4-Hour Performance by Acuitybar
axis: DimArrivalMode[AcuityBand]
values: [4 Hour Performance %]
tooltip: [Admission Rate %]
  • Sorted by AcuitySort
  • Data labels with status word
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

Confirm these with the business before build. Each one changes the model or measures if the answer differs.
#Assumption or questionImpact if different
14-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.
2Year-on-year uses calendar dates; the NHS financial year only labels periods.Add financial-period columns and rebase PY measures on them.
3Bed occupancy is G&A only at the midnight census, not an 08:00 snapshot.Change census grain and the BedType filter in bed measures.
4Targets are monthly by site, so target measures are not sliced by ward, acuity or hour.Extend FactSiteTarget grain and relate it to further dimensions.
5Site RLS mapping comes from an Entra ID group per site maintained by IT.Add a security mapping table sourced from the workforce system.
6Ward 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.

Make one for your own report

Start free