Getting a Power View report within Excel 2013 to work with SharePoint
Published Jan 15 2019 02:57 PM 141 Views
Microsoft
First published on MSDN on Jan 09, 2013

I was setting up my SharePoint 2013 server to be able to use an Excel 2013 workbook that had a Power View Report in it.  However, when I tried opening the workbook, I got the following error:

In the ULS logs of my SharePoint server that had RS installed on it, I saw the following:

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EntityDataSource'. ---> NoAvailableStreamingServerException: We cannot locate a server to load the workbook Data Model. ---> Microsoft.AnalysisServices.SPClient.Interfaces.ExcelServicesException: We cannot locate a server to load the workbook Data Model. ---> Microsoft.Office.Excel.Server.WebServices.ExcelServerApiException: We cannot locate a server to load the workbook Data Model.

I have a separate SharePoint App Server that has Excel Services setup.  In the ULS Log on that box, I saw the following:

01/09/2013 08:47:09.45         w3wp.exe (0x04CC)         0x0C2C        Excel Services Application         Data Model         27        Medium         SSPM: Initialization failed on server DRBALTAR\PowerPivot: Microsoft.AnalysisServices.ConnectionException: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.0.0.11:2382

In my configuration, I installed the Analysis Services Server on a separate server which is allowed with SharePoint 2013 and SQL 2012 SP1 for a PowerPivot Deployment.  Before I dug in a little more, I did a quick search to see what I could find for the actual message “We cannot locate a server to load the workbook Data Model”.  I ended up finding the following KB Article:

"We cannot locate a server to load the workbook Data Model" error on a SharePoint site when you refresh a PivotTable in an Excel 2013 workbook
http://support.microsoft.com/kb/2769345/EN-US

I already knew that I had a server defined for the Data Model settings within Excel Services.

Of note, the ConnectionException above is actually pretty descriptive.  When I installed the AS Instance (PowerPivot) on DrBaltar, I had opened up the Firewall for the instance itself, but I hadn’t opened up SQL Browser.  After opening SQL Browser through the firewall on DrBaltar, I still got a failure. However, now the message was different from within Power View:

Here was the message in the ULS Log on my first SharePoint Server:

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EntityDataSource'. ---> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: SetAuthContext need to be run as sysadmin.
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()

Looking at a Profiler trace we can see a telling clue:

I hadn’t provided Admin rights to the RSService Account for the Analysis Services Instance.

After adding the RS Service Account, the Power View sheet that was in the Excel 2013 Workbook came up within SharePoint 2013.

Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton

Version history
Last update:
‎Jan 15 2019 02:57 PM
Updated by: