August 2014 – The White Pages

Last week, a new version of the Power BI Data Management Gateway was released. If you’re unfamiliar with it, it is the Power BI component that allows for workbooks stored in the cloud to be refreshed on a regular basis with data that exists on-premises, or outside of the hosting center.

I’ve been using the gateway since its initial availability in preview form, and in my opinion, this is the most significant functionality change yet. Until this release there were a grand total of three possible data source types that could be refreshed. With this release, the total increases to 18 by my count (you could argue 22, but that’s plenty).

With past versions, I would write up a quick post on how it is configured, but that has been done, along with the complete list of supported data sources and a helpful video on this blog post by the Power BI team. In addition, an very comprehensive (although amazingly already in need of update) white paper on hybrid data scenarios has just been published by Microsoft here.

The big change here is that this multitude of data sources is supported for data retrieved by Power Query, and NOT by Power Pivot natively. The catch is that they’re only supported for Power Query queries. There is absolutely nothing wrong with this, but it does require us to change our approach a bit to using the data management gateway.

As I first mentioned in a post almost a year ago on Using the Data Management Gateway, and in a number of posts since, data connection strings needed to line up with Power Pivot connections. At the time, the only supported sources were SQL Server and Oracle (for the gateway) and Power Query wasn’t supported at all. Version 1.1 of the Gateway brought Power Query support, but only for those 2 supported data sources. With this release, the Power Query support includes not only all of the new data sources, but also the three original Power Pivot data sources (note: Power Pivot data connections can be found in the Power Pivot add-in UI, while Power Query connections are available on the data tab in Excel).

As of this release of the Data Management Gateway, there is almost no reason to use native Power Pivot connections any longer. My recommendation is therefore that unless there is a good reason for not doing so, you should try to use Power Query for all data acquisition tasks. It is quite clearly the way forward, and will only gain in supported capabilities. My suspicion is that Power Pivot connections will be retained for backward compatibility reasons only.

With that said, there are a couple of good reasons for using Power Pivot connections directly. One of these reasons is if your data source is online, whether it is SQL Azure, SharePoint Online, or Project Server online. With these data sources, a Data Management Gateway is not required for refresh to work from an embedded Power Pivot connection. However, if Power Query is used to access these sources, it is.

What this means is that for Power Pivot connections to these sources, a refresh allows the Power BI service in the cloud to directly access these data sources in the cloud. However, because ALL Power Query connections require the Data Management Gateway for refresh operations, a Power Query refresh operation will require all of the data to be first downloaded to the on-premises gateway, and then sent back up to the Power BI service in the cloud. While functional, this is hardly the most efficient approach.

Apart from this one small caveat, this version of the Data Management Gateway spells the way forward. Additional data source support should come fast a furious, and the Power Query focus means that we can start to rely its powerful transformational capabilities without having to sacrifice refreshability (if that’s even a word….).

7 Comments

I recently encountered a relatively puzzling circumstance on a multilingual blog site. In this scenario, the customer was using the SharePoint blog as part of their public facing web site and while the English site was working fine, the French site was not. More accurately, I should state that it wasn’t working for me, but I’ll get to that shortly.

For the most part, the French blog site was working. The posts showed up on the main page, and could be accessed. However, if you opened up the source list for the posts, either by using “All Content” or by selecting “Manage Posts”, the posts would appear, but clicking on them would result in the error “Page not found – The page you’re looking for does not exist”.

The first clue was that although the list is named “posts”, the site itself is a French site, so the actual list name is “Billets” as seen below.

This difference is a feature of SharePoint 2013, and it happens because this is a multilingual site that has been enabled for multiple language display. In my case, my user profile identifies my preferred language as English, and SharePoint automatically translates all system generated text into English. With this feature, an English system administrator can easily navigate a French site because pages like system settings are automatically translated.

Unfortunately, in the case of the blog site, the translation system gets a little overzealous. Clicking on one of the blog posts from the system view results in the error mentioned above. Why is there an error? All we need to do is to look at the URL to discover the reason.

As you can see, the list name requested is “Posts” but we know that the actual name is “Billets”. The URL itself is being translated for me. So how do we avoid this problem?

As good as the translation system is, it’s not worth the error, so we need to turn it off for this site. Doing so is as simple as turning it on. From the Site settings page, select “Language settings” under the Site Administration section.

From there, simply deselect all alternate languages, and automatic translation will no longer be performed.

Once this is done, the URL will no longer be translated, and opening posts will work. You will now need to navigate the site settings in a different language, and know that “Language settings” is the same as “Paramètres linguistiques”, but most SharePoint admins can navigate here with their eyes closed anyway.

I hope that this helps.

3 Comments

Month: August 2014

Power BI Data Management Gateway 1.2 Changes the Game

Incorrect URL When Using a Multilingual SharePoint Blog Site