Evolusys Blog

Categories Arrow

About blog

The headline and subheader tells us what you're offering, and the form header closes the deal. Over here you can explain why your offer is so great it's worth filling out a form for.

Subscribe to Email Updates

Posts by Tag

see all

Recent Posts

Tags: Analysis Services, Power BI

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.

Key advantages

  • 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.

Prerequisite:

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

Connection Steps:

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

Prerequisite:

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.

Configuration: Development 

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

Overview

The ALM Toolkit is used to track the differences in measure calculations and dimensions between source and target environments.

Key advantages:

  • 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.

Connection steps:

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:

Click OK.

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.

Conclusion

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.

ADD SUBHEADING

Recent Blog Posts