Overview of XMLA endpoint R/W for PowerBI Premium
XMLA endpoint read/write for Power BI is a newly announced feature in March 2020 by Microsoft. In this post we will explore the XMLA endpoint functionality, elaborate why and when it should be used, and go through hands-on examples using third-party BI tools to connect with XMLA endpoint.
Bridging the gap between Self-service BI and Enterprise BI
Historically, businesses that use traditional BI tools operate in a highly controlled environment owned and managed by IT teams with centralised large-scale BI deployment for users all over the word. This approach was intended mainly for complex semantic models with high scalability and life cycle management requirements.
A few years ago, a new family of business intelligence products appeared on the market and became more and more attractive: the so-called Self-Service BI tools.
Today, businesses require agile decision-making supported by data. Self-service BI drives action by providing business decision-makers access to the data and tools they need to analyse data specific to their questions. They can then quickly report on that analysis and make decisions to advance the business.
The XMLA endpoint feature comes to help, bridging Enterprise and self-service BI, while drastically improving agility and performance. It allows customers to start projects straight in Power BI so that they still have all the capabilities of Enterprise BI while at the same time taking advantage of Power BI Premium features.
All-inclusive platform: Single, easily accessible location in Power BI Workspace and at the same time using third party tools to perform additional tasks like Power BI dataset management, advanced semantic modelling, debugging, monitoring,
Improve collaboration between business and IT : Business users can harness Power BI models in a corporate infrastructure totally managed by IT team.
What is XMLA endpoint Read/Write ?
A Power BI dataset is based on an Analysis Services model as the back end. XMLA endpoints bring Analysis Services features and capabilities to Power BI Premium.
XMLA endpoint is a protocol that allows Business Intelligence client tools to interact with an Analysis Services instance, by introducing greater flexibility to control, manage, and monitor Power BI datasets in the service.
XMLA endpoints provide access to the Analysis Services engine in the Power BI service. Thanks to these endpoints, the same Enterprise BI tools that connect to Analysis Services for application lifecycle management, governance, complex semantic modelling, debugging, and monitoring will be available with Power BI.
Using XMLA endpoint: Hands-on examples
We often need to use SQL Server management studio for scripting and refreshing data in this section or other tools like Visual Studio to author and publish tabular model projects, sometimes we need also to compare and track differences between two models. In this section we will show you how to achieve this by introducing three different examples of using the XMLA endpoint functionality.
Before getting started, let’s have a look at how to get the endpoint URL from Power BI
Get the XMLA endpoint URL
From Power BI Service, go to a premium capacity allocated workspace, under the Premium Capacity, you will see the XMLA endpoint connection URL:
The syntax is as follows:
powerbi://api.Power bi.com/v1.0/myorg/<workspace name>
Sample 1 - Connect to a Power BI Dataset with SSMS
For scripting and performing refreshes we have SSMS (SQL Server Management Studio) which is one of the third-party BI tools that we can use to connect to an SSAS Tabular model.
Power BI dataset deployed on a Power BI workspace assigned to a premium capacity in Power BI service. To learn more, see XMLA read & write
Run SQL Server Management Studio from your Desktop, choose the server type “Analysis Services” and copy the workspace URL under “Server Name”:
Now from the Connection Properties tab, under “Connect to database”, choose “Browse server”
Select the Dataset you want to use and click “Connect“.
Here is a sample connection created using XMLA endpoint through SSMS:
Thanks to the Read feature of XMLA Read & Write you can for example refresh your Power BI Dataset or even a single table / partition from SSMS.
Sample 2 - Authoring and publishing with Visual Studio
The latest version of Visual Studio.
SQL Server Data Tools: you can add this feature by clicking on the menu “Extension” > Manage Extensions
Then search for the extension “Microsoft Analysis Services Projects”.
Create a new Analysis Services Tabular Project and Choose the compatibility level 1500: The compatibility level specifies the behavior of the Analysis Services engine.
The compatibility level 1500 indicates that the version supported is SQL Server 2019 Azure Analysis Services. For more information about the compatibility level see Compatibility level for tabular models.
In your project properties you can specify the deployment options.
In the server box you can specify your XMLA Endpoint URL that you can get from your development premium workspace
Once your deployment environment is created, choose the environment Development
Note that thanks to the SSAS option Solution configuration we have the possibility to make multi-environment deployments. (DEV, QUAL, PROD)
From the Build menu, select Deploy Solution.
You can check that your Model has successfully been deployed on your development workspace (premium) on Power BI service.
Sample 3 - Deployment with ALM Toolkit
The ALM Toolkit is used to track the differences in measure calculations and dimensions between source and target environments.
- Ability to compare data models between two Power BI reports.
- Allow multiple developers to work on the same data model and reduce the effort required to merge changes.
- Ability to selectively deploy changes to a target environment.
- Create a report detailing changes that can easily be shared with stakeholders.
Open ALM Toolkit, then specify the endpoint URLs that you want to compare, for example enter you DEV workspace as the Source and the PROD workspace as the Target:
On the main screen of the tool you will find the comparison between the DEV and PROD datasets.
Note that you can see different type of actions, such as Update, Skip, Create and Delete.
These actions let to selectively push changes by allowing you to skip, update, create, or delete prior to making an update.
The differences detected by ALM Toolkit between 2 models include differences in relationships, new measures, new columns, new table name, any update on model objects (Example: update on DAX formula). To get updates related to roles you should check the box “Include roles” from the Options menu:
Update the target model directly within the toolkit
You can then update your target model directly within ALM toolkit. However, prior to that, you must validate selections using the Validate button.
This will show you the different updates that will be carried out on your target model:
You can then click the update button to update the target.
XMLA endpoint is probably one of the most eagerly awaited features added to Power BI. Using XMLA endpoints Read & Write capability allows you to use client tools to interact with Power BI datasets in the service. Concrete examples have been presented in this post.
However, as of now XMLA endpoints do not provide the ability to create a tabular project from SSDT directly on a workspace server (Power BI). This functionality would further strengthen and expand the usefulness of XMLA endpoints – we hope this will be available soon.
What are your thoughts about XMLA endpoint? Feel free to contact us for more informations.