Picture this: your organization has a sprawling Power BI environment with dozens of premium datasets, and your data engineering team needs to automate schema migrations between environments without touching the Power BI UI. Your finance team wants to use Excel's mature pivot table tooling to query live semantic models rather than exporting stale CSV files. And your DBA wants to run diagnostic queries against your datasets at 3 AM when nobody's looking. With the standard Power BI REST API, you'd be writing workarounds for each of these scenarios separately, fighting the platform more than leveraging it.
The XMLA endpoint changes all of this. It exposes your Power BI datasets through the Analysis Services protocol — the same industry-standard, battle-tested protocol that SQL Server Analysis Services has used for over two decades. When your dataset becomes reachable via XMLA, it stops being a blob managed exclusively by Power BI's GUI and becomes a first-class Analysis Services database that every compatible tool in the ecosystem can read, query, and write to. Tabular Editor, SQL Server Management Studio, DAX Studio, Azure Analysis Services migration scripts, custom Python clients — they all work against XMLA endpoints without modification.
By the end of this lesson, you will have genuine, practical competence in deploying and working with XMLA endpoints in production environments. You'll understand the protocol well enough to debug connection failures, architect secure multi-tool pipelines, and make informed decisions about when XMLA is the right tool and when it isn't.
What you'll learn:
This lesson assumes you are already comfortable with the following:
You do not need to be an Analysis Services veteran. The concepts will be introduced where they're needed.
Before touching a single configuration screen, let's get the mental model right, because most of the confusion around XMLA endpoints stems from people treating them as a vague "advanced feature" rather than understanding what the protocol actually does.
XMLA — XML for Analysis — is a SOAP-based protocol standardized in 2000 by an industry consortium. Every major OLAP vendor was involved. The idea was simple and powerful: create a transport-agnostic protocol for querying and managing analytical databases so that client tools didn't need vendor-specific drivers for every server they wanted to talk to. Two operations form the foundation of the protocol: Discover (retrieve metadata about the server, databases, cubes, and objects) and Execute (send a command or query and get results back).
When Microsoft built Analysis Services, they adopted XMLA as the native protocol and layered their own command language on top of it. In the Tabular era, that command language became TMSL — Tabular Model Scripting Language — a JSON dialect for describing and manipulating Tabular model objects. When you use SSMS to "Script Database as CREATE to a file," you're generating TMSL. When Tabular Editor saves a model, it writes TMSL. When the Power BI deployment pipeline applies changes, it's TMSL under the hood.
When Microsoft introduced XMLA endpoints for Power BI Premium, they were essentially surfacing the Analysis Services engine that already powers Power BI datasets. Power BI has always used a variant of the Tabular engine internally — the XMLA endpoint just exposes that engine through the standard protocol rather than hiding it behind proprietary REST APIs.
This matters architecturally because it means the XMLA endpoint isn't an adapter or a translation layer. It's direct access to the engine. Queries you run via XMLA are as performant as queries Power BI's own rendering engine would run. DDL operations you execute via TMSL are the same operations the Power BI service runs internally. There's no penalty for using the endpoint, and no information is hidden from you.
The endpoint URL for a Power BI workspace follows this format:
powerbi://api.powerbi.com/v1.0/myorg/[WorkspaceName]
This is an Analysis Services connection string prefix. Tools that know how to connect to Azure Analysis Services work against this string with minimal reconfiguration — you're pointing at the same engine type, just hosted differently.
XMLA access is controlled at two levels: the capacity level (where you enable it at all) and the workspace level (where you control read vs. read/write). Getting both right before you start is critical.
Navigate to the Power BI Admin Portal. In the left navigation, select "Capacity settings," then choose your Premium capacity. Within the capacity settings, find the "Power BI workloads" section and locate the XMLA Endpoint setting.
You have three options here: Off, Read Only, and Read Write. The choice you make here sets the ceiling for every workspace assigned to this capacity. You cannot enable write access for a specific workspace if the capacity is set to Read Only. Think of the capacity setting as the maximum permission level, and the workspace setting as the per-workspace policy within that ceiling.
For a production environment, the most common pattern is to set the capacity to Read Write but explicitly configure individual workspaces based on their role in your deployment pipeline. Your development workspace gets read/write for data engineers. Your production workspace gets read-only for general tooling, with write access restricted to service principals used by your CI/CD pipeline.
Warning: Setting a capacity to Read Write doesn't mean everyone can write to every dataset. Standard workspace role permissions still apply. But it does open the door to write operations, so you want to ensure your workspace-level role assignments are tight before enabling it in production.
If you're using PPU rather than a dedicated capacity, XMLA is available but scoped differently. Each PPU user's workspace is backed by their individual capacity. The admin portal configuration for PPU works through the tenant-level settings under "Premium Per User" in the Power BI Admin Portal. The key difference is that only other PPU users can connect via XMLA to PPU-backed workspaces — users with standard Pro licenses will get authentication errors even if they have workspace access. This catches teams off-guard frequently when they're testing XMLA in a PPU environment and then wondering why some users can't connect.
Once the capacity allows XMLA, navigate to your workspace settings. Click the three-dot menu on the workspace in the Power BI service, select "Workspace settings," then go to "Premium." Here you'll find the XMLA Endpoint setting for that specific workspace. You can set it to Off, Read Only, or Read Write — but again, only within the ceiling set by the capacity.
For the workspace-level setting to actually restrict anything meaningfully, you need to pair it with appropriate workspace roles. Workspace Members and Admins can always perform read operations via XMLA if it's enabled. Write operations via XMLA require at minimum the Member role, and some operations (like creating new datasets) require Admin or Contributor access. The exact operations permitted by each role are worth reviewing in Microsoft's documentation for your specific version, as this has changed across service updates.
SSMS is the reference implementation for XMLA connectivity. If it works in SSMS, the protocol is configured correctly and you can troubleshoot other tools from a known-good baseline.
Open SSMS (version 18.0 or later — earlier versions don't support the Power BI endpoint format correctly). In the Connect to Server dialog, set the Server Type to "Analysis Services." For the Server Name, enter the workspace XMLA endpoint URL:
powerbi://api.powerbi.com/v1.0/myorg/FinanceAnalytics
Replace FinanceAnalytics with your actual workspace name. If your workspace name contains spaces, encode them as %20 or wrap the name in quotes — SSMS handles both formats.
For Authentication, select "Azure Active Directory — Universal with MFA." This is the right choice for interactive sessions where you're connecting as your own user identity. Enter your organizational email address in the User Name field, then click Connect. You'll be redirected through your tenant's MFA flow before the connection completes.
Once connected, expand the server node in Object Explorer. You'll see databases listed — each database corresponds to a dataset in your workspace. Expand a database and you'll find the familiar Analysis Services tree: Tables (which map to Power BI tables), Measures, Roles, Data Sources, and Partitions. Everything you see here is the live object model of your Power BI dataset.
Right-click on a database and select "Script Database as > CREATE To > New Query Editor Window." SSMS generates the complete TMSL definition of your dataset. This is your dataset as the engine understands it — raw and complete, including partition definitions, data source connection strings (redacted for credentials), all measure expressions, relationship definitions, and role memberships. Save this file. You've just extracted a fully reproducible definition of your dataset in a format you can version control, diff, and deploy.
Tip: The TMSL output from SSMS is exactly what you'd feed back to the endpoint to recreate the dataset from scratch. This is the foundation of scripted dataset deployment.
With a database selected, open a new MDX or DAX query window. For Tabular models, you'll almost always want DAX. SSMS supports both, but MDX is a legacy query language for multidimensional models. For a Tabular dataset, use DAX queries with the EVALUATE keyword:
-- Query the Sales dataset's fact table with a measure
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Year],
'Geography'[Region],
"Total Revenue", [Total Revenue],
"Units Sold", [Units Sold],
"Avg Deal Size", DIVIDE([Total Revenue], [Units Sold])
)
ORDER BY 'Date'[Year], [Total Revenue] DESC
This runs server-side and returns results in the Results pane. You're executing this against the live in-memory model, the same engine your Power BI reports hit. Performance profiling, query plan inspection, and result validation all happen here before you ever touch a report file.
Understanding TMSL is what separates XMLA power users from people who just connected SSMS once. TMSL is the language you'll use to automate everything from partition refreshes to schema migrations.
The most common operation you'll automate via TMSL is data refresh. The REST API can trigger a refresh, but TMSL gives you fine-grained control over exactly which objects get refreshed and how.
{
"refresh": {
"type": "full",
"objects": [
{
"database": "SalesAnalytics",
"table": "FactSales"
},
{
"database": "SalesAnalytics",
"table": "DimDate"
}
]
}
}
Compare this to a partition-level refresh, which lets you refresh only specific date partitions rather than the entire fact table:
{
"refresh": {
"type": "full",
"objects": [
{
"database": "SalesAnalytics",
"table": "FactSales",
"partition": "FactSales-2024-Q4"
}
]
}
}
This is a massive performance improvement for large datasets. If your fact table has 5 years of data partitioned by quarter, and only Q4 2024 changed, you refresh one partition instead of the entire table. With VertiPaq, a full refresh of a large table involves decompressing all column dictionaries, re-encoding, and rebuilding all indexes. A partition refresh scopes that work to just the affected partition.
The type parameter gives you additional control. Beyond full, you have:
clearValues — removes data from the partition without dropping it, useful when you need to reset state before re-loadingcalculate — recalculates derived columns and measures without re-loading source data, used after schema changes that don't affect base datadataOnly — refreshes data but skips hierarchy recalculationautomatic — the engine decides what needs refreshing based on what's changed (similar to incremental refresh logic)For datasets with significant data volumes, partition management is where XMLA delivers its most tangible value. Let's walk through a realistic scenario: you have a FactSales table partitioned by month, and you need to add a new partition for January 2025 as part of your month-end processing.
First, inspect the current partition definitions:
{
"create": {
"parentObject": {
"database": "SalesAnalytics",
"table": "FactSales"
},
"partition": {
"name": "FactSales-2025-01",
"source": {
"type": "m",
"expression": [
"let",
" Source = Sql.Database(\"prod-sql.database.windows.net\", \"SalesDB\"),",
" FilteredRows = Table.SelectRows(",
" Source{[Schema=\"dbo\",Item=\"FactSales\"]}[Data],",
" each [OrderDate] >= #date(2025, 1, 1) and [OrderDate] < #date(2025, 2, 1)",
" )",
"in",
" FilteredRows"
]
}
}
}
}
After creating the partition, you refresh it:
{
"refresh": {
"type": "full",
"objects": [
{
"database": "SalesAnalytics",
"table": "FactSales",
"partition": "FactSales-2025-01"
}
]
}
}
This sequence — create partition, refresh partition — is the building block of incremental data pipelines managed entirely outside Power BI's built-in incremental refresh feature. You get complete control over partition boundaries, source queries, and refresh scheduling through external orchestration tools like Azure Data Factory or Apache Airflow.
Adding a calculated column or measure to a deployed dataset without re-publishing the PBIX file is one of those operations that feels like magic the first time you do it via XMLA:
{
"createOrReplace": {
"object": {
"database": "SalesAnalytics",
"table": "FactSales",
"measure": "Gross Margin %"
},
"measure": {
"name": "Gross Margin %",
"expression": "DIVIDE([Gross Profit], [Total Revenue])",
"formatString": "0.00%",
"description": "Gross margin as a percentage of revenue"
}
}
}
The createOrReplace command is idempotent — if the measure already exists, it updates it; if it doesn't exist, it creates it. This is what you want for deployment scripts because you're not managing state manually.
Warning: Schema changes to published datasets that Power BI reports depend on can break those reports if you rename or remove columns. Adding new measures is safe. Renaming existing measures or columns requires updating all dependent report files as well. Plan schema changes in coordination with your report development team.
Tabular Editor is the professional's tool for Power BI and Analysis Services model development. Tabular Editor 2 is free and open source. Tabular Editor 3 is commercial and adds advanced features like DAX debugger and query benchmarking. Both connect to XMLA endpoints identically.
Open Tabular Editor. In the File menu, select "Open > From DB." In the connection dialog, enter the XMLA endpoint URL for your workspace. Select "Azure Active Directory" or "Azure Active Directory Password" depending on whether you're using MFA. Enter your credentials and connect. After a moment, you'll be prompted to select which database (dataset) to open.
Tabular Editor loads the entire model definition into its editor. You can now use the Best Practice Analyzer (BPA) to scan your model against a ruleset — Microsoft's published rules, community rules, or your own organization's standards. Running BPA against a production dataset via XMLA is one of the most valuable governance activities you can automate. You get a complete list of violations without touching a PBIX file or needing the data loaded locally.
To run a BPA scan programmatically using Tabular Editor's command-line interface:
TabularEditor.exe "powerbi://api.powerbi.com/v1.0/myorg/FinanceAnalytics" \
"FinancialReporting" \
-BPA "C:\Rules\BestPracticeRules.json" \
-BPAOutput "C:\Reports\BPA_Results.csv"
This connects to the live dataset, runs BPA, and writes results to a CSV — all scriptable, all automatable.
Tabular Editor also supports a C# scripting interface that runs directly against the model object model. This is extremely powerful for bulk operations:
// Script to add a description to all measures missing one
foreach(var measure in Model.AllMeasures.Where(m => string.IsNullOrEmpty(m.Description)))
{
measure.Description = "No description provided — please document this measure.";
}
Running this script via XMLA against a production dataset updates every measure definition in place. The change is immediate and persistent.
DAX Studio is purpose-built for DAX query development, query performance analysis, and dataset diagnostics. Connect to a Power BI XMLA endpoint by clicking the "PBI/SSAS Tabular" button in DAX Studio's connection dialog and entering your endpoint URL.
Once connected, DAX Studio gives you access to the VertiPaq Analyzer, which is invaluable for understanding dataset memory consumption:
In DAX Studio, go to Advanced > View Metrics. The VertiPaq Analyzer loads statistics about every table and column in your dataset: cardinality, segment count, dictionary size, data size, and total size. This is how you find bloated columns that are consuming disproportionate memory, or identify relationships with poor cardinality that cause slow query performance.
For query performance investigation, DAX Studio's Server Timings feature shows you how much time each phase of query execution took: formula engine time, storage engine time, and the number of storage engine cache and non-cache calls. Here's how to use it:
Enable Server Timings and Query Plan in the Output ribbon. Then write and run your DAX query:
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Customer'[Segment],
'Date'[FiscalYear],
"Revenue", [Total Revenue],
"Prev Year Revenue", CALCULATE([Total Revenue], DATEADD('Date'[Date], -1, YEAR))
),
'Customer'[Segment] IN {"Enterprise", "Mid-Market"}
)
DAX Studio shows you the complete query plan, annotated with timing information. You can see exactly which storage engine queries were generated, whether they hit the cache, and how long each took. This level of insight is impossible from the Power BI service UI — XMLA connectivity is what makes it possible.
For programmatic access from Python — building data pipelines, automated testing frameworks, or custom governance tooling — you can use the python-adomd package, which wraps the Analysis Services ADOMD.NET library.
First, install the package. This requires .NET to be available on your system:
pip install python-adomd
Connecting and querying:
import adomd
connection_string = (
"Provider=MSOLAP;"
"Data Source=powerbi://api.powerbi.com/v1.0/myorg/FinanceAnalytics;"
"Initial Catalog=FinancialReporting;"
"User ID=app@yourorg.com;"
"Password=your_service_principal_secret;"
"Authentication=ActiveDirectoryPassword"
)
conn = adomd.connect(connection_string)
query = """
EVALUATE
SUMMARIZECOLUMNS(
'Department'[Name],
'BudgetCycle'[Year],
"Actual Spend", [Total Actual Spend],
"Budget", [Total Budget],
"Variance", [Budget Variance],
"Variance %", [Budget Variance %]
)
"""
cursor = conn.cursor()
cursor.execute(query)
results = cursor.fetchall()
for row in results:
print(row)
conn.close()
For service principal authentication (which is what you'll use in automated pipelines), the connection string changes slightly:
connection_string = (
"Provider=MSOLAP;"
"Data Source=powerbi://api.powerbi.com/v1.0/myorg/FinanceAnalytics;"
"Initial Catalog=FinancialReporting;"
"User ID=app:your_client_id@your_tenant_id;"
"Password=your_client_secret"
)
The app:client_id@tenant_id format is the Analysis Services convention for service principal authentication. The service principal must be added to the workspace with at least the Member role, and the Power BI Admin Portal must have "Allow service principals to use Power BI APIs" enabled.
Tip: For production Python integrations, use Azure Key Vault to retrieve the client secret rather than hardcoding it. The
azure-keyvault-secretsandazure-identitypackages make this straightforward.
This is where everything comes together. A well-designed XMLA-powered CI/CD pipeline lets your team develop model changes in feature branches, validate them against a test environment, and promote them to production with zero manual steps in the Power BI UI.
The pattern has three environments mapped to three workspaces:
FinanceAnalytics-Dev — where data engineers make changes freelyFinanceAnalytics-Test — deployed automatically on merge to main branchFinanceAnalytics-Prod — deployed on release tag, with manual approval gateEach workspace has the XMLA endpoint enabled. Dev and Test have read/write. Prod has read/write but scoped to only the deployment service principal.
The pipeline starts by serializing the model to a version-controlled format. Tabular Editor's CLI supports serializing a connected model to a folder structure:
TabularEditor.exe \
"powerbi://api.powerbi.com/v1.0/myorg/FinanceAnalytics-Dev" \
"FinancialReporting" \
-FOLDER "C:\src\FinancialReporting\" \
-SERIALIZE
This generates a folder where each table, measure, relationship, and data source is a separate JSON file. This folder structure diffs cleanly in Git — you can see exactly which measure expressions changed, which relationships were modified, and who made the change. A PBIX file, by contrast, is a binary format that diffs as noise.
The folder structure looks like this:
FinancialReporting/
├── database.json
├── tables/
│ ├── FactSales.json
│ ├── DimDate.json
│ └── DimCustomer.json
├── measures/
│ ├── Total Revenue.json
│ └── Gross Margin %.json
├── relationships/
│ └── FactSales_DimDate.json
└── roles/
└── Finance Team.json
Your CI pipeline (Azure DevOps, GitHub Actions, etc.) runs Tabular Editor to deploy the model definition from the serialized folder to the target workspace:
# GitHub Actions workflow step for deploying to Test
- name: Deploy Financial Reporting Model to Test
run: |
TabularEditor.exe \
"powerbi://api.powerbi.com/v1.0/myorg/FinanceAnalytics-Test" \
-FOLDER "./FinancialReporting/" \
-DEPLOY "${{ secrets.SERVICE_PRINCIPAL_APP_ID }}" \
"${{ secrets.SERVICE_PRINCIPAL_SECRET }}" \
"${{ secrets.TENANT_ID }}" \
-DEPLOYROLES \
-DEPLOYROLEMEMBERSONLY
env:
TABULAR_EDITOR_SERVICE_PRINCIPAL: true
The -DEPLOYROLES flag deploys role definitions, and -DEPLOYROLEMEMBERSONLY is an important subtlety: it updates role membership without touching role definition permissions. This matters because your Test workspace might have different role members than Prod (test users vs. production users), but the role definitions themselves should be identical. Separating these concerns prevents your CI pipeline from accidentally granting production users access to the test environment.
Before deploying to production, run automated validation:
# Run Best Practice Analyzer — fail the pipeline if critical rules violated
TabularEditor.exe \
"./FinancialReporting/" \
-BPA "C:\Rules\OrganizationBPARules.json" \
-BPAErrorOnWarnings \
-EXITCODES
The -EXITCODES flag makes Tabular Editor return a non-zero exit code when BPA violations are found, which your CI system treats as a build failure. You can also run custom C# scripts as part of validation:
// Validation script: Ensure all measures have descriptions
var measuresWithoutDescription = Model.AllMeasures
.Where(m => string.IsNullOrEmpty(m.Description))
.ToList();
if(measuresWithoutDescription.Any())
{
var names = string.Join(", ", measuresWithoutDescription.Select(m => m.Name));
Error($"The following measures are missing descriptions: {names}");
}
If the script calls Error(), the CLI exits with a failure code. Your pipeline catches this and blocks the deployment until the model definition meets your quality standards.
One persistent challenge in XMLA-based deployments is data source connection strings. Your Dev model points to a development SQL Server. Your Prod model points to the production SQL Server. The model definition in Git needs to handle this without storing environment-specific values in version control.
The standard approach is to use parameter substitution in your pipeline. Tabular Editor supports custom C# scripts that can modify the model before deployment:
// Environment substitution script — run before deployment
var dataSource = Model.DataSources.FirstOrDefault(ds => ds.Name == "SalesDB");
if(dataSource != null)
{
var connectionString = dataSource.ConnectionString;
connectionString = connectionString.Replace(
"DEV-SQL-SERVER",
Environment.GetEnvironmentVariable("TARGET_SQL_SERVER")
);
dataSource.ConnectionString = connectionString;
}
The actual credentials (username and password) are managed separately through Power BI's REST API gateway credentials API — you never store credentials in TMSL definitions, and XMLA operations don't expose them.
Enabling XMLA read/write is a significant security decision. The endpoint exposes capabilities that the Power BI UI deliberately restricts, and you need to be deliberate about who and what has access.
Interactive tools like SSMS and DAX Studio work with user identities. Automated pipelines should always use service principals. There are several reasons for this:
User credentials expire, rotate, and are tied to individuals who leave organizations. A pipeline dependent on a specific user's credentials will fail the day that person's account is disabled. Service principals are application identities that don't expire automatically and are managed independently of individual employees.
Service principals also give you a clean audit trail. When you see TMSL operations in your Analysis Services trace logs attributed to SVC_PowerBI_Deploy@yourorg.com, you know it was an automated pipeline. When you see your own UPN, you know it was an interactive session. Mixing the two makes audit analysis much harder.
To create a service principal for XMLA access:
An important architectural point: XMLA read access bypasses row-level security (RLS) for users with Workspace Admin or Member roles. This is by design — these are privileged users who can already see model definitions that contain RLS rules. But it means that a data engineer with workspace Member access querying via SSMS sees all rows in every table, regardless of what RLS rules are defined.
For datasets where RLS is a compliance requirement (HIPAA, GDPR data segmentation, etc.), ensure that workspace Member role is not granted broadly. Users who should be subject to RLS should interact with the dataset only through Power BI reports, not through direct XMLA connections. Document this distinction explicitly in your organization's data access policies.
All XMLA operations are logged in Azure Analysis Services trace events, which in turn flow into your Power BI activity logs. You can query the audit log programmatically:
# PowerShell: Retrieve XMLA-related activity from Power BI audit log
Connect-MgGraph -Scopes "Reports.Read.All"
$startDate = (Get-Date).AddDays(-7).ToString("yyyy-MM-ddTHH:mm:ssZ")
$endDate = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ssZ")
$activities = Get-PowerBIActivityEvent `
-StartDateTime $startDate `
-EndDateTime $endDate `
-ActivityType "ExportArtifact"
$xmlaOperations = $activities | Where-Object {
$_.Activity -in @("AnalyzeInExcel", "QueryDataset") -or
$_.RequestId -like "*XMLA*"
}
$xmlaOperations | Select-Object UserID, Activity, DatasetName, WorkspaceName, TimeGenerated
For a complete audit picture, connect your Power BI activity logs to Azure Monitor and build queries in Log Analytics that can alert on unusual XMLA write patterns — for example, TMSL create or delete operations against production datasets originating from user identities rather than service principals.
Having direct access to the engine lets you apply optimizations that are impossible through the Power BI GUI.
After a partition refresh, the VertiPaq engine compresses column data. But compression quality depends heavily on column sort order within the partition. You can use XMLA to inspect compression ratios and identify columns that might benefit from a different sort order in the source query:
In DAX Studio with VertiPaq Analyzer loaded, navigate to the Columns tab. Sort by "Dictionary Size" descending. Columns with high dictionary sizes relative to their row count often indicate high cardinality that's resisting compression. If you see a string column with millions of distinct values (like a free-text description column), consider whether it needs to be in the model at all, or whether it should be stored only at an aggregate grain.
Production performance issues are often masked by the VertiPaq cache. Use DAX Studio's "Clear Cache" option before benchmarking to measure true cold cache performance:
-- Benchmark: Cold cache performance of complex DAX measure
EVALUATE
CALCULATETABLE(
TOPN(
100,
SUMMARIZECOLUMNS(
'Customer'[AccountID],
'Date'[Month],
"LTV Score", [Customer LTV Score],
"Churn Risk", [Churn Risk Score],
"Recommended Action", [Recommended Action]
),
[Customer LTV Score],
DESC
)
)
Run this five times with cache cleared each time and record the storage engine time from Server Timings. Consistent cold-cache query times give you a realistic baseline for report load times for users who haven't hit that query recently — which is most users, most of the time.
When refreshing multiple tables that have dependencies (foreign key relationships, calculated columns that reference other tables), the order of processing matters. The Analysis Services engine handles dependencies automatically during a full database refresh, but when you're managing individual partition and table refreshes via TMSL, you own the sequencing.
The correct order for a standard star schema refresh:
calculate command) if any calculated columns depend on cross-table logicA TMSL sequence command handles this:
{
"sequence": {
"operations": [
{
"refresh": {
"type": "full",
"objects": [
{ "database": "SalesAnalytics", "table": "DimDate" },
{ "database": "SalesAnalytics", "table": "DimCustomer" },
{ "database": "SalesAnalytics", "table": "DimProduct" }
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{ "database": "SalesAnalytics", "table": "FactSales", "partition": "FactSales-2025-01" }
]
}
}
]
}
}
The sequence wrapper ensures operations execute in order. Within each operation, the objects listed can process in parallel — so your three dimension tables refresh simultaneously, then the fact partition refreshes after all dimensions are complete.
In this exercise, you'll connect to a Power BI Premium workspace via XMLA, extract the model definition, add a new measure programmatically, and validate the change.
You'll need: SSMS 18+, DAX Studio, a Power BI Premium or PPU workspace with at least one published dataset containing a date table and a numeric measure.
Step 1: Enable XMLA In the Power BI Admin Portal, ensure your capacity has XMLA set to Read Write. In your target workspace settings, set XMLA Endpoint to Read Write.
Step 2: Connect SSMS
Open SSMS, connect to Analysis Services using your workspace endpoint URL (powerbi://api.powerbi.com/v1.0/myorg/YourWorkspace). Use Azure Active Directory Universal with MFA. Expand the server in Object Explorer and confirm your dataset appears as a database.
Step 3: Script the Existing Model
Right-click your dataset database in SSMS, choose "Script Database as > CREATE To > Clipboard." Paste the output into a new file called model_baseline.json. This is your before-state.
Step 4: Add a Measure via TMSL Open a new query window in SSMS with your database selected. Enter and execute the following TMSL, adapting the table name and measure expression to match a table in your actual dataset:
{
"createOrReplace": {
"object": {
"database": "YourDatasetName",
"table": "FactSales",
"measure": "QoQ Revenue Growth"
},
"measure": {
"name": "QoQ Revenue Growth",
"expression": "DIVIDE([Total Revenue] - CALCULATE([Total Revenue], DATEADD('Date'[Date], -1, QUARTER)), CALCULATE([Total Revenue], DATEADD('Date'[Date], -1, QUARTER)))",
"formatString": "+0.00%;-0.00%;0.00%",
"description": "Quarter-over-quarter revenue growth rate. Positive values indicate growth."
}
}
}
Click Execute (F5). If the command succeeds, SSMS returns an empty result set with no error — TMSL commands don't return data, they return success/failure.
Step 5: Validate in DAX Studio Open DAX Studio, connect to the same endpoint, and run:
EVALUATE
{[QoQ Revenue Growth]}
If the measure was created successfully, you'll get a single-row result with the measure value. Test it in a more realistic context:
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Quarter],
"QoQ Growth", [QoQ Revenue Growth]
)
ORDER BY 'Date'[Quarter]
Step 6: Check Server Timings In DAX Studio, enable Server Timings before running the above query. Observe the storage engine vs. formula engine time split. DATEADD-based calculations are typically formula engine intensive — if you see 80%+ formula engine time, the query is a candidate for optimization using alternative DAX patterns like CALCULATE with date filters.
Step 7: Clean Up Remove the measure you created using a TMSL delete command in SSMS:
{
"delete": {
"object": {
"database": "YourDatasetName",
"table": "FactSales",
"measure": "QoQ Revenue Growth"
}
}
}
Nine times out of ten, this is one of three things: the capacity XMLA setting is Off or Read Only when you need write access; the workspace name in your connection string has capitalization or spacing that doesn't exactly match the workspace name in the Power BI service; or you're connecting to a workspace that's on shared capacity rather than Premium.
Check by navigating to the workspace in Power BI service and looking at the workspace settings. The capacity badge should show "Premium" or "Premium Per User." If you see no badge or a "Pro" badge, XMLA is not available.
Make sure "Allow service principals to use Power BI APIs" is enabled in the Power BI Admin Portal under Tenant settings > Developer settings. Even with workspace membership, service principals need this tenant-level setting enabled to authenticate via XMLA. Additionally, if you're using a security group to scope service principal access, confirm that your specific service principal's app registration is in that group.
Cached query results in Power BI's rendering engine can mask changes for a few seconds to minutes. Refresh the report browser tab. For schema changes (new measures, columns), the report may need to be republished from Desktop if it was built with an older model version — the report's field list in Desktop won't auto-update to reflect server-side schema changes.
If you're running parallel partition refreshes that affect the same table, Analysis Services uses table-level locking during certain phases. Parallel refreshes on separate partitions of the same table generally work, but if another process (like a scheduled refresh from Power BI service) is running against the same table simultaneously, you'll see transaction conflicts. Implement retry logic in your automation, and coordinate XMLA-managed refreshes with Power BI service scheduled refresh timing to avoid overlap.
M (Power Query) expressions in TMSL must be serialized as JSON string arrays where each line is a separate array element. The \n line continuation you see in Power BI Desktop doesn't work — you must split the expression into an array. SSMS's scripting function handles this correctly when you script an existing dataset, but if you're hand-authoring TMSL, watch for this pattern:
"expression": [
"let",
" Source = ...,",
" Result = ...",
"in",
" Result"
]
Not:
"expression": "let\n Source = ...\n"
Both formats are technically valid in some TMSL parsers, but the array format is more reliable across tool versions.
If you enable XMLA in a PPU workspace and a user with standard Pro license tries to connect, they'll receive an authentication error even if they're a workspace admin. This is a licensing enforcement, not a permissions error. The error message isn't always clear about this. Ensure that all users who need XMLA access to PPU workspaces have PPU licenses assigned.
You now have a complete picture of the XMLA endpoint ecosystem: what the protocol is and why it works the way it does, how to configure access at the capacity and workspace level, how to connect the full range of professional tools from SSMS to Python, and how to build production-grade CI/CD pipelines that treat Power BI datasets as code-managed artifacts rather than GUI-dependent blobs.
The practical effect of mastering XMLA is that your organization's data models become significantly more governable. You can version control model definitions, automate deployment with quality gates, partition large datasets precisely, and give specialist tools like DAX Studio the access they need to provide real diagnostic value. The distinction between a Power BI environment that's "managed" and one that's "governed" is largely whether XMLA-powered automation and tooling is in place.
Where to go next:
The XMLA endpoint is not a feature you enable once and forget. It's an architectural capability that grows more valuable the more you build on top of it. The teams that invest in understanding it deeply are the ones that stop fighting their Power BI platform and start treating it as a programmable analytical infrastructure.