UrbanPulse Data Lab
UrbanPulse · Smart City on Microsoft Fabric
Module 5 of 10
Now that live data is flowing, write KQL queries across all three streams and pin them as live tiles to a Real-Time Intelligence Dashboard. By the end you'll watch the city pulse update on screen, second by second.
From the eh_urbanpulse_rti Eventhouse, click the Database tab in the top header. On the toolbar, click Real-Time Dashboard → + New dashboard. Name it rtd_urbanpulse_city.
Launching the dashboard from the Eventhouse pre-wires it to eh_urbanpulse_rti as a data source, so you can skip to adding tiles.
The new dashboard opens in View mode. Click the toggle in the top-right and switch to Editing. The toolbar now shows + Add visual, Add Markdown, Add alert, Add data source, and Add parameter.
eh_urbanpulse_rti (auto-selected because you launched from the Eventhouse).Critical patients. Optionally add a Conditional formatting rule that turns the number red when the value is greater than 0.HospitalVitals
| where timestamp > ago(15m)
| summarize arg_max(timestamp, *) by patient_id
| where condition == "critical"
or spo2 < 90
or heart_rate > 120
or temperature_f > 101.5
| countTop 5 heart rate. Optionally enable Conditional formatting on the condition column to colour critical rows red.HospitalVitals
| where timestamp > ago(15m)
| summarize arg_max(timestamp, *) by patient_id
| project patient_id, condition, heart_rate, spo2, temperature_f, timestamp
| order by heart_rate desc
| take 5to_location as the X axis and patients as the Y axis automatically.Live room occupancy.HospitalMovement
| summarize arg_max(timestamp, *) by patient_id
| where event_type != "Discharged"
| summarize patients = count() by to_location
| order by patients desc
| render columnchart with (title="Live room occupancy")line and Value = trains.Train fleet status (last 10m).TrainTelemetry
| where timestamp > ago(10m)
| summarize arg_max(timestamp, *) by trainId
| summarize trains = count() by line, status
| render piechart with (title="Train fleet status · last 10m")Back on the dashboard canvas, drag the tiles to match the recommended grid below. Tiles 1 and 2 are KPI / table on the top row; Tiles 3 and 4 are the two charts on the bottom row.
Your dashboard should now look like the reference screenshot below: Critical patient Stat top-left, Top 5 heart rate table top-right, Live room occupancy column chart bottom-left, and Train fleet status pie chart bottom-right.
Top-right of the dashboard → Auto refresh → set 15 seconds. Click Save on the toolbar, then toggle the top-right switch back to View. Tiles will continue to update without the editor chrome.
Real-Time Dashboards visualise live data, but operators often need a push when something crosses a threshold - not a tile they have to be looking at. Fabric Activator (Reflex) lets you bind a rule to any KQL stream or dashboard tile and route alerts to Teams, email, or Power Automate.
You'll wire one rule that fires every time the Critical patients tile crosses zero.
On the dashboard toolbar (Editing mode), click Add alert. The Activator pane slides in from the right and pre-fills the underlying KQL from the tile that's currently selected.
If nothing is selected, click Tile 1 - Critical patients first so the alert is bound to that tile's query.
In the Activator pane:
act_urbanpulse_alerts.CriticalPatientsDetected.Click Next.
value (the integer the Stat tile produces)greater than0Every 1 minuteActivator evaluates the underlying KQL on that cadence and fires the rule the first time the count crosses the threshold (it won't re-fire on every poll while the count stays high).
Pick the simplest channel for the lab:
UrbanPulse - critical patients detected. Body: include the value and timestamp tokens.For production, swap email for a Teams channel post or a Power Automate flow that pages an on-call rotation.
Click Save and start. Activator provisions the rule and begins polling. The first fire arrives within ~1 minute (the producers always have at least one critical patient in the last 15-minute window).
So far HospitalVitals, HospitalMovement, and TrainTelemetry are all Bronze - raw, schema-on-write copies of what landed via
Eventstream. A KQL update policy is a stored expression that runs on every ingest into a
source table and writes the transformed rows into a target table. Chain them and you get medallion tiers
that stay in sync as new events stream in - no orchestrator needed.
In your KQL query window (Eventhouse → KQL Database → Query), run:
// SILVER target - one clean, typed row per vitals event
.create table HospitalVitals_clean (
patient_id: string,
age: int,
condition: string,
heart_rate: int,
bp_systolic: int,
bp_diastolic: int,
temperature_f: real,
spo2: int,
is_critical: bool,
timestamp: datetime
)
.alter table HospitalVitals_clean policy streamingingestion enableAn update policy needs two pieces: a function that transforms rows, and a policy declaration that wires it to a source table.
// 1. The transform function (called by KQL on every batch ingest into Bronze)
.create-or-alter function HospitalVitals_to_clean() {
HospitalVitals
| where isnotempty(patient_id) and heart_rate > 0
| extend is_critical = (
heart_rate > 130 or heart_rate < 45
or spo2 < 90
or bp_systolic > 180 or bp_systolic < 80
)
| project patient_id, age, condition,
heart_rate, bp_systolic, bp_diastolic,
temperature_f, spo2, is_critical, timestamp
}
// 2. The policy: every ingest into HospitalVitals fires the function and writes to HospitalVitals_clean
.alter table HospitalVitals_clean policy update
```
[
{
"IsEnabled": true,
"Source": "HospitalVitals",
"Query": "HospitalVitals_to_clean()",
"IsTransactional": true,
"PropagateIngestionProperties": true
}
]
```IsTransactional: true means a Silver write only commits if the function succeeded - bad source rows don't break the chain. Combined with PropagateIngestionProperties, retention and caching policies inherit too.
For Gold we don't need another physical table - a materialized view keeps an always-current "latest row per patient" projection that answers dashboard queries in milliseconds.
.create materialized-view with (backfill = true) PatientStatus_now on table HospitalVitals_clean
{
HospitalVitals_clean
| summarize arg_max(timestamp, *) by patient_id
}backfill = true seeds the view from existing Silver rows (so it works immediately on the data already mirrored from Bronze). New Silver rows update the view incrementally.
Run all three to see the tiers light up:
HospitalVitals | count // BRONZE: total raw rows
HospitalVitals_clean | count // SILVER: typed + filtered, should match (no bad rows)
PatientStatus_now | count // GOLD: should equal active patient count (~15)Point a dashboard tile at PatientStatus_now and you've got a sub-second "current critical patients" KPI without a single ETL job.
The recipe is identical. For practice, try:
TrainTelemetry → TrainTelemetry_clean (drop GPS-zero rows; is_delayed = status == "DELAYED") → TrainStatus_now mat-view (arg_max by trainId)HospitalVitals_clean instead of raw
HospitalVitals - which means Module 8's Hospital Data Agent gets cleaned data
with the is_critical flag pre-computed. Less prompt-shaping, more accurate answers.
And because the OneLake-availability toggle from Module 3 cascades to every table, your Silver/Gold
tiers are also available as Delta in OneLake automatically - so the Direct Lake semantic
model can read them too.
Module 6 turns the warm-path Lakehouse data into a Power BI report using Direct Lake - no import refresh, no DirectQuery latency.