Manage Learn to apply best practices and optimize your operations.

How to restore SharePoint business intelligence features

Microsoft removed business intelligence features from SharePoint 2016, and only an upgrade to two other platforms allows IT pros to get them back. Here's how.

In a surprising move, Microsoft pulled various business intelligence capabilities from SharePoint 2016, and the...

only way to restore some of these features is by upgrading to Office Online Server and SQL Server 2016.

It's worth noting that SharePoint Online and Power BI are two completely different apps within Office 365. The SharePoint business intelligence capabilities stem largely from Excel, which is able to gather, visualize, filter and analyze data. SharePoint, in turn, allows for workbook sharing and enables users to view and interact with Excel workbooks through a browser. The Power BI app provides greater business intelligence functionality than Excel or SharePoint Online, but because it's able to publish reports to the web, Power BI reports are sometimes displayed within SharePoint. The roles played by Excel, SharePoint, and Power BI are illustrated on Office.com.

One change Microsoft recently made was to remove the Excel Services functionality from SharePoint 2016. The rationale for doing so was that the Excel Services functionality has been integrated into Excel Online, which is part of Office Online Server, the follow-on version of Office Web Apps Server.

Microsoft advises customers that use the Excel Services feature in SharePoint 2013 to deploy Office Online Server. Doing so provides a workaround, making Excel Services functionality available. Specifically, Excel Online is required for the following functionality:

  • Viewing or editing Excel workbooks from within a browser.
  • Using SharePoint's Excel Web Access Web Part.
  • Providing Office Data Connection file support.
  • Programmatically accessing Excel through JavaScript OM or user-defined function assemblies.
  • Supporting SOAP and REST protocols.

Although Excel Online is essential to regain SharePoint business intelligence capabilities, simply deploying Office Online Server with Excel Online isn't enough. You will need to configure SharePoint to use Excel Online. This is a four-step process.

Steps to regaining SharePoint business intelligence capabilities

The first step in this process is to create a binding between SharePoint and Office Online Server. To do so, you will need to launch the SharePoint 2016 Management Shell, using the "Run as Administrator" option. Upon doing so, enter the following command:

New-SPWOPIBinding -ServerName <the fully qualified domain name that you are using as an internal URL>

The second step in the process involves verifying that Office Online Server is configured to use the correct zone to communicate with SharePoint 2016. In production environments, Office Online Server should typically be configured to use the Internal-HTTPS zone. You can verify the zone with the following command:

Get-SPWOPIZone

The third step in the process is to change the zone name, if necessary. The command is:

Set-SPWOPIZone -zone "internal-https"

Other valid zone names include:

  • Internal-http
  • External-https
  • External-http

The last step in the process is to enable the Excel SOAP API. The commands are:

$Farm = Get-SPFarm

$Farm.Properties.Add("WopiLegacySoapSupport", "<the URL of your Office Online server farm>/x/_vti_bin/ExcelServiceInternal.asmx");

$Farm.Update();

When you test to make sure Excel Online works, it is important to make sure that you are not logged in using a System Account. Otherwise, some of the Excel Online functionality will be disabled as a security measure. The easiest way to test Excel Online is to log in as a standard SharePoint user, and try to open an Excel document that is stored in a SharePoint document library.

Note, too, that SharePoint Online and Power BI are two different apps within Office 365. The SharePoint business intelligence capabilities stem from Excel, which can gather, visualize, filter and analyze data. SharePoint, in turn, enables workbook sharing and allows users to view and interact with Excel workbooks through a browser. The Power BI app provides greater business intelligence functionality than Excel or SharePoint Online, but because it can publish to the web, Power BI reports are sometimes displayed within SharePoint.

Unfortunately, not all SharePoint business intelligence capabilities can be regained simply by deploying Office Online Server. Recovering some of SharePoint 2016's business intelligence tools requires an upgrade to SQL Server 2016. That's because SQL Server 2014 Power Pivot and Power View add-ins for SharePoint 2016 can't be deployed or even used in SharePoint Server 2016, according to Microsoft. Upgrading to SQL Server 2016 enables the use of the following business intelligence features:

  • Power Pivot Gallery
  • Scheduled Data Refresh
  • Power View Reports (this applies to those that are either standalone or embedded in an Excel workbook)
  • Power View Subscriptions
  • Report Alerting
  • Power Pivot Management Dashboard
  • BISM Link Support

Upgrading to SQL Server 2016 also allows SharePoint users to use another workbook's data model as a data source.

Although SharePoint business intelligence capabilities are missing, they can be reinstated by deploying Office Online Server with Excel Online and SQL Server 2016. It is worth noting, however, that Microsoft plans to deprecate several of the Excel-related features. These features and capabilities still exist (following the deployment of Excel Online and SQL Server 2016), but will likely be removed from the next version. As such, it is a good idea to begin phasing out use of such functionality.

Microsoft will depreciate the following features:

  • Trusted data provider
  • Trusted file locations
  • Trusted data connection libraries
  • Unattended service accounts
  • Excel Services-related Windows PowerShell cmdlets
  • Opening Excel workbooks from the SharePoint Central Administration site

Next Steps

SharePoint 2013 vs. SharePoint 2016: What's the difference?

Features that won't make it to SharePoint 2016

Microsoft expands e-discovery in SharePoint 2016

This was last published in June 2016

Dig Deeper on Enterprise SharePoint strategy

PRO+

Content

Find more PRO+ content and other member only offers, here.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

Will you upgrade to Office Online Server and SQL Server 2016 to restore SharePoint BI features?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataManagement

SearchERP

SearchOracle

SearchSAP

SearchSQLServer

Close