PowerPivot 2008 R2 and SharePoint 2010: We cannot locate a server to load the workbook Data Model

  • Article

Problem: Customer was experiencing the error below when trying to view a PowerPivot workbook in their SharePoint site. This was only occurring in their production farm.

I also found this error in the logs:

Troubleshooting:

  • verified they had the SQL Server analysis service instance enabled on the PowerPivot/SharePoint server (problem still occurred)
  • verified that their Excel Services account has the below permissions on the SQL Server Analysis Server (problem still occurred)

Resolution

When running the following commands to see the build level of SSAS, we found the problem:
$server = "serverInstance\PowerPivot"
$aso = New-Object Microsoft.AnalysisServices.Server
$aso.Connect($server)
Write-Host "The SSAS server version is: "$aso.Version

We saw that the SSAS (SQL Server Analysis Server) version is different in Staging than Production and it is working in Staging not in Production. The PowerPivot Add-in version is 11.0.3437 on the App servers in both Staging and Production. They had to update the SSAS instance on Production to the same (11.0.3437), it was previously at 11.0.3000.0