Power BI Data Management Gateway 1.2 Changes the Game

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).

image

image

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….).

Incorrect URL When Using a Multilingual SharePoint Blog Site

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.

SNAGHTML1a423938

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.

SNAGHTML1a4988fc

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.

image

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

image

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.

Problems Installing PowerPivot for SharePoint and Reporting Services From SQL Server 2012 SP2 on SharePoint 2013

I recently performed an installation of both PowerPivot and Reporting services for a customer that was running SharePoint 2013. In preparation for the installation, the customer had made the SQL media available to us (something that I always appreciate), so installation looked to be quick and easy. Since SP2 was released for SQL Server 2012 over a month ago (June 2014), they had downloaded the ISO file from MSDN that already included SP2.

I went ahead and installed the bits for both PowerPivot for SharePoint and for SQL Server Reporting Services. The next step in my case was to run the PowerPivot for SharePoint Configuration tool. There are normally two such tools available, the original one, for SharePoint 2010, and with the release of SQL Server 2012 SP1, there is the “PowerPivot for SharePoint 2013 Configuration Tool” which as the name might imply, is for configuring on SharePoint 2013. However, after this installation, there was only the one tool, which led me to believe that they had been consolidated. However running it resulted in the “Assembly not found” error that you get when installing on the wrong platform.

The next step in installing SSRS is normally to go and add the Service Application in Central Administration. However, it wasn’t available to add, and the typical PowerShell commands to register it (Install-SPRSService and Install-SPRSServiceProxy) resulted in not found errors. Something was clearly amiss.

After poking around a bit I was able to determine that the PowerPivot engine that was running was 11.0.2100.6, which corresponds to the RTM version of SQL Server 2012. (You can find a list of build numbers here) SharePoint Server 2013 requires at least SP1 for PowerPivot for SharePoint and for SSRS integrated mode. The problem had been identified, but what caused it?

After speaking with Todd Klindt (keeper of the SharePoint Patches log), it turns out that he had a similar experience with the SQL SP1 installation media. There is, or at least was a bug in the slipstream version of SQL Server 2012 SP1 that was available through Microsoft’s standard distribution channels. This bug caused only the RTM bits to get installed, not the patches. This has been documented in this Microsoft KB article. The bug was fixed, and the download media was updated within a few weeks. However, it appears that the same production glitch that caused the problem with SP1 happened again with the initial release of SP2, as documented in this blog post.

Happily, the problem has since been corrected, and any new downloads of SQL 2012 with SP2 should not be affected. However, if you have used an affected version, the fix is simple. All that is necessary is that you patch your install with the standalone SQLServer 2012 SP2 installer, available here. Ensure that you patch all elements, including your PowerPivot for SharePoint instance. You’ll know that you have the problem if you don’t see the PowerPivot for SharePoint 2013 configuration tool – there should be two as follows:

Perhaps the SQL team should talk to the SharePoint team about patching. Just sayin’…..

Changes to Data Loading Features in Power Query – May 2014

The Power BI team continues to deliver new features at a rapid pace. The May 2014 release of Power Query is no exception. There are quite a few new features packed into this release, which you can read about at your leisure but I’m particularly interested in the ones pertaining to data loading, as I’ve discussed several of the limitation in this area in the past.

This release is major indeed. There are three significant changes to the data loading features in this build.

Configurable defaults for data loading

I’ve just posted an article on how to do this and why. To put it simply, the default data load behaviour is to load data into a worksheet in most cases. This leads to larger than necessary workbooks, to the point where they may not work properly with Power BI. Now you can change this default behaviour, which will be welcome for anyone doing serious data modeling.

Worksheet Size Warning

If you do decide that you want to load data to the worksheet, or you’re simply unaware of the issues, you will be prompted to consider loading to the data model once your data hits 10 MB, which is the maximum non-model workbook size in Power BI. Prior to this update, the user wouldn’t know that there was a problem until after they tried to use the workbook in Office 365.

Data Model Preservation

Prior to this update, it wasn’t possible to modify the query without losing all changes to the data model, or formatting in worksheet tables. This release of Power Query remedies this situation. You can now go back and make changes to your query without having to recreate the model.

Power BI is being built in a surprisingly collaborative way, with a large amount of input from the community. I’ve never seen this done to this extent at Microsoft, and it’s very good to see. I know that all of these features have been asked for and discussed by the community in the last few months, and here they are. Kudos to everyone involved, and keep it up. This product keeps getting better and better.

Set Your Data Load Defaults in Power Query

When using Power Query to load data from a data source, the user has three choices as to where that data can be stored. It can go into a worksheet in the workbook, into the data model, or into both. The default behaviour (and therefore the one that will be used most commonly) is to load into a worksheet when one table is being imported, or into the data model when multiple tables are selected.

The reason for the load to worksheet default is understandable, as casual users will expect to see the data immediately after it is imported – they won’t want to open the PowerPivot model editor (which is itself turned off by default in Excel 2013). However, loading to the worksheet introduces quite a few limitations, as I’ve described (OK, whined about) mostly here, but also here and here. These limitations mostly revolve around the million row limit in Excel, the fact that data is stored in the model much more efficiently, and the file size limits when working with Power BI.

The great news is, that as of the May 2014 build of Power Query . These data loading defaults are now user configurable. If you’re taking the time to read this post, chances are that you should set them.

The defaults are Power Query wide, and are therefore located in the Power Query options. Once Excel is open,  go to the Power Query tab and click the Options button.

image

Now, in addition to joining the Customer Experience Program, you can set your data load settings. I always import only to the model, so I check the custom option, and Load to Data Model.

image

I can’t be completely sure, but in my limited testing, it appears that this option setting does not sync between instances of Excel on different machines the way that other (non add in) settings do. You will therefore need to set this for every Excel installation that you have. Now if there was only a Group Policy setting that could be pushed out….

Kudos to the Power Query team for implementing this – it’s a welcome addition to the product.