Skip to content
BranchesTags

Repository files navigation

Agentic BI Development — Solid + Power BI (PBIP)

Build a full Power BI project from a Solid semantic model without modeling in Power BI or dragging fields onto a canvas. Cursor talks to the Solid MCP, grounds metrics in certified SQL (text2sql), and writes human-readable TMDL (semantic model) + PBIR (report). A small Python toolkit assembles a Desktop-ready .pbip you can open against live Snowflake—or push to Fabric when you’re ready.

No manual semantic model in Power BI. No canvas click-ops. The model you certified in Solid becomes the model in Power BI.

How it works

Solid semantic model (tables, metrics, relationships)
        │
        ├─ YAML export ──► adapter ──► TMDL
        └─ Solid MCP ────► grounded SQL / glossary / asset metadata
                              │
                              ▼
                    Agent writes DAX + PBIR JSON
                              │
                              ▼
              reports/<slug>/…Desktop-style .pbip
                              │
              ┌───────────────┴───────────────┐
              ▼                               ▼
     Power BI Desktop                  Fabric / XMLA (optional)
     open .pbip, live Snowflake        deploy or Git-sync to workspace
  1. Start from the model — A Solid semantic model for your domain (tables, columns, metrics, relationships).
  2. Ground with the Solid MCP — Cursor invokes text2sql (and related tools) so joins, filters, and metric logic come from the certified model—not guesses.
  3. Translate SQL → DAX — Grounded SQL informs TMDL measures and relationships.
  4. Build the report — The agent scaffolds PBIR pages/visuals and binds them to the model; scripts assemble a .pbip.
  5. Open in Power BI — Drop the project into Desktop (or deploy to the cloud). Keep editing in Cursor anytime.

Skill files and validation scripts under .cursor/rules/ and docs/specs/ encode PBIR/TMDL landmines (schema URLs, theme packages, relationship forests, and so on) so the project opens on the first try more often than not.

Prerequisites

Solid

You need a Solid environment with a semantic model you trust, plus MCP access:

  1. Solid workspace / environment with your semantic model built and certified.
  2. Management key with MCP enabled, and the Semantic Layer ID (UUID) for that model.
  3. Configure Cursor (or your IDE) for Solid’s streamable-http MCP at https://mcp.production.soliddata.io/mcp — see Getting Started with the Solid MCP Server.
  4. Export the model as Solid YAML into schemas/ (example checked in: schemas/retail_sales.yaml).

For scripted MCP calls from this repo, copy .env.example → .env and set:

SOLIDDATA_MANAGEMENT_KEY=…
SEMANTIC_LAYER_ID=…

Scripts authenticate with the x-solid-management-key header (powerbi_middleware.solid_client). There is no in-process LLM API key—the IDE agent is the model.

Power BI & data

  • Power BI Desktop with the PBIR preview enabled (required to open generated projects).
  • Snowflake connectivity from the machine that opens Desktop (DirectQuery).
  • Python 3.10+ for the assemble/validate scripts.

Optional: Fabric / workspace deploy

If you want XMLA deploy or Fabric Git Integration instead of (or after) Desktop:

  • Power BI Premium Per User (PPU) or a Fabric F SKU
  • Entra ID app registration with admin consent for scopes such as Dataset.ReadWrite.All and Report.ReadWrite.All
  • Env vars in .env for Entra + FABRIC_WORKSPACE_ID (and GIT_REPO_PATH if using --push)

Quick start (Desktop)

1. Install

macOS / Linux:

python3 -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"

Windows (use a real python.org install—not the Microsoft Store stub). Prefer the venv explicitly:

powershell -File scripts\bootstrap.ps1
.\.venv\Scripts\python.exe -c "import powerbi_middleware"

See Windows Python notes if bare python fails in Cursor terminals.

2. Solid YAML + assemble

# Use your export, or the sample retail model
python scripts/e2e_local_pbip.py \
  --schema-path schemas/retail_sales.yaml \
  --output-slug demo \
  --name Demo

Windows:

.\.venv\Scripts\python.exe scripts\e2e_local_pbip.py `
  --schema-path schemas/retail_sales.yaml `
  --output-slug demo `
  --name Demo
# or: scripts\e2e_local_pbip.cmd --schema-path schemas/retail_sales.yaml --output-slug demo --name Demo

3. Open in Desktop

Open reports/demo/Demo.pbip in Power BI Desktop against live Snowflake DirectQuery.

If the connection fails: fix it once in Desktop, copy non-secret partition/M snippets into templates/snowflake-connection/, regenerate with --connection-template templates/snowflake-connection, and confirm reopen works. Never commit passwords or *.pbi/localSettings.json / cache.json.

4. Agent day-to-day

  1. Point Cursor at this repo with Solid MCP connected.
  2. Ask for a dashboard / pages from a use case (the agent should pick the right Solid model via MCP, ground metrics, write TMDL + PBIR).
  3. Optionally validate with middleware pbir_validate, then reopen the .pbip in Desktop.
  4. Commit (no secrets, no Desktop cache files).

Example prompt shape (adapt to your domain):

Build a new Power BI report dashboard + relevant raw table pages from scratch
with the correct Solid model and the right visuals for:

<your use case bullets>

Optional: Fabric layout / XMLA / Git

Same artifacts; use when the workspace has Fabric/PPU and XMLA (or Fabric Git Integration).

python scripts/e2e_solid_to_git.py \
  --schema-path schemas/retail_sales.yaml \
  --report-path /path/to/pbir/report \
  --output-path .e2e_out \
  --layout fabric

Optional flags: --deploy (XMLA; requires Entra credentials) and --push (requires GIT_REPO_PATH and a remote connected to the Fabric workspace).

Path When Output
Desktop (default) Power BI Desktop, no Fabric API reports/<slug>/ with .pbip
Fabric PPU / Fabric + XMLA or Git layout="fabric"; optional --deploy / --push

Repo layout

schemas/                         # Solid YAML exports
reports/<slug>/                  # <Name>.pbip, <Name>.SemanticModel/, <Name>.Report/
templates/snowflake-connection/  # optional Desktop-seeded non-secret snippets
src/powerbi_middleware/          # YAML → TMDL, PBIR scaffold/rebind, assemble, validate
.cursor/rules/                   # agent skill rules for PBIR/TMDL + Solid→PBIP
docs/specs/                      # PBIP/PBIR schema reference (canonical for agents)

Reality check

  • PBIR / PBIP are in public preview — behavior and APIs may change before GA. See Microsoft’s PBIR considerations (export, subscriptions, Embedded, size limits, etc.).
  • Not every metric maps 1:1 to tidy DAX — simple aggregates translate cleanly; window / share-of-total style logic may be flagged for review rather than emitted broken.
  • Desktop is the final judge — structural validation catches many issues; opening the .pbip in Power BI Desktop is the real test.
  • Agents sometimes stumble — expect occasional self-correction on script or format errors; re-run or nudge if needed.

Risks and mitigation

Risk Mitigation
Preview churn (PBIR/TMDL / Fabric Git) Track Microsoft Learn; validate in Desktop after format changes
Generated Snowflake connection won’t open Desktop escape hatch; commit non-secret snippets under templates/snowflake-connection/
Entra API consent (Fabric path) Obtain admin consent before --deploy; document scopes in .env.example
Invalid PBIR after agent edits pbir_validate; reopen in Desktop before commit
Schema shape drift Solid YAML adapter + fixture; update adapter with real exports

Pipeline details

Data layer

  1. Load schema (load_schema detects Solid vs normalized YAML).
  2. Generate TMDL (generate_tmdl); optional --connection-template merge.
  3. Optionally deploy via XMLA (xmla_deploy) when Entra credentials exist.

Visual layer

  1. Scaffold minimal PBIR (pbir_scaffold) or rebind field refs (pbir_rebind — deterministic map, no LLM).
  2. Day-to-day layout/measure edits: Cursor (or similar) under reports/, grounded by Solid MCP.

Validation

  • Desktop: Open the .pbip and confirm visuals query Snowflake.
  • Fabric (optional): After Git sync or XMLA deploy, confirm model + report in the workspace.

PBIR visual constraints for agent/human design input: docs/wireframe-pbir-checklist.md.

Tests

pytest tests/ -v

Troubleshooting

  • Auth / Entra errors: Ensure admin consent for required scopes; check Entra vars in .env.
  • Schema not found / unexpected shape: Use --schema-path. Normalized format: tables with name, columns, measures. Solid-style exports are auto-adapted; if a real export fails, update solid_yaml_adapter + fixture together.
  • Snowflake won’t connect in Desktop: Use the escape hatch; merge non-secret templates only.
  • Validation failures: Check PBIR-supported visuals; see wireframe checklist.
  • Git remote not set (--push): Configure remote on the Fabric-connected repo.
  • Windows: python is a 0-byte stub: Use .\.venv\Scripts\python.exe or scripts\e2e_local_pbip.cmd (see below).

Windows Python notes

Cursor agent terminals often omit User PATH entries, so bare python / python3 resolve to the Microsoft Store stub (WindowsApps\python.exe, 0 bytes) and py may be missing.

  1. Install Python 3.12 from python.org and enable Add python.exe to PATH.
  2. Disable Store aliases: Settings → Apps → Advanced app settings → App execution aliases → turn OFF python.exe and python3.exe.
  3. Bootstrap: powershell -File scripts\bootstrap.ps1
  4. Always run via the venv: .\.venv\Scripts\python.exe scripts\… or scripts\e2e_local_pbip.cmd

References

About

No description, website, or topics provided.

Resources

Readme
Activity
Custom properties

Stars

1 star

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages