What Is the XMLA Endpoint for Power BI and Why Should I Care?

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

The news about XMLA endpoint connectivity to Power BI datasets is now all around the internet after the public preview announcement of that last week. However, I realized that the XMLA endpoint term is still too technical for many Power BI report developers. Many people come to me asking what exactly is XMLA endpoint, and what is the benefits of it? what it can do? or most importantly; as a report developer, why should I care? Well, I’m going to answer all of these questions in this article. Let’s dig in. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Behind the Scene of a Power BI Dataset

To understand what an XMLA endpoint is and what it does for you, first you need to understand the backstage of a Power BI dataset. By backstage I mean what is behind the beautiful Power BI report that you see, Or let’s say, what are things that you don’t see!

A Power BI report is a visualization element connected to an in-memory dataset behind the scene (I am talking about the most common method of using Power BI; Import Data). the in-memory dataset behind the scene, has all the data loaded into the memory, with all calculations, relationships and the logic to connect to the data source. When you open a *.PBIX file, behind the scene, there are two elements; a report (visualization part), and a dataset (data model). This separation is visible in the Power BI Desktop app resource details in the Task Manager;

As you can see in the above screenshot, there is a Microsoft SQL Server Analysis Services task running under the Power BI Desktop list. A Power BI report stores its data into the memory which is managed by the Microsoft SQL Server Analysis Services (Let’s call it as its shorter familiar name SSAS) engine. Even if you run Power BI Desktop on a machine that doesn’t have SSAS installed, you will see this service, because Power BI Desktop automatically installs a version of SSAS with it.

A Power BI dataset is hosted through a SQL Server Analysis Services Engine.

When you publish your *.pbix file to the website, you will also see this separation of the data model (dataset), and the visualization (report);

When you host your report (or publish it) in the Power BI service, the dataset will be managed by a version of SSAS installed on a cloud machine that you don’t see it.

SSAS is More Than What You See

So now that you know a Power BI dataset is an SSAS model behind the scene, the next question is, what is the point? Or what is the benefit of it for me? To understand that, let’s see what SSAS is actually.

SSAS is a modeling engine in Microsoft SQL Server, and it is now more than 20 years old mature technology. SSAS is a server-side modeling technology. It means the model will be hosted in a server, and there are client tools available to work with it. Through many years; the two most common client tools to work with SSAS were and still are: SQL Server Data Tools (SSDT), and SQL Server Management Studio (SSMS).

These tools are not only to build the model in SSAS, but also to manage it. And when it comes to management, It means monitoring, controlling, backup and restore of the model, and many other features. Here is a view of an SSAS database (model) from SSMS.

 

SSAS models can be monitored using a lot of queries and command called Dynamic Management Views (DMV). For example, the command below shows all the users querying or working with this SSAS model;

SQL Server Analysis Services is a server-side technology, that can give you a lot of details about the model, and can be monitored through client tools using many scripts and commands such as Dynamic Management Views.

Can I Access the SSAS Model of My Power BI Dataset?

Well, you learned so far that your Power BI dataset is an SSAS model behind the scene. And you learned that you can control and monitor SSAS models using some client tools. However, when the Power BI dataset is hosted in the Power BI service, how you can connect to that SSAS model? how you can control, manage, or monitor it? Or even more importantly; Why it is import to do it?

If you can connect to the SSAS model of your Power BI dataset, you can connect to the data model directly. You can see how many users are using it. You can see what processes take longer and what slower. You can leverage all those monitoring features to build a better model moving forward.

So far, there was no way to connect to the Power BI dataset hosted in the service except two methods: Power BI Desktop (using Get Data from Power BI dataset), and Excel (using Power BI Publisher for Excel, or Analyze in Excel).

Both of these tools are reporting tools. You can (somehow) run monitoring queries from these tools too, but these are not built for that purpose. If you can access it through other client tools, then you would have better control. The good news is that; now you can! using XMLA endpoint.

XMLA Endpoint

Now is a good time to explain what XMLA endpoint is. XMLA endpoint creates a connectivity channel for other tools and services (which can be third party tools too) to the SSAS model. XMLA endpoint is available for SSAS models hosted through SQL Server and been used for a long time already. That is why you can use a tool such as SSMS and connect to the local SSAS engine. Without an XMLA endpoint, the access to the SSAS model will be very limited and most probably unsupported.

XMLA endpoint creates a connectivity channel for other tools and services (which can be third party tools too) to the SSAS model.

The good news is that; XMLA endpoint is now available for Power BI datasets. Well, it means now you can use ANY client tools that support XMLA connectivity to connect to Power BI datasets. When I say ANY, I mean it, In addition to SSMS or SSDT or Microsoft SQL Server client tools, you can use third-party tools, such as DAX Studio, and Power BI Helper (coming very soon), and also tools such as Tableau! Yes, you heard it right; You can use Tableau to connect to a Power BI dataset hosted in the Power BI service, and then have your visualization in Tableau. (Although probably you won’t have a superb visualization like what Power BI has, because you are using Tableau, but you can do it 😉 anyways).

This diagram from Microsoft says it all actually, all of these tools now can be used to connect to a Power BI dataset hosted in the service;

In other words; Power BI dataset is not now just for Power BI tools, it can be the source for any other tools that have the XMLA connectivity support; Tableau, SSMS, Power BI Helper, and etc.

How does XMLA Connection work?

At this stage, the XMLA connectivity is just announced and has some limitations, however, through the time, it will be enhanced more and more. Right now, only read-only XMLA connection can be made to workspaces assigned to a premium capacity in Power BI service.

XMLA endpoint URL

If you go to a premium capacity allocated workspace, under the Premium Capacity, you will see the XMLA endpoint connection URL;

The format is:

powerbi://api.powerbi.com/v1.0/myorg/<workspace name>

workspace name can have spaces and is case sensitive. Example:

powerbi://api.powerbi.com/v1.0/myorg/Reza SAMPLE workspace

Admin Control under Capacity Configuration

Your capacity admin should enable you to use the XMLA endpoint. This is possible through Admin Portal -> Capacity Setting -> selecting the capacity -> Workloads

If you want to use this feature, XMLA Endpoint should be set to 1.

Client Tools

And you should have a client tool for this type of connection. While you are waiting for the next version of Power BI Helper (coming very soon), you can use a tool such as SSMS. However, you would need SSMS 18.0 RC1 or above, which can be download here. And client tools can have these libraries installed to be able to connect.

Sample Connection

Here is a sample of connection created using XMLA endpoint through SSMS;

And an example of querying to see how many users are using this dataset now using DMVs.

Summary

XMLA endpoint is most probably one of the major game changer features added to Power BI in the last few months. Using XMLA endpoints you can use client tools to control, manage, and monitor Power BI datasets in the service. Also, a Power BI dataset now can be used as the data model for other visualization tools such as Tableau. Power BI Helper will give you the functionality of connecting through XMLA endpoint VERY soon. XMLA endpoint at this point of time is read-only, so it won’t give you the ability to write changes back to the model, but the read/write is in the plan and hopefully comes soon. When that becomes an available backup, restore and many other features such as migrating from live connection to import data or etc can be achievable options. What is your feeling about XMLA endpoint? Share your thoughts in the comments below.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

13 thoughts on “What Is the XMLA Endpoint for Power BI and Why Should I Care?

  • This is exactly what I’ve been looking for! Thanks for the article.
    I have one question, can we connect to the dataset without using the service? We are restricted to PBIRS.

  • Thanks you very much!!! now I have a better idea of what XMLA can bring to me!

    Just a quertion. If I can now connect to a Power BI dataset hosted in the Power BI service… is it included Dataflows? What it comes to my mind is, now i have a shorcut to recover my cooked dataflows! Is it like this? let me give an example. If I upload some datasets as dataflows and the I perfom som PowerQuery transformation over them… let’s say more tha 10.000 records…. Could I get the transformed data throufh SMSS, PoweShell, DaxStudio etc? It would be good for the ones who don’t have a Premium licence!

    Javier López

    • Hi Javier
      XMLA endpoint is for datasets not for dataflows. datasets and dataflows have totally different backend structures. the dataset is using SSAS behind the scene, and dataflow is using CSV files.
      Cheers
      Reza

  • Hallo, thanks so much for article.
    I have two questions:
    1) Do I understand right, that XMLA Endpoint connectivity with SQL tools works only with Power BI Premium? (Does not work with Power BI Pro – but Power BI Desktop can connect to Dataset with Pro license)
    2) And please do you mean, that MS plan to connect Dataset from Excel/PowerPivot? (Like to any other tabular model)
    Thank you

    • Hi.
      Yes, using XMLA endpoint, you can connect to the dataset even from Excel/PowerPivot too. and Yes, it is at the moment only supported for dedicated capacity (premium). However, I’m hoping this becomes available for Pro as well.

Leave a Reply

Your email address will not be published. Required fields are marked *