Troubleshooting Power BI Preview Tabular Data Refresh

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!

 

 

 

 

 

 

 

 

SQL Saturday Nashville Precon

On Friday January 16th, I will be doing a precon for SQL Saturday Nashville with Stacia Misner (b|t) entitled “Finding Your Balance: BI in the Cloud, On Premises, or Both”. Stacia and I wrote a white paper for Microsoft last year on this topic, and we’ve continued to evolve the course material. This class will be aimed at a variety of IT folks—I’d really recommend it for IT managers, as we’ll cover a good overview of most of the cloud services available from Microsoft that are applicable to business intelligence (BI). For BI practitioners, in addition to the overview, we will demo Power BI, including the all new Power BI designer and dashboard features.

In addition to covering the BI side of data warehousing, we will also cover what you need to know from a network, active directory, and performance perspective to make cloud or hybrid data warehouse solutions work for you.

In this full-day workshop, you learn:

  • How to use Azure components to build a BI infrastructure completely or partially in the cloud
  • What changes to your solution architecture are necessary when you move components to the cloud
  • Best practices for configuring and networking Azure resources
  • How to integrate your cloud-based solution with your on-premises Active Directory (both Power BI and Azure IaaS and PaaS)
  • How to securely connect Power BI to your Azure and on-premises data sources
  • How to configure supported data sources for scheduled refresh
  • Best practices for administering Power BI components in Office 365

I hope you can join use in Nashville.

%d bloggers like this: