Usage Metrics Snapshot — Unlimited Power BI Usage History
Snapshot the hidden Report Usage Metrics Model semantic model into Lakehouse Delta tables via XMLA / sempy.evaluate_dax. Six append-only tables (Views, Reports, Users, Dates, DistributionMethods, Platforms), one SnapshotUtc column for deduplication, optional retention. Schedule daily after 04:00 UTC and you've broken Power BI's 30-day usage history wall — forever. Requires: Premium / PPU / Fabric capacity, attached Lakehouse, tenant setting "Usage metrics for content creators" enabled. # Usage Metrics Snapshot Snapshot the hidden **Report Usage Metrics Model** semantic model into Lakehouse Delta tables for **unlimited history**. ## Why Power BI's built-in usage metrics dataset only retains **30 days** of activity (rolling window). By querying it daily over XMLA via `sempy` and appending the rows to Lakehouse tables, we accumulate full history we control. ## Prerequisites - Workspace on Premium / PPU / Fabric capacity (XMLA endpoint enabled). - The `Report Usage Metrics Model` dataset exists in the workspace. It is auto-created the first time anyone clicks *More options → View usage metrics report* on a report. - A Lakehouse attached to this notebook (any Lakehouse in the workspace). - Tenant setting *“Usage metrics for content creators”* enabled. ## How to operate 1. Set `WORKSPACE_ID` below. 2. Run all cells once to verify. 3. Schedule the notebook **daily after 04:00 UTC** (the source refreshes around 03:00 UTC). ## What lands in the Lakehouse One Delta table per source table, prefixed `usage_metrics_*`, with an extra `SnapshotUtc` column. Append-only. | 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 | ## 1. Configuration import sempy.fabric as fabric from datetime import datetime, timezone, timedelta # ---- EDIT ME -------------------------------------------------------------- WORKSPACE_ID = "<your-workspace-guid>" # e.g., da2e15a8-c06d-4da0-ad10-c68aba63e564 DATASET_NAME = "Report Usage Metrics Model" TABLES = [ "Views", # fact "Reports", # dimensions "Users", "Dates", "DistributionMethods", "Platforms", ] # How many days of snapshots to keep (set to None to keep forever) RETENTION_DAYS = 400 # --------------------------------------------------------------------------- snap_ts = datetime.now(timezone.utc) print(f"Snapshot timestamp: {snap_ts.isoformat()}") ## 2. Snapshot all tables Each table is pulled with `EVALUATE 'TableName'` (full table scan). Column names are cleaned up (DAX returns them as `'Table'[Column]`). The result is appended to the corresponding Lakehouse Delta table with `mergeSchema=true` so new columns added by Microsoft over time are tolerated. results = [] for t in TABLES: df = fabric.evaluate_dax( dataset=DATASET_NAME, workspace=WORKSPACE_ID, dax_string=f"EVALUATE '{t}'", ) # Strip table name prefix from column names (DAX returns "TableName[Column]") df.columns = [c.split("[")[-1].rstrip("]") if "[" in c else c for c in df.columns] df["SnapshotUtc"] = snap_ts.isoformat() table_name = f"usage_metrics_{t.lower()}" sdf = spark.createDataFrame(df) (sdf.write .mode("append") .option("mergeSchema", "true") .saveAsTable(table_name)) results.append((t, table_name, len(df))) print(f" {t:25s} -> {table_name:40s} {len(df):>8} rows") print("\nDone.") ## 3. Retention (optional) Trim snapshots older than `RETENTION_DAYS` so the tables don't grow forever. Default 400 days ≈ 13 months — enough for YoY comparisons. if RETENTION_DAYS: cutoff = (snap_ts - timedelta(days=RETENTION_DAYS)).isoformat() for _, table_name, _ in results: spark.sql(f"DELETE FROM {table_name} WHERE SnapshotUtc < '{cutoff}'") print(f" Trimmed {table_name} (< {cutoff})") else: print("Retention disabled — keeping all snapshots.") --- # Verification & query examples The cells below are **not part of the daily job** — use them to verify the snapshot worked and to demo how to query the historical data. ## 4. Snapshot health check for _, table_name, _ in results: df = spark.sql(f""" SELECT '{table_name}' AS table_name, COUNT(*) AS total_rows, COUNT(DISTINCT SnapshotUtc) AS snapshot_count, MIN(SnapshotUtc) AS first_snapshot, MAX(SnapshotUtc) AS latest_snapshot FROM {table_name} """) df.show(truncate=False) ## 5. Peek at the latest snapshot of `Views` display(spark.sql(""" WITH latest AS ( SELECT MAX(SnapshotUtc) AS ts FROM usage_metrics_views ) SELECT v.* FROM usage_metrics_views v JOIN latest ON v.SnapshotUtc = latest.ts LIMIT 20 """)) ## 6. Daily views per report (full history) De-duplication pattern: take the **latest snapshot per natural key** so overlapping 30-day windows don't double-count. Adjust the `PARTITION BY` columns to whatever the real key columns are in your `Views` table (inspect with the previous cell). display(spark.sql(""" 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 """)) ## 7. Top reports last 30 days (joined to `Reports` dim) display(spark.sql(""" WITH ranked_views AS ( SELECT v.*, ROW_NUMBER() OVER ( PARTITION BY Date, ReportGuid, UserGuid ORDER BY SnapshotUtc DESC ) AS rn FROM usage_metrics_views v ), latest_reports AS ( SELECT r.*, ROW_NUMBER() OVER ( PARTITION BY ReportGuid ORDER BY SnapshotUtc DESC ) AS rn FROM usage_metrics_reports r ) SELECT r.DisplayName, COUNT(*) AS views, COUNT(DISTINCT v.UserGuid) AS distinct_users FROM ranked_views v LEFT JOIN latest_reports r ON r.ReportGuid = v.ReportGuid AND r.rn = 1 WHERE v.rn = 1 AND v.Date >= date_sub(current_date(), 30) GROUP BY r.DisplayName ORDER BY views DESC """)) https%3A%2F%2Fgithub.com%2FKornAlexander%2FPBI-Tools%2Fblob%2Fmain%2FNotebook%2520Gallery%2FUsage%2520Metrics%2520Snapshot.ipynbFabric Spark Pool Optimiser
Fabric Spark Pool Optimiser — right-size your Spark pools in 3 minutes Every workspace in Microsoft Fabric gets the same default Spark pool. Medium node, up to 10 nodes. Nobody changes it — even in production, even when the actual workload is a 5,000-row dimension table or a monitoring notebook reading 0.002 GB. This notebook analyses 7 days of real Spark session history across all your workspaces and tells you exactly which pools are oversized, undersized, or correctly sized — with a step-by-step configuration guide for each one. What it does: - Auto-discovers all workspaces you have access to - Detects orchestrator workspaces automatically (runMultiple / Data Factory) - Separates automated pipeline sessions from interactive dev sessions — dev sessions skew duration data and are excluded from the CU calculation - Analyses GB read/written/shuffled via the Spark History stages API - Estimates monthly CU savings based on real usage - Renders an interactive dashboard directly in the notebook output No lakehouse needed. No configuration. Just import and Run All. Tested across two organisations. In one run: 50 workspaces analysed, 8 pools to change, 1,441 CU estimated monthly saving. Feedback welcome — especially if you find API behaviour that differs in your environment. https%3A%2F%2Fgithub.com%2Fenekoegiguren%2Ffabricsparkpooloptimiser
Semantic Link – Dataflow Gen1 to Gen2 Migration Assistant
Semantic Link – Dataflow Gen1 to Gen2 Migration Assistant is a reusable Fabric Notebook that helps developers safely modernize Power BI semantic models by detecting Dataflow Gen1 sources, mapping them to Dataflow Gen2 equivalents, updating the model BIM, creating a backup, rebinding the correct Fabric connection, refreshing the model, and validating the result. The tool follows a dry-run-first workflow to reduce manual edits, credential mistakes, and migration risk. Link Repooooooo!!: https://github.com/vicente2121/ChallengeSemantiklabs.git https%3A%2F%2Fgithub.com%2Fvicente2121%2FChallengeSemantiklabs%2Fblob%2Fac67250f4044e0db6d63b87a2fd84b36913e5089%2FSemantic%2520Link%2520%25E2%2580%2593%2520Dataflow%2520Gen1%2520to%2520Gen2%2520Migration%2520Assistant.ipynbSemantic Link - Power BI Fixer
🔧 Power BI Fixer — The All-in-One Development Environment for Power BI in Fabric Author: Alexander Korn · Solution Engineer Data Platform @ Microsoft Repository: github.com/KornAlexander/semantic-link-labs (branch: feature/pbi-fixer-ui) Website: actionablereporting.com/pbi-fixer Built on: Semantic Link Labs by Michael Kovalsky 📋 Abstract The Power BI Fixer is an interactive development environment that runs natively inside Microsoft Fabric Notebooks. It combines Semantic Link, ipywidgets, and TOM (Tabular Object Model) to provide a comprehensive tool for scanning, exploring, fixing, and documenting Power BI reports and semantic models — all from a single notebook cell. The Problem It Solves Power BI developers face a fragmented workflow: Report issues (pie charts, missing data labels, page sizes) require manual visual-by-visual fixes in Power BI Desktop Model best practices (missing descriptions, floating point types, foreign key visibility) need Tabular Editor or manual scripting No single tool lets you scan a report AND its underlying model, see the results side-by-side, and fix everything in one workflow PBIR format adoption is slow because upgrade tooling is scattered The PBI Fixer addresses all of these by providing 12+ interactive tabs, 17 report fixers, 25+ semantic model fixers, and a unified scan-and-fix workflow — running entirely in a Fabric Notebook. ~17,000 lines of new Python code across 5 UI & tab modules (9,439 lines — incl. Fix All, Model Explorer, Report Explorer, Prototype, Translations, Model Diagram, About), 15 report fixers (3,038 lines), 4 report helpers (593 lines), and 33 semantic model fixers (3,797 lines) — all written from scratch for this project. 🎬 Demo Video Watch the full walkthrough: Power BI Fixer v2 Demo on YouTube https%3A%2F%2Fgithub.com%2FKornAlexander%2Fpbi_fixer%2Fblob%2Fmain%2F2026_SemanticLink_AlexanderKorn_PowerBIFixer.ipynb
Delta Lake Property Enforcer
DeltaPropEnforcer is a production-oriented Microsoft Fabric notebook designed to safely and consistently enforce Delta Lake table properties across a Lakehouse environment. It provides a controlled, idempotent approach to applying configuration standards at scale, with built-in dry-run support, guardrails to prevent unintended overwrites, and clear logging for transparency and auditability. The notebook is intended to help data platform and analytics teams reduce configuration drift, standardize operational settings, and manage Delta table behavior in a repeatable, governance-friendly way within Microsoft Fabric. Notebook: https://github.com/masonpiwonka/fabric-notebooks/blob/main/DeltaPropEnforcer.ipynb LinkedIn: Mason Piwonka | LinkedIn https%3A%2F%2Fgithub.com%2Fmasonpiwonka%2Ffabric-notebooks%2Fblob%2Fmain%2FDeltaPropEnforcer.ipynb
Nov2025_Subhadip_Pal_CitiesOfTomorrow
The data show a clear, actionable pattern: urban green cover and renewable energy adoption are the strongest correlates of higher urban sustainability. While transport access is beneficial, its effect is smaller than greening and clean energy adoption. Clustering the cities reveals three archetypes — sustainable leaders (high green & low carbon), mixed-profile cities (high green but unexpectedly high carbon), and at-risk cities with low green cover and elevated carbon — suggesting that different policy mixes (greening, emissions controls, transport investments) are appropriate for each group. https%3A%2F%2Fgithub.com%2FSubhadipPal16052000%2FNov2025_Subhadip_Pal_CitiesOfTomorrow-Cities of Tomorrow: Fabric-Ready Urban Sustainability Playbook
🏙️ Findings Summary: Cities of Tomorrow – Urban Growth & Sustainability This project leveraged Python and standard data science libraries (pandas, numpy, matplotlib, seaborn) to perform an exploratory data analysis (EDA) on an urban planning dataset sourced from Kaggle. The mission was to uncover insights into how cities evolve, sustain, and innovate for the future, ultimately aiming to inform city planners and policymakers. 📊Methodology & Data Quality The analytical workflow involved loading the dataset, conducting extensive Exploratory Data Analysis (EDA), and visualizing key patterns. The data quality was found to be high, with no missing values or duplicates detected across the features. A preliminary statistical review showed the Urban Sustainability Score had a mean of 0.48 and a standard deviation ($\sigma$) of 0.17, indicating moderate variability in current sustainability performance across the studied urban areas. Feature correlation analysis identified several strong relationships between various urban metrics. ✅Key Sustainability Drivers & Insights The analysis yielded several crucial insights regarding the core drivers of urban sustainability: Balancing Density and Green Space: A strong positive correlation was found between the Population Density (when balanced) and Green Space Ratio with higher sustainability scores. This suggests that cities managing density effectively while preserving ample green areas achieve better outcomes. The Power of Transit: Public Transport Accessibility was identified as a top driver of urban sustainability. Efficient, widespread transit systems are crucial for reducing carbon footprints and improving urban quality of life. Environmental Initiatives: Both Renewable Energy Usage and the Waste Recycling Rate showed significant positive impacts on the sustainability score, confirming the importance of direct environmental policy initiatives. Crucial Social Infrastructure: Cities with a higher Education Index and broader Healthcare Coverage consistently demonstrated better sustainability outcomes. This highlights the essential, supportive role of robust social infrastructure in achieving long-term urban sustainability goals. 🎯Conclusion The findings underscore the multifaceted nature of urban sustainability, which is driven by a complex interplay of environmental, infrastructural, and social factors. The results provide city planners with actionable areas for strategic focus, specifically in enhancing public transit, prioritizing green infrastructure, and investing in core social services to build truly sustainable "Cities of Tomorrow." https%3A%2F%2Fgithub.com%2FSkarthikak%2FPython-Docs%2Fblob%2Fbb8118f8483e3d34ad99425118d6ed50bf9e198c%2FNov2025_%255BSaikarthikAK%255D_CitiesOfTomorrow.ipynbOptimize Power BI Report with Model Best Practice Analyzer and Vertipaq Analyzer using Semantic Link
Every Power BI Developer pays attention to the underlying performance of their dashboard beyond just churning out reports. This is why adhering to best practices rules should no longer feel like a task, irrespective of your level of experience with Power BI. Semantic Link Labs is here to get the job done. If you are reading about Semantic Link Labs for the first time, you can read my demo introductory article here. This is a Python library developed by Michael Kovalsky that makes this process easier with just a few lines of code in Microsoft Fabric Notebook. Our Use Case: David, A Power BI developer at the AMA enterprise, got several complaints from colleagues that the Sales and Return dashboards take too long to load and sometimes time out. David thought of many reasons why this could have happened, but he wants to start by using the Model Best Practice Analyser Method and Vertipaq Analyzer to investigate the issue. Before this day, David knew he could use an external tool like Tabular Editor for this task. But he recently found out about a new Python library called Semantic Link Lab using Microsoft Fabric Notebook that can help him figure out this issue with the report with just a few lines of code. The sample Sales & Returns Sample v201912 dashboard use for this demo is available for download here You can also download this Microsoft Fabric Notebook used for this Demo here. David’s Task: Perform a Model Best Practice Analyser (BPA) on the Semantic Model of your Power BI report. Perform a Vertipaq Analyzer on your semantic model. Let’s get started: Open your Notebook In Microsoft Fabric, let's investigate what went wrong. Before we get started, we must make sure we have the Semantic Link Labs library installed as well as the other necessary libraries. #Install the Semantic link Labs library in your Fabric notebook %pip install semantic-link-labs After that, we will also be installing the necessary libraries in your notebook. ### Once installed, run this code to import the library into your notebook import sempy_labs as labs from sempy_labs import report as rep from sempy_labs.report import ReportWrapper 1. Performance Model Best Practice Analyzer (BPA) on a Semantic Model report. Model Best Practice Analyzer uses best practice rules to scan your semantic models for bad DAX code, improper formatting and usage of data types, inappropriate relationships, and potentially risky calculation patterns that should be fixed in your report. #Model Best Practice Analyzer import sempy_labs as labs # Enter the name or ID of your semantic model dataset = 'Sales & Returns Sample v201912' # Enter the name or ID of the workspace in which the semantic model # resides workspace = 'Fabricday' labs.run_model_bpa(dataset=dataset, workspace=workspace) When you toggle on the results below, we can see that we have: 16 DAX expression with high rule violation severity, 10 Medium severity, report pages with other details in the Sales & Return Sample v201912 Dashboard. 64 Formatting Medium rule violation severity and 41 on low-level/hints severity. 146 Maintenance low-level/hints rule violation severity and 10 Medium rule violation severity. 46 Performance Medium rule violation severity. & Returns Sample v201912 2. Perform a Vertipaq Analyzer on your Semantic Model. import sempy_labs as labs # Enter the name or ID of your semantic model dataset = 'Sales & Returns Sample v201912' # Enter the name or ID of the workspace in which the semantic model # resides workspace = 'Fabricday' #x = labs.vertipaq_analyzer(dataset=dataset, workspace=workspace) # Setting export='table' will export the results to delta tables in # the lakehouse attached to the notebook x = labs.vertipaq_analyzer(dataset=dataset, workspace=workspace, export='table') # Setting export='zip' will export the results to a .zip file in the lakehouse attached to the notebook. # x = labs.vertipaq_analyzer(dataset=dataset, workspace=workspace, export='zip') The result below shows that the Vertipaq Analyzer Analysis of the Sales & Returns Sample v201912 has been exported to a delta table in my Lakehouse. A snapshot of the Vertipaq Analyzer in my Lakehouse. This shows the Vertipaq Analyzer columns, hierarchies, tables, model, partitions, relationships and tables which can be visualize to understand the semantic model performances. Conclusion If you ever have a slow Power BI Dashboard that takes time to load, and you want to understand the reason for the slow performance. You can download the Microsoft Fabric Notebook used for this optimisation and enhancement Power BI report with Semantic Link Labs here. References Semantic Link Labs Git Hub Repository by Michael Kovalsky: https://github.com/microsoft/semantic-link-labs Feel free to connect with me via my socials below if you want to discuss further. LinkedIn: Musili Adebayo Twitter: Musili_Adebayo https%3A%2F%2Fgithub.com%2FMusili-Adebayo%2F-Musili-Adebayo-fabric_day_project_dashboard%2Fblob%2Fmain%2Fpowerbi_report_notebook.ipynbDelta table statistics, maintenance and properties configuration
This notebook serves as an example of the possible implementation and subsequent automation of audit and maintenance operations on the tables in our lakehouse. It extends functionality and allows you to streamline maintenance operations available from the lakehouse explorer and schedule them as needed, as well as check the evolution of the tables residing in it and the configuration of their properties. In this way, we achieve a more efficient, robust, and complete ecosystem. You will need to: Download and import the notebook to a Fabric capacity backed workspace Attach your own lakehouse to the notebook in order to test and try the samples provided Configure the specified variables as you need them before running, specially the properties setting section The notebook is divided into different cells for each section, including comments and type hinting. The code does not modify your own data. However it will delete unused Delta table files if the VACUUM operation is executed, read everything first before executing anything. Feel free to adapt the samples for your own needs, hope you find it useful. Credits & acknowledgments Sandeep Pawar for its base analysis for Delta table stats Miles Cole for its brilliant article about table compaction Notebook is available here. https%3A%2F%2Fgithub.com%2Fl2aFa%2Fpbi-notebook-gallery%2Fblob%2Fmain%2Fdelta_one_for_all.ipynb
