Thursday, January 19, 2017

SharePoint 2013 PowerPivot workbooks–Resolving error “We cannot locate a server to load the workbook data model”

I have a collection of PowerPivot Excel workbooks in a SharePoint 2013 farm. I had configured everything including a SQL Server Analysis Services (SSAS) server in PowerPivot mode and SQL Server PowerPivot Service Application.

Suddenly filters and sliders in my all PowerPivot reports stopped working throwing following error message.

          1

Then I checked ULS logs, which showed me the cause of the error

“Uncaught CLR exception crossing the Interop boundary: Microsoft.AnalysisServices.Streaming.ServerNotFoundException: There are no servers available or actively being initialized.   
at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.GetAvailableServers()”
   

That means, the Excel Services cannot locate my SSAS server. Following are the diagnostics I did

  • Check if the SSAS server is up – Success
  • Ping the SSAS server from SharePoint serve and vice versa – Success
  • Check if my SSAS PowerPivot instance is listed in Excel Services –> Data Model settings – Success
  • Check if relevant services are running from SQL Server Configuration Manager – Success
  • Check if firewall is enabled or ports are blocked – Gotcha !!

My domain firewall is enabled after a restart. Initially I kept it disabled to make things simple. But it seams that I need a concrete solution.

Following are the steps I used to open required ports

1. What are the ports need to be open?

As a practice I will open 2383, 2382 ports for a SSAS server. Since we have a named instance <SSAS Server>/PowerPivot, we need to open the custom port used by that instance.

2. How to find custom port for named SSAS instance

  • Navigate to “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig”
  • Open msmdredir.ini in text editor
  • Get the port specified in that file

         image

3. Configure a firewall rule

  • Open Windows Firewall with Advanced Security
  • Create a new Inbound rule
  • Specify following ports to open

           image

That fixed the issue.