SQL 2016 PowerPivot Analysis Services Fails on Default Instance


Scenario:

You are installing PowerPivot for SharePoint 2016 (or 2013) using SQL 2016 and you notice that you are no-longer restricted to having the instance name "POWERPIVOT" to make it function. As such, you decide to install PowerPivot Analysis Services on the default instance for the sake of simplicity. You continue with your Office Online Server and SharePoint configuration and proceed with testing your first PowerPivot workbook. The workbook opens without issues, but as soon as you attempt to interact with the PowerPivot workbook, you receive the following error: "Cannot locate a server to load the workbook data model".

Problem:

At the moment, there is a known issue causing issues with SSPM streaming between OOS/Excel Services and PowerPivot when it is installed on the default instance. There is no known fix date for this issue.

You will notice a few symptoms that are user facing and on the back end. First, as stated above you will be unable to refresh (browser or scheduled) or interact with Excel workbooks that contain PowerPivot data models. On the back end, you will notice that no data models are being created inside of the PowerPivot Analysis Services instance, but all connectivity to the instance will seem without flaw as you will have no issues connecting with SQL Management Studio or PowerShell.

Workaround:

For now, you will be required to install PowerPivot Analysis Services on a named instance of SQL. The name of the instance is not relevant as long as it is within the constraints of the current naming limitations for all SQL named instances. Installing on a named instance causes no security issues with test or production deployments. When this issue is resolved, if you wish to migrate your PowerPivot Analysis Services installation to the default instance, you can do a side-by-side installation change your configuration at that time.

Update: Official KB Published: https://support.microsoft.com/en-us/kb/3199726


Comments (0)

Skip to main content