CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

Rate This
  • Comments 5

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
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.

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
http://twitter.com/awsaxton

Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
  • Hi Adam,

    I tried this solution but this didn't work for me :(

    Can you suggest some other solution??

  • I had the same problem but de solution above did not work for me. I solved the problem reconfiguring the Excel Service Application Target Id of the Secure Store Service and refreshing the key of the service.

  • Hi Adam,

    I had a similar trouble and yo solution work perfectly !

    Thanks a lot

  • Excellent ! Your solution worked perfectly in my case. Big Thanks!

  • Great stuff! It worked! The issue is I had to circumvent that dreadful 'Cannot show requested dialog' bug when opening Analysis Services properties in SQL Management Studio, though (which is happening in a SQL 2012 w/ SP1, despite Microsoft saying it has been fxed :) )

Page 1 of 1 (5 items)