Power BI’s built-in usage metrics report is great — until you need history beyond 30 days. The underlying dataset is a rolling window: yesterday’s view counts are there, last quarter’s are gone. If you want to track adoption trends, prove ROI, or spot reports that have quietly died, you need to capture that data yourself.
The fix is surprisingly simple: a single Fabric notebook that queries the hidden Report Usage Metrics Model semantic model over XMLA and appends each daily snapshot to Lakehouse Delta tables. Unlimited history, append-only, ~30 lines of Python.
Honest Disclaimer — Prefer FUAM
Before you build this: if you have any meaningful scale of Power BI / Fabric tenant, use FUAM (Fabric Unified Admin Monitoring) instead. FUAM is the Microsoft-published, open-source accelerator that snapshots not just usage metrics but capacity events, activity logs, refresh history, inventories — the whole tenant story — into a Lakehouse with a ready-made semantic model and report. It is far more complete than what’s in this post.
So when do I reach for the little notebook below instead?
- A single workspace where you just need report-level view history and don’t want to deploy a full monitoring solution.
- You need a quick fix today and FUAM rollout is weeks away.
- You want to understand how the underlying mechanism works — the notebook is small enough to read end-to-end in five minutes.
For everything else: start with FUAM.
The Problem: 30 Days Is Not Enough
- Rolling window. Each day, the oldest day falls off. Anything older than 30 days is permanently gone — you cannot recover it.
- No YoY, no trends. Did adoption grow over the last year? Which reports are losing users? You literally cannot answer these questions with the built-in model.
- No ownership. Microsoft controls the model. Schema changes happen without notice. You cannot extend it, share it, or join it to your own dimensions cleanly.
The Solution: Daily Snapshot to Lakehouse
The hidden Report Usage Metrics Model is a normal semantic model — it just happens to be auto-managed by Microsoft. That means you can hit it with XMLA queries like any other model. Using sempy.fabric.evaluate_dax, we pull each source table, stamp a SnapshotUtc column, and append to Delta tables in a Lakehouse:
import sempy.fabric as fabric
from datetime import datetime, timezone
WORKSPACE_ID = "<your-workspace-guid>"
DATASET_NAME = "Report Usage Metrics Model"
TABLES = ["Views", "Reports", "Users", "Dates", "DistributionMethods", "Platforms"]
snap_ts = datetime.now(timezone.utc)
for t in TABLES:
df = fabric.evaluate_dax(
dataset=DATASET_NAME,
workspace=WORKSPACE_ID,
dax_string=f"EVALUATE '{t}'",
)
df.columns = [c.split("[")[-1].rstrip("]") if "[" in c else c for c in df.columns]
df["SnapshotUtc"] = snap_ts.isoformat()
(spark.createDataFrame(df).write
.mode("append")
.option("mergeSchema", "true")
.saveAsTable(f"usage_metrics_{t.lower()}"))
That’s the whole engine. Schedule it daily after 04:00 UTC (the source refreshes around 03:00 UTC) and you have a permanent historical record.
What Lands in the Lakehouse
One Delta table per source table, prefixed usage_metrics_*, with an extra SnapshotUtc column. Append-only — every run adds today’s full snapshot.
| Source table | Lakehouse table | Role |
|---|---|---|
Views | usage_metrics_views | Fact |
Reports | usage_metrics_reports | Dimension |
Users | usage_metrics_users | Dimension |
Dates | usage_metrics_dates | Dimension |
DistributionMethods | usage_metrics_distributionmethods | Dimension |
Platforms | usage_metrics_platforms | Dimension |
Three Design Decisions Worth Calling Out
- Append, not merge. Every snapshot is the full 30-day window. Yes, days overlap across snapshots — but storage is cheap and append is bulletproof. Deduplication happens at query time (see below), not at ingest time.
mergeSchema=true. Microsoft occasionally adds columns to the usage metrics model. With schema merging on, the next snapshot just absorbs the new columns instead of crashing.- Retention with
DELETE. Default 400 days (≈13 months, enough for YoY) — but you can setRETENTION_DAYS = Noneto keep forever. The delete is a normal Delta operation, so time travel still works if you mess up.
Querying Without Double-Counting
Because every snapshot contains the last 30 days, the same activity row appears in up to 30 snapshots. To get clean numbers, take the latest snapshot per natural key:
WITH ranked AS (
SELECT v.*,
ROW_NUMBER() OVER (
PARTITION BY Date, ReportGuid, UserGuid
ORDER BY SnapshotUtc DESC
) AS rn
FROM usage_metrics_views v
)
SELECT Date,
ReportGuid,
COUNT(*) AS views,
COUNT(DISTINCT UserGuid) AS distinct_users
FROM ranked
WHERE rn = 1
GROUP BY Date, ReportGuid
ORDER BY Date DESC, views DESC
For older history (where a date appears in only one snapshot), the deduplication is a no-op — the row simply passes through. For the most recent 30 days, you always pick the freshest version of each fact.
Prerequisites
- Workspace on Premium / PPU / Fabric capacity (XMLA endpoint enabled — read is enough).
- The
Report Usage Metrics Modeldataset exists in the workspace. It is auto-created the first time anyone clicks More options → View usage metrics report on any report in that workspace. - A Lakehouse attached to the notebook (any Lakehouse in the same workspace works).
- Tenant setting “Usage metrics for content creators” enabled (admin portal).
Try It — Fabric Notebook Gallery
The notebook is also submitted to the Fabric Notebook Gallery. Grab the .ipynb directly from GitHub and import it into any Fabric workspace:
📓 Usage Metrics Snapshot.ipynb — one notebook, six Delta tables, daily schedule. Set WORKSPACE_ID, attach a Lakehouse, schedule after 04:00 UTC. Done.
Take It Further — A Power BI Report on Top
The Lakehouse tables are already the hard part. From here, building a proper Adoption & Usage Power BI report is a short hop:
- Point a Direct Lake semantic model at the six
usage_metrics_*Delta tables — no import, no refresh schedule, fresh on the next snapshot. - Wrap the "latest snapshot per natural key" dedup logic in a SQL view in the Lakehouse SQL endpoint so the model stays simple. One view per fact, deduped, ready to consume.
- Build the obvious pages: Adoption over time (views & distinct users by month, YoY), Top & bottom reports, Dying reports (had >X views last year, <Y this quarter), Platform & distribution mix.
- Join to your own Reports dim (owner, business domain, criticality) for actionable cuts — e.g. "critical reports with zero views in 30 days".
That’s the whole point of owning the history: once the data lives in your Lakehouse, you can model and visualise it however your stakeholders need — not however Microsoft’s built-in report happens to ship it.
Wrap-Up
One notebook, one daily schedule, six Delta tables — and the 30-day blind spot is gone. You own the history, you can join it to your own data, and you can build proper adoption dashboards on top.
The full notebook (with retention, health-check, and example query cells) is on GitHub: KornAlexander/PBI-Tools → Notebook Gallery / Usage Metrics Snapshot.ipynb.
