It’s usually the case that you refresh Power BI datasets/semantic models in the Power BI service. But there are cases when you want to refresh a table or partition individually. You can do that with XMLA endpoint and SSMS.
What is the XMLA endpoint?
To put it simply, it’s a connection endpoint through which you can manage Tabular Models. In the case of Power BI, it’s Power BI datasets.
XMLA stands for “XML for Analysis”. A Microsoft documentation states:
Azure Analysis Services, SQL Server Analysis Services, and Power BI Premium use XML for Analysis (XMLA) protocol for communications between client applications and an Analysis Services instance.
What can you do with XMLA endpoints?
With the XMLA endpoint, you can manage Tabular Models (Power BI datasets, Azure Analysis Services, SQL Server Analysis Services) remotely without opening Power BI desktop app. When I say “manage”, it means:
- Full / partial data refreshes on-demand
- Whatever data model operations you can do in Power BI desktop
Being able to manage Power BI datasets remotely is huge especially when we live by this best practice of separating the report from dataset.
You might’ve experienced Power BI desktop could be slow when data is large. But with the XMLA endpoint, you can do those tabular model management operations quicker because you wouldn’t do them in RAM on your laptop. But instead, you’d utilize the power of Power BI service for that.
On top of that, XMLA endpoints allows us to refresh an individual table or partition in your dataset. That means you’re no longer needing to wait for minutes or hours by refreshing the whole dataset just because there was a data change in one of your small dimension tables. You see how the XMLA endpoint makes tabular model management easier!
Prerequisite for using the XMLA endpoint
- Premium Per User license or Premium Capacity
- Allow XMLA endpoint in the admin portal under tenant settings section
- Turn on the Read and Write permission in the admin portal under Premium Per User or Premium Capacity section
Connect to Power BI Datasets Through XMLA Endpoint
You can connect to Tabular Models through XMLA endpoints with tools like SSMS, DAX Studio, Tabular Editor, and SQL Server Profiler. In this post, I’ll go through these two things:
- Refresh a table and a partition in SSMS
- Add a DAX measure to an existing Power BI dataset
Get the Connection String
Firstly, you’ll need to get a connection string so that SSMS/Tabular Editor can connect to Power BI datasets remotely:
- Go to your workspace and click on “Settings”
- Click on “Premium” and copy the value for “Workspace Connection”
Refresh a table / partition in SSMS (SQL Server Management Studio)
Connect to your workspace
- Open SSMS and paste in the connection string you just copied. Make sure to choose “Analysis Service” as server type on the top.
- After successfully logging in, expand “Databases”. You’ll see a list of datasets available in your workspace.
Partial refresh – refreshing a table with XMLA endpoints
- Expand the dataset. Expand the tables and right click on the table you want to refresh.
- Click on “Process Table”. Make sure the table you want to refresh is checked. Choose “Process Full” from the dropdown options for mode (you’ll see the description of each mode as you choose one in the dropdown). There are two ways to refresh from here. One option is to click on “Script”.
- You’ll see a configuration script populated. Notice the refresh type, table, and dataset are specified.
- Execute the script and wait until it’s finished.
- Once it’s done, go to Power BI service and check refresh history. Notice that the type of refresh is “Via XMLA Endpoint”. This will be the same when you refresh the whole dataset.
- Second option for refreshing is to simply click “OK” instead of generating the script. And wait for it to finish executing. Check in the Power BI service if the refresh has been logged.
Refresh a partition with XMLA endpoints
- Expand a model/dataset whose table is partitioned or incremental refresh is implemented. Right click on the table and click on “Partitions”.
- You’ll see all the partitions for the table. Select the partition(s) you want to refresh. And click on the icon for refresh.
- That opens up a new windows. Make sure you select the partition(s) you want to process. Choose the mode you’d like.
- Just like refreshing a table, execute the script and wait until it’s done.
- Confirm you see that refresh in the refresh history.
Add a DAX measure in Tabular Editor
- Open Tabular Editor and click on the icon. Paste in your connection string and click “OK”. You might be prompted to log into your Microsoft account.
- That’ll open a windows showing a list of datasets available in your workspace. Choose the one you’d like and click “OK”.
- Now you connected to your dataset in the Power BI service. This is no different when you’re connecting to a local power bi model in Tabular Editor! Create a DAX measure and save it with clicking on the icon or Ctrl+S.
- Confirm the measure has been added to your dataset by going to the Power BI service.
Enable large dataset storage format
One pro tip when working with XMLA endpoints is to enable large dataset storage format. By doing that, you’ll get a better performance for write operations executed through XMLA endpoints (Microsoft documentation). You can enable large dataset storage format at either workspace level or at dataset level.
- To enable it at workspace level, you go to your workspace and slick on “Settings”. And choose “Large dataset storage format” for the default storage format under “Premium” tab.
- To enable it at dataset level, click on the dots to expand options and go to dataset settings. Scroll towards the bottom and turn on large dataset storage format.
You can manage Tabular Models remotely without using Power BI desktop through XMLA endpoints. It can give you additional feature of doing partial refreshes such as a table refresh and partition refresh for your model. This definitely gives us more flexibility for collaboration and management of your models.
I didn’t explain this in this post, but you can actually refresh tables/partitions from Tabular Editor. These two articles explain how to do it step-by-step:
And here’s a few other documentations on XMLA endpoints: