PowerView spreadsheet throws error when displayed Sharepoint

Question

I'm trying to access a PowerView spreadsheet in Sharepoint 2013 and I get the error below:

An error occurred while loading the model for the item or data source 'EntityDataSource'. Verify that the connection information is correct and that you have permissions to access the data source.

Here is what I have:

Server 1 - Sharepoint 2013 Server, with RS for Sharepoint Add-in installed, Analysis services feature installed

Server 2 - Contains the SQL Server installation which is used for holding the CRM 2013 databases as well as Sharepoint 2013 Config and Content databases.

• I have created a Power View report in Excel 2013, using the OData
  feed from my CRM 2013 organization. I saved this as .xlsx file and
  uploaded to my Sharepoint site as a normal document.

• I have created an SSRS Application in Sharepoint Central Administration and configured it to run under a service account

• I have added the following 'Servers' under my Excel Application -> Data Model settings in Sharepoint

  SQLServerName

  SQLServerName\SQLInstanceName

  SharepointServerName

  SharepointServerName\SQLInstanceName

• I have enabled the PowerView Integration Feature, Report Server Integration Feature in my sharepoint site collection features.

I am trying to browse to my sharepoint site and click to open the .xlsx file and I get this error.

Below is the detailed error message:

Cannot create a connection to data source 'EntityDataSource'.Microsoft.AnalysisServices.SPClient We cannot locate a server to load the workbook Data Model. We cannot locate a server to load the workbook Data Model.Microsoft.Office.Excel.Server.WebServicesWe cannot locate a server to load the workbook Data Model.

Answer 1

Thank you @mmarie for the answers/comments.

I was able to partially resolve the issue by 're-installing' the Analysis services on my Sharepoint server. So it turns out I installed it the wrong way first. Followed this article to fix it.

Analysis services for SP 2013

I'm currently experiencing problem with the 'Data Refresh' within the Power Pivot/Power View reports. I have added my service account to the '"Act as part of the operating system" role on the Sharepoint server based on this article Data Refresh, however it did not resolve the issue.

Please let me know if anyone has any inputs.

Answer 2

This question has been asked a couple times on the SharePoint SE site.

There is an MS support article from Microsoft that addresses this error. It suggests you do the following:

1. In the Application Management section of the Central Administration home page, click Manage service applications.
2. On the Manage Service Applications page, click the Excel Services service application that you want to configure.
3. On the Manage Excel Services page, click Data Model. Click Add Server.
4. In the Server Name box, type the name of the Analysis Services instance that you want to add.
5. Click OK.

If that doesn't work, someone solved this problem by installing SQL Server 2012 SP1.

Someone else solved this issue by ensuring that the account used for Excel Services is an SSAS admin and then rebooting