Using Power Query with SharePoint Lists and Lookup Fields

As I’ve explained many times before, querying SharePoint data directly is a bad idea. The SharePoint data storage mechanisms simply aren’t designed for querying of any scale, hence the lookup limitations that have been imposed upon it. The best approach to querying SharePoint list data is to first load it into a data warehouse or data mart of some sort. However, both Reporting Services (SSRS) and Power Query support direct access to SharePoint lists. While I try to strongly dissuade people from doing this with Reporting Services, properly used, Power Query is a totally viable means of querying SharePoint list data.

Why is this? With SSRS, every query goes back to the data source for retrieval.  Power Query is different – it’s analogous to SQL Server Integration Services, which is an ETL management product. It loads source data into a repository, in this case, an embedded xVelocity, or Power Pivot model which can be considered a “personal data warehouse”. Queries against this mini data warehouse are fast, and don’t rely on SharePoint  retrieval mechanisms, and can be used quite effectively in reports.

There are a couple of subtleties to querying SharePoint list items with Power Query, and I will briefly walk through the process below.

With Excel open, click the Power Query tab, select “From Other Sources” and the select “From SharePoint List”.

image

Next, enter the URL for the SharePoint site (or subsite) that contains the list you wish to query.

image

If it is the first time accessing this site, you will be prompted for credentials. If your site is Office365, be sure to enter organizational credentials. If it is on premise, use Windows credentials.

Once entered, you will be presented with a list of SharePoint lists in the Power Query Navigator window. Select the list that you wish to query, in our case, Announcements. When selected, click the edit button to edit the query.

image

The data, or a subset will load into the query editor window. You will see all of the list item fields expressed as columns, and for the most part, using the correct data type. At this point you can remove any columns that are unnecessary, or filter any undesired rows. There are a couple of SharePoint field types that bear special mention.

Lookup fields are a lookup into another SharePoint list. Internally, the SharePoint item stores this as an ID and display value, but Power Query gives you access to all of the properties of the related item as a one-to-one relationship. Essentially, what you can do is to flatten that relationship by incorporating the related item’s attributes.

If you scroll to a column of this particular type, you will see the value expressed as a hyperlink with the value “Record”. Clicking on it will drill down to one related record, but that’s not what we want to do. We want to expand the properties for all items in the list. The way that you do this is  to click on the expand icon in the column header. In our case, we want to expand the “CreatedBy” field. CreatedBy is a standard list field, of the Person type. Person fields are actually a special case of a lookup field, so it exhibits this behaviour.

image

Here, we are interested in retrieving the user’s name and mobile phone, so we deselect all of the other fields. A new column will be created for every expanded field in the format sourcefieldname.attributename .

image

Attachments are another special case. There can be multiple attachments for a single list item, a one to many relationship. The hyperlink is therefore “Table”. Clicking on the column header expand for this column looks similar, but with an important difference. Options are available to either expand or aggregate the related items.

image

Selecting expand will create a new source record for each related item, and the only columns that will differ will be the items selected from the related table (Name in our case). Aggregate will not create any new records, but will summarize the related fields. For numeric fields, they can be totalled or averaged, and for text fields they can be counted.

Once ready, click “Close and Load” from the Query Editor ribbon, and the list data will load to either your model, or your workbook, depending on what your preferences are. Of course, I always recommend that you load to the model only.

Once loaded, any visualizations and queries will work against the model. The data can be refreshed at any point either manually, or automatically if using the Data Management Gateway. Keep in mind however that refreshes will operate against the source list.

Reporting Services Web Part Won’t Save Parameters with SharePoint 2013 SP1

We recently performed an installation of SharePoint 2013 with SP1 for a customer using the slipstreamed ISO available on MSDN. Part of the rollout in this case involved migrating a number of reports from a standalone instance of SQL Server Reporting Services (SSRS) to SSRS running in SharePoint integrated mode. However, during the creation of some new reports, we observed something odd. The custom parameter values were not being saved for web parts that were using these reports.

SSRS web parts in SharePoint allow for a report to be displayed within a web part, a therefore in part of the page. Report parameters created in the report can be exposed as web part properties. Therefore, you can have multiple instances of the same report on different pages using different parameters. You can also connect the web part to other web parts, such as SharePoint filters, and have the parameters driven by these filter values. However, in this particular case, none of it was working. The parameter could be saved, and applied, but when the page was saved, the parameter values would revert to their defaults.

After doing some digging, I came across this discussion on the MSDN forums. The discussion indicated that there may be a regression bug in SharePoint 2013 Service Pack 1 that exhibited this behaviour, and that the application of the May 2014 Cumulative Update had helped them somewhat. We had never seen this issue with builds prior to SP1. Given that our farm had not yet gone live, we quickly acquired and applied the July 2014 CU in the hopes that it would help.

It did.

In our case, it fixed the problem completely, however there was one small negative side effect. All of the web parts that had been added to pages before the update completely lost all of their saved properties, including the report that they were pointing to. In our case, this was no big deal, but if you have a lot of these, it could be an annoyance. I also don’t know if this would happen generally with the July 2014 CU, or it was only because that the original system was affected (as I suspect).

In any event, this apparently was a regression bug introduced with SP1, and applying the July 2014 CU clearly fixed this problem for us. Our new baseline for SharePoint 2013 will not be sP1, but SP1 with the July 2014 CU.

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