pbid-tom-live

name

pbid-tom-live

description

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, ExecuteReader for EVALUATE, DAX validation pre-save, TMSL refresh types (full / calculate / automatic / dataOnly / clearValues / defragment), VertiPaq DMVs ($SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS, TMSCHEMA_*), query listener via DISCOVER_SESSIONS, EVALUATEANDLOG trace events (DAXEvaluationLog), FE-vs-SE Server Timings profiling, DAXLib UDF packages (CL 1702+), Calendar Column Groups (CL 1604+). Invoke when user mentions TOM, ADOMD, msmdsrv, VertiPaq, DMV, EVALUATEANDLOG, Server Timings, daxlib.

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 is a preview IPC surface hosted inside the Desktop process — a named pipe pbi-desktop-bridge-<processId> 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:<PORT> via Microsoft.AnalysisServices.Tabular.Server.Connect()
ADOMD DAX	Data Source=localhost:<PORT> on AdomdConnection

$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

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

$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), <filter>).

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.

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: <Subscribe>, <ImageSave>, <Batch><Discover>, 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(<Value>, [<Label>], [<MaxRows>]) 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 — 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:

$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 <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