PivotTable Operation Failed: We can not locate a server to load the workbook Data model.

Hi All,

I have SP2013 and PowerPivot feature enabled in BI site collection. I am trying to connect to SQL Server 2012 SP1 and SSAS cube. There is no problem during connection. But while trying to refresh/using the slicer I am getting the following error.

Appreciate if you can help or lead to fix the issue.

Regards

Sakti Mohapatra

May 18th, 2015 6:08pm

Hi Sakti,

For this issue, I recommend to add the server name of the Analysis Services instance to the Data Model in Excel Service Application.

If above cannot work, please restart SQL Server Analysis Services (POWERPIVOT) service on SQL Server Configuration Manager.

For more details, please refer to the link below:

http://www.sharepointpitstop.com/2013/10/we-cannot-locate-server-to-load.html

Thanks,

Victoria

Free Windows Admin Tool Kit Click here and download it now
May 19th, 2015 9:05am

Hi Victoria,

Excel Service Data model had the details of Analysis service instance .i.e. <ServerName>\POWERPIVOT.

As per your suggestion, I restarted the SQL Server Analysis Services(POWERPIVOT) on SQL Server Configuration Manager.

But it did not work.

Should I see the instance of "SQL Server Analysis Services(POWERPIVOT)" in SQL Server 2012? Please let me kow.

Regards

Sakti

May 19th, 2015 1:03pm

Hi Sakti,

Please check if the Excel Service account has the right permission on the Analysis Services instance and please do an IISReset to see how it works.

Thanks,

Victoria

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 1:19am

Hi Victoria,

Excel Service account has the right permission on the Analysis Services instance and I did an IISReset and it did not work.

Regards

Sakti

May 20th, 2015 12:09pm

Hi Sakti,

Did you use Office Web Apps(OWA) for viewing the PowerPivot workbook?

If yes, then you need to set OWA to support the workbooks in SharePoint.

http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/01/31/powerpivot-for-sharepoint-browser-refresh-fails-data-refresh-not-supported-in-office-web-apps.aspx

If your SharePoint environment is configured to use Kerberos, you need to modify the C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\PowerPivot\Web.config file.

Change all instances of <transport clientCredentialType="Ntlm"/> to <transport clientCredentialType="Windows"/>.

Please also check the ULS log file, after you configure the Excel Service Application for verbose logging, in the Diagnostic logging section of SharePoint Central Administration.

Thanks,

Victoria

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 1:30am

Hi Victoria,

Thanks for the reply. The last thing what I did was reboot the SP server and not getting the error any more.

Though power pivot started working, still I did not get my answer what was the FIX to the issue.

I think Microsoft should have a guide line which should cover all sorts of error one can come across and the steps to fix it.

For fixing issue,I had already suppress OWA from handling the .xlsx file type and force SharePoint to use SharePoint view mode and SharePoint environment is configured to use Claim based authentication.

Regards

Sakti

May 21st, 2015 8:52am

Hi Sakti,

I am glad that your issue is resolved.

To know more regarding the root cause of this issue, we need to capture verbose log, fiddler log for SharePoint and SQL server.

Unfortunately, forum support has limited support and resources regarding debugging request, if you would like to debug, I recommend you to submit a case to our premier support where our engineer can remote help you directly.

Thanks,

Victoria

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 10:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics