A Power BI report on 723,905 real fire engine mobilisations, connected live to a PostgreSQL database rather than a spreadsheet.
Live report (requires Power BI login) · screenshots below
This is the reporting layer of a two-part project. The database it reads is built by uk-lfb-sql-analysis, which takes the published CSV and normalises it:
LFB CSV -> PostgreSQL (normalised) -> reporting views (star) -> Power BI
160MB uk-lfb-sql-analysis this repo this repo
Pointing Power BI at the raw CSV would work and would be worse. The file has two conventions for missing values, 2,694 duplicated primary keys and dates that Power BI reads as US format, so the cleaning would live inside Power Query where nobody can review it, and would have to be repeated in the next report that touches the same data. Cleaning belongs in the database; the report reads what the database already knows to be true.
The analysis schema is a snowflake, which is right for storage and wrong for
Power BI. sql/reporting_views.sql flattens it into a star without touching
the underlying tables.
pbi_date pbi_station
\ /
\ /
pbi_mobilisations ------ pbi_delay
|
pbi_borough
| View | Rows | Notes |
|---|---|---|
pbi_mobilisations |
723,905 | Fact. One row per appliance per incident |
pbi_date |
1,461 | Every day 2021-2024, built with generate_series |
pbi_borough |
38 | Region hierarchy flattened, plus an unknown member |
pbi_station |
111 | With a volume band, plus an unknown member |
pbi_delay |
11 | Ten LFB codes plus "No delay recorded" |
Unknown members. 2,723 mobilisations have no borough and four name no station. Left as NULLs they join to nothing, and Power BI invents a blank row that appears in every slicer and cannot be labelled. Mapping them to an explicit member at key 0 keeps every fact row joined and every total honest: the report can then say "not recorded" out loud instead of showing a gap.
Date table. Time intelligence needs one row per day with no gaps.
Deriving dates from the fact table cannot provide that - a day with no
mobilisations has no row, and every year-on-year comparison silently skips
it. generate_series builds the spine.
Selecting a borough filters every visual on the page:
Filtering by volume band:
Cross-filtering from the heatmap:
Cards for mobilisations, incidents, mean response and % within the 6-minute target, each with its year-on-year change. A line chart of monthly mean response against a constant target line. A filled map of London by borough. A table of boroughs ranked by mean response.
A scatter of call volume against mean response, one point per station, with the London mean drawn on both axes: the bottom-right quadrant is busy and slow, and is where a resourcing conversation starts. A ranked bar of the slowest stations. Sliced by volume band.
A matrix of hour of day against day of week, conditionally formatted, which is where the counter-intuitive finding shows up: daytime is slower than the evening rush. A breakdown by response band, and delay reasons by frequency and by mean response.
| Year | First pumps | Within 6 min | Hit rate |
|---|---|---|---|
| 2021 | 102,659 | 74,280 | 72.4% |
| 2022 | 118,850 | 83,275 | 70.1% |
| 2023 | 120,538 | 83,593 | 69.3% |
| 2024 | 127,693 | 87,088 | 68.2% |
The hit rate has fallen every year while call volume rose 24%.
The slowest window is daytime, 10:00-15:00 (330s mean), not the evening rush (324s). The fastest is late evening, 20:00-23:00 (299s).
Needs Power BI Desktop and the lfb_sql database from
uk-lfb-sql-analysis.
1. Create the views
psql -U postgres -d lfb_sql -f sql/reporting_views.sqlThe script ends by checking that every fact row joins to every dimension. All five orphan counts should be zero.
2. Connect
Home > Get data > More > Database > PostgreSQL database.
- Server:
localhost:5432 - Database:
lfb_sql - Data Connectivity mode: Import
Import, not DirectQuery: 723,905 rows compress to a few MB in the model, and Import keeps every DAX measure available and the report fast. DirectQuery would send a query per visual and disable half the time intelligence.
Select the five pbi_ views. Load.
3. Relationships
Model view. Power BI guesses some of these and guesses at least one wrong, so check every line. All four are one-to-many, single direction, from the dimension to the fact:
| From | To |
|---|---|
pbi_date[date_key] |
pbi_mobilisations[date_key] |
pbi_station[station_id] |
pbi_mobilisations[station_id] |
pbi_borough[borough_id] |
pbi_mobilisations[borough_id] |
pbi_delay[delay_code_id] |
pbi_mobilisations[delay_code_id] |
Leave cross-filter direction as Single. Both-directions filtering is occasionally necessary and always the first thing to suspect when a total looks wrong.
4. Mark the date table
Select pbi_date > Table tools > Mark as date table > date_key.
Skipping this does not raise an error. It makes SAMEPERIODLASTYEAR return
wrong numbers, which is worse.
5. Sort columns
Month and day names sort alphabetically until told otherwise - April, August, December.
pbi_date[month_name]> Column tools > Sort by column >month_numberpbi_date[day_name]> Sort by column >day_numberpbi_date[month_year]> Sort by column >month_start
6. Measures
Home > Enter data > name it _Measures > Load. Delete its Column1. Then add
each measure from dax/measures.md with Home Table set to
_Measures.
7. Publish
Home > Publish > choose a workspace. In the Power BI Service, the report needs a gateway to refresh against a local database - without one it stays a snapshot of the data as imported, which is fine for a portfolio.
lfb-powerbi-dashboard/
├── sql/
│ └── reporting_views.sql # the star schema, with orphan checks
├── dax/
│ └── measures.md # 32 measures with their reasoning
├── screenshots/ # 6 screenshots (3 pages x clean + filtered)
├── docs/
│ └── build_sheet.html # visual reference, open beside Power BI
├── LFB_Response_Dashboard.pbix
└── README.md
London Datastore - LFB Mobilisation Records, January 2021 to December 2024, published under the UK Open Government Licence.