> Sample blueprint generated by Blueprint BI (https://app.milestonebi.com/samples/healthcare). Shown exactly as generated.

# 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.

## 1. Scope and inputs

**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))

## 2. Data architecture

### Data sources
| Source | System | Refresh | Notes |
|---|---|---|---|
| ED attendances | Oracle (EDIS / PAS) | Daily 02:00 | Patient-level ED attendance events with arrival, triage and departure timestamps. |
| Bed state census | Oracle (PAS ward module) | Daily 01:00 | Midnight occupied and available beds per ward. |
| Ward and specialty reference | Excel / CSV | Weekly | Ward bed type, division and site mapping maintained by information team. |
| Operational targets | Excel / CSV | Monthly | Monthly 4-hour and occupancy trajectories by site. |

### Ingestion: Microsoft Fabric Data Factory pipelines
Incremental load on Oracle LastModified watermark; full reload of reference files. Copy activities land Oracle extracts and SharePoint files into Bronze daily.

### Layers
- **Bronze** (Fabric Lakehouse (Delta)): Raw Oracle extracts and files, append-only with load timestamp.
- **Silver** (Fabric Lakehouse with Spark notebooks): Cleansed, pseudonymised attendances with derived wait flags and conformed codes.
- **Gold** (Fabric Lakehouse (Delta star schema)): Facts and conformed dimensions optimised with V-Order for Direct Lake.

### Semantic layer: Power BI semantic model (Fabric) (Direct Lake)
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.

| Area | Design |
|---|---|
| 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 |
| 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 |

### Governance and 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.

## 3. Data model: Star schema (galaxy with two facts)

Measures are stored on a dedicated `_Measures` table.

### FactEDAttendance (fact)
_Grain: One row per ED attendance_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| AttendanceID | NVARCHAR(20) | degenerate | hidden | Source attendance number. |
| ArrivalDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Arrival date key. |
| SiteKey | INT | foreign key | → DimSite[SiteKey]; hidden | Hospital site. |
| ArrivalModeKey | INT | foreign key | → DimArrivalMode[ArrivalModeKey]; hidden | Arrival mode and acuity. |
| ArrivalHour | INT | attribute |  | Hour of arrival 0-23. |
| TimeInDeptMinutes | INT | additive | hidden | Total minutes arrival to departure. |
| HandoverMinutes | INT | additive | hidden | Ambulance handover minutes; null for walk-ins. |
| Within4HoursFlag | INT | additive | hidden | 1 if 240 minutes or less. |
| Over12HoursFlag | INT | additive | hidden | 1 if over 720 minutes. |
| HandoverOver30Flag | INT | additive | hidden | 1 if handover exceeded 30 minutes. |
| AdmittedFlag | INT | additive | hidden | 1 if admitted to a ward. |

### FactBedCensus (fact)
_Grain: One row per ward per day at midnight census_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| CensusDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Census date. |
| WardKey | INT | foreign key | → DimWard[WardKey]; hidden | Ward. |
| SiteKey | INT | foreign key | → DimSite[SiteKey]; hidden | Site of ward. |
| AvailableBeds | INT | semi additive | hidden | Open staffed beds. |
| OccupiedBeds | INT | semi additive | hidden | Occupied beds. |
| NoCriteriaToResideBeds | INT | semi additive | hidden | Patients with no criteria to reside. |

### FactSiteTarget (fact)
_Grain: One row per site per month_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| MonthStartDateKey | INT | foreign key | → DimDate[DateKey]; hidden | First day of month. |
| SiteKey | INT | foreign key | → DimSite[SiteKey]; hidden | Site. |
| FourHourTarget | DECIMAL(5,4) | non additive | hidden | Monthly 4-hour trajectory. |
| OccupancyTarget | DECIMAL(5,4) | non additive | hidden | G&A occupancy ceiling. |

### DimDate (dimension · marked as date table)
_Grain: One row per calendar day_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | primary key | hidden | yyyymmdd key. |
| Date | DATE | date |  | Calendar date. |
| WeekEnding | DATE | date |  | Sunday week-ending date. |
| MonthNumber | INT | attribute | hidden | Calendar month 1-12. |
| MonthYear | NVARCHAR(10) | attribute | sort by MonthYearSort | e.g. Apr 2025. |
| MonthYearSort | INT | attribute | hidden | yyyymm sort. |
| DayOfWeekName | NVARCHAR(10) | attribute | sort by DayOfWeekNumber | Mon-Sun. |
| DayOfWeekNumber | INT | attribute | hidden | 1 Monday to 7 Sunday. |
| FinancialYear | NVARCHAR(7) | attribute |  | NHS April-March year, e.g. 2025/26. |

### DimSite (dimension · SCD type 1)
_Grain: Hospital site_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| SiteKey | INT | surrogate key | hidden | Surrogate key. |
| SiteCode | NVARCHAR(10) | business key | hidden | ODS site code; RLS key. |
| SiteName | NVARCHAR(100) | attribute |  | Site name. |
| EDType | NVARCHAR(30) | attribute |  | Type 1 ED, UTC etc. |

### DimWard (dimension · SCD type 2)
_Grain: Inpatient ward_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| WardKey | INT | surrogate key | hidden | Surrogate key. |
| WardCode | NVARCHAR(10) | business key | hidden | PAS ward code. |
| WardName | NVARCHAR(100) | attribute |  | Ward name. |
| Division | NVARCHAR(50) | attribute |  | Clinical division. |
| BedType | NVARCHAR(30) | attribute |  | G&A, Critical Care, Maternity, Paediatric. |
| ValidFrom | DATE | date | hidden | SCD2 start. |
| ValidTo | DATE | date | hidden | SCD2 end. |

### DimArrivalMode (dimension · SCD type 1)
_Grain: Arrival mode and acuity combination_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| ArrivalModeKey | INT | surrogate key | hidden | Surrogate key. |
| ArrivalModeCode | NVARCHAR(10) | business key | hidden | ECDS arrival mode code. |
| ArrivalMode | NVARCHAR(30) | attribute |  | Ambulance, Walk-in, Other. |
| AcuityBand | NVARCHAR(30) | attribute | sort by AcuitySort | Manchester triage category. |
| AcuitySort | INT | attribute | hidden | 1 Immediate to 5 Non-urgent. |

### Relationships

| 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 |

## 4. DAX measures

30 measures in 8 display folders.

### ED Activity

**ED Attendances** · `#,0`
```dax
ED Attendances =
COUNTROWS ( FactEDAttendance )
```
Number of ED attendances.

**Attendances Within 4 Hours** · `#,0`
```dax
Attendances Within 4 Hours =
SUM ( FactEDAttendance[Within4HoursFlag] )
```
Attendances completed within 240 minutes.

**Admission Rate %** · `0.0%`
```dax
Admission Rate % =
DIVIDE ( SUM ( FactEDAttendance[AdmittedFlag] ), [ED Attendances] )
```
Share of attendances admitted.

### 4 Hour Standard

**4 Hour Performance %** · `0.0%`
```dax
4 Hour Performance % =
DIVIDE ( [Attendances Within 4 Hours], [ED Attendances] )
```
Share of attendances within 4 hours.

**4 Hour Target** · `0.0%`
```dax
4 Hour Target =
VAR T = SUMX ( FactSiteTarget, FactSiteTarget[FourHourTarget] * CALCULATE ( [ED Attendances] ) ) RETURN DIVIDE ( T, CALCULATE ( [ED Attendances], TREATAS ( VALUES ( FactSiteTarget[SiteKey] ), DimSite[SiteKey] ) ) )
```
Activity-weighted monthly 4-hour trajectory from targets file.

**4 Hour Variance pts** · `+0.0%;-0.0%`
```dax
4 Hour Variance pts =
VAR Cur = [4 Hour Performance %] VAR T = [4 Hour Target] RETURN IF ( NOT ISBLANK ( Cur ) && NOT ISBLANK ( T ), Cur - T )
```
Points above or below the 4-hour trajectory.

**Breaches** · `#,0`
```dax
Breaches =
[ED Attendances] - [Attendances Within 4 Hours]
```
Attendances exceeding 4 hours.

### Time Intelligence

**4 Hour Performance % PY** · `0.0%`
```dax
4 Hour Performance % PY =
CALCULATE ( [4 Hour Performance %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
4-hour performance for the same period last year.

**4 Hour Performance YoY pts** · `+0.0%;-0.0%`
```dax
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 )
```
Percentage-point change in 4-hour performance year on year.

**ED Attendances PY** · `#,0`
```dax
ED Attendances PY =
CALCULATE ( [ED Attendances], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Attendances in the same period last year.

**ED Attendances YoY %** · `+0.0%;-0.0%`
```dax
ED Attendances YoY % =
VAR PY = [ED Attendances PY] RETURN IF ( PY > 0, DIVIDE ( [ED Attendances] - PY, PY ) )
```
Year-on-year growth in attendances.

### Waits

**12 Hour Waits** · `#,0`
```dax
12 Hour Waits =
SUM ( FactEDAttendance[Over12HoursFlag] )
```
Attendances over 12 hours from arrival.

**12 Hour Waits %** · `0.0%`
```dax
12 Hour Waits % =
DIVIDE ( [12 Hour Waits], [ED Attendances] )
```
Share of attendances over 12 hours.

**12 Hour Waits % Target** · `0.0%`
```dax
12 Hour Waits % Target =
0.02
```
Constant ceiling of 2% for 12-hour waits.

**Average Time in Dept (mins)** · `#,0`
```dax
Average Time in Dept (mins) =
AVERAGE ( FactEDAttendance[TimeInDeptMinutes] )
```
Mean minutes from arrival to departure.

### Ambulance

**Ambulance Handovers** · `#,0`
```dax
Ambulance Handovers =
CALCULATE ( [ED Attendances], NOT ISBLANK ( FactEDAttendance[HandoverMinutes] ) )
```
Attendances with a recorded ambulance handover.

**Handovers Over 30 Min %** · `0.0%`
```dax
Handovers Over 30 Min % =
DIVIDE ( SUM ( FactEDAttendance[HandoverOver30Flag] ), [Ambulance Handovers] )
```
Share of handovers exceeding 30 minutes.

**Handovers Over 30 Min Target** · `0.0%`
```dax
Handovers Over 30 Min Target =
0.05
```
Constant ceiling of 5% for delayed handovers.

### Beds

**Occupied Beds** · `#,0`
```dax
Occupied Beds =
VAR LastCensus = CALCULATE ( MAX ( FactBedCensus[CensusDateKey] ), ALLSELECTED ( DimDate ) ) VAR D = MAX ( DimDate[DateKey] ) VAR UseKey = MIN ( D, LastCensus ) RETURN CALCULATE ( SUM ( FactBedCensus[OccupiedBeds] ), DimWard[BedType] = "G&A", DimDate[DateKey] = UseKey )
```
G&A occupied beds on the last census date in context.

**Available Beds** · `#,0`
```dax
Available Beds =
VAR LastCensus = CALCULATE ( MAX ( FactBedCensus[CensusDateKey] ), ALLSELECTED ( DimDate ) ) VAR D = MAX ( DimDate[DateKey] ) VAR UseKey = MIN ( D, LastCensus ) RETURN CALCULATE ( SUM ( FactBedCensus[AvailableBeds] ), DimWard[BedType] = "G&A", DimDate[DateKey] = UseKey )
```
G&A available beds on the last census date in context.

**G&A Bed Occupancy %** · `0.0%`
```dax
G&A Bed Occupancy % =
VAR Avail = [Available Beds] RETURN IF ( Avail > 0, DIVIDE ( [Occupied Beds], Avail ) )
```
Occupied over available G&A beds at last census.

**Occupancy Target** · `0.0%`
```dax
Occupancy Target =
VAR T = AVERAGE ( FactSiteTarget[OccupancyTarget] ) RETURN T
```
Occupancy ceiling from targets file, averaged across sites.

**Occupancy Variance pts** · `+0.0%;-0.0%`
```dax
Occupancy Variance pts =
VAR Cur = [G&A Bed Occupancy %] VAR T = [Occupancy Target] RETURN IF ( NOT ISBLANK ( Cur ) && NOT ISBLANK ( T ), Cur - T )
```
Occupancy above or below the ceiling.

**No Criteria to Reside** · `#,0`
```dax
No Criteria to Reside =
VAR LastCensus = CALCULATE ( MAX ( FactBedCensus[CensusDateKey] ), ALLSELECTED ( DimDate ) ) VAR UseKey = MIN ( MAX ( DimDate[DateKey] ), LastCensus ) RETURN CALCULATE ( SUM ( FactBedCensus[NoCriteriaToResideBeds] ), DimDate[DateKey] = UseKey )
```
Patients without criteria to reside at last census.

### Labels

**Selected Period Label** · `Text`
```dax
Selected Period Label =
VAR MinD = MIN ( DimDate[Date] ) VAR MaxD = MAX ( DimDate[Date] ) RETURN "Period: " & FORMAT ( MinD, "d mmm yyyy" ) & " to " & FORMAT ( MaxD, "d mmm yyyy" )
```
Selected date range for page subtitles.

### RAG

**4 Hour Performance RAG** · `0`
```dax
4 Hour Performance RAG =
VAR _Value = [4 Hour Performance %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 0.78, 1,    -- Green
        _Value >= 0.73, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ 78.0%), 2 Amber (≥ 73.0%), 3 Red (< 73.0%). Drives conditional formatting for 4-Hour Performance.

**G&A Bed Occupancy RAG** · `0`
```dax
G&A Bed Occupancy RAG =
VAR _Value = [G&A Bed Occupancy %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 0.92, 1,    -- Green
        _Value <= 0.95, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 92.0%), 2 Amber (≤ 95.0%), 3 Red (> 95.0%). Drives conditional formatting for G&A Bed Occupancy.

**12 Hour Waits RAG** · `0`
```dax
12 Hour Waits RAG =
VAR _Value = [12 Hour Waits %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 0.02, 1,    -- Green
        _Value <= 0.04, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 2.0%), 2 Amber (≤ 4.0%), 3 Red (> 4.0%). Drives conditional formatting for 12-Hour Waits.

**Handovers Over 30 Min RAG** · `0`
```dax
Handovers Over 30 Min RAG =
VAR _Value = [Handovers Over 30 Min %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 0.05, 1,    -- Green
        _Value <= 0.1, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 5.0%), 2 Amber (≤ 10.0%), 3 Red (> 10.0%). Drives conditional formatting for Ambulance Handovers Over 30 Minutes.

**Average Time in Dept (mins) RAG** · `0`
```dax
Average Time in Dept (mins) RAG =
VAR _Value = [Average Time in Dept (mins)]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 240, 1,    -- Green
        _Value <= 300, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 240), 2 Amber (≤ 300), 3 Red (> 300). Drives conditional formatting for Average Time in Department.

## 5. KPIs and RAG rules

| KPI | Definition | Value measure | RAG measure | Target | Green | Amber | Red |
|---|---|---|---|---|---|---|---|
| 4-Hour Performance | Attendances within 4 hours / total ED attendances. | [4 Hour Performance %] | [4 Hour Performance RAG] | 78% | ≥ 78.0% | ≥ 73.0% | < 73.0% |
| G&A Bed Occupancy | Occupied G&A beds / available G&A beds at last midnight census. | [G&A Bed Occupancy %] | [G&A Bed Occupancy RAG] | 92% | ≤ 92.0% | ≤ 95.0% | > 95.0% |
| 12-Hour Waits | Attendances over 12 hours from arrival / total attendances. | [12 Hour Waits %] | [12 Hour Waits RAG] | 2% | ≤ 2.0% | ≤ 4.0% | > 4.0% |
| Ambulance Handovers Over 30 Minutes | Handovers over 30 minutes / all recorded ambulance handovers. | [Handovers Over 30 Min %] | [Handovers Over 30 Min RAG] | 5% | ≤ 5.0% | ≤ 10.0% | > 10.0% |
| Average Time in Department | Mean minutes from arrival to departure. | [Average Time in Dept (mins)] | [Average Time in Dept (mins) RAG] | 240 minutes | ≤ 240 | ≤ 300 | > 300 |

RAG measures return 1 (green #15803D), 2 (amber #B45309) or 3 (red #B91C1C). Use conditional formatting by rules on the RAG measure. Status is never shown by colour alone: pair each colour with an icon shape or the word.

## 6. Report design

### Page 1: ED Flow Summary
_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.

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Headline Flow KPIs | kpi strip | values: [4 Hour Performance %]; values: [G&A Bed Occupancy %]; values: [12 Hour Waits %]; values: [Handovers Over 30 Min %]; values: [Average Time in Dept (mins)] | Status icon and word from each KPI RAG measure; Variance vs target and YoY shown beneath values |
| Weekly 4-Hour Performance vs Trajectory | line | axis: DimDate[WeekEnding]; values: [4 Hour Performance %]; values: [4 Hour Target]; tooltip: [Breaches] | Constant line at 78% national standard; Target as dashed series |
| 4-Hour Performance by Site | bar | 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 |
| Daily G&A Bed Occupancy | line | axis: DimDate[Date]; values: [G&A Bed Occupancy %]; tooltip: [No Criteria to Reside] | Constant lines at 92% and 95%; Data label on last point |
| 12-Hour Waits by Day of Week | column | axis: DimDate[DayOfWeekName]; values: [12 Hour Waits]; tooltip: [12 Hour Waits %] | Data labels on; Sorted by day of week |

### Page 2: Bed Occupancy & Flow Detail
_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

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Ward Occupancy at Last Census | matrix | rows: DimWard[Division]; rows: DimWard[WardName]; values: [Available Beds]; values: [Occupied Beds]; values: [G&A Bed Occupancy %]; values: [No Criteria to Reside] | Occupancy icon and word from [G&A Bed Occupancy RAG]; Sorted by occupancy descending; Filter: DimWard[BedType] is G&A |
| Attendances by Arrival Mode | donut | legend: DimArrivalMode[ArrivalMode]; values: [ED Attendances] | Percentage data labels |
| Arrivals and Performance by Hour | column | axis: FactEDAttendance[ArrivalHour]; values: [ED Attendances]; y: [4 Hour Performance %] | 4-hour % as line on secondary axis |
| 4-Hour Performance by Acuity | bar | axis: DimArrivalMode[AcuityBand]; values: [4 Hour Performance %]; tooltip: [Admission Rate %] | Sorted by AcuitySort; Data labels with status word |

### Theme

| Role | Colours |
|---|---|
| Data colours (in order) | #312E81, #4338CA, #4F46E5, #6366F1, #818CF8, #A5B4FC, #9CA3AF |
| Good / neutral / bad | #15803D, #B45309, #B91C1C |
| Text / background | #1F2937, #FFFFFF |
| Font | Segoe UI |

## 7. 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.
