Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016

Last updated on October 31, 2017

SharePoint has supported PowerPivot enabled workbooks since they were first introduced with the initial PowerPivot for Excel Add-On. This support was initially provided via PowerPivot for SharePoint, which was comprised of two major components. The first component was a special, and mostly hidden instance of SQL Server Analysis Services (SSAS) running in what came to be known as SharePoint mode. The other component was a SharePoint shared service application that provided for the scheduled refresh of workbooks and an administrative dashboard.

Initially, these two components came as a single product, PowerPivot for SharePoint, available on the SQL Server install media. The PowerPivot for SharePoint that shipped with SQL Server 2008 R2, and with 2012 prior to SP1 would install both components on to a SharePoint server. Beginning with SharePoint 2013 and SQL Server 2012 SP1 it became possible to separate the two components, allowing for greater scalability. An entry for each SSAS server running in SharePoint mode simply needed to be added to the Excel Services service application (the “BI Servers”), and they would automatically be used for any embedded PowerPivot models.

 With the deprecation of Excel Services in SharePoint 2016, this support moves to Office Online Server 2016 (OOS), and with it, some further decoupling. It is now possible to support the data model with having first installed the PowerPivot for SharePoint service application. Technically, this was possible in SharePoint 2013, but it was far from obvious as to how to make it happen. All that is necessary for PowerPivot workbook support is an Analysis Services server running in what is now called “PowerPivot Mode”.

PowerPivot Mode is that very same specialized version of Analysis Services that exists to support data models that was previously referred to as “SharePoint Mode” in earlier versions. The option to install it is now front and center along with “Tabular Mode” and “Multidimensional and Data Mining mode”. To install a PowerPivot mode Analysis Services server, simply install Analysis Services as normal, and choose “PowerPivot Mode” when you get to the options screen.

The other important thing to note in this dialog box is the inclusion of the computer account for the OOS server(s) as (an) administrator(s). This is necessary because the OOS machines will need to create and tear down data models from Excel workbooks on demand, and the OOS process on these machines run as the Network Service account. Note that this is NOT necessary because of EffectiveUserName as with a regular Tabular Mode SSAS server. EffectiveUserName is not used in PowerPivot mode. It the server gets installed without this account added as administrator, it can be added later via SSMS 2016 in the same manner described in my earlier article regarding Tabular mode SSAS.

Once the PowerPivot mode SSAS server is up and running, the OOS server needs to be configured to use it (If you don’t yet have an operational OOS environment, please see this earlier article to get up and running). As with all other OOS configuration options, this is done through PowerShell. The command to add a PowerPivot mode server is:

New-OfficeWebAppsExcelBIServer –ServerId NAUTILUSSQLBI\SHAREPOINT

In the example above, the name of the PowerPivot mode server is NAUTILUSSQLBI and the instance name is SHAREPOINT. I have found it helpful to use named instances for PowerPivot Mode servers.

Additional servers can be added to the farm in a load balanced configuration by simply rerunning the above command with any additional server ids. This is similar to the way it was done in Excel Services, by adding them to the BI Servers list.

Once this has been put into place, it should be possible to interact with any Excel workbooks that contain a data model. All slicers and pivots should work. This will not, however allow you to schedule data refreshes, or use any other PowerPivot for SharePoint features. For that, you’ll need to add PowerPivot for SharePoint to your SharePoint farm, and that is the topic of an upcoming article.