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:

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.

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

• Click “Create”

• Enter required fields

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

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”

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

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

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)”

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

• Select/enter your Power BI Service credentials

Baaam!

Connecting to Power BI Service from Excel

• Open Excel
• Click Get Data
• From Database, click “From Analysis Services”

• Paste the Workspace Connection you copied earlier
• Click “Use the following User Name and Password” then enter your credentials
• Click Next

• From dropdown list select a desired dataset then click Next

• Click Finish

Here you go!

Connecting to Power BI Service from DAX Studio

• Open DAX Studio
• Select “Tabular Server”
• Paste the Workspace Connection
• Click Connect

• Select/enter your Power BI Service credentials

All done!