SQL 2016 PowerPivot Analysis Services default instance name failure

If you install SQL Analysis Services 2016 in SharePoint mode on the default instance of SQL Server and then pair it with SharePoint 2013 Excel Services or Office Online Server, no data models are created for any PowerPivot workbooks. This behavior occurs even if the entire configuration is correct. You don't experience any other symptoms and have no other issues in connecting to the instance.

This problem causes all actions against PowerPivot workbooks in the browser to fail after they are opened. These actions include, but are not limited to, slicing, interactive data refresh, and scheduled data refresh. Additionally, you can't use PowerPivot workbooks as data sources. This problem does not affect the refresh and interaction capabilities of regular Excel workbooks.

In this scenario, you typically receive the following error message:

Cannot locate a server to load the workbook data model
This is a known issue that's being investigated by our product teams. There is currently no target date for a fix . The following workaround is very simple and should not cause any issues in test or production environments.
To work around this issue, install PowerPivot Analysis Services on a named instance of SQL Server. The name of the instance is not relevant as long as it's compliant with the current naming limitations for all SQL named instances. Installing on a named instance causes no security issues with test or production deployments. After this issue is resolved, you can perform a side-by-side installation to change your configuration if you want to migrate your PowerPivot Analysis Services installation to the default instance.
PowerPivot Data Refresh fails; PowerPivot for SharePoint 2016; PowerPivot Analysis Services 2016; SQL 2016;
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Article ID: 3199726 - Last Review: 10/17/2016 21:27:00 - Revision: 3.0

Microsoft SharePoint Server 2016, Excel Services in SharePoint Server 2013, Microsoft SharePoint Server 2013, Microsoft Office Online Server, Microsoft SQL Server 2016 Enterprise

  • KB3199726