Find Which Semantic Models Use a Specific Database Table — Across All Workspaces
Ever needed to know which Power BI semantic models reference a specific source table before making a change or deprecating it? This reusable Fabric Notebook scans all accessible workspaces in one run and identifies every semantic model that references your target table — no manual clicking required. Key Features: ✅ Scans all workspaces in a single run ✅ Filter by workspace name keyword (e.g. [DEV] only) ✅ Three scan methods per model — datasources, model objects, and BIM/M query expressions ✅ Deduplicates results across scan methods ✅ Logs skipped models separately (permission errors) ✅ Export results to CSV in Lakehouse Libraries used: semantic-link-labs, sempy.fabric https%3A%2F%2Fgithub.com%2FBIWithSadia%2Fbi-toolkit%2Fblob%2Fmain%2FDB_table_across_all_workspaces.ipynb
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.ipynbDP-600 Study Companion Notebook for Microsoft Fabric
I created this study companion notebook while preparing for the DP-600: Implementing Analytics Solutions Using Microsoft Fabric certification. The repository brings together key Microsoft Fabric concepts including OneLake, Lakehouse, Data Warehouse, Direct Lake, Semantic Models, DAX, Security, Governance, and Monitoring in a single reference guide. The goal is to help analytics engineers, Power BI developers, and certification candidates understand Fabric architecture through practical notes, diagrams, and notebook-based examples. Repository: https://github.com/sabledattatray/dp600-study-companion-notebook Feedback and suggestions are welcome. I hope this resource helps others on their Microsoft Fabric learning journey. https%3A%2F%2Fgithub.com%2Fsabledattatray%2Fdp600-study-companion-notebookFabric 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%2FfabricsparkpooloptimiserFix Slicers with Custom Visual SLICERBAR
Fabric-Notebooks/Migrate Slicers to SLICERBAR.ipynb at main · KornAlexander/Fabric-Notebooks Migrate Native Slicers → SLICERBAR This notebook consolidates the native Power BI slicer visuals on a report page into a single SLICERBAR custom visual using the fix_migrate_slicer_to_slicerbar fixer from the PBI Fixer build of semantic-link-labs. Purpose Reports often accumulate many individual slicer visuals that clutter the canvas and consume real estate. The SLICERBAR custom visual collapses all of those filters into one compact, modern control. This fixer automates that migration directly against the report definition (PBIR) — no manual rebuilding required. What it does For the page you specify, the fixer: Finds the SLICERBAR visual on the page. If none exists, it creates a narrow one in the top-right corner and registers the custom visual in the report. Finds every native slicer visual on the page. For each slicer, extracts its field (Entity + Property) and adds it to the Slicer Bar's query + configuration. Skips duplicates (a field already present in the Slicer Bar) but still removes the redundant native slicer. Deletes the original native slicers once migrated. Requirements The report must be in PBIR format. If it is in the legacy format, run Upgrade to PBIR first. Use scan_only=True for a safe dry run before committing changes. 1. Install Install the PBI Fixer build of semantic-link-labs. The PySpark kernel restarts automatically after install — that is expected. Run this once per session. %pip install git+https://github.com/KornAlexander/semantic-link-labs.git@feature/pbi-fixer-ui -q 2. Import the fixer from sempy_labs.report._Fix_MigrateSlicerToSlicerbar import fix_migrate_slicer_to_slicerbar 3. Dry run (recommended first) scan_only=True reports exactly which slicers would be migrated — nothing is written to the report. Review the output before running the real migration. fix_migrate_slicer_to_slicerbar(report="report name", page_name="page name", scan_only=True) 4. Run the migration This applies the changes: it creates the Slicer Bar if missing, migrates the native slicers into it, and deletes the originals. fix_migrate_slicer_to_slicerbar(report="report name", page_name="page name") Parameters Parameter Description report Name or ID of the report. page_name Display name of the page to migrate. workspace Workspace name or ID. Defaults to the attached lakehouse / notebook workspace. scan_only If True, only reports what would change without writing. Notes When no Slicer Bar exists on the page, a new one is placed top-right, 200 px wide, full page height, titled Slicers. The SLICERBAR custom visual is registered in report.json automatically (idempotent). Re-running on a page that already has all fields migrated simply removes any leftover duplicate native slicers. https%3A%2F%2Fgithub.com%2FKornAlexander%2FFabric-Notebooks%2Fblob%2Fmain%2FMigrate%2520Slicers%2520to%2520SLICERBAR.ipynbGet a list of Custom Visuals
The following two scripts check for custom visuals in use for the whole tenant and lists them. Lightweight alternative for individual reports %pip install semantic-link-labs --quiet import sempy_labs as labs from sempy_labs.report import ReportWrapper cv = ReportWrapper(report="Report Name", workspace="Workspace Name").list_custom_visuals() display(cv) This script checks for custom visuals in use for the whole tenant and lists them. %pip install semantic-link-labs --quiet import sempy_labs as labs from sempy_labs.report import ReportWrapper cv = ReportWrapper(report="Report Name", workspace="Workspace Name").list_custom_visuals() display(cv)<p> </p><p>For all reports in current or all workspaces the following notebook works:<li-code lang="markup"># Custom Visuals Inventory across a Power BI / Fabric Workspace **Author:** Alexander Korn — Solution Engineer Data Platform, Microsoft **Last updated:** 2026-05-15 **Runtime:** Microsoft Fabric Notebook (Python) **Dependencies:** [`semantic-link-labs`](https://github.com/microsoft/semantic-link-labs) --- ## Purpose Governance teams frequently need to answer a deceptively simple question: > **Which custom (third-party / AppSource / organizational) visuals are actually used in our Power BI reports — and where?** This information is essential for: - **Tenant governance** — admins disabling or restricting custom visuals need an impact assessment first. - **Security & compliance** — custom visuals are arbitrary code; you should know what is in production. - **Lifecycle management** — identifying unused or deprecated visuals before cleanup. - **Migration planning** — moving reports between tenants or to Fabric requires knowing every visual dependency. ## What this notebook does It scans **every Power BI report in a given workspace** (or list of workspaces) and produces a tidy table: | Workspace | Report | Custom Visual Name | Custom Visual Display Name | |-----------|--------|--------------------|----------------------------| Under the hood it uses `sempy_labs.report.ReportWrapper.list_custom_visuals()` from semantic-link-labs, which parses the report definition (PBIR) and returns the visuals registered in the report. > ℹ️ Reports stored in the legacy single-file PBIX layout are skipped — see **Notes & limitations** at the bottom for how to convert them. ## How to use 1. Attach this notebook to any Lakehouse in your target workspace (no tables are written — the lakehouse attachment is only required by the Fabric Python runtime). 2. Run the install cell once per session (or bake `semantic-link-labs` into a custom Fabric environment). 3. Set `MODE` (and optionally `WORKSPACES`) in the configuration cell. 4. Run all cells. The final cell renders the inventory table and a few aggregations. --- ## 1. Install dependencies Run this once per Fabric session. To avoid the install on every run, add `semantic-link-labs` to a [custom Fabric environment](https://learn.microsoft.com/fabric/data-engineering/create-and-use-environment) and attach it to the notebook. %pip install semantic-link-labs --quiet ## 2. Configuration Pick the **scan mode**: - `"current"` — scan only the workspace this notebook is attached to. - `"list"` — scan the workspaces named in `WORKSPACES`. - `"all"` — scan every workspace the executing identity can see (slow on large tenants). # Scan mode: "current" | "list" | "all" MODE = "current" # Used only when MODE == "list" — workspace names or IDs. WORKSPACES: list[str] = ["Demo"] ## 3. The function `list_custom_visuals_in_workspace(workspace)` returns a tidy DataFrame with one row per (report, custom visual) pair. It silently skips paginated reports and any report it can't parse, logging the reason. import traceback import pandas as pd import sempy.fabric as fabric from sempy_labs.report import ReportWrapper def _resolve_workspace(workspace: str | None) -> str: """Resolve workspace name — fall back to the notebook's current workspace.""" if workspace: return workspace ws_id = fabric.get_notebook_workspace_id() return fabric.resolve_workspace_name(ws_id) def list_custom_visuals_in_workspace(workspace: str | None = None) -> pd.DataFrame: """Return all custom visuals used by Power BI reports in a workspace. Columns: Workspace, Report, Custom Visual Name, Custom Visual Display Name. """ ws = _resolve_workspace(workspace) try: reports = fabric.list_reports(workspace=ws) except Exception: print(f" · could not list reports in '{ws}':") traceback.print_exc() return pd.DataFrame(columns=["Workspace", "Report", "Custom Visual Name", "Custom Visual Display Name"]) type_col = "Report Type" if "Report Type" in reports.columns else None if type_col: reports = reports[reports[type_col].isin(["PowerBIReport", "Power BI Report"])] name_col = "Name" if "Name" in reports.columns else "Report Name" rows = [] for _, r in reports.iterrows(): report_name = r[name_col] try: rw = ReportWrapper(report=report_name, workspace=ws) cv = rw.list_custom_visuals() except Exception as e: print(f" · skipping '{report_name}': {type(e).__name__}: {e}") continue if cv is None or cv.empty: continue used_col = next((c for c in cv.columns if c.lower().startswith("used")), None) used = cv[cv[used_col] == True] if used_col else cv # noqa: E712 for _, v in used.iterrows(): rows.append({ "Workspace": ws, "Report": report_name, "Custom Visual Name": v.get("Custom Visual Name"), "Custom Visual Display Name": v.get("Custom Visual Display Name"), }) return pd.DataFrame( rows, columns=["Workspace", "Report", "Custom Visual Name", "Custom Visual Display Name"], ) def list_custom_visuals_multi(workspaces: list[str | None]) -> pd.DataFrame: """Run the inventory across multiple workspaces and concatenate the results.""" frames = [] for ws in workspaces: print(f"Scanning workspace: {ws or '<current>'}") frames.append(list_custom_visuals_in_workspace(ws)) return pd.concat(frames, ignore_index=True) if frames else pd.DataFrame() ## 4. Report format inventory (PBIR vs. PBIRLegacy) Lists every Power BI report in the configured workspace(s) along with its **storage format** (`PBIR` or `PBIRLegacy`). Reports flagged as `PBIRLegacy` will be skipped by the custom-visuals scan in section 5. This uses the Power BI REST API `format` field directly — the same signal that [`sempy_labs.report.upgrade_to_pbir`](https://semantic-link-labs.readthedocs.io/en/stable/sempy_labs.report.html) reads. To actually upgrade, see the tip at the bottom of the cell. The same `MODE` / `WORKSPACES` configuration applies. from sempy.fabric import FabricRestClient _client = FabricRestClient() def list_report_formats(workspace: str | None = None) -> pd.DataFrame: """List Workspace, Report, Format ('PBIR' / 'PBIRLegacy') for every PBI report. Uses the Power BI REST API `format` field — same signal that ``sempy_labs.report.upgrade_to_pbir`` relies on, but read-only. """ ws_name = _resolve_workspace(workspace) ws_id = fabric.resolve_workspace_id(ws_name) resp = _client.get(f"/v1.0/myorg/groups/{ws_id}/reports").json() rows = [ {"Workspace": ws_name, "Report": r["name"], "Format": r.get("format", "Unknown")} for r in resp.get("value", []) if r.get("reportType") in (None, "PowerBIReport") ] return pd.DataFrame(rows, columns=["Workspace", "Report", "Format"]) # Reuse the same MODE / WORKSPACES configuration as the rest of the notebook. def _targets_from_config() -> list[str | None]: if MODE == "current": return [None] if MODE == "list": return list(WORKSPACES) if MODE == "all": all_ws = fabric.list_workspaces() if "Type" in all_ws.columns: all_ws = all_ws[all_ws["Type"] == "Workspace"] name_col = "Name" if "Name" in all_ws.columns else "Workspace Name" return all_ws[name_col].tolist() raise ValueError(f"Unknown MODE '{MODE}'.") formats = pd.concat( [list_report_formats(ws) for ws in _targets_from_config()], ignore_index=True, ) if formats.empty: print("No Power BI reports found.") else: counts = formats["Format"].value_counts().to_dict() print("Report format summary — " + ", ".join(f"{k}: {v}" for k, v in counts.items())) display(formats) # Tip: to convert all PBIRLegacy reports automatically, run: # import sempy_labs as labs # labs.report.upgrade_to_pbir(workspace="Demo") ## 5. Run the inventory Resolves the workspace list from `MODE` and runs the scan. The result is always stored in `df`. def _resolve_workspace_list(mode: str, workspaces: list[str]) -> list[str | None]: """Translate MODE + WORKSPACES into the actual list of workspaces to scan.""" if mode == "current": return [None] if mode == "list": if not workspaces: raise ValueError("MODE='list' requires at least one entry in WORKSPACES.") return list(workspaces) if mode == "all": all_ws = fabric.list_workspaces() if "Type" in all_ws.columns: all_ws = all_ws[all_ws["Type"] == "Workspace"] name_col = "Name" if "Name" in all_ws.columns else "Workspace Name" return all_ws[name_col].tolist() raise ValueError(f"Unknown MODE '{mode}'. Use 'current', 'list', or 'all'.") targets = _resolve_workspace_list(MODE, WORKSPACES) print(f"MODE='{MODE}' → scanning {len(targets)} workspace(s).\n") df = list_custom_visuals_multi(targets) if df.empty: print("\nNo custom visuals found.") else: print(f"\nFound {len(df)} (report, custom visual) pairs across {df['Workspace'].nunique()} workspace(s) and {df['Report'].nunique()} report(s).") display(df) ## 6. Quick aggregations A few common slices the governance team typically wants right after the raw list. if not df.empty: # Most-used custom visuals top_visuals = ( df.groupby("Custom Visual Display Name") .agg(Reports=("Report", "nunique"), Workspaces=("Workspace", "nunique")) .sort_values("Reports", ascending=False) .reset_index() ) display(top_visuals) # Reports with the most custom visuals heavy_reports = ( df.groupby(["Workspace", "Report"]) .agg(CustomVisuals=("Custom Visual Display Name", "nunique")) .sort_values("CustomVisuals", ascending=False) .reset_index() ) display(heavy_reports) else: print("No data to aggregate — result set is empty.") --- ## Notes & limitations ### Reports must be stored in PBIR format This notebook reads each report's enhanced report definition (PBIR / `definition.pbir`) via the Fabric REST API. Reports still stored in the legacy single-file PBIX layout cannot be parsed by `ReportWrapper` — they are flagged as `Legacy (PBIX)` in section 4 and **skipped with a `NotImplementedError`** in section 5. **Two ways to convert a report to PBIR:** - **Power BI Service (Edit in browser)** — open the report in the service, click **Edit**, make any tiny change (or none), and **Save**. The service automatically rewrites the report to PBIR on save. Often the fastest option for bulk conversion. - **Power BI Desktop** — open the report, enable **File → Options → Preview features → "Power BI Project (.pbip) save format"** and **"Store reports using enhanced report format (PBIR)"**, then republish. PBIR is the default for any report authored natively in Fabric. ### Other notes - Only **Power BI reports** are inspected — paginated reports are skipped automatically. - Detection relies on visuals registered in the report's `definition.pbir`. A visual that is registered but not placed on a page can still appear; the `Used in Report` flag from semantic-link-labs is honoured when present. - Requires the executing identity to have at least **Viewer** role on every workspace it scans. - semantic-link-labs is an open-source community project maintained by Microsoft engineers — column names may evolve between releases. Pin a version in your custom Fabric environment for production use. ## License MIT — feel free to adapt and reuse. https%3A%2F%2Fgithub.com%2FKornAlexander%2FPBI-Tools%2Fblob%2Fmain%2FNotebook%2520Gallery%2FCustom%2520Visuals%2520Inventory.ipynb
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.ipynbPower BI Access Audit
Have you ever tried to see who has access across your workspaces or datasets? Which rights do those people have? If you are taking care of multiple workspaces this can be tricky. Luckily, you can now follow me through this article, where I am going to explain how to get data for this "PBI Access Audit" easily with Notebooks. My notebook is based on PBI REST API, which is known for years. Unfortunately, it has not been that easy to use it without cloud services like Azure portal and resources like Azure Functions, Azure App, Azure Blob Storage etc. Now we can take advantage of all the ingredients in MS Fabric. Use case? As I mentioned earlier, we would like to know some basic understanding of "Who?" and "Where" has someone access. I will focus basically on MS Fabric Workspaces and Semantic Models, in theory we could get deep dive analysis on all Fabric items, but then this article would be 10x longer and I would repeat myself. Who is it for? For everybody who needs to maintain and keep an eye on workspace(s) and its data. It is especially helpful if you are doing administrator of a capacity, but you are not an Admin for the entire MS Fabric tenant. Why not tenant Admin? Admins can run very specific and powerful REST APIs, while others are limited and need to use solution such as the one I am going to explain. Prerequisites? 1. You should have access to at least one Microsoft Workspace with right to create and run notebooks. 2. You should have access to at least one Fabric Lakehouse with write permission. Let's start Open new Microsoft Notebook and connect your Fabric Lakehouse. In my case I am working with a LH called apiREST: Once you are connected, create a new code section. I personally created multiple sections for better readability, but you can merge it together - outcome will be the same. 1. Importing all crucial modules This will ensure that all pieces of code will run smoothly. import sempy.fabric as fabric import pandas as pd from pyspark.sql import SparkSession from pyspark.sql.types import StructType, StructField, StringType from pyspark.sql.functions import current_date, lit #Instantiate the client client = fabric.FabricRestClient() 2. Getting WORKSPACES It is crucial to get workspaces first, because we use IDs later. Be careful, that workspace id is the same as group id (based on Microsoft documentation). # Make API call url_workspace = "https://api.powerbi.com/v1.0/myorg/groups" response = client.get(url_workspace) # Normalize JSON response_workspaces = pd.json_normalize(response.json()['value']) # Define schema schema_workspaces = StructType([ StructField("id", StringType(), True), StructField("name", StringType(), True), StructField("isReadOnly", StringType(), True), StructField("isOnDedicatedCapacity", StringType(), True) ]) # Create Spark DataFrame df_workspaces = spark.createDataFrame(response_workspaces[["id", "name" , "isReadOnly", "isOnDedicatedCapacity"]], schema=schema_workspaces) display(df_workspaces) # Collect group IDs into a list group_ids = [row.id for row in df_workspaces.collect()] 3. Getting WORKSPACE USERS At this moment we are going to loop through all workspaces and get all users with their permissions. ############################################# # Defying function workspaces_users_api def workspaces_users_api(group_id): # Make API call url_group_users = f"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/users" response_group_users = client.get(url_group_users) # Normalize JSON response_group_users = pd.json_normalize(response_group_users.json()['value']) # Define schema schema_workspaces_users = StructType( [StructField("displayName", StringType(), True), StructField("emailAddress", StringType(), True), StructField("groupUserAccessRight",StringType(), True), StructField("identifier", StringType(), True), StructField("principalType", StringType(), True)]) # Create Spark DataFrame df_group_users = spark.createDataFrame(response_group_users[["displayName", "emailAddress" , "groupUserAccessRight", "identifier", "principalType"]], schema=schema_workspaces_users) # Add additional columns to DataFrame df_group_users = df_group_users.withColumn("group_id", lit(group_id)) df_group_users = df_group_users.withColumn("todaysDate", current_date()) return df_group_users ############################################# # Iterate through group IDs and fetch WORKSPACE USERS combined_workspaces_users_df = None for group_id in group_ids: df_workspaces_users = workspaces_users_api(group_id) if df_workspaces_users: if combined_workspaces_users_df is None: combined_workspaces_users_df = df_workspaces_users # First DataFrame else: combined_workspaces_users_df = combined_workspaces_users_df.union(df_workspaces_users) # Append to the combined DataFrame # Display the combined DataFrame display(combined_workspaces_users_df) 4. Getting DATASETS In case we want to see access to individual semantic models, we need to get first list of those semantic models. Be aware, that dataset id is the same as semantic model id (based on Microsoft documentation). ############################################# # Defying function datasets_api def datasets_api(group_id): # Define expected columns expected_cols = ["id", "name", "createdDate", "configuredBy", "webUrl"] # Make API call with error handling url_datasets = f"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets" try: response_url_datasets = client.get(url_datasets) response_url_datasets.raise_for_status() # Raises HTTPError for bad responses except Exception as e: print(f"Error fetching datasets for group {group_id}: {e}") return None # Parse and clean response raw_data = response_url_datasets.json().get('value', []) cleaned_data = [item for item in raw_data if isinstance(item, dict) and item] if not cleaned_data: print(f"No valid datasets found for group {group_id}") return None # Normalize JSON response_datasets = pd.json_normalize(cleaned_data) # Define schema dynamically schema_datasets = StructType([StructField(col, StringType(), True) for col in expected_cols]) # Select only available columns available_cols = [col for col in expected_cols if col in response_datasets.columns] df_datasets = spark.createDataFrame(response_datasets[available_cols], schema=schema_datasets) # Add additional columns df_datasets = df_datasets.withColumn("group_id", lit(group_id)) df_datasets = df_datasets.withColumn("todaysDate", current_date()) return df_datasets ############################################# # Iterate through group IDs and fetch DATASETS combined_datasets_df = None for group_id in group_ids: df_datasets = datasets_api(group_id) if df_datasets: if combined_datasets_df is None: combined_datasets_df = df_datasets # First DataFrame else: combined_datasets_df = combined_datasets_df.union(df_datasets) # Append to the combined DataFrame # Display the combined DataFrame display(combined_datasets_df) # Extract group Dataset IDs as a list dataset_ids = [row.id for row in combined_datasets_df.collect()] 5. Getting DATASET USERS Finally we can get the final info about the people who have access to Semantic Models. Access to Semantic Models usually reflect access to PBI Reports as well (which is difficult to get without tenant admin rights). ############################################# # Defying dataset_users_api def dataset_users_api(group_id, dataset_id): # Define expected columns expected_cols = ["identifier", "datasetUserAccessRight", "principalType", "groupId", "datasetId"] # Make API call with error handling url_users = f"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/users" try: response = client.get(url_users) response.raise_for_status() except Exception as e: print(f"Error fetching users for dataset {dataset_id} in group {group_id}: {e}") return None # Parse and clean response raw_data = response.json().get('value', []) cleaned_data = [item for item in raw_data if isinstance(item, dict) and item] if not cleaned_data: print(f"No valid users found for dataset {dataset_id} in group {group_id}") return None # Normalize JSON response_users = pd.json_normalize(cleaned_data) # Ensure metadata columns exist for col in ["groupId", "datasetId"]: if col not in response_users.columns: response_users[col] = None response_users["groupId"] = group_id response_users["datasetId"] = dataset_id # Define schema dynamically schema_users = StructType([StructField(col, StringType(), True) for col in expected_cols]) # Select only available columns available_cols = [col for col in expected_cols if col in response_users.columns] df_users = spark.createDataFrame(response_users[available_cols], schema=schema_users) # Add additional columns df_users = df_users.withColumn("todaysDate", current_date()) return df_users combined_users_df = None for group_id in group_ids: df_datasets = datasets_api(group_id) if df_datasets: dataset_ids = [row.id for row in df_datasets.collect()] for dataset_id in dataset_ids: df_users = dataset_users_api(group_id, dataset_id) if df_users: if combined_users_df is None: combined_users_df = df_users else: combined_users_df = combined_users_df.union(df_users) display(combined_users_df) 6. Creating Delta Tables Right now we have all data we need. We only need to store them in our Lakehouse. #Saving df_workspaces delta_table_path = "Tables/WorkspacesAPI" #fill in your delta table path df_workspaces.write.format("delta").mode("append").option("mergeSchema", "true").save(delta_table_path) #Saving df_workspaces_users delta_table_path = "Tables/WorkspacesUsersAPI" #fill in your delta table path combined_workspaces_users_df.write.format("delta").mode("append").option("mergeSchema", "true").save(delta_table_path) #Saving combined_datasets_df delta_table_path = "Tables/DatasetsAPI" #fill in your delta table path combined_datasets_df.write.format("delta").mode("append").option("mergeSchema", "true").save(delta_table_path) #Saving combined_datasets_df delta_table_path = "Tables/DatasetsUsersAPI" #fill in your delta table path combined_users_df.write.format("delta").mode("append").option("mergeSchema", "true").save(delta_table_path) If everything goes well, you should refresh your lakehouse and see new data tables with your data. At this moment you can easily create your Semantic Model with Delta Tables and connect your Power BI report. Model should be very easy and rather small. Final words My example is very simple but can be used for much more. Just check the REST API documentation and you can adjust my code for other purposes. https%3A%2F%2Fgithub.com%2FMigasuke%2FPBI-Access-Audit%2Fedit%2Fmain%2FAudit%2520File
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-Using AI in Microsoft Fabric Notebooks (With X's API Data)
🧾 In short: this notebook walks you through retrieving live data from X (formerly Twitter), applying AI for insights (from free open source models), and visualizing results in Fabric, blending practical use cases in weather monitoring with AI integration. (This notebook can effectively be imported and run freely by anyone with a Microsoft Fabric Trial) 🔑Set Up X (formerly Twitter) Developer Access To retrieve live tweets in this demo, you’ll first set up access to the X (Twitter) Developer API, then store your token securely in Azure Key Vault. 1) Sign in / Sign up Go to the X Developer Portal: https://developer.x.com/en If you already have an account, click Sign in. Otherwise, click Sign up and follow the prompts. 2) Choose a plan Select the Free plan (sufficient for this notebook) or go ahead for the paid versions. The Free tier typically allows 1 request per user every 15 minutes. A single request can return up to 100 tweets. 3) Generate a Bearer Token In the Developer Portal: Open Projects & Apps from the left navigation. Select your project. Click Generate a Bearer Token and copy it. Security note: Treat tokens like passwords. Never commit them to source control and never print them in logs. 4) Store the token securely (Azure Key Vault) Recommended: Save the Bearer Token as a secret in Azure Key Vault. Give the secret a clear name (e.g., TwitterBearer). 🚀Let's Fetch Live Tweets from BoM QLD Now that your X Developer access is set up and your token is safely stored, it's time to retrieve the latest weather notices directly from the Bureau of Meteorology (QLD). Check out the official X account: @BOM_Qld Note: This code uses Python only and not PySpark. PySpark requires Spark jobs, which add overhead. For large datasets, PySpark offers better performance, but since this demo collects only ~100 rows of data, Python is sufficient. Install the required packages (if not already installed) #pip install tweepy import tweepy import pandas as pd from notebookutils import mssparkutils from notebookutils.visualization import display # (Recommended Approach) Store and Get the Bearer Token secret from Azure Key Vault bearer_token = mssparkutils.credentials.getSecret("https://< Add Your Key Vault Here >.vault.azure.net/", "TwitterBearer") # (Alternative Approach) Swap the above with the below and enter bearer token directly # bearer_token = "YOUR_BEARER_TOKEN_HERE" # Connect to X API, authenticate with the Bearer Token and pause if you hit your X API rate limits client = tweepy.Client(bearer_token=bearer_token, wait_on_rate_limit=True) # Get user id user = client.get_user(username="BOM_Qld") # Fetch the latest N tweets tweets = client.get_users_tweets( id=user.data.id, max_results=100, # Change as required (and as your X (twitter) plan allows) tweet_fields=["created_at", "text"] ) # Convert to DataFrame and format date df = pd.DataFrame([{"created_at": t.created_at, "text": t.text} for t in tweets.data]) df["created_at"] = pd.to_datetime(df["created_at"]).dt.strftime("%Y%m%d") # Display the collected data display(df) The data from X (i.e. 100 rows of tweets as specified) 🤖Using AI Models in Microsoft Fabric AI can help us classify text into categories and summarise information for deeper insights. We’ll now use AI models to enrich the collected tweets so that they can be visualised in Power BI more effectively. 📊AI in Microsoft Fabric Microsoft Fabric provides built-in AI Functions, built with OpenAI models such as: Classify: https://learn.microsoft.com/en-us/fabric/data-science/ai-functions/classify Summarise: https://learn.microsoft.com/en-us/fabric/data-science/ai-functions/summarize?tabs=column-summary ⚠️However, at the time of creating this notebook, these built-in functions are not available in the Trial version of Fabric. Therefore many interested and potential users have not been able to trial these AI functions. 🌍Open-Source Alternative (Hugging Face) To work around this limitation, we’ll use open-source AI models from Hugging Face. In particular, Facebook (Meta) models. The models are downloaded once at runtime into your Fabric environment. Therefore your data never leaves Fabric — it is not sent to Hugging Face servers. This makes the approach secure for demos and learning purposes. 🛡️ A Note on Safety These Facebook (Meta) models are widely adopted, open-source, and safe to experiment with in a demo context. That said: Microsoft Fabric (paid) AI functions come with Microsoft security guarantees and would be the recommended approach for Production workflows. Otherwise, please be aware that there are methods to containerize open-source AI models (such as storage in Azure) if alternative models are preferred. Let’s load the models and apply them to our dataset 👇 from transformers import pipeline # Load AI models (download once, cached locally in Fabric runtime) summarizer = pipeline("summarization", model="facebook/bart-large-cnn") classifier = pipeline("zero-shot-classification", model="facebook/bart-large-mnli") # Categories for classification categories = ["Severe Warning", "Flood", "Heat", "Forecast", "Outlook"] # Apply AI models to the data df["summary"] = df["text"].apply(lambda t: summarizer(t, max_length=30, min_length=5, do_sample=False)[0]["summary_text"]) df["category"] = df["text"].apply(lambda t: classifier(t, candidate_labels=categories)["labels"][0]) # Display the dataframe showing the TWO NEW AI generated columns! display(df) With just those few lines of code, we get our two new AI generated summary and category columns! You may now want the actual weather from BoM. 🌦Australian Weather Updates – Brisbane The Bureau of Meteorology (BoM) provides updated weather information across Australia every 30 minutes. The below notebook code fetches the latest weather data specifically for Brisbane. Tip: To get data for other locations, visit https://www.bom.gov.au/ and find the Product and Station codes for your desired area. import requests import pandas as pd import plotly.express as px # Variables (Navigate to https://www.bom.gov.au/ to identify your desired Product and Station) PRODUCT = "IDQ60801" STATION = "94576" URL = f"https://www.bom.gov.au/fwo/{PRODUCT}/{PRODUCT}.{STATION}.json" HOURS = 3 # The last N hours to display # Get the data r = requests.get(URL, headers={"User-Agent": "Mozilla/5.0"}, timeout=20) r.raise_for_status() data = r.json().get("observations", {}).get("data", []) df = pd.DataFrame(data) if df.empty: raise RuntimeError("No data returned from BoM") # Data cleansing - Fromat timestamps, drop duplicates, and sort columns df["Timestamp"] = pd.to_datetime(df["local_date_time_full"], format="%Y%m%d%H%M%S", errors="coerce") df = df.dropna(subset=["Timestamp"]).drop_duplicates("Timestamp").sort_values("Timestamp").reset_index(drop=True) # Rename the column names for readibility rename_map = { "air_temp": "Air Temp (°C)", #"rel_hum": "Humidity (%)", "wind_spd_kmh": "Wind (km/h)", "rain_trace": "Rain (mm)" } df = df.rename(columns={k: v for k, v in rename_map.items() if k in df.columns}) # Limiting the display to the last N hours (per set Variable) cutoff = df["Timestamp"].max() - pd.Timedelta(hours=HOURS) df = df[df["Timestamp"] >= cutoff].reset_index(drop=True) # Ensure all metric columns are numeric for col in rename_map.values(): if col in df.columns: df[col] = pd.to_numeric(df[col], errors="coerce") # Select only numeric columns for plotting ycols = [c for c in rename_map.values() if c in df.columns and pd.api.types.is_numeric_dtype(df[c])] # Plot the dataframe with Plotly fig = px.line(df, x="Timestamp", y=ycols, title=f"Brisbane ({STATION}) – Last {HOURS} Hours BoM Observations") fig.update_layout( plot_bgcolor="white", paper_bgcolor="white", font=dict(color="black"), xaxis=dict(showgrid=True, gridcolor="lightgrey"), yaxis=dict(showgrid=True, gridcolor="lightgrey"), legend=dict(title="Metric") ) fig.show() The Plotly visual above is interactive. For some who may be wondering about visualising the data within the Notebook with Power BI: ⚠️Pre-warning: QuickVisualize in Microsoft Fabric While QuickVisualize provides a familiar Power BI-like interaction, its current functionality is extremely limited. Power BI remains the world’s leading BI tool, but using powerbiclient inside a notebook gives only a lightweight version (useful perhaps for spinning up a quick semantic model), not the full Power BI experience. Interesting to see how this evolves over time… from powerbiclient import QuickVisualize, get_dataset_config # Prepare the same data for Power BI as used for Plotly df_pbi = df[["Timestamp"] + ycols] # renders the quick report in the Fabric notebook cell qv = QuickVisualize(get_dataset_config(df_pbi)) qv.set_size(500, 1400) qv (The Notebook is attached for you to import and run) https%3A%2F%2Fgithub.com%2F
