Error in the data model when refreshing external data in Excel

Question

I have a BI Center site, I'm trying to open an Excel sheet in the PivotView gallery but it gives me this error:

Workbook cannot be opened

I have uploaded the Excel sheet in another library but it still gives the same error message.

Also when I open the Excel sheet using client application it works fine.

How do I fix this?

UPDATE:

I'm using the same excel sheet again. I'm fetching data from an SP list using Odata feed connection; http://sharepointsite/_vti_bin/listdata.svc In the connection properties I set it to "None" so that it uses SSS

In central Admin > Application Management > Manage Service Application > Secure Store Service, I have created a new targetd application ID, members: domain users and set the credintials to an account that has access on the list data (has access on sharepoint site in general) then I upload the excel to sharepoint to open it in web view, I refresh the data connection to fetch new data but I get this error everytime:

External Data Refresh Failed: An error occurred while working on the Data Model in the workbook. Please try again.

We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:

DataFeed connection_name listdata

ULS Logs:

External Data ajeso Information Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '', has been specified but has not been used. The following system error occurred: Class not registered The provider 'Microsoft.Mashup.OleDb.1' is not registered. , ConnectionName: ThisWorkbookDataModel, Workbook: Book1.xlsx.

Another one:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '', has been specified but has not been used. Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: The remote name could not be resolved: 'sharepointsite'. , ConnectionName: ThisWorkbookDataModel, Workbook: Book2.xlsx.

Answer 1

To promote the answer for others, as this was solved in the comment section of the question:

The The workbook cannot be opened -error is supposedly caused because the account running the Excel Services at Central Administration doesn't have the proper permissions to the Content Database.

The appropriate rights can be granted with the following commands:

$w = Get-SPWebApplication –Identity <URL of the Web application>
$w.GrantAccessToProcessIdentity("<domain\ServiceAccount>")

Source including the full explanation.

UPDATE:

This MSDN article suggests the issue is on the privileges of the service account running the Analysis Services (See the part "Windows Authentication with Workbook Data Connections and Interactive Data Refresh").

Assuming the environment is running on SQL 2012 (if not, change the SQL version to the one you have at the beginning of the article), briefly these are the steps to add the required privileges:

1. On the Analysis Services Server running in SharePoint mode, Add the Analysis Services service account to the "Act as part of the operating system" privilege:
   1. Run secpol.msc
   2. Click "Local Security Policy", then click "Local policies", and then click "User rights assignment".
   3. Add the service account.
2. Restart Excel Services and reboot the Analysis Services server.
3. Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary. If the backend data source is on the same server as the Analysis Services instance, Kerberos Constrained Delegation is not required. However, the Analysis Services service account requires the right to Act As Part Of The Operating System.