EPISODE 4– From Self Service Cloud BI to Enterprise Ready Cloud BI (Connecting to on premises Analysis Services – tabular)

This is the 4th post in the series about Power BI dashboards. All posts can be found here

The previous post about Power BI designer can be found here

I probably didn’t do my homework correctly since I wasn’t able to connect to my on-premises tabular instance of SQL Server Analysis Services. So let’s try again.

Analysis Services Connector : The game changer

First of all it is noticeable to remark that with the ability to connect to your local instance of SQL Server Analysis Services you are now bypassing the creeping limitation of Power BI in Office 365 which only allowed spreadsheets of max 250 MB to be uploaded. 250 MB columnar compressed data gave an approx. 2GB of data in tabular instance.

All you need to get ready is the Analysis Services Connector and you can get it either from you Power BI Dashboards browser window  :

I have already described the step to make this part work on this post so I won’t linger on the description of how to set it up in stead I’d like to point out those 2 main advantages to using the connector:

1. Live querying data in your Tabular instance
2. No limitation about data size (already said that – but I’m really thrilled about it)
3. Passing the effective username to your tabular instance (The Kerberos Killer)

So after my last blog post I started trying to find out where the error was. If it were on Azure I was doomed with few ways of finding out how to solve it. If it were on my premises I was able to investigate and eventually solve it. I started as I often do when I encounter problems, I went for a run…

At km 7 I realized that during my last troubleshooting jogging tour I concluded to run a Profiler against my SQL Server instance. I should do the same this time and the next (it will probably save me the run….)

Recap of the problem :

Which really doesn’t help me at all !!

I fired up under SQL Server Profiler, connected to my local tabular instance and queried my data source again :

Which on the other hand give me some more meaningful information about the error.

So the error is that the user I am connecting with : admin (at) rbaccaro.onmicrosoft.com is not allowed to connect to my instance. And it is truly not allowed since my instance is not at all in this domain.

The documentation for Power BI Analysis Services Connector which clearly indicates that for connecting to on-premises data source one should synchronize Active Directory with Active Directory Azure services : Directory Sync Scenario

According to the documentation :

Prerequisites : Configure Azure Active Directory Sync (DirSync) between your on premises Active Directory and Azure Active Directory

So this is what I did. But after second thoughts I realized that it would only work if you were using the same domain on both sides – on-premises and Azure or federated your domains. Since I am using a test domain only living on my local VMs it is not option for me. If you were in a real life situation then your domain administrator would have federated your Company domain to so you would be able to login to http://powerbi.com with your company windows credentials. But here it is not the case.

Setting up the connection between a Local Domain and Power BI Analysis Services Connector for demo purposes

This is a 5 steps operation. Very simple !! (if your using Azure VMs the steps are differents)

Step 1 : You need a testdomain.onmicrosoft.com.

Which you can obtain in several ways. Either with an Office 365 trial subscription or on the Azure Portal. Go onto the Directory tab and find your Default Directory or another one you wish to use. Then on the Domains tab choose add

Make sure your domain name is unique, otherwise it won’t validate. I have chosen United States as the region since Power BI dashboards is only available for US tenants. May be it’s not relevant in this case but better safe than sorry !!

Remark : Adding your own local test domain to Office 365 won’t work either since you need to verify it with your domain provider and AFAIK there is no way to avoid it and no way to validate it (with TX record)

Step 2 : You still need the Power BI Analysis Services Connector

As described in my second blog post and here.

Step 3 : Add a UPN suffix to your domain so you can create a user with the right domain name

In my case I am using a Power BI tenant called rbaccaro.onmicrosoft.com so on my domain controller I went to Active Directory Domains and Trusts and add a UPN suffix. (right click on the Active Directory Domains and Trust –> Properties)

And add the alternative UPN suffix that I need (rbaccaro.onmicrosoft.com). If you are in doubt look in your Power BI dashboard for the name of the user that you are using

Step 4 : Add a user in your AD

to match the user your are using on your Power BI dashboard (in this case admin (at) rbaccaro.onmicrosoft.com).

Once you’ve added the user go to next step

Step 5 : Give permissions to your user on your tabular instance !

and you should be able to see your tabular model on the Power BI dashboard.

You are now ready to unleash the Power of Enterprise Ready Cloud BI !!

Happy Power BIing !!

Tags: azure ad, configuration, dashboards, no sharepoint, office 365, Rehfeld, setup