Condividi tramite

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

  • Articolo

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

Comments

  • Anonymous
    May 15, 2013
    Hi Adam, I tried this solution but this didn't work for me :( Can you suggest some other solution??

  • Anonymous
    October 16, 2013
    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.

  • Anonymous
    December 10, 2013
    Hi Adam, I had a similar trouble and yo solution work perfectly ! Thanks a lot

  • Anonymous
    May 04, 2014
    Excellent ! Your solution worked perfectly in my case. Big Thanks!

  • Anonymous
    June 19, 2014
    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 :) )

  • Anonymous
    August 26, 2016
    Thats fascinating...

  • Anonymous
    September 04, 2016
    Its magnificent :)