Just before Christmas, Microsoft delivered a present to business intelligence (BI) geeks everywhere—we got access to the Power BI Dashboard preview, and additionally got a few new toys. A dedicated client that integrates all of the tools in the Power suite, and a new data gateway to allow us to refresh Power BI charts from an on-premises tabular model. In addition, to tabular Microsoft give us the option to connect to a wide variety of cloud data sources (see Figure 1), along with a new API for Power BI that enables developers to connect their own custom data sources with Power BI. All of these are great features.
Figure 1 Get Data Screen in Power BI Dashboard
Since Stacia Misner (b|t) and I are doing a precon on Hybrid Power BI in Nashville in a couple of weeks, I wanted to setup a demo environment. I thought this would be pretty easy—and the initial setup was. The first thing you need to do is download the Analysis Services connector from the Power BI website.
Figure 2 Download Analysis Services Connector
At first, I didn’t think too much about this. I had a small tabular database on my laptop and I tried to connect it. I’m not going to walk through the entire setup here (it’s pretty basic). When I went into Power BI to try get data from my on-premises source, I found the following error:
SQL Server Analysis Services
Test12
The Analysis Services you are trying to reach (or the gateway to it) appears to be down or your access is denied; please contact the publisher of the server! If this is an unexpected error, please refer to the technical information below!
Technical details:
Activity Id: be9f376f-ef6c-a916-054b-525699aaf4ef
Request Id: f986e722-308a-55ec-6377-173d6f5d5c88
Date: 2015-01-07 14:03:47Z (UTC)
I ran profiler against AS (which along with extended events are really the only way to get information from a Tabular instance)—and I got the following:
Figure 3 Error in SQL Server Profiler
I know my password was correct, as the client side tool makes a connection to AS and validates the connection. This initial configuration was running on my domain joined laptop, with single sign on (SSO) configured on the Office 365 side. I wasn’t responsible for the full setup of SSO and directory integration, so I decided to build out a test environment in my lab. This involved building a new Office 365 tenant, configuring Active Directory Certificate Services (ADCS) and Active Directory Federation Services (ADFS) to perform directory sync with Office 365. That’s beyond the scope of this post, but for more detail see Stacia and I’s white paper on the topic. So I did all of that—and then I got the following:
Figure 4 The Point When Joey punched his desk
At this point I started digging around the Power BI help—it’s pretty good, but it’s not very search engine savvy, you will have to search on your own. I came across a blog from Greg Galloway (b) where I realized the problem. Remember Analysis Services only uses domain authentication. And even though our directories are synced, I don’t have single sign on configured, so as opposed to coming in as username@corp.contoso.com they were coming in as username@nashprecon.onmicrosoft.com.
Figure 5 Users Synced with AD
Greg proposes a solid workaround here—add an alternative UPN suffix and add those users to AS. This allows me to create users in the corp.contoso.com domain that have
Figure 6 Alternative UPN Suffix Creation
So I created a user in that domain:
Figure 7 User Creation
And then, everything worked.
Figure 8 Connection to AS Tabular in Power BI
There are a couple of things I’d like to mention here—and I’ll add (and I’ll post them to the product team)
- There’s no way to edit/remove/add the connection from the Power BI site—it was only through the connector. This is inconsistent with the behavior in legacy Power BI sites. Also, it makes it difficult to verify properties in your connection (and the client lets you change a few things, but not the server name), so there is a 1:1 relationship between gateways and tabular instances
- The error messages could be way more helpful—I realize this is challenging in a hybrid world, but links to guidance on troubleshooting would be helpful
- After I federated AD with Office 365, I no longer had the option to login to my Office 365 in the initial setup process for the connector. As far as I know, this isn’t documented anywhere.
Good luck in your testing!
