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.
Hi John,
Thanks for all of your posts. I’ve really been enjoying them.
One question. Is it possible to run Office Online Server (OOS) without SharePoint? I’m wondering about the scenario where all I want is web-based Excel and nothing else.
Tim
My comment subscription email expired so I’m commenting again to get another email.
[…] SharePoint 2016 BI Capabilities – http://whitepages.azurewebsites.net/2016/03/business-intelligence-sharepoint-2016/ […]
I am having a heck of a time determining if I need to install SQL Server on the Sharepoint server to get my SSRS reports to work. Is this true?
The reason is I do not see the Reporting service option in General Application Settings and I have the Reporting Service Add In installed.
-Chris
[…] Tips and Tricks Custom visuals gallery for Power BI John’s what you need post for BI in SharePoint […]