Using PowerPivot for SharePoint with SharePoint 2016

Last updated on October 31, 2017

While the capabilities previously provided with Excel Services have been moved to Office Online Server (OOS) in the 2016 version of SharePoint, PowerPivot for SharePoint (PP4SP) has not. PP4SP remains a SharePoint service application in the 2016 edition of the product. This service application is responsible for providing the automatic data refresh capability for PowerPivot for SharePoint enabled workbooks. As an aside, it can also refresh connected workbooks, as I discuss here. Given that the rendering engine now exists on a separate server, there are a few additional steps to perform, and this article aims to walk through them.

Basic Installation

Prior to setting up PowerPivot for SharePoint, you’ll need a SharePoint farm that has been enabled for PowerPivot workbooks, as I have previously outlined here. The Add-In is available from Microsoft here.

Installing is a simple matter of downloading the add-in and running setup. You’ll be presented with a straightforward dialog box with 4 options.

This should be installed on every SharePoint server in the farm, whether or not it will run the Service application. Technically the first option is not required for front end web servers, but it is small, and I like to keep my options open. After clicking next, the bits will be installed.

Like SharePoint, once the bits are installed, they must be configured. This is done through the PowerPivot for SharePoint configuration tool, which the earlier setup installed. It should be available from the application list on the server. It works much the same as it did with earlier versions of PP4SP and SharePoint. Run it and you’ll be prompted for the installation type. Select “Configure or repair..” and click OK. Next, you’ll be presented with the configuration detail dialog. The dialog contains a number of configuration nodes, which drive a series of PowerShell scripts that are used to perform the configuration (tip – click on the Script tab to see the scripts in question). The exclamation point icon indicates that parameters need to be supplied.

The first node is mandatory – Configure or Repair.

Here, you enter the credentials of the user that can perform the configuration – I normally use the user that was used to configure SharePoint in the first place – spSetup in my demo environments. This is the only step that is critical. However, I find it to be good practice to change the name of the Service application and database.

The default values begin with “Default Power Pivot…” and the database contains a GUID as part of its name. When searching alphabetically for PowerPivot, I tend to look under P not D, so I remove the word default from both, the GUID from the database, and further change the database name to conform with naming conventions. Finally, it’s a good idea to check the Site Collection that will be activated.

The configuration tool will activate the PowerPivot solution in one site collection by default. It can be activated later in others, but it’s worth starting off on the right foot.

Click the “validate” button, and if all of the indicators are green, go ahead and complete the configuration.  Once configured, no further Central Admin work should be necessary, at least not at this point.

PowerPivot Gallery

A PowerPivot gallery is not required. All of the PowerPivot for SharePoint features can be used in a regular document library, but the gallery centralizes things and makes these features more discoverable. It should be noted that just as with SharePoint 2013, the PowerPivot gallery is a customized document library that uses Silverlight to display its contents. This dependency on Silverlight means that in order to use it, workstations must have Silverlight installed, and neither Google Chrome, nor Microsoft Edge browsers will support it.

To create a new PowerPivot Gallery, navigate to the site contents of the target SharePoint site, and select “Add an app”. Select the PowerPivot gallery and give it a name. If you don’t see PowerPivot Gallery as an option, you may need to enable the PowerPivot Feature for Site collection in the Site Collection features list. Once added, upload a PowerPivot enabled workbook. This workbook should contain a data model where the data was imported directly into PowerPivot (not via Power Query). Once uploaded, after a few moments, the thumbnails from the workbook objects should show up in the gallery. It should be possible to interact with the workbook, as PP4SP is not required for that, but the two (or 3 depending on whether or not SSRS has been installed) icons on the right of the workbook provide access to PP4SP capability.

In order to set up scheduled refresh, click on the calendar icon (The Excel icon is for using Excel as a data source – see below). This opens up the data refresh history for this workbook. To configure it, click on the “Configure Schedule link”. On the configuration screen, select the enable check box, enter the desired schedule, and the credentials needed to connect to the source data. For testing purposes it is more deterministic to explicitly enter credentials here, but refresh supports a “refresh account” (configured via the Secure Store Service), or any Secure Store Service credentials. Also, selecting “Also refresh as soon as possible” will immediately force a refresh cycle, which will begin within 5 minutes of saving, and is useful for testing.

Once complete, open the refresh history for the workbook, and you should see either a stopwatch icon, indication a refresh is in progress, a green check mark, indicating successful completion, or a red x, indicating failure.

One thing should be noted – data models created by using Power Query in Excel will always fail – this is true as of March 2016. Power Query refresh has been stated as a feature for PowerPivot for SharePoint 2016, but as of this writing, it has not yet been included.

Workbook as a Data Source – Kerberos Enablement

The URL of a workbook that contains a data model can be used in a connection string in another workbook, and PowerPivot for SharePoint can intelligently route that connection to the backing SSAS PP Mode server. To the consuming workbook, it looks just like a regular SSAS server.

In prior versions of SharePoint and PP4SP, using a workbook as a data source “just worked”, because the service and the workbook were all on the same server. With OOS, the server is on a different server. OOS needs to connect to the source workbook (the one with the data model) with the credentials of the consuming user, which means that for this to work, Kerberos Constrained Delegation (KCD) must be configured between OOS and SharePoint.

To be sure, you only need to configure KCD if you wish to use Excel files as a data source. If not, this step can be safely skipped.

You need to allow the computer account for the OOS server to delegate credentials to the account running the HTTP service for the SharePoint application that contains the workbooks to be used as data sources. In the example below, the OOS Server is NAUTILUS2016OOS, the service account is NAUTILUS\spApps, and the application is http://home.nautilus.local. This PowerShell can be run on any Domain Controller server.

$allowedPrincipals = @()
$allowedPrincipals += Get-ADComputer -Identity NAUTILUS2016OOS

# Set the delegation property on the application pool identity.
Set-ADUser spApps -PrincipalsAllowedToDelegateToAccount $allowedPrincipals

# Set the Service Principal Names for the application pool identity.
SetSPN -S HTTP/home NAUTILUS\spApps
SetSPN -S HTTP/home.nautilus.local NAUTILUS\spApps

Once successfully configured, it should be possible to use Excel files that contain data models as a data source for other Excel files. To create a new one, click the Excel icon beside the data refresh history icon in the PowerPivot gallery.

Wrapping Up

One other feature requires further configuration to work, and that is the PowerPivot Administration dashboard. Security constraints now prevent the use of Central Administration as a container which means that the dashboard must be set up in a regular site collection. This  requires Server to Server (S2S) trust to be configured. Given that this is not a user facing feature, it’s out of scope for this article, but details on how to do it can be found in the Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016 document.

Setting up PowerPivot for SharePoint will still not give you the ability to render Power View reports in a browser whether they are created standalone, or in an Excel workbook. For that, it is necessary to set up SQL Server Reporting Services (SSRS) in SharePoint mode, as Power View rendering is part of SSRS. That will be the topic of an upcoming article.