powerbi-to-sl

powerbi-to-sl — Power BI → Keboola Semantic Layer Migration

Read existing PowerBI artifacts → map to Keboola SL payloads → write JSON files → user confirms → push via sl-toolkit or direct REST. Companion to sl-toolkit (greenfield); this is the brownfield path.

---

Step 0 — Identify input

Ask the user where the PowerBI artifacts are, then auto-detect format:

INPUT_DIR="$(read -p 'Path to PowerBI artifacts: ' x; echo "$x")"
python3 -c "
import os, sys
p = sys.argv[1]
tmdl = [f for f in os.listdir(p) if f.endswith('.tmdl')] if os.path.isdir(p) else []
jsons = [f for f in os.listdir(p) if f.endswith('_semantic_layer.json')] if os.path.isdir(p) else []
if tmdl: print('tmdl')
elif jsons: print('per-table-json')
else: print('unknown')
" "$INPUT_DIR"

If unknown, ask the user to clarify, or have them run Microsoft's Power BI Modeling MCP with --readonly against their dataset to produce TMDL first.

---

Step 1 — Business questions (before mapping)

"Three quick questions:

1. What is the Keboola bucket prefix for these tables? (e.g., in.c-pbi-migration — used to build tableId for each semantic-dataset)
2. Semantic model name + display name? (e.g., acme-pbi-migration / "Acme Power BI Migration")
3. Snowflake or BigQuery dialect? (default: Snowflake)"

Persist answers as BUCKET_PREFIX, MODEL_NAME, MODEL_DISPLAY, DIALECT.

---

Step 2 — Run the migrator

python3 scripts/migrate.py \
  --input "$INPUT_DIR" \
  --input-format "$FORMAT" \
  --bucket-prefix "$BUCKET_PREFIX" \
  --model-name "$MODEL_NAME" \
  --model-display "$MODEL_DISPLAY" \
  --dialect "$DIALECT" \
  --output ./out/

Output layout:

out/
  semantic-model.json
  semantic-dataset/
    <bucket-prefix>.<table>.json   # one per PowerBI table
  semantic-metric/
    <measure-slug>.json            # one per PowerBI measure
  semantic-relationship/
    <from>__<to>.json              # one per PowerBI relationship
  WARNINGS.md                      # DAX-not-translated, unmapped types, etc.

---

Step 3 — Review WARNINGS.md with the user

The migrator preserves DAX measure expressions verbatim in semantic-metric.sql. Show the user WARNINGS.md and ask whether to:

• Punt — leave DAX in place; downstream Kai (or a human) rewrites as SQL on-demand.
• Auto-translate trivial cases — SUM('Table'[Col]) → SUM("Table"."Col"), COUNT, DISTINCTCOUNT, AVERAGE. Anything with CALCULATE, filter context, RELATED, time-intelligence, or variables stays flagged.
• Human pass — open the WARNINGS list and rewrite line by line.

Default: punt + show the list. Trivial-case translation is opt-in.

---

Step 4 — Validate against Keboola SL schemas (jsonschema)

python3 -c "
import json, jsonschema, glob, sys, os
schemas_dir = os.environ['SCHEMAS_DIR']
schemas = {
    name: json.load(open(f'{schemas_dir}/semantic-{name}_schema_1.0.0.json'))
    for name in ['model','dataset','metric','relationship']
}
errs = 0
for kind, schema in schemas.items():
    pattern = './out/semantic-model.json' if kind == 'model' else f'./out/semantic-{kind}/*.json'
    for f in glob.glob(pattern):
        try:
            jsonschema.validate(json.load(open(f)), schema)
        except jsonschema.ValidationError as e:
            errs += 1
            print(f'❌ {f}: {e.message}')
print(f'{errs} validation errors' if errs else '✅ all valid')
"

The same validation runs as a pytest assertion in tests/test_smoke.py. Set SCHEMAS_DIR to the directory containing the Keboola metastore semantic-* JSON schemas.

---

Step 5 — Push (optional, gated on user confirmation)

Confirm with the user before any write. Two paths:

Path A — via sl-toolkit (preferred if that plugin is installed; it owns the canonical generate–validate–push pipeline):

"Hand off to sl-toolkit? It owns the canonical push pipeline for any SL model. Or push directly to the metastore REST API?"

If sl-toolkit chosen, invoke its push step with --source ./out/.

Path B — direct REST (when sl-toolkit is not available):

import json, os, urllib.request, glob
from concurrent.futures import ThreadPoolExecutor
TOKEN = os.environ['KBC_STORAGE_TOKEN']
METASTORE = os.environ['KBC_METASTORE_URL']  # e.g. https://metastore.us-east4.gcp.keboola.com

def post(kind, payload):
    req = urllib.request.Request(
        f'{METASTORE}/api/v1/repository/semantic-{kind}',
        data=json.dumps(payload).encode(),
        headers={'X-StorageAPI-Token': TOKEN, 'Content-Type': 'application/json'},
        method='POST')
    return urllib.request.urlopen(req, timeout=30).read()

# Model first — get the UUID, propagate to children
model_uuid = json.loads(post('model', json.load(open('./out/semantic-model.json'))))['data']['id']
for kind in ['dataset','metric','relationship']:
    payloads = []
    for f in glob.glob(f'./out/semantic-{kind}/*.json'):
        p = json.load(open(f))
        p['modelUUID'] = model_uuid
        payloads.append(p)
    with ThreadPoolExecutor(max_workers=10) as ex:
        list(ex.map(lambda p: post(kind, p), payloads))

---

Mapping reference

Power BI	Keboola SL
tables[].name	semantic-dataset.name + tableId
tables[].description	semantic-dataset.description
tables[].columns[].name	semantic-dataset.fields[].name
tables[].columns[].dataType	semantic-dataset.fields[].type *
tables[].columns[].description	semantic-dataset.fields[].description
PK column hints	semantic-dataset.primaryKey
tables[].measures[].expression (DAX)	semantic-metric.sql (verbatim)
tables[].measures[].description	semantic-metric.description
relationships[]	semantic-relationship

Type mapping: string→string, int64/integer→integer, double/decimal/currency→decimal, dateTime/date→datetime/date, boolean→boolean. Unknown types fall back to string and get logged.

---

Known gotchas

• Per-table JSON loses Power Query M steps. The older get_semantic_model output does not capture M expressions. If full fidelity is required, re-extract with Microsoft's Power BI Modeling MCP --readonly flag to produce TMDL, which captures DAX and M natively.
• DAX ≠ SQL. Verbatim preservation is the default; downstream Kai or a human translates. CALCULATE, RELATED, time-intelligence, and variables do not survive trivial translation.
• PowerBI table names contain spaces and non-ASCII characters. Slugify for filenames; keep originals in name/displayName. UTF-8 paths are fine on macOS/Linux but watch for filesystem-quirky environments.
• PK detection is best-effort. Power BI doesn't always mark PKs explicitly; the migrator uses (a) explicit isKey markers in TMDL, (b) relationship to columns as a heuristic, (c) falls back to empty primaryKey + a WARNINGS entry asking the user to pick.
• Relationship cardinality. manyToOne and oneToOne → inner; oneToMany → left from the "many" side. manyToMany produces a WARNINGS entry — the SL model expects star-schema friendliness.

---

Why two skills exist (powerbi-to-sl vs. sl-toolkit)

Aspect	sl-toolkit	powerbi-to-sl (this)
Direction	Greenfield (Keboola tables → SL)	Brownfield (Power BI → SL)
Input	Keboola storage + SQL transformations	TMDL folder or per-table JSON
Generation	LLM-driven from schema + KPIs	Mechanical structural translation
Validation	Auto-fix + LLM critique	jsonschema validate + WARNINGS
Push	Owns push pipeline	Hands off to sl-toolkit push

When both are installed, this skill produces SL payloads and delegates push to sl-toolkit. Reuse, not overlap.