# Pbid Tom Live

> Use for scripting an open Power BI Desktop model via its localhost msmdsrv.exe Analysis Services proxy — TOM (Microsoft.AnalysisServices.Tabular) for metadata, ADOMD.NET (AdomdConnection) for DAX. Covers PowerShell setup with retail.amd64 AMO/TOM + ADOMD NuGet DLLs, discovering the msmdsrv port (msmdsrv.port.txt / netstat), connecting via server Databases Model, SaveChanges / UndoLocalChanges, Ex…

- **Type:** Skill
- **Install:** `agentstack add skill-wardawgmalvicious-claude-config-pbid-tom-live`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [wardawgmalvicious](https://agentstack.voostack.com/s/wardawgmalvicious)
- **Installs:** 0
- **Category:** [AI & ML](https://agentstack.voostack.com/c/ai-and-ml)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [wardawgmalvicious](https://github.com/wardawgmalvicious)
- **Source:** https://github.com/wardawgmalvicious/claude-config/tree/main/skills/pbid-tom-live

## Install

```sh
agentstack add skill-wardawgmalvicious-claude-config-pbid-tom-live
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

## Power BI Desktop — Live TOM / ADOMD Operations

Local Analysis Services proxy inside Power BI Desktop. Enables TOM (metadata) and ADOMD.NET (DAX query) connections from PowerShell. Does not work for thin reports, Direct Lake models, or remote XMLA endpoints. For those use a remote XMLA / Power BI REST approach — see fabric-tmdl for semantic-model authoring and fabric-tmdl-api for Fabric semantic-model definition REST call patterns (createItemWithDefinition, updateDefinition).

> **Note (April 2026)**: Direct Lake on OneLake gained calculated-column / calculated-table preview support. The local msmdsrv proxy still does not bind to Direct Lake models — author and edit those columns through the remote XMLA endpoint (or the Desktop UI) and use TOM there. Local TOM / ADOMD scripting in this skill remains scoped to Import / DirectQuery models.

### Prerequisites

| Requirement | Notes |
|---|---|
| Power BI Desktop | Open with a non-thin model loaded |
| PowerShell + `nuget` | `winget install Microsoft.NuGet` |
| `Microsoft.AnalysisServices.retail.amd64` | TOM metadata DLLs |
| `Microsoft.AnalysisServices.AdomdClient.retail.amd64` | DAX query DLLs |

Packages extract to `$env:TEMP\tom_nuget\...\lib\net45\`. Load via `Add-Type -Path`. If TOM calls fail with missing type / compatibility level errors, swap to the newer `Microsoft.AnalysisServices` (.NET 8) package.

### Discovering Running PBID Instances

Each open `.pbix`/`.pbip` spawns one `msmdsrv.exe` on a random localhost port. When multiple are running, connect to each, read `$server.Databases[0].Name`, and ask the user which to target.

| Method | Works On | Command |
|---|---|---|
| Port file (non-Store) | Desktop installer | `Get-Content "$env:LOCALAPPDATA\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\*\Data\msmdsrv.port.txt"` |
| Port file (Store) | MS Store install | `Get-Content "$env:LOCALAPPDATA\Packages\Microsoft.MicrosoftPowerBIDesktop_*\LocalState\AnalysisServicesWorkspaces\*\Data\msmdsrv.port.txt"` |
| netstat | Any | Filter `netstat -ano` LISTENING rows by `(Get-Process msmdsrv).Id` |

> **Watch (verified July 2026, preview)**: The [Power BI Desktop Bridge](https://learn.microsoft.com/power-bi/developer/agentic/power-bi-desktop-bridge-overview) is a preview IPC surface hosted inside the Desktop process — a named pipe `pbi-desktop-bridge-` speaking JSON-RPC 2.0 (Content-Length framing), enabled by default under **Options → Preview Features → "Enable external tool access to Power BI Desktop through secure local APIs"**. Discover methods with `bridge.manifest` (call it first; the surface changes per Desktop version, and unsupported methods return `-32601 MethodNotFound`). This *could* eventually supersede the msmdsrv port-discovery + AS-proxy dance in this skill — pipe name keys off the PID (no `msmdsrv.port.txt` / `netstat` needed), and one Desktop window = one bridge. **Not there yet**: it's preview with an unstable API, currently oriented toward report-authoring / agentic scenarios, and does not replace TOM metadata + ADOMD DAX access covered here. Track the manifest and GA status before relying on it. Re-verify the Learn page before acting on this note.

### Connection Strings

| Target | Connection String |
|---|---|
| TOM metadata | `Data Source=localhost:` via `Microsoft.AnalysisServices.Tabular.Server.Connect()` |
| ADOMD DAX | `Data Source=localhost:` on `AdomdConnection` |

```powershell
$basePath = "$env:TEMP\tom_nuget\Microsoft.AnalysisServices.retail.amd64\lib\net45"
Add-Type -Path "$basePath\Microsoft.AnalysisServices.Core.dll"
Add-Type -Path "$basePath\Microsoft.AnalysisServices.Tabular.dll"

$server = New-Object Microsoft.AnalysisServices.Tabular.Server
$server.Connect("Data Source=localhost:$port")
$db = $server.Databases[0]
$model = $db.Model
```

Always `$server.Disconnect()` when done. Only `$model.SaveChanges()` persists modifications; without it edits are discarded. `Ctrl+Z` in PBI Desktop cannot undo TOM saves.

### Executing DAX via ADOMD.NET

```powershell
Add-Type -Path "$env:TEMP\tom_nuget\Microsoft.AnalysisServices.AdomdClient.retail.amd64\lib\net45\Microsoft.AnalysisServices.AdomdClient.dll"
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$conn.ConnectionString = "Data Source=localhost:$port"
$conn.Open()

$cmd = $conn.CreateCommand()
$cmd.CommandText = "EVALUATE SUMMARIZECOLUMNS('Date'[Year], ""@Total"", [Total Sales])"
$reader = $cmd.ExecuteReader()
while ($reader.Read()) {
	for ($i = 0; $i -lt $reader.FieldCount; $i++) {
		"$($reader.GetName($i)) = $($reader.GetValue($i))"
	}
}
$reader.Close(); $conn.Close()
```

**DAX rules over ADOMD:**

- Fully-qualify columns: `'Sales'[Amount]`, never `[Amount]`
- Measures unqualified: `[Total Revenue]`
- String literals: double quotes, escape `""` inside PowerShell here-strings
- Prefer `SUMMARIZECOLUMNS` over summarize; prefer `DIVIDE`; never `IFERROR`
- Reader returns fully-qualified column names. Access by index via `GetName($i)` / `GetValue($i)` — short-name indexing silently returns blanks
- Use `ExecuteNonQuery()` for TMSL commands; `ExecuteReader()` only for `EVALUATE`

### Validating DAX Before Saving

```powershell
$testExpr = "SUM('Sales'[Amount]) / COUNTROWS('Sales')"
$cmd.CommandText = "EVALUATE ROW(""@Test"", $testExpr)"
try { $cmd.ExecuteReader().Close(); "VALID" } catch { "INVALID: $($_.Exception.Message)" }
```

For table expressions wrap in `COUNTROWS()`. For RLS filter expressions wrap in `CALCULATETABLE(ROW("@OK",1), )`.

### Refreshing the Model

Three equivalent methods:

| Method | Call | When |
|---|---|---|
| TMSL via TOM | `$server.Execute($tmsl)` | Full flexibility; error rowset |
| TOM API | `$model.Tables["X"].RequestRefresh([...]); $model.SaveChanges()` | Simplest programmatic |
| TMSL via ADOMD | `$cmd.ExecuteNonQuery()` with TMSL | When only ADOMD is loaded |

| Refresh Type | TMSL | Effect |
|---|---|---|
| `full` | `"full"` | Drop, re-query, recompress, recalc DAX |
| `calculate` | `"calculate"` | Recalc DAX only (no source hit) |
| `automatic` | `"automatic"` | Per-partition: full if never processed else calculate |
| `dataOnly` | `"dataOnly"` | Re-query, skip DAX recalc |
| `clearValues` | `"clearValues"` | Drop data without reloading |
| `defragment` | `"defragment"` | Recompress dictionaries |

Refresh dependencies: dimensions first, facts second, then a `calculate` to recompute DAX. Or issue `full` on the database and let the engine order it. Refreshes are synchronous — bump shell timeouts to 300000ms+ for large models.

### VertiPaq / Storage Engine DMVs

All run via ADOMD `$conn`.

| DMV | Use |
|---|---|
| `$SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS` | Per-column `USED_SIZE`, `DICTIONARY_SIZE`, `RECORDS_COUNT`, `SEGMENT_NUMBER` |
| `$SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS` | Per-column `CARDINALITY` (distinct values) |
| `$SYSTEM.DISCOVER_SESSIONS` | Last command + `SESSION_LAST_COMMAND_ELAPSED_TIME_MS` / `CPU_TIME_MS` |
| `$SYSTEM.DISCOVER_COMMANDS` | Active commands (longer `COMMAND_TEXT` than sessions) |
| `$SYSTEM.TMSCHEMA_TABLES` / `_MEASURES` / `_COLUMNS` / `_RELATIONSHIPS` | Model metadata via SQL-like syntax |

Filter out internal tables with `WHERE LEFT(DIMENSION_NAME,1) <> '$'`. High cardinality = large dictionary = memory hotspot.

```sql
SELECT DIMENSION_NAME, SUM(USED_SIZE + DICTIONARY_SIZE) AS TotalBytes, SUM(RECORDS_COUNT) AS Rows
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
WHERE LEFT(DIMENSION_NAME,1) <> '$'
GROUP BY DIMENSION_NAME
ORDER BY TotalBytes DESC
```

### Query Listener (Capturing Visual DAX)

Poll `$SYSTEM.DISCOVER_SESSIONS` once per second. When `SESSION_LAST_COMMAND` changes and starts with `DEFINE|EVALUATE|VAR `, record it. Timings come from `SESSION_LAST_COMMAND_ELAPSED_TIME_MS` and `SESSION_CPU_TIME_MS`.

Ignore internal traffic: ``, ``, ``, `MDSCHEMA_*`, `SELECT * FROM $SYSTEM.*`.

Visual query anatomy:

| Pattern | Meaning |
|---|---|
| `SUMMARIZECOLUMNS` | Main group-by + measures |
| `__DS0FilterTable` | Slicers/page filters |
| `TOPN` wrapping SUMMARIZECOLUMNS | Visual has Top N filter |
| `IGNORE(...)` | Column in group-by but not filter |
| `__ValueFilterDM1` | Measure-based visual filter |
| `CALCULATE(...)` around a measure | Visual-level override |

Limits: `SESSION_LAST_COMMAND` is truncated for very large queries; intermediate queries between polls are missed (use 200-500ms interval for busy reports); thin-report queries are invisible.

### EVALUATEANDLOG Debugging

`EVALUATEANDLOG(, [], [])` wraps a DAX expression, returns it unchanged, and emits intermediate results as a JSON trace event (`DAXEvaluationLog`, event class ID 135). PBI Desktop only; passthrough in Service / SSAS / Azure AS.

**Setup via TOM Trace API:** dot-source [scripts/setup-evaluateandlog-trace.ps1](scripts/setup-evaluateandlog-trace.ps1) — assumes a connected `$server` (see "Discovering Running PBID Instances" above) and leaves `$trace`, `$evalEvents`, and `$job` in scope for the caller. The script body is the synchronized-ArrayList pattern; the rationale, cache-clear requirement, and FE/SE timing quirks are below.

**Critical:** `Register-ObjectEvent -Action` runs in a separate runspace; `$global:` does not cross. Use a synchronized `ArrayList` via `-MessageData`.

**Always clear the VertiPaq cache** before each debug query; cached results skip EVALUATEANDLOG:

```powershell
$server.Execute('{ "clearCache": { "object": { "database": "' + $db.Name + '" } } }') | Out-Null
```

Events arrive **asynchronously**. Use a persistent trace across queries, record `$evalEvents.Count` before each call, and `Start-Sleep -Seconds 2` before reading. Some calls emit no event due to engine optimizations. `COUNTROWS(EVALUATEANDLOG(table))` is a known broken pattern.

**JSON payload fields:**

| Field | Meaning |
|---|---|
| `expression` | Wrapped expression text |
| `label` | Optional label argument |
| `inputs` | Columns affecting evaluation context |
| `data[].input` / `.output` | Per-context input/output pairs |
| `data[].rowCount` | True row count (not truncated by MaxRows) |

Cleanup: `$trace.Stop(); Unregister-Event $job.Name; $trace.Drop()`.

### Performance Profiling (FE vs SE Split)

Equivalent to DAX Studio's Server Timings. Subscribe to `QueryEnd`, `VertiPaqSEQueryEnd`, `VertiPaqSEQueryCacheMatch`.

| Event | Use | Columns |
|---|---|---|
| `QueryEnd` | Total query time | TextData, Duration, CpuTime, EventClass |
| `VertiPaqSEQueryEnd` | Per-scan SE duration | TextData, Duration, CpuTime, EventClass |
| `VertiPaqSEQueryCacheMatch` | Cache hits | **TextData + EventClass only** — adding Duration/CpuTime crashes `$trace.Update()` |

Formula Engine time = `QueryEnd.Duration - Σ VertiPaqSEQueryEnd.Duration`.

Take 6-12 samples, compare **medians** not means, discard the first cold-cache run as warm-up. If ranges overlap significantly, the delta is noise.

### DAXLib Package Patterns

daxlib.org is a registry of DAX UDF (user-defined function) packages. Requires compatibility level **1702+** — installing upgrades CL automatically and is **irreversible**; confirm with user before running on models below 1702.

| Concept | Notes |
|---|---|
| Function format | `function 'PackageId.FunctionName' = ( p1: STRING, p2: NUMERIC VAL, e: SCALAR EXPR ) => ...` |
| Parameter modes | `VAL` (eager, pre-evaluated); `EXPR` (lazy, evaluated in callee context). `EXPR` required for measure refs |
| Parameter types | `STRING`, `INT64`, `NUMERIC VAL`, `SCALAR EXPR`, `ANYVAL`, `ANYREF EXPR`, `COLUMN` |
| Tracking annotations | `DAXLIB_PackageId`, `DAXLIB_PackageVersion` on every function |
| Call syntax | `[PackageId.FunctionName](arg1, arg2, ...)` — bracket notation like a measure |
| Package tracking | Scan `$model.UserDefinedFunctions` by annotation to list/update/remove |

**CLI** (`daxlib`): `search | info | versions | functions | download` (standalone); `add | update | remove | installed` (require `--port ` and .NET 8 SDK). Updates replace all functions matched by `DAXLIB_PackageId` annotation; user-created functions are untouched.

SVG measures from `DaxLib.SVG` return `data:image/svg+xml;utf8,...`. Set the column's data category to **"Image URL"** so Power BI renders inline in Table/Matrix/Card/Multi-row Card.

### Calendar Column Groups (Compatibility Level 1604+)

Declarative date hierarchy + time intelligence mapping. A `Calendar` lives on a date table and contains one or more `CalendarColumnGroup` entries, each mapping a column to a `TimeUnit` enum. With calendars defined, `DATESYTD` / `DATESMTD` / `SAMEPERIODLASTYEAR` / `DATEADD` automatically adapt to the declared hierarchy.

| Time Unit | Purpose | Complete/Partial |
|---|---|---|
| `Year` | Complete year | complete |
| `Quarter` | Includes year context (`Q3 2024`) | complete |
| `QuarterOfYear` | Position 1-4 | partial |
| `Month` | Includes year (`January 2024`) | complete |
| `MonthOfYear` | Name/number without year | partial |
| `Week` | Includes year (`2024-W49`) | complete |
| `WeekOfYear` | Week number only | partial |
| `Date` | Specific date | complete |
| `DayOfMonth` / `DayOfWeek` / `DayOfYear` | Day positions | partial |

**Rules:**

- Calendar names must be unique across the entire **model**, not just the table
- Each calendar draws columns from one host table only
- Never repeat a time unit within the same calendar
- A column mapped to a unit in one calendar must map to the same unit in every other calendar it participates in
- Each column group has one **primary** column (sort key) + optional **associated** columns (display labels). If `Year Month` is sorted by `Year Month Number`, the Number column is primary
- Only **one** `TimeRelatedGroup` per calendar — combine `IsWeekend`, `Season`, `RelativeMonth`, etc. into it
- Week-based (ISO / 4-4-5) calendars: always pair ISO weeks with ISO year; map `Period` to the `Month` time unit

```powershell
$cal = New-Object Microsoft.AnalysisServices.Tabular.Calendar
$cal.Name = "Gregorian Calendar"
$g = New-Object Microsoft.AnalysisServices.Tabular.CalendarColumnGroup
$g.TimeUnit = [Microsoft.AnalysisServices.Tabular.TimeUnit]::Month
$g.PrimaryColumn = $dateTable.Columns["Year Month Number"]
$g.AssociatedColumns.Add($dateTable.Columns["Year Month"])
$cal.CalendarColumnGroups.Add($g)
$dateTable.Calendars.Add($cal)
$model.SaveChanges()
```

### Transactions and Rollback

`SaveChanges()` is a single atomic commit — any validation failure rolls back the whole batch. For pre-commit inspection, use `$model.UndoLocalChanges()` to discard pending modifications since the last save. There is no explicit begin/commit on the local AS proxy.

### Finding the Source File Path

TOM does not expose `.pbix`/`.pbip` path. Most reliable source is PBI Desktop's `User.zip` → `Settings.xml` → `FileHistory` entries (ordered by `lastAccessedDate`). The first entry is usually (but not guaranteed to be) the currently-open file. Confirm with user when ambiguous.

Fallbacks: non-Store install exposes path in `Get-Process PBIDesktop | Select MainWindowTitle`. `Win32_Process` `msmdsrv.exe` command line reveals the workspace path but **not** the source file.

### Reference

- Microsoft Learn: [Tabular Object Model (TOM) — introduction](https://learn.microsoft.com/analysis-services/tom/introduction-to-the-tabular-object-model-tom-in-analysis-services-amo)
- Microsoft Learn: [Install, distribute, and reference TOM (assembly split, NuGet, dependencies)](https://learn.microsoft.com/analysis-services/tom/install-distribute-and-reference-the-tabular-object-model)
- Microsoft Learn: [External tools in Power BI Desktop](https://learn.microsoft.com/power-bi/transform-model/desktop-external-tools)
- Comprehensive MS Learn link bundle (TOM/AMO/ADOMD APIs / TMSL refresh / DMVs / trace events / external-tools / compatibility levels / remote XMLA / DAXLib): [references/REFERENCE.md](references/REFERENCE.md)

### Gotchas

| Issue | Cause | Fix |
|---|---|---|
| TOM connect fails | Thin report connected to remote model; Dire

…

## Source & license

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

- **Author:** [wardawgmalvicious](https://github.com/wardawgmalvicious)
- **Source:** [wardawgmalvicious/claude-config](https://github.com/wardawgmalvicious/claude-config)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-wardawgmalvicious-claude-config-pbid-tom-live
- Seller: https://agentstack.voostack.com/s/wardawgmalvicious
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.