The Power BI Fellowship

Automation and APIs Track

Once a team owns more than a handful of models, clicking stops scaling. This track covers the APIs and scripts BI engineers use to inventory a tenant, trigger and monitor refreshes, run tests and change models in bulk. You don't need a tenant: the files in data/tracks/automation are realistic API responses, so you can build and check your scripts offline, then point them at a real tenant when you have one.

Before you start Python 3 (requests, msal) or PowerShell 7 (MicrosoftPowerBIMgmt module). Optional: a Fabric trial tenant with an app registration. Never commit secrets: use environment variables or a key vault.

REST APIs, service principals and a tenant inventory

Data: ApiGroups ApiDatasets ApiReports ApiRefreshes ApiViews
Why it matters
You can't govern what you can't see. A scripted inventory answers "what do we have, who owns it, is it used, is it healthy" in minutes, every week, without asking anyone.
Typical production failure
An inventory script authenticates with a developer's personal account; when they leave, it stops, and nobody notices for a month.
When to use it
A service principal (app registration) in a security group allowed by the tenant settings, least-privilege workspace access, secrets outside the code, and the Power BI or Fabric REST APIs (admin APIs or the scanner API for tenant-wide views).
When not to
Hard-coded secrets, personal credentials, and admin APIs when workspace-level permissions would do.

Assignments

Build a workspace inventory from API responses

B · Objective 45 min · uses groups.json, datasets.json, reports.json, refreshes.json, activity_views.json

Write a script that turns the API responses into one inventory table: workspace, semantic model, owner, reports, last refresh status, endorsement, and whether each report was viewed in the last 90 days.

Requirements
  • Read the five JSON files (they have the same shape as the real API responses).
  • One row per report, with its model and workspace.
  • Summary counts for the governance review.
  • Structure it so the file-reading part can be swapped for real API calls.
Expected result: 5 workspaces, 11 semantic models, 18 reports. 7 reports had no views in 90 days; 6 models have no endorsement (2 certified); 1 model's latest refresh failed.

Set up a service principal properly

B · Objective 30 min · uses —

Write the setup checklist your team will follow to give the inventory script its own identity instead of a person's account.

Requirements
  • App registration and client secret or certificate, and where the secret lives.
  • Security group, and the tenant settings that must allow it.
  • Workspace access (which role, which workspaces) or read-only admin API access, and why.
  • Token request: authority, scope and flow.
Expected result: An app registration in an allowed security group; "Service principals can call Fabric public APIs" enabled for that group (plus read-only admin API access only if you need tenant-wide data); Viewer or Member access only on the workspaces it inventories; secret in a key vault or pipeline secret, never in code; client-credentials flow with scope https://analysis.windows.net/powerbi/api/.default.

The secret in the repository

C · Problem 20 min · uses —

Sam pushed the inventory script to the team's GitHub repository with the client secret in a variable. The repository is internal, but 40 people can read it. What do you do, in order?

Work out
  • Containment first.
  • Clean-up of the repository and its history.
  • Prevention.
What good looks like: Rotate (revoke) the secret immediately, check sign-in logs for the app, then remove it from code and history (and treat it as compromised regardless), move it to a secret store, and add secret scanning and a pre-commit hook. Rewriting history alone isn't enough: the secret was already exposed.

Interview questions

  • What is a service principal and why use one for automation?
  • Which tenant settings control service principals in Fabric?
  • Admin APIs vs regular APIs?
  • How would you inventory every workspace in a large tenant?

Assessment

Your inventory script should authenticate with:

  • The BI lead's account
  • A service principal in an allowed security group, secret in a key vault
  • A shared password in the script
  • Anonymous access

To read metadata for every workspace in the tenant efficiently, use:

  • GET /groups for each user
  • The admin scanner API (getInfo, scanStatus, scanResult)
  • Export to Excel from the portal
  • XMLA on each model

Extend your inventory with each model's data sources and gateway (datasources endpoint, or the scanner API's datasourceInstances).

GET /groups/{groupId}/datasets/{datasetId}/datasources; group sources by server to see which models depend on which systems.

Refresh automation, monitoring and tests through the API

Data: ApiRefreshes ApiDatasets
Why it matters
Refreshes should run after upstream data is ready, not at a fixed time that hopes it is; and failures should reach a team, not one person's inbox.
Typical production failure
A 05:30 schedule runs before the warehouse load finishes on busy days, and reports show yesterday's data with no error at all.
When to use it
Trigger refresh from the pipeline that loads the data, poll the status with backoff, log every run, alert on failure, and run test queries after success.
When not to
Tight polling loops (you'll hit API limits) and retrying a failing refresh forever.

Assignments

Trigger a refresh and poll until it finishes

B · Objective 40 min · uses refreshes.json

Write a script that refreshes one semantic model, waits for the result without hammering the API, and logs the outcome. Then use the mock refresh history to report the failure rate.

Requirements
  • POST the refresh, read the request id, poll the refresh history with increasing waits, stop on Completed, Failed or a timeout.
  • Log start, end, duration, status and error details.
  • From refreshes.json: total runs, failed runs, and the model that failed most.
Expected result: 13 of 77 runs in the mock history failed. The script polls with backoff, exits non-zero on failure so the calling pipeline fails too, and writes one log line per run.

Run the model tests after every refresh

B · Objective 30 min · uses —

Your known-input tests from the testing track run in DAX query view. Run them automatically after each refresh with the Execute Queries API, and fail the pipeline if any test fails.

Requirements
  • POST a DAX query to executeQueries; parse the JSON result.
  • Compare each test's actual and expected value.
  • Know the API's limits (one query per call, row limits) and permissions (Build on the model).
Expected result: A script that posts the test query, reads results.tables[0].rows, fails on any row where Pass is false, and is called after a successful refresh.

Design tenant-wide refresh monitoring

C · Problem 30 min · uses refreshes.json, datasets.json

Elena wants to know about any failed refresh of a certified model within 15 minutes, and a weekly view of refresh duration trends. Design it.

Work out
  • Where the data comes from (APIs, workspace monitoring, capacity metrics).
  • How alerts are routed (team, severity).
  • How you avoid alert fatigue.
What good looks like: A scheduled job (every 15 minutes) that reads refresh history for certified models and alerts a team channel on Failed or Completed-with-warning; a weekly report of duration trends; severity from the model's endorsement and consumers; suppression of repeats.

Interview questions

  • How do you trigger and track a refresh through the REST API?
  • What is enhanced refresh?
  • Why trigger refresh from the data pipeline instead of a schedule?
  • What does the Execute Queries API allow?

Assessment

Your script polls refresh status every second. What's the risk?

  • None
  • Hitting API rate limits (HTTP 429) and being throttled
  • The refresh becomes slower
  • The model is locked

A refresh status is Completed but a table kept old data because of a credential problem. Your monitoring should:

  • Treat it as success
  • Treat warnings on production models as failures
  • Ignore warnings
  • Restart the capacity

Add your refresh script to a pipeline (Fabric pipeline, Azure DevOps or GitHub Actions) after the step that loads the warehouse.

Pass workspace and model IDs as variables; keep the secret in the pipeline's secret store.

XMLA, TOM, TMSL and Tabular Editor scripting

Why it matters
Model-wide changes (descriptions on 200 measures, format strings, partitions, a new calculation group) are slow and error-prone by hand. Scripts make them repeatable and reviewable.
Typical production failure
Someone renames twenty measures by hand in production through XMLA; three are misspelt, and the PBIX can no longer be downloaded to check.
When to use it
Tabular Editor scripts or TOM in a pipeline for bulk metadata changes on the PBIP in Git; TMSL or XMLA for partition refreshes and deployments; INFO DAX functions to inspect metadata.
When not to
Editing production models directly through XMLA outside the release process.

Assignments

Find every measure without a description

A · Guided 20 min · uses starter project with your measures
  1. Open DAX query view in Power BI Desktop on a model with at least ten measures.
  2. Run EVALUATE INFO.MEASURES() and look at the columns it returns.
  3. Filter to measures whose Description is blank; return table name, measure name and expression.
  4. Save the query in the repository as a governance check.
Expected result: A query listing every measure without a description, ready to run on any model (or through executeQueries in a pipeline).

Add descriptions in bulk from the KPI dictionary

B · Objective 40 min · uses your KPI dictionary (CSV)

Your KPI dictionary (Sprint 01–05) lives in a CSV with MeasureName and Description. Write a script that applies the descriptions to the model, reports measures missing from the dictionary, and never overwrites a description someone wrote by hand without saying so.

Requirements
  • Tabular Editor C# script or TOM.
  • A dry-run mode that only reports what would change.
  • Run against the PBIP (via Tabular Editor) and commit the result through a pull request.
Expected result: Matched measures get descriptions; unmatched names are listed; overwrites are reported in the dry run; the change is a reviewable diff in the TMDL files.

Refresh only what changed

C · Problem 30 min · uses a model with incremental refresh on an F or PPU capacity (or the trial)

A correction was loaded into the warehouse for March 2026 only. Refresh just that partition of FactSales, not the whole model, and prove that nothing else was reprocessed.

Work out
  • Find the partition name (SSMS, Tabular Editor or INFO.PARTITIONS()).
  • Refresh it with TMSL through the XMLA endpoint (or enhanced refresh via REST).
  • Show the refresh time compared with a full refresh.
What good looks like: A TMSL refresh command targeting FactSales' 2026 Q1 (or March) partition, a refresh that takes a fraction of the full refresh, and partition metadata showing only that partition's refresh time changed.

Interview questions

  • What is the XMLA endpoint?
  • TOM vs TMSL vs TMDL?
  • What's the risk of changing a published model through XMLA?
  • How do INFO DAX functions help governance?

Assessment

To refresh one partition of a large model, use:

  • Publish the PBIX again
  • A TMSL refresh command (or enhanced refresh) targeting that partition
  • Delete the table
  • Scheduled refresh

Where should bulk metadata changes (descriptions on 200 measures) be made?

  • Directly in production via XMLA
  • In the PBIP in Git with a script, then deployed through the pipeline
  • By hand in the Service
  • In Excel

Write a Best Practice Analyzer rule (JSON) that flags measures without a description, and run it in Tabular Editor.

Scope: Measure; expression: string.IsNullOrWhitespace(Description). Add it to the team's BPA rule file in the repository.