# Power BI MCP Gateway

A remote MCP server that lets any MCP client (Claude, VS Code and GitHub Copilot, Cursor, ChatGPT,
your own agents) talk to Power BI semantic models **as the signed-in user**, with the
organisation's business knowledge kept centrally on the server instead of in every client's
instruction files.

It works with any Power BI semantic model in a workspace that has an XMLA endpoint, which means
**Premium Per User, Premium or Fabric capacity**. It is independent of organisation, industry and
model schema: everything domain-specific comes from a *skills folder* that each deployment supplies.

## Why

Microsoft's hosted Power BI MCP server can run DAX for a user, but it needs a model id you have to
know, it carries no business vocabulary, and its `GenerateQuery` tool runs on Copilot, which needs
a Copilot licence and an F2 or P1 capacity (on Premium Per User it fails with
`AI_Scenarios_SkuNotSupported`). The gateway sits in front of it:

| Need | Microsoft's hosted server | Gateway |
|---|---|---|
| Which models exist | none; you must know the id | `list_semantic_models`: only what the user can open, curated ones with description, scope and key measures |
| Business knowledge | none | server instructions, `get_business_context`, one prompt per recipe, `skill://` resources |
| DAX generation | `GenerateQuery` on Copilot: Copilot licence plus F2/P1 capacity, not Premium Per User | `generate_dax` on your own Foundry (Azure OpenAI) deployment, grounded in schema, glossary and rules, with one repair round; no Copilot, no Fabric capacity |
| Client onboarding | an Entra app registration per client | OAuth proxy with dynamic client registration: paste the URL, sign in |
| Execution | schema, DAX, report metadata | the same, called with the user's on-behalf-of token, so Build permission and row-level security apply |

The gateway never calls `GenerateQuery`. `generate_dax` writes the query on the deployment's own
Foundry model and runs it through the hosted server's `ExecuteQuery`, which only needs Build
permission on the model and an XMLA-capable workspace. Microsoft's open-source
[skills-for-fabric](https://github.com/microsoft/skills-for-fabric) and
[powerbi-modeling-mcp](https://github.com/microsoft/powerbi-modeling-mcp) take the same route:
the client's own LLM writes the DAX, guided by skill files. There is no Microsoft library for DAX
generation; the reusable part is their DAX guideline text, which a deployment can fold into its
`skill://dax-rules`.

Nothing runs under a service identity. Every Power BI call carries a token issued for the signed-in
user, which is also what the Premium Per User terms of use require for multi-user applications.

## Architecture

```
MCP client (Claude, VS Code, Cursor, ChatGPT, agent code)
   |  Streamable HTTP + OAuth 2.1 (dynamic client registration against the gateway)
   v
Gateway  (Azure Container Apps)                          FastMCP + Azure OAuth proxy
   |-- /mcp        tools, prompts, resources, instructions   <- skills folder, loaded at startup
   |-- /authorize  /token  /register  /auth/callback         <- Microsoft Entra ID behind it
   |
   |-- per call: on-behalf-of token for https://api.fabric.microsoft.com
   |      |-- Fabric REST      workspaces and semantic models the user can open
   |      `-- hosted Power BI MCP   GetSemanticModelSchema / ExecuteQuery / GetReportMetadata
   `-- managed identity -> Foundry model deployment (Responses API) for generate_dax
```

## Tools

| Tool | Purpose |
|---|---|
| `list_semantic_models` | Models the user can open; curated first with description, scope, date table, key measures, recipes |
| `get_business_context` | The deployment's glossary: vocabulary, measures, dates, model traps, recipe index |
| `get_recipe` | A validated query sequence for a recurring analysis |
| `get_semantic_model_schema` | Tables, columns, measures with descriptions, relationships |
| `execute_dax` | Run 1 to 4 DAX queries as the user (default 250 rows) |
| `generate_dax` | Question to DAX, optionally executed; returns DAX, explanation, assumptions, rows |
| `get_report_metadata` | Pages, visuals and filters of a report the user can open |
| `recall` / `remember` / `forget` | Notes users attach to one model (a correction, a trap, the measure for a recurring question). They follow the model: visible and writable only to people Power BI lets open it, and folded into the schema notes and `generate_dax` grounding |

Prompts: one per recipe in the skills folder. Resources: `skill://glossary`, `skill://dax-rules`,
`skill://catalog`, `skill://recipes/{name}`. The tool contract is in
[docs/connect/other-agents.md](docs/connect/other-agents.md).

## Skills: the part you own

`skills/` in this repository is a **fictional example** (Contoso Retail). A deployment brings its
own folder with the same five parts and the gateway is built with it:

| File | Purpose |
|---|---|
| `instructions.md` | How the assistant should work with the tools; sent to every client |
| `glossary.md` | Business vocabulary, which measure answers which question, sign conventions, traps |
| `dax-rules.md` | House rules for DAX generation |
| `recipes/*.md` | Validated query sequences; each becomes a prompt and a resource |
| `catalog.yaml` | Curated models: id, workspace, scope, key measures, notes |

Nothing about a real organisation belongs in this repository. See
[docs/skills-authoring.md](docs/skills-authoring.md) for writing a folder and
[docs/private-skills.md](docs/private-skills.md) for running many deployments from this one codebase.

## Deploy

Prerequisites: PowerShell 7, Azure CLI 2.78+, signed in with rights to register Entra applications
and grant admin consent, and Contributor on the subscription. The tenant needs the Power BI MCP
endpoint enabled (see [docs/administration.md](docs/administration.md)).

```powershell
# try it with the example skills
.\deploy\deploy_to_azure.ps1 -AcrName <globally unique registry name>

# a real deployment: private skills + resource names from a profile, engine pinned to a release
.\deploy\deploy_to_azure.ps1 -Profile C:\deployments\<org>\deploy\profile.json
```

The script creates or updates, idempotently: the Entra app (confidential client, `access_as_user`
scope, delegated Power BI permissions, admin consent), a Foundry resource with a model deployment,
a container registry, Log Analytics, a Container Apps environment and the container app with a
managed identity. It stages only runtime code and the selected skills for the build; `.env`, Git
history and private notes never reach the image. Re-run after changes; `-SkipFoundry` skips the
model part, `-SkipBuild` keeps the running image.

Releases publish the engine image to `ghcr.io/plainsightpro/powerbi-mcp-gateway:<version>`; a
deployment profile with `EngineImage` layers its skills on that image instead of building the
source, which is how deployments upgrade without forking.

## Connect

Users need the gateway URL, a Microsoft work account, a licence matching the workspace and Build
permission on the models they query. Walkthroughs per tool, usage guidelines and troubleshooting
are in [docs/](docs/README.md). In short:

```bash
claude mcp add --transport http powerbi https://<host>/mcp      # then /mcp and sign in
```

Claude Desktop, claude.ai, ChatGPT: add a custom connector with the URL, no client id. VS Code:
`{"type": "http", "url": "https://<host>/mcp"}` in `mcp.json`; Cursor: `{"url": "https://<host>/mcp"}`
under `mcpServers`.

## Local development

```powershell
uv sync                                  # .venv from uv.lock, dev tools included (ruff, pyright, pytest)
Copy-Item .env.example .env             # Entra app values, Foundry endpoint, optional PBIMCP_SKILLS_DIR
uv run pytest -q
uv run python -m powerbi_mcp             # http://localhost:8000/mcp
```

`http://localhost:8000/auth/callback` is registered on the Entra app by the deploy script, so a
local server runs the full sign-in. `az login` supplies the Foundry credential locally; the
signed-in user needs the Cognitive Services OpenAI User role on the Foundry resource, or set
`PBIMCP_FOUNDRY_API_KEY` for the session.

Three checks exist: `python -m powerbi_mcp --check-skills <folder>` validates a skills folder
offline, `scripts/smoke_test.py` runs the token exchange, catalog, schema, query and DAX generation
headlessly with the Azure CLI identity (the deploy script's `-PreauthorizeAzureCli` enables it), and
`scripts/check_gateway.py https://<host>/mcp` verifies a deployed gateway through the real browser
sign-in.

## Known limits

- The app runs one replica. Its OAuth state (client registrations, encrypted tokens) lives in an
  Azure Table, so a redeploy keeps users signed in; scaling out additionally needs
  `stateless_http=True` on the server (MCP sessions are per replica).
- Skills are deployment-wide, not filtered by the user's permissions; separate deployments for
  groups that must not share business knowledge.
- `generate_dax` returns one query per call; multi-step analyses follow recipes.

## License

MIT. Built by [Plainsight](https://plainsight.pro); contributions welcome.