What Does XMLA Endpoints Mean for Power BI and How to Test it for Free?

Test Environment from Power BI XMLA Endpoint

XMLA endpoint connectivity for public preview has been announced late March 2019. As at today, it is only available for Power BI Premium capacity users. This sounds like a massive restriction to a lot of people who don’t have a Premium capacity, but they’d love to see how it works. In this article I show you an easy way to get your hands to Power BI XMLA endpoint as quick as possible. Before I start, I’d like to simply explain what XMLA endpoint is and what it really means for Power BI users.

Power BI is Like Onion! It has layers!

Generally speaking, Power BI has two different layers, presentation layer and data model layer. Presentation layer is the visual layer, the one you make all those compelling reports and visualisations. The data model as the name resembles, is the layer that you make your data model in. This layer is the one you can access it via XMLA connectivity.

In a Power BI Desktop file, you can see both layers:

Different layers of Power BI

How XMLA Relates to Different Layers in Power BI?

As you may have already guessed, XMLA is only related to the data model layer and it has nothing to do with the presentation layer. So you may connect to a data model, browse the data model, import data from the model to other platforms like Excel and so forth.

XMLA Is Not New!

Seriously? Yes, seriously. It is not new. It’s been around for many years and perhaps you’ve already used it zillions of times. Whenever you’re connecting to an instance of SQL Server Analysis Services, either Multidimensional or Tabular from any tools like SQL Server Management Studio (SSMS), Power BI Report Builder, Excel, Tableau, etc…, you’re using XMLA connectivity indeed.

Power BI is an Instance of SSAS Tabular

It is true. Power BI runs a local instance of SSAS Tabular model. So, whenever you open a Power BI Desktop file (PBIX), Power BI creates a local instance of SSAS Tabular model with a random local port number that can be accessed on your local machine only. When you close the file, the local instance of SSAS Tabular is shut down and its port number is released.

I first revealed the fact that you can connect to the underlying data model in Power BI Desktop from whole different range of tools like SSMS, SQL Server Profiler, Excel, etc… on Jun 2016. So, we indeed were using XMLA to connect to Power BI data models for a long time. We can even take a step further to import our Power BI data models into an instance of SSAS Tabular. In that sense, we are literally generating XMLA scripts from Power BI to create the same data model in SSAS Tabular. How cool is that?

Sooo… What is new then?

XMLA Endpoints for Power BI, What’s New?

So far, I only talked about Power BI Desktop, the report authoring tool installed on our local machine. What normally happens after we build our report is that we publish those reports into Power BI Service. Right? You guessed it. The importance of XMLA endpoints is that it makes a connectivity channel to all models published to Power BI Service. This is amazing. Just think about the endless possibilities it can open for us. You can now connect to the data models published to Power BI Service just like you normally connect to an instance of SSAS. You can use SSMS (v18.0 RC1), DAX Studio, Excel, Tabular Editor, Tableau, etc… to connect to published data models to Power BI Service.

Here is a table showing how Power BI resources map to SSAS Tabular instance:

SSAS
Tabular
Power BI Service Power BI Desktop
Server (Instance) App Workspace Localhost:PORT_NUMBER (when a PBIX file is opened)
Databases Datasets A Random database name
Connections Connections Connections
Tables Tables Tables
Roles Roles Roles

Here is a screenshot of SSMS connected a Power BI Service Workspace using XMLA Endpoints, a local Power BI Desktop file opened on my local machine and an instance of SSAS Tabular.

SSMS Connected to Power BI Service  Through XMLA Endpoints

How to Test Power BI XMLA Endpoints for Free?

As mentioned earlier, XMLA endpoints is available in Power BI Premium Capacity which is a massive roadblock for a lot of us who don’t have access to a Power BI Premium capacity. But the good news is that XMLA is also available in Power BI Embedded Capacity. You may ask, so what? I also don’t have access to an Embedded capacity. Well, you fall in one two categories below:

  • You have an MSDN subscription
  • You don’t have that one either

Either way, the good news is that you can create an Embedded Capacity in azure if you already have an MSDN subscription. If you don’t have it, don’t worry, you can get a free trial subscription.

Now let’s see how we can create a Power BI Embedded Capacity.

Creating Power BI Embedded Capacity

Login to your Azure tenant with the same account as your Power BI Service, then:

  • Click “Create a resource”
  • Type in “Power BI” in the search box
  • Click “Power BI Embedded” from the list
Power BI Embedded in Azure Portal
  • Click “Create”
Creating Power BI Embedded Capacity in Azure Portal
  • Enter required fields
Creating Power BI Embedded Capacity in Azure Portal
  • Click create

Note that you select A1 pricing tier otherwise it drains all your credit very quickly. It’s good to click “View full pricing details” to have a better idea on around the costs.

Power BI Embedded Pricing Tiers

As you see, even A1 pricing tier is NOT cheap at all. So, you may consider pausing the capacity when not used.

We are almost there, just one more step to take.

Making a Power BI Workspace a Part of Embedded Capacity

After you create your embedded capacity there is just one little thing you need to do is to login to Power BI Service and make
any desired workspace a part of your embedded capacity.

  • Click “Workspaces”
  • Find a desired Workspace and click ellipsis button
  • Click “Workspace settings”
Power BI Workspace Settings
  • In the settings pane click “Premium” tab
  • Switch “Dedicated Capacity” on
  • After you switch the dedicated capacity on you see a dropdown box that you can select an available capacity for the workspace
  • As you probably noticed the “Workspace Connection” is the one you are after. You can copy the connection then click “Save”
Power BI Workspace Connection

After you save the changes you immediately see that fabulous diamond icon shows up on the Workspace.

Premium Workspace in Power BI Service

How to Connect to Power BI Service Datasets from Different Tools?

As you expect it is really easy from here.

Connecting to Power BI Service from SQL Server Management Studio (SSMS)

As stated earlier you need to install SSMS v18.0 RC1 otherwise you get the following error:

“The connection string is not valid. (Microsoft.AnalysisServices.AppLocal.AdomdClient)”

Error in SSMS when connecting to Power BI Service Premium Workspace
  • Open SSMS
  • From “Connect to Server” select “Analysis Services” for Server Type
  • Paste the Workspace Connection in “Server Name”
  • From “Authentication” dropdown select “Windows Authentication”
Connecting from SSMS to Power BI Premium XMLA endpoint
  • Select/enter your Power BI Service credentials
Microsoft Credential Check

Baaam!

Browsing Power BI Premium in SSMS with XMLA endpoint

Connecting to Power BI Service from Excel

  • Open Excel
  • Click Get Data
  • From Database, click “From Analysis Services”
Connecting to Power BI Premium from Excel with XMLA endpoint
  • Paste the Workspace Connection you copied earlier
  • Click “Use the following User Name and Password” then enter your credentials
  • Click Next
Connecting to Power BI Premium from Excel with XMLA endpoint
  • From dropdown list select a desired dataset then click Next
Browsing to Power BI Premium from Excel with XMLA endpoint
  • Click Finish

Here you go!

Browsing to Power BI Premium from Excel with XMLA endpoint

Connecting to Power BI Service from DAX Studio

  • Open DAX Studio
  • Select “Tabular Server”
  • Paste the Workspace Connection
  • Click Connect
Connecting to Power BI Premium from DAX Studio with XMLA endpoint
  • Select/enter your Power BI Service credentials
Microsoft Login Credentials
Querying Power BI Premium from DAX Studio with XMLA endpoint

All done!

6 thoughts on “What Does XMLA Endpoints Mean for Power BI and How to Test it for Free?

  1. getting this error:

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Error connecting to ‘powerbi://api.powerbi.com/v1.0/myorg/XMLAtest’.

    ——————————
    ADDITIONAL INFORMATION:

    The client is not connected to an Analysis Server. (Microsoft.AnalysisServices.NodeContextServices)

    any idea?

    1. Hi Adam,
      Welcome to BIInsight.
      From the error message I suspect you haven’t made the Workspace you’re trying to connect to a part of your Embedded capacity or your Embedded capability is paused.
      Cheers

  2. Hi, this is great but after connecting xmla, what’s the real use of after connecting in practical Business?

    1. Hi Brij,

      Welcome to BIInsight.com.
      Well, there are a lot of use cases. For instance, before making XMLA endpoints generally available, your Power BI models were limited to Power BI and Excel as your only data visualisation choices. But now you can connect to your Power BI models in the cloud from virtually any data visualisation tool like Tableau, SSRS, Power BI Paginated etc…
      Note that XMLA is currently available in read-only mode, so after you can writing capabilities are supported then there will be endless possibilities from creating your model programmatically to backup/restore etc…
      So this is why opening XMLA endpoints is so cool.
      Hope that helps.

      Cheers.

Leave a Reply

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


The reCAPTCHA verification period has expired. Please reload the page.