Powerbi To Sigma

Power BI → Sigma

> Windows / first run — run the environment doctor before anything else: > bash scripts/doctor.sh (macOS/Linux/Git Bash) or powershell -ExecutionPolicy Bypass -File scripts\doctor.ps1 (Windows). > It checks Ruby/Python/Node/bash and flags the Python "Store stub" + CRLF with exact fixes. Details: refs/environment.md.

Preflight the workbook spec before POST (mandatory)

Before POSTing any workbook spec, run ruby scripts/lib/preflight_lint.rb — it exits 1 with a precise message on the two migration-killer bugs: a table with aggregate columns + dimensions but no groupings (renders raw detail rows), and a malformed control (missing id/controlId/controlType or nesting value fields under a value object instead of flat, a non-double-nested source, or a list control wired to neither source nor filters — a filters-only list control is valid). Fix every violation first — never POST past it, and never conclude a feature is "unsupported" from an Invalid kind error (it means the inner fields are wrong). Verified shapes: sigma-workbooks controls.md / tables.md.

Phase 0 — Choose where to build (ask first when no destination given)

Don't silently land the migrated data model + workbook in an auto-picked folder. If the user didn't supply a destination (no --folder ), ASK before building:

1. ruby scripts/pick-destination.rb list → { workspaces, folders (editable, with parentName), myDocuments }
2. Let the user pick ONE: a workspace (its id lands content in the workspace root),

an existing folder, My Documents (when non-null — null for service tokens), or create a new folder: ruby scripts/pick-destination.rb create --name "" [--parent ]

1. Pass the chosen id as --folder . folderId accepts a workspace id or a folder id.

If a destination is already supplied, honor it silently — don't ask.

> READ FIRST — refs/operating-contract.md: the fidelity guardrails (render + value-check EVERY page against the source; never ship empty or silently drop a tile; don't spin — surface blockers). > Status: foundation (validated end-to-end 2026-05-31 on the "Employee Dashboard" workforce report). > Beads: build = beads-sigma-cs2; converter gaps = j89 (M-Snowflake path), tkd (element names / schemaVersion / folderId). > Defers to: sigma-workbooks (canonical workbook spec), sigma-data-models (DM spec), the convert_powerbi_to_sigma MCP tool, and tableau-to-sigma/scripts/* (reused verbatim for posting + layout + parity).

What's proven (the happy path, validated once)

1. CONNECT   device-code login, well-known PowerBI-Desktop client, NO Entra app   → scripts/fabric-extract.py
2. EXTRACT   Fabric getDefinition?format=TMSL → model.bim   (+ .pbix Report/Layout for visuals)
3. CONVERT   local vendored converter (converter/powerbi.mjs via node) — model.bim + connectionId + db/schema → Sigma DM JSON   [MCP only as manual fallback]
4. POST DM   fix spec (schemaVersion + folderId/ownerId + element names) → POST /v2/dataModels/spec
5. WORKBOOK  Data page (master tables per DM element) + chart elements → POST /v2/workbooks/spec
6. LAYOUT    PBIX/PBIR visual x,y,w,h → 24-col grid XML → put-layout.rb
7. VERIFY    sigma-mcp-v2 query each element returns real rows; Phase 6 = compare vs PBI executeQueries (DAX)

Step 0 — Front door: resolve the connection once (scripts/intake.rb)

Resolve the Sigma warehouse connection a SINGLE time up front so no phase free-searches /v2/connections (the token sink):

ruby scripts/intake.rb --workdir  --tool powerbi-to-sigma --mode file \
  [--connection ] [--name ]

Caches /connection.json (the orchestrator reads it when --connection is omitted — point --out at the same `) and writes intake.json (run-start + mode feed the telemetry ping). Multiple connections + no id/name → it lists them and asks you to pick; never guesses. (Power BI input is almost always --mode file` — TMSL + PBIR exports.)

Phase 1 — Connect (no Entra app required)

The corporate tenant blocks Entra app creation, Git integration, and XMLA (PPU). The working path:

• scripts/fabric-extract.py — device-code via well-known public client ea0616ba-638b-4df5-95b9-636659ae5121 (Power BI Desktop), scope https://api.fabric.microsoft.com/.default. User signs in once at the device URL; token cached.
• truststore.inject_into_ssl() is mandatory (first line) — corp TLS inspection on api.fabric.microsoft.com; uses macOS keychain CA.
• See refs/connection.md for the full recipe + surprises (works on My-workspace, device-code not CA-blocked).
• Alternative connector (optional): where the model is on PPU/Fabric capacity, the Power BI Modeling MCP reaches it over XMLA from macOS (no Entra app, no secrets) and can ExportTMSL the model the converter eats — useful when getDefinition REST is unavailable or for ad-hoc model exploration. Setup + two-stage connect recipe in refs/pbi-modeling-mcp.md. Not the default; device-code above is.

Phase 2 — Extract (FAST DISCOVERY — designed for 30-50 workspace / 20-40 report estates)

• Model: getDefinition?format=TMSL (202 LRO → poll Location) → base64 model.bim part = the TMSL/TOM JSON the MCP eats. Works even on My-workspace.
• One concurrent fetch, not two serial scripts. The model TMSL and the report definition are INDEPENDENT artifacts — fabric-extract.py --report --report-out-dir DIR [--report-bundle PATH] fires both getDefinition LROs concurrently (shared pool, hard cap 4 per principal — Fabric throttles getDefinition; >4 risks 429 long-tails). LRO polling is 0.5s-first + backoff (1s, 2s, then Retry-After capped at 4s) instead of sleeping the full Retry-After before the first status check — Fabric routinely advertises Retry-After: 20 for definitions that are ready in . A workspace ID is 2 cheap GETs; the old serial walk of every workspace was 15-30s at 30-50 workspaces. Without --workspace, enumeration fans out **8-wide** (cheap metadata GETs, not LROs) → **~2-3s** for a 30-50 ws estate, and the result is **session-cached** at /tmp/pbiauth/estate-map.json (override PBIESTATECACHE), invalidated automatically on any name miss; --no-cache` bypasses.
• Measured (live, 2026-06-11, EMPLOYEE DASHBOARD): old serial path (fabric-extract + extract-pbir) = 46.3s; new concurrent fetch = 5.1s cold / 3.6-3.9s warm-cache — byte-identical output parts. Every run writes a per-task timings.json to --out-dir (the evidence trail; always emitted).
• Batch / fleet extraction (the assessment path): fabric-extract-batch.py --reports "A,B,C" [--workspace W] [--all] --out-root DIR --pool 4 flattens each report into two artifact tasks (model TMSL + report definition) and pools them 4-wide; each report's bound model resolves via the Power BI REST datasetId (name-match fallback). Measured: 3 reports (6 artifacts) = 7.5s wall vs ~16s serial-equivalent fast-polling and ~2.3 min on the old per-report serial path. Output per report: model/, report/, report-bundle.json + a root manifest.json and timings.json.
• Layout: a .pbix is a zip; Report/Layout is UTF-16LE JSON with per-visual x,y,w,h (canvas px, 1280×720 default). The model in a .pbix is a binary DataModel blob — NOT usable; get the model via getDefinition or a .pbit's DataModelSchema.
• See refs/powerbi-visual-layout.md for the Report/Layout & PBIR parsers and the visualType→Sigma-kind table. The shared fetch layer (token, fast LRO, pooled fetch, estate cache, timings) lives in scripts/pbi_fabric.py.
• Style fidelity — refs/style-fidelity.md: reproducing the PBI report's look, not just its data. The extractor captures the report theme name, card value color, and matrix totals; the builder emits a Sigma themeName/themeOverrides (palette from lib/pbi_theme.rb — drives donut/pie + series colors), KPI-card styling (value.color + titleOrient: bottom), a donut null→(Blank) coalesce so the palette maps per-slice like PBI, and re-expresses a totals-bearing tableEx/matrix as a pivot-table with a grand-total row. Also documents the one deliberate non-transform (PBI thousands-K number format).

Phase 2.5 — SOURCE-FRESHNESS PREFLIGHT (import-mode models, bead fmte)

Import-mode PBI models are frozen snapshots; Sigma reads the LIVE warehouse. Before any parity side-by-side, capture the dataset's freshness so staleness deltas are called out UP FRONT (mirrors qlik-to-sigma Phase 1.5):

"$PY" scripts/pbi-freshness.py --workspace  --dataset  \
  --tmsl model.bim --out $WORK/freshness.json

Pulls the refresh history (GET datasets/{id}/refreshes via the cached token) — last successful refresh + FAILED refreshes (expired warehouse creds are the classic cause; surfaced loudly) — plus a cheap executeQueries row-count/max-date snapshot per table (the per-table probes run 4-wide in parallel — a 6-table model snapshots in one round-trip's wall time). The preflight is NON-BLOCKING: it is only CONSUMED at Phase 6/7 parity, so run.sh (stage 1.5) and migrate-powerbi.rb (Phase 1.5) launch it as a background lane concurrent with Convert/Build and join it (replaying its log) right before parity — 3-8s of Power BI round-trips off the critical path. A run that stops at a gate leaves the detached probe to finish; the resume run reuses the written freshness.json. Phase 6/7 parity is then LED by the staleness banner, and deltas classify MATCH / STALE-EXPLAINED / DIVERGENT — only DIVERGENT blocks (a "Sigma shows more data" delta on a stale snapshot is explained, not a conversion error). migrate-powerbi.rb also always writes per-phase timings.json and prints a PHASE TIMINGS line at every terminal exit.

Phase 3 — Convert (local, zero-config)

The conversion runs locally by default: migrate-powerbi.rb executes the vendored converter bundle (converter/powerbi.mjs, convertPowerBIToSigma) in-process via a node shim — no clone, no npm install, no network, no MCP, no data egress. A dev's own build wins via --mcp-dir / $PBI_MCP_DIR. Only if the bundle is also absent does it gate (exit 10) with instructions to run the convert_powerbi_to_sigma MCP tool manually and resume with --converter-out. The hosted MCP is a fallback, not the default path.

convertPowerBIToSigma(model_json, connection_id, database, schema).

> ⚠️ --converter-out takes the converter's output — never a hand-authored spec. > The flag exists so you can run the converter (the fallback convert_powerbi_to_sigma > MCP tool when the local bundle is unavailable), save its > result, and resume the pipeline with it (convert-model.rb --converter-out ). > It is not an invitation to write dm-raw.json by hand. Hand-authored specs skip > the converter's column-name/SQL/formula-prefix guarantees and reliably produce > Missing "kind" field, source.statement: undefined, and dependency not found > errors (validate-spec.rb now catches the first two, but the right fix is to feed it > real converter output). If the MCP tool is unavailable, STOP and gate — don't fabricate.

• DAX measures → Sigma metrics. ~70% mechanical; see refs/dax-to-sigma-coverage.md and fixtures/MANIFEST.md (test oracle: 94 DAX expressions bucketed a/b/c).
• PromoteHeaders: if pbi-dm-signature.py reports promoted_header_tables (the model's M-query used Table.PromoteHeaders), the warehouse table's real columns are auto-named (C1, C2, …) with the semantic names in row 0 — the TMSL sourceColumn names will NOT resolve. Verify the landed table's real columns and remap with convert-model.rb --table-map (in Sigma formulas the columns appear as C 1, C 2, … and in JOIN SQL alias them, e.g. c.C2 AS CUSTOMER_NAME).
• Known gap j89: the Snowflake Snowflake.Databases(...) + Navigation M pattern isn't parsed → pass database/schema explicitly until fixed.
• DAX gaps → gap-scout: for measures the converter buckets b (restructure) or c (no-equivalent) — RANKX, ALLEXCEPT, SUMMARIZE, USERELATIONSHIP, PATH* — spawn the gap-scout sub-agent (scripts/gap-scout.md): it proposes a Sigma translation, validates it against the live API (scripts/scout-validate.py), and persists the rule to ~/.powerbi-to-sigma/learned-rules.yaml (loaded by scripts/learned-rules.py) so future conversions auto-apply it. Time-intelligence (YTD/SPLY) is usually translatable — see refs/measure-patterns.md, not the scout.

Phase 3.5 — Reuse an existing DM? (avoid sprawl — the reuse-first DM gate every converter runs before building)

Before posting a NEW data model, check whether an existing Sigma DM already covers the same warehouse tables (don't add a 4th near-identical "Orders" DM):

python3 scripts/pbi-dm-signature.py --bim /tmp/pbix/model.bim --out $WORK/dm-signature.json
ruby scripts/find-or-pick-dm.rb --workbook-signature $WORK/dm-signature.json \
  --out $WORK/dm-match.json --auto-pick     # exit 0 = candidate ≥ min-score

pbi-dm-signature.py derives {warehouse_tables (DB.SCHEMA.TABLE from the M nav), referenced_columns, measures} from the model.bim. If a candidate scores high AND there's no tie, --auto-pick recommends reuse (sets auto_picked:true — WARN about inherited columns/RLS/metrics); on a tie it falls back to ASK. To reuse: skip Phase 4, point the workbook masters at the matched recommended_dm_id

• its element ids (describe it), and continue at Phase 5. Otherwise post new.

Phase 4 — Post the data model

The converter output (sigmaDataModel) needs 3 fixups before POST /v2/dataModels/spec (gap tkd):

1. schemaVersion: 1 at top level (else schemaVersion: Invalid 1: undefined).
2. folderId + ownerId at top level — pull from a reference DM (the tableau-to-sigma reuse logic, find-or-pick-dm.rb).
3. Element name on each base warehouse-table element (= source.path[-1]) — the converter only names joined View elements, but workbook masters reference DM elements by name.

Then: tableau-to-sigma/scripts/post-and-readback.rb --type datamodel. See refs/spec-fixups.md.

Phase 5 — Build the workbook

• Data page: one hidden table master per DM element used (source: {kind:data-model, dataModelId, elementId}, columns [ElementName/Col]).
• Chart elements source from a master (source:{kind:table, elementId:}), columns [dim, meas]:
• bar/line: xAxis:{columnId}, yAxis:{columnIds:[...]}
• pie/donut: color:{id}, value:{id}
• text: {kind:text, body:"## ..."}
• measure formula wraps the master col: CountDistinct([Master/Col]), Sum([Master/Col]), date dim DateTrunc("month",[Master/Col]).
• POST /v2/workbooks/spec (post-and-readback --type workbook). Chart-element shapes mirror tableau-to-sigma/scripts/build-charts-from-signals.rb.

Phase 5c — Coverage report (NEVER silently drop a component)

The build never silently drops what it can't resolve — every drop, downgrade, and approximation is recorded to $WORK/coverage.json and surfaced as ONE consolidated MIGRATION COVERAGE readout (the doctrine the RLS section already states, extended to visuals). migrate-powerbi.rb prints it automatically after the workbook POST; for a standalone build-workbook-from-pbir.rb run pass --coverage-out and read the file. The report leads with what carried over (an approximated treemap→bar or a degraded missing-field visual still lands with its data; only a dropped visual is truly absent) so the common "it drops a lot" perception — usually just gaps that were never surfaced in one place — is answered with the real number.

• severity: dropped (no element built) · degraded (built, lost a role/field/sort) · approximated (built as a substituted Sigma kind, e.g. treemap/funnel/gauge/wate

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: twells89
• Source: twells89/sigma-migration-skills
• License: MIT

Install and usage instructions live in the source repository linked above.