A star schema for hospital activity

A slow or untrustworthy Power BI report more often has a model problem than a DAX problem. Hospital activity data makes this likely, because it arrives shaped by the systems that record it: patient administration extracts with one row per consultant episode, outpatient files with one row per appointment slot, and waiting-list files that are really a census of open pathways taken every week.

This is the design I’d start from, using a synthetic acute hospital with two sites, seven specialties and data from April 2023 to 20 August 2026. The generator is in projects/article-examples/power-bi/, and the tables below come from it.

The model

    +----------------------------------------------------------------------+
    | Date   (Date, Financial Year, Month, Week, Weekday)                  |
    +----------------------------------------------------------------------+
        |             :                    |                     |
        | admission   : discharge          | appointment         | snapshot
        | date        : date               | date                | date
        | (active)    : (inactive)         |                     |
        v             v                    v                     v
  +----------------------------+   +------------------+   +----------------------+
  | Admissions                 |   | Outpatients      |   | Waiting List         |
  | 1 row per spell            |   | 1 row per        |   | 1 row per snapshot,  |
  |                            |   | appointment      |   | specialty, site,     |
  |                            |   |                  |   | weeks waiting        |
  +----------------------------+   +------------------+   +----------------------+
               ^                            ^                       ^
               |                            |                       |
Specialty -----+----------------------------+-----------------------+
Site ----------+----------------------------+-----------------------+
Demographics --+----------------------------+

Three fact tables, four dimensions, every relationship one-to-many and filtering in one direction, from dimension to fact. Each design choice below is one I’d defend in a model review.

Grain first: episode, spell or pathway

The grain of a fact table is what one row means. Get it written down before you load anything, because the three candidate grains in hospital data answer different questions:

• A consultant episode is the time a patient spends under one consultant. A patient who is transferred from a general surgeon to a cardiologist has two episodes.
• A spell is one continuous stay in the hospital, from admission to discharge. It contains one or more episodes.
• An RTT pathway runs from referral to the start of treatment. It can contain several outpatient appointments and, eventually, an admission.

“How many admissions did we have?” is a spell question. Counting episode rows answers something else. In the synthetic data, 10% of emergency spells and 3% of elective spells have more than one episode, and some later episodes move to another specialty. Three attempts at admissions by specialty for FY 2025/26:

-- Wrong: counts consultant episodes, not admissions
Admissions := COUNTROWS ( Episodes )

-- Better, but not additive across specialties
Admissions := DISTINCTCOUNT ( Episodes[Spell ID] )

-- Right: a spell-grain fact table
Admissions := COUNTROWS ( Admissions )

Counting episodes overstates FY 2025/26 admissions by 9.7%. The distinct count gets the total right, but a spell with episodes in two specialties appears in both rows, so the rows add up to 55,590 while the total says 53,980. That’s hard to explain to someone reconciling against a published return. It also puts a distinct count over a near-unique column into every visual, which gets expensive as the table grows; the VertiPaq article covers why cardinality drives cost.

The spell-grain table avoids both problems, but it forces a decision you need to document: which specialty does a spell belong to? Here it’s the specialty of the first episode, the admitting specialty. Discharging specialty is also defensible. What matters is that the choice is written into the table’s description in the model, so nobody has to guess. If you need consultant-level analysis, keep Episodes as a second fact table rather than making it the only one. If the source has no spell ID, SQL window functions for patient pathways shows how to build continuous spells from overlapping and back-to-back stays.

Event facts and periodic snapshots

Admissions and outpatient appointments are event facts: one row per thing that happened, stamped with the date it happened. They add up across every dimension, including time.

The waiting list is different. It’s a periodic snapshot: every Sunday, a count of the pathways that are open, by specialty, site and weeks waited. Snapshots add up across specialty and site, but not across time. The same patient is on the list every week until treatment, so summing four weekly snapshots counts them four times.

-- Wrong in any visual that shows more than one snapshot per cell
Waiting List := SUM ( 'Waiting List'[Pathways] )

-- Right: the last snapshot in the period
Waiting List :=
VAR LastSnapshot = MAX ( 'Waiting List'[Snapshot Date] )
RETURN
    CALCULATE ( SUM ( 'Waiting List'[Pathways] ), 'Date'[Date] = LastSnapshot )

The summed version is four or five times too large and jumps whenever a month has five Sundays. The corrected measure takes the latest snapshot date visible in the current filter context, so a month shows its last week, a financial year shows its last week, and a single week shows itself. DAX for waiting-list KPIs goes further into RTT measures.

Pathways can also be modelled as an accumulating snapshot: one row per pathway, with a column for each milestone (referral, first appointment, decision to treat, clock stop) that fills in as the pathway progresses. That’s the right shape for questions like “how long from referral to first appointment?”, and it brings in more role-playing dates. For a complete waiting-list model, see the RTT waiting list semantic model project.

Conformed dimensions

A dimension is conformed when every fact table that uses it uses the same one, with the same keys and the same labels. That’s what lets one visual show three facts side by side:

The dimensions I’d conform across hospital activity:

• Date, one row per day, with financial year (April to March), month, ISO week and weekday. The time intelligence article covers what else it needs.
• Specialty, one row per treatment function code, with the specialty name and division as columns. Division lives in this table, not in a separate one (more on snowflakes below).
• Site, one row per hospital site.
• Demographics, with age band, sex and deprivation quintile, and no identifiers. A reporting model whose users don’t need patient-level detail has no reason to hold an NHS number, date of birth or postcode. Derive the bands upstream and load only the bands.

Demographics works well as a junk dimension: every combination of five age bands, three sex codes (including “Not known”) and five deprivation quintiles, 75 rows in all, with one integer key on each fact row. Three low-cardinality attributes become one small table and one small key column, instead of three columns repeated on every fact row.

Role-playing dates

An admission has an admission date and a discharge date, and both need the Date dimension. Power BI allows only one active relationship between two tables, so one relationship is active (admission date) and the other is inactive, used by the measures that need it:

-- Wrong: counts discharged spells by the day they were *admitted*
Discharges :=
CALCULATE ( [Admissions], NOT ISBLANK ( Admissions[Discharge Date] ) )

-- Right: switch the relationship for this measure
Discharges :=
CALCULATE (
    [Admissions],
    USERELATIONSHIP ( Admissions[Discharge Date], 'Date'[Date] ),
    NOT ISBLANK ( Admissions[Discharge Date] )
)

With 'Date'[Weekday] on the rows for FY 2025/26:

The naive measure just repeats the admissions pattern, because the filter still arrives through the admission date. The corrected one shows what the synthetic data was built to contain: half of the overnight stays due to end at a weekend run on to Monday. That weekend discharge gap is exactly what a flow report exists to show, and the naive measure hides it completely. The NOT ISBLANK filter keeps patients who are still in hospital (457 spells at the extract date) out of the count when no date filter is applied.

The alternative is a second copy of the date table, Discharge Date, with its own active relationship. I’d use that when people need to filter by both roles at once (“admitted in March, discharged in April”). It costs a small extra table and some care with column names, so that “Year” in one table isn’t confused with “Year” in the other.

Relationship direction and cardinality

Every relationship in this model is one-to-many, from dimension to fact, filtering in a single direction. Three rules keep it that way:

• One-to-many only. A one-to-one relationship usually means two tables should be one. A many-to-many between a dimension and a fact usually means the dimension has duplicate keys, which is a data quality problem to fix upstream.
• Single direction by default. A bidirectional relationship lets filters flow from a fact back into a dimension and then on into the other facts that share it. A slicer meant for admissions starts quietly filtering the waiting list. Bidirectional filtering also costs query time, and Microsoft’s guidance is to minimise it. When a slicer should only list items with data, filter the slicer visual with a measure ([Admissions] is not blank). When one calculation needs the other direction, turn it on inside that measure only with CROSSFILTER ( Admissions[Specialty Key], 'Specialty'[Specialty Key], BOTH ).
• Integer or date keys. Relate dates on a column of Date type, so time intelligence behaves (the time intelligence article explains why), and use integer surrogate keys for the other dimensions.

Why not a snowflake

Source systems often hold specialty and division as separate tables, and it’s tempting to load them that way: Division related to Specialty, Specialty related to the facts. That snowflake has costs. Filters travel along a longer chain of relationships, the Data pane shows extra tables that hold one or two columns each, and you can’t build a Division > Specialty hierarchy because a hierarchy can’t span tables.

Flatten it in Power Query or the source view instead:

The repeated division names cost almost nothing, because VertiPaq stores each distinct value once, in the column’s dictionary.

The review checklist

Before I call a healthcare activity model done:

• Each fact table’s grain is written in its description, and admissions are counted at spell grain.
• Snapshot facts have measures that pick a single snapshot, never a plain SUM over time.
• Specialty, Site, Date and Demographics are shared by every fact that uses them, and no patient identifiers are loaded.
• Every relationship is one-to-many and single-direction, and every inactive relationship has a measure that uses it.
• There are no snowflaked dimension tables.

With that in place, the DAX gets simpler. The CALCULATE article shows what those filters do once they reach the facts.