Tuesday, October 29, 2013

PivotTable Operation Failed: We cannot locate a server to load the workbook Data Model

While creating a demo with PowerPivot (Excel Services) on SharePoint 2013, I came across this error.

PivotTable Operation Failed: We cannot locate a server to load the workbook Data Model


Cause

The Analysis Services instance has not been configured in the Central Administration site or Analysis Services needs to be restarted

Resolution

Solution 1

You can also make sure that the steps displayed here are followed. In my case this was already done. But I am still listing it down.To work around this issue, configure the Excel Services service application on the SharePoint server. To do this, follow these steps:

1. In the Application Management section of the Central Administration home page, click Manage service applications.
2. On the Manage Service Applications page, click the Excel Services service application that you want to configure.
3. On the Manage Excel Services page, click Data Model.
4. Click Add Server.
5. In the Server Name box, type the name of the Analysis Services instance that you want to add.
6. Click Ok.

Solution 2

I restarted the SQL Server Analysis Services (POWERPIVOT) service on SQL Server Configuration Manager. That fixed the issue for me. If that didn’t help try an IISRESET.

2 comments:

  1. This is happening to me daily. I restart the service then it stops working.

    ReplyDelete
  2. anyone knows the root cause by now? and a way to fix it permanently?

    ReplyDelete