SSAS & SharePoint 2013: We cannot locate a server to load the workbook Data Model

After recently walking through a SharePoint 2013 BI installation following the instructions on MSDN, I ran into an issue actually uploading and viewing Excel files that had Power Views built into them.

Specifically, I was encountering the following error:

We cannot locate a server to load the workbook Data Model

Now if you head to Google to look up this error, you’ll stumble upon this technet article: http://support.microsoft.com/kb/2769345/en-us

Basically, the fix is to ensure that the Analysis Services instance has been configured in Central Admin. However, in my particular case, the instance was there, and if I attempted to connect to it using management studio, there was no issue.

ExcelDataModel

I also verified that within the instance settings, the service account I had specified for the Excel Service Application had the correct permissions.

ExcelSQLSSAS

So what was causing the issue?? I started digging into the logs a bit deeper and utilizing the ULS Log Viewer, I was able to spot the following in the logs:

SSPM: Initialization failed on server BNSSQL\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.1.2:2382

Please note that I specifically set a filter on:

  • Product: Excel Services Application
  • Category: Data Model

So it would seem that I needed to open port 2382 on SQL Server for SQL Browser to work successfully as per this MSDN article.

Client connection requests for a named instance of Analysis Services that do not specify a port number are directed to port 2382, the port on which SQL Server Browser listens. SQL Server Browser then redirects the request to the port that the named instance uses.

After doing that, I stopped and started Excel Services on the server, and ran through the logs again. This time, I got past the first issue, and was now seeing:

SSPM: Initialization failed on server BNSSQL\POWERPIVOT: Microsoft.AnalysisServices.ConnectionException: A connection cannot be made. Ensure that the server 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.1.2:49165.

Now I can’t find anything detailing that I need to open this port and why. So I went ahead and opened this port, and after restarting Excel Services, I was able to successfully establish a link and my Excel Power Views are now working successfully.

ExcelSuccessfulConnection
Hope that helps someone else out there. If you know of why 41965 needs to be opened, I’d love to hear it.

, , ,

One Response to “SSAS & SharePoint 2013: We cannot locate a server to load the workbook Data Model”

  1. Denta Arief March 25, 2014 at 2:33 pm #

    Did you know how to fix it ? i am currently stuck in this issue…
    hope you can give me a clue…
    Thanx’s before..

Leave a Reply