SQL Server 2016–Which Edition Do You Need for Business Intelligence?

For the past several releases, SQL Server has come in 6 possible editions. Developer, Express, Web, Standard, Business Intelligence, and Enterprise. Developer, Express and Web are for specific workloads, which leaves Standard, BI, and Enterprise. The choice of which edition to use would seem to be obvious – the one named Business Intelligence. However, Enterprise contained all of the features that the BI edition did, and in many cases, wound up being a better choice from a licensing perspective. Standard mode also provided many BI capabilities, but not all.

The biggest difference (but not the only one) from a BI standpoint between Standard, and either BI or Enterprise edition was the support of the Tabular Mode in SQL Server Analysis Services. For those unaware, Tabular Mode is the engine behind PowerPivot, and increasingly importantly, Power BI. From a price standpoint the difference between Standard and either BI or Enterprise is quite significant. This has put the Tabular model out of reach for some small and medium sized businesses which is unfortunate, given that tabular is at the center of Microsoft’s future BI efforts.

SQL Server 2016 removes the BI Edition as an option, leaving us with a choice between only Standard and Enterprise. The biggest news in my opinion from a licensing perspective with 2016 is that Tabular Mode will now be supported in Standard Edition. This puts the tabular model within the reach of all organizations, and closes the licensing gap in the BI stack. This is fantastic news.

There are of course limitations with Standard mode. Tabular in Standard Mode is restricted to 16 GB of RAM, which may seem like a lot, but keep in mind that tabular is an in-memory technology. It’s possible to bump into this limit fairly quickly, but it’s a limit that serves the small/medium business space rather well.

PowerPivot for SharePoint also remains an Enterprise only feature. However, given the capabilities available in Power BI, and the upcoming rendering capabilities of SSRS, this may be less important than it previously was.

Given that it’s relatively simple to move from Standard to Enterprise (from a technology perspective), this approach allows organizations to get up and running, and then scale up if necessary. It removes that up front Enterprise cost barrier. It’s much easier to get budget for and Enterprise license when its value has already been proven.

Another difference between Standard and Enterprise in SSAS is that Standard mode does not support partitioning, perspectives or DirectQuery. DirectQuery allows for real-time analytical reports, which removes the cached data storage from the picture. All queries go directly back to the source. An explanation of partitions and perspectives is beyond the scope of this post, but if you don’t know what they are, the chances are that you don’t need them.

From an SSRS standpoint, the traditional differences between Standard and Enterprise are still in place. These include data alerting, data driven subscriptions, PowerView support  and scale out capability. All of the new features of SSRS 2016 are available in both Standard and Enterprise modes with one very notable exception. The new Mobile Reports are only available with Enterprise.

Mobile reports are the result of last year’s acquisition of Datazen, which has been fully integrated into SSRS. It allows on-premises SSRS servers to provide rich mobile reports on a variety of platforms. If your organization is using Power BI already, then you likely have a mobile solution, but if not, Mobile reports may fill that gap.

A complete summary of the differences between all of the different SQL Server editions can be found here. A quick PDF chart of what’s new in SQL Server can be found here.

In summary, both Standard and Enterprise editions of SQL Server 2016 are now suitable for use in business Intelligence solutions. The decision to move to Enterprise can now be based on scale and enterprise requirements, not on basic functionality. This, in my opinion, is all to the good. 

What you need for Business Intelligence in SharePoint 2016

Over the past few weeks, I’ve put together a number of posts that outline the intricacies of setting up SharePoint 2016 with its BI workloads, in particular Excel, PowerPivot, and SQL Server Reporting Services. With the full release today of SharePoint 2016, I wanted to summarize these posts, and to provide some context.

The major change to the BI world is of course the fact that Excel Services is no longer included, its capabilities having been replaced by Office Online Server (OOS). The posts below discuss the implications of this change, as well as how to configure all of the BI features in the new platform.

Article Description
Rethinking Business Intelligence in SharePoint and SQL Server 2016 My take on the changes to on-premises BI in the Microsoft world, and what the implications are for the present and future
Adding Excel Services Capabilities to a SharePoint 2016 Farm How to Set up Office Online Server to support the services previously available in Excel Services
Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016 How to set up SharePoint 2016 and Office Online Server to support Excel workbooks with embedded PowerPivot data models
Using PowerPivot for SharePoint with SharePoint 2016 How to configure the PowerPivot for SharePoint 2016 service application
Configuring SSRS 2016 Integrated Mode with SharePoint 2016 How to configure SQL Server Reporting Services 2016 Integrated mode in SharePoint 2016
Integrating SharePoint 2016 with SSRS Native Mode How to configure SQL Server Reporting Services 2016 Native mode and integrate it with SharePoint 2016

Just a quick glance at the articles above will show a deep dependency on SQL Server 2016. For example, in prior versions of SharePoint, multiple versions of SSRS were supported on SharePoint. This is no longer the case with SharePoint 2016. To be clear, I am talking about the BI components (SSRS, PowerPivot for SharePoint) and not the core database server for SharePoint. SharePoint 2016 requires SQL Server 2016 versions of both PowerPivot for SharePoint and SSRS. This means that if you’re invested in Business Intelligence in SharePoint 2013, you’re going to need to wait for SQL Server 2016 before you upgrade in a production environment.

SQL Server 2016 is currently at the Release Candidate (RC0) stage, and its release won’t be that far off. You can get started today on your test migrations, knowing that the full release will likely be available by the time your testing is complete. The articles above were all written while using the CTP 3.3 version of SQL Server 2016.

Looking through the articles you’ll find a number of configurations, and requirements that line up with specific scenarios. Below is a quick guide to outline what is required to support what feature in the SharePoint 2016 BI space.

Feature Requirements
Excel workbooks connected to SSAS Data Sources Kerberos Constrained Delegation (KCD) between OOS and SSAS data source

OR

EffectiveUserName enabled on OOS Server(s)

OOS Server account(s) added to Admin list on SSAS server(s)

Connected Excel workbooks to Windows Authenticated SQL Server Data Sources KCD between OOS and SQL Server

Claims to Windows Token Service running on OOS Server with Network Service enabled

Connected Excel workbooks using stored credentials (Excel Services Authentication Options) Secure Store Service (SSS) credential created

OOS machine account added to SSS Members list

“AllowHttpSecureStoreConnections = true” set on OOS server if HTTP is used

PowerPivot enabled Excel workbooks SSAS PowerPivot Mode server available

SSAS PP Mode server added to BI server list on OOS Server via New-OfficeWebAppsExcelBIServer cmdlet

OOS Server account added to Administrators list of SSAS PowerPivot Mode Server

Automatic Refresh of PP enabled workbooks PowerPivot for SharePoint

Silverlight (client side)

PowerPivot Gallery PowerPivot for SharePoint

Silverlight (client side)

Excel files as a data source PowerPivot for SharePoint

PP4SP must have admin access on SSAS PP mode Server

KCD between OOS and SharePoint application

Claims to Windows Token Service running on OOS Server with Network Service enabled

External ODC file support
PowerPivot Management Dashboard
S2S Trust Configured between OOS and SharePoint
Power View reports SSRS Integrated mode

Silverlight (client side)

Power View in Excel
Power View with Excel as a data source
SSRS Services account must be added to the Admin group on the BI server

Silverlight (client side)

I’ll update this post if anything significant changes between now and the release of SQL Server 2016, but this should help those interested get up to speed today on Business Intelligence in SharePoint 2016.

Integrating SharePoint 2016 with SSRS Native Mode

Why on earth would I want to write an article on this topic? Surely, if I am using SharePoint and SQL Server Reporting Services, I should be running in in SharePoint Integrated mode, right? That’s certainly the message that I have been delivering for quite some time. However, the game has changed significantly with SSRS 2016. Last October, Microsoft outlined their reporting roadmap, and that roadmap included a significant investment in SSRS. The roadmap was very clear as to Microsoft’s intentions.

“Reporting Services is our on-premises solution for BI report delivery”

Reporting Services is the solution, not SharePoint with Reporting Services, or PerformancePoint with Reporting Services, just Reporting Services. For several years now, the path to any new features in SSRS led through SharePoint Integrated mode. Features like Power View reports were only made available in Integrated mode as an example. While that was great for those invested in SharePoint, it presented an adoption issue for those that were not. This issue has prompted Microsoft to remove the SharePoint dependency, while still providing solid integration. In short, the goal for SSRS is to run with SharePoint, not on it.

In my opinion, this is all to the good. By making SharePoint integration pluggable, the product team can focus on one codebase instead of two, and spent more energy on features. This does however have some negative impact on administrators that will need to again manage two security models, but in the ideal world, it should be transparent to end users.

The immediate impact of this refocusing is that Native mode now receives new feature priority. If we look at the current state of SSRS 2016 (RC0 at the time of this writing), only a few of the major new features will be available in SharePoint integrated mode.

SSRS New Features

Native SharePoint Integrated
HTML 5 Based Rendering Engine

Customizable Parameters Pane

New UI for Report Builder

New Web Portal

Mobile Reports

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

KPIs

Pin to Power BI Dashboard

Render Power BI Desktop files

HTML 5 Based Rendering Engine

New UI for Report Builder

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

This new disparity is likely to leave some in the SharePoint world feeling left behind. The reality is that although this may seem like the case in the short term, Microsoft stated that “We will continue to support embedding of BI content into SharePoint”. For the record, that statement is open ended enough to include both SSRS and Power BI. The improvements to integrated mode in SharePoint 2016 are a testament to this support. It would have been just as easy to leave Integration mode in its previous state (like PerformancePoint). My view is that ultimately Native mode reports will work with SharePoint in much the same manner that current Integrated mode ones do. In fact, it’s possible to do some of this today – to embed Native mode SSRS Reports into SharePoint. That’s what the remainder of this article describes.

The ability to embed Native mode SSRS reports has actually been available since SharePoint 2003. It fell by the wayside after Integrated mode was introduced in SQL Server 2008 R2, but it has continued to be there. What is needed is a Native mode SSRS Server, and the Native mode SharePoint web parts.

Installing and Configuring SSRS Native Mode

Native mode SSRS is installed from the SQL Server media. It should be installed on a NON SharePoint server. Run the SQL Server installer, and eventually you will be taken to the feature selection screen.

Native mode SSRS installs as a SQL Server instance, and it is the only option necessary to install. The SharePoint add-in is only used for Integrated mode.

Once installed, it is necessary to run the Reporting Services configuration tool. The first step in configuration is to set up the web service URL.

Once the desired options are set, click Apply and the SSRS web service will be set up. Next, click on the Database node to configure the SSRS database. If the SQL Server database is installed on the same machine, you can use it, but you can use any SQL Server at your disposal. The only restriction is that the database engine must be at least the same edition level as SSRS (ie Standard vs Enterprise).

To create a new SSRS database, click the “Change Database” button and provide the database parameters.

Two databases will actually be created, one of them a Temp database. I recommend using the word “Native” or some other identifier in the name, particularly when both Native and SharePoint Integrated mode servers may be used. Complete the database creation process, and move to the Report Manager URL node.

Click Apply to create the SSRS Report Manager. The initial URL will always be based on the machine name, but once complete, you can click on the Advanced tab to add additional URLs. This is how you can add a Fully Qualified Domain Name (FQDN) to your SSRS server, which is strongly recommended if you will be integrating SSRS with Power BI. Power BI users will need to connect directly to the SSRS server to view SSRS reports, and this requires an FQDN.

There are other steps to be performed at this point, including Power BI integration and exporting the Encryption keys, but this is all that is necessary for basic configuration. You should now be able to navigate to your Report Manager URL and create reports. The next step is therefore to integrate them with Sharepoint.

Integrating with SharePoint

Native mode ships with a pair of web parts that allow SSRS web parts to be embedded into a SharePoint page. The web parts are embedded in an installable .cab file that can be found in the folder “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Reporting Services\SharePoint” where C: is the installation drive, and “130” is the major installation version for SQL Server (130, or 13.0 corresponds to SQL Server 2016). The name of the file is RSWebParts.cab. Copy this file to a SharePoint server in the farm, and from there, it can be installed from either PowerShell, or the gool ol’ STSADM command. With PowerShell, the command is:

Install-SPWebPartPack -LiteralPath “D:\Software\RSWebParts.cab” -GlobalInstall

Where “D:\Software” is the folder that the file was copied to. The corresponding STSADM command is:

STSADM.EXE -o addwppack -filename “D:\Software\RSWebParts.cab” -globalinstall

Unfortunately, I and many others have found that the version of the .CAB file distributed with SQL Server 2012 and above are incompatible with SharePoint 2013 and 2016 – the web parts fail to deploy. The good news (and the bad) is that the web parts are unchanged from SQL Server 2008 R2, and that version of the .CAB file will work with modern SharePoint. Of course, not everyone has a SQL Server 2008 R2 server lying around, so if you happen to need the file, I include it here:

RSWebParts.cab from SQL Server 2008 R2

Using the Web Parts

Once deployed, the two web parts, Report Viewer and Report Browser will appear under the miscellaneous section when a web part is inserted into a page. Report browser allows the browsing of reports on a server, and Report Viewer renders them. By connecting the two, it is possible to provide a highly interactive navigation of the report server right in a SharePoint server. However, editing the Report Viewer web part reveals that it is lacking some very fundamental capabilities.

Native Mode Web Part

Integrated Mode Web Part

The Native mode web part is missing all of the view control features that are available to the Integrated mode part, which means that when it comes to Native mode reports, you get what you get. However, more concerning is the fact that it is also missing parameters. There is no way to configure parameters for, or pass parameter values to Native mode reports embedded on a page.

Add to this limitation the fact that these web parts are approximately 10 years old – they were designed for SharePoint 2007. They are able to render the new chart types, but not through the new HTML renderer. These limitations make it very difficult to recommend their use, except in a few very specific scenarios.

Recommendations

So what is a Report driven SharePoint administrator to do? All of the cool new features are showing up in Native mode, but except in certain circumstances, there no really good way to embed those reports in Sharepoint pages. It seems a difficult question, but the reality is that these choices are not necessarily mutually exclusive. SSRS Integrated mode is getting many of the modernization improvements and continues to be a totally viable platform moving forward. If you want or need to take advantage of the new SSRS features like mobile reports, parameter pane customization, or Power BI integration, you can stand up a separate SSRS Native mode server, and even integrate it with SharePoint using the older web parts.

Taking this dual approach means that you’ll be well positioned to gradually move assets from Integrated mode to Native mode as the embedding story and capabilities improve.

Configuring SSRS 2016 Integrated Mode with SharePoint 2016

SQL Server Reporting Services (SSRS) has experienced some very significant improvements in the 2016 version. As has been the case Since SQL Server 2005 SP1, it runs in either Native, or SharePoint Integrated mode. Integrated mode (the subject of this article) requires SharePoint 2016, and it is required for SharePoint to be able to render Power View reports in a browser.  This article walks through the setup and configuration of SSRS 2016 Integrated mode in a SharePoint 2016 farm.

The process for setting up SSRS in Integrated mode is little changed with 2016. The process consists of installing the bits on the SharePoint server(s), creating and configuring the service applications, deploying the solution, and configuring document libraries to contain report elements.

Installing SSRS 2016 on Sharepoint Servers

When running in integrated mode, SSRS MUST be installed on a server that is part of the SharePoint farm. This only makes sense because it is deployed as a SharePoint Service Application. Unfortunately, the fact that is distributed as part of the SQL Server media causes confusion for some.

As of this writing, SSRS must be installed on a SharePoint 2016 that is configured in a Custom Role. MinRoles are new to SharePoint 2016, and SSRS does not support any other role than the Custom role. If your server is not running the Custom role, installation will succeed, but SSRS will be shut down by the roles engine during the next maintenance window. In order to check which role your server is using, and to possibly change it, you can use either PowerShell or Central Admin. With Central Admin, the setting is found in “System Settings”, under the “Servers” category as “Convert server role in this farm”.

Selecting this option opens the role configuration dialog, which is quite simple.

If the role is already set to Custom, you are good to go. Otherwise, it can be changed with the “New Role” drop down dialog.

Once the correct role is in place, SSRS can be installed. The first step is to mount the SQL Server media on a SharePoint server, and run the standard SQL Server installer. SSRS Integrated mode is part of the Shared Features collection (ie no SQL instances are installed), and it consists of two parts.

The first option, “Reporting Services – SharePoint” is the actual SSRS Service application. This should be installed on any SharePoint servers allocated to doing the heavy lifting of rendering reports – the “app” servers. The second option “Reporting Services Add-in …” is used to connect a SharePoint server to an instance of the SSRS Service application. This should be installed on any SharePoint front-end servers at a minimum, but I recommend installing it on all of them as a convenience.

After a few “Next”s and “OK”s, the SSRS bits should be installed on a server. The next step is to Create and configure the Shared Service Application itself.

Creating the SSRS Shared Service Application

Once the bits are installed, an SSRS Service application is created in the same manner as any other service application. From the Service Applications interface in Central Administration, select “New” from the ribbon, and then select “SQL Server Reporting Services Service Application”.

You will then be presented with a configuration dialog where you will need to specify a name for the service and a few other configuration parameters.

I typically use the same application pool as most of the other SharePoint services, and I always change the name of the database. The default database name contains a GUID, and nobody likes GUIDs in their database names. The SSRS will actually create 3 databases, one with the name specified, and two others that use this name as a base. Also, if you’ll be using other Reporting Services databases on the same SQL Server – for Native mode as an example, it’s a good idea to name it so that it’s easily distinguishable. In this example “Integrated” is added to the end.

Scrolling down, you’ll see options for activating the SSRS features in all of the farm’s site collections. The features can be activated from the site collections as well; this is simply a convenience.

Once saved, additional SSRS configuration items can be configured, and should be. At the very least, the subscription options should be configured, and the encryption key should be backed up, but these operations are not essential for basic setup, so they will not be done here. The next operation will be to enable a document library for SSRS reports.

Creating a Reporting Library

Enabling a document library in SharePoint for SSRS reports is unchanged from the past several versions. The first step is to add a new document library by going into “All Content” for a site, and selecting “Add an App”. You may be tempted to select “Reports” or “Report Document Library” at this point – don’t. The “Reports” library template that ships with SharePoint 2016 and prior contains content types for creating Excel documents in prior versions, web pages – that’s it. It has nothing to do with SSRS reports.

Select a Simple document library, give it a name (something like SSRS Reports, or SSRS library), and let it be created. Then, go into the library settings, click Advanced settings, and enable the use of content types. Next, add the SSRS content types to the library by clicking “Add from existing site content types”, selecting the “SQL Server Reporting Services Content Types” category, and then selecting “Data Connections” and “SSRS Report”. Unless you have a specific need, do not add the “Report Builder Model” content type. Models are a deprecated artifact and exist only for backward compatibility.

Once added, click OK, and you will be returned to library settings. At this point I like to remove the “Documents” content type from the list to restrict it to reports, but that will depend on your requirements. At this point you should be able to create a new report or data source by selecting new in the library’s ribbon and choosing the appropriate item. This library can now be used to store reports.

The final step is to enable and confirm support for Power View.

Power View Support

Power View support in SharePoint 2016 is provided through SSRS Integrated mode (and ONLY through SSRS Integrated mode). It is manifested in 3 different areas:

  1. Creating and viewing a standalone Power View report from a data connection
  2. Creating and viewing a standalone Power View report from an Excel workbook in a PowerPivot gallery
  3. Using a browser to view a Power View report contained in an Excel workbook

1. Creating and viewing a standalone Power View report from a data connection

Standalone Power View reports utilize BISM (BI Semantic Model) connections. BISM connections can be added to a SharePoint library by adding the “BI Semantic Model Connection” content type to the library – this would normally be done for a connections library. A BISM connection can also be created through an SSRS data source by selecting “Microsoft BI Semantic Model for Power View” as its data source type.

Creating a Power View report from either connection type follows the same process. In the library, click the ellipsis for the connection, and then the second ellipsis. From there, select “Create Power View Report”

Provided that Silverlight is available on the client, Power View should launch, and you should be able to build a report on the underlying data.

2. Creating and viewing a standalone Power View report from an Excel workbook in a PowerPivot gallery

Creating a Power View report is significantly simpler. Once SSRS is installed, it adds a small Power View icon to every workbook that is in a Power Pivot gallery.

Simply click on the icon, Power View will launch, and you can build a report on the data model that is contained in the workbook. There is however one additional step necessary for this to work. Because the data model is actually stored in the SSAS PowerPivot mode server(s), and it is SSRS (remember, Power View is part of SSRS) that is working with the model (not OOS), the service account for SSRS needs to be added to the Administrators list on the SSAS PP mode server(s). In our case, the service account is NAUTILUS\spServices.

3. Using a browser to view a Power View report contained in an Excel workbook

Power View reports that have been embedded in an Excel workbook require no additional configuration, they should “just work” once SSRS is configured. However, as with the PowerPivot gallery, SSRS needs access to the data models, and therefor its service account needs to be in the administrators list (see above).

Wrapping Up

Once installed and configured, you will have access to the new HTML5 based rendering engine and new visuals available to SSRS 2016. You will also be ale to work with your existing Power View investments. However, you will not be able to use the new mobile reports, Reporting Dashboards, Parameters customization, and Power BI integration. For that, you’ll need a Native mode SSRS instance, and yes, it can be connected to SharePoint. That will be the topic of an upcoming article.

Using PowerPivot for SharePoint with SharePoint 2016

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.