Getting a Power View report within Excel 2013 to work with SharePoint

  • Article

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:

image

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
https://support.microsoft.com/kb/2769345/EN-US

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

image

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:

image

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:

image

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

image

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

image

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