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

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!

Like this: Like Loading...