Not able to refresh External data in Excel SharePoint 2010

Question

I am not able to refresh the data in Excel when to see the excel report in a browser. When I press Refresh All options the page gets refresh but no update in data is reflected.

Actually, I have two versions of SQL Server installed on my machine

1. Sql Server 2008 R2 (Standard Edition) for SSRS

2. SQL Server 2012 R2 (Enterprise Edition) for PowerPivot and PowerView

I think the issue is whenever excel services try to refresh the data they search for Old assemblies of Analysis service but it was overwritten by New assemblies while installing SQL server 2012 R2.

I have created whole Excel service application but issue is still there.

Also, I am not able to publish the workbook in SharePoint library with authenticated users credential, the workbook only publishes with Admin's credential. Is there any group where I need to add authenticated user account.

Please tell me how to come out of these issues.

Thanks in advance

Answer 1

What are the actual errors you are seeing?

These are things I've encountered that fix the majority of PowerPivot and Excel Workbook refresh issues.

• If you are using SQL 2012 PowerPivot SSAS for SharePoint - make sure you upgrade the Excel Plugin (if using Excel 2010) and be sure the workbook is upgraded.
• Where is the Data Source you are refreshing? If it is external to the SSAS instance, you can only use your credentials if you have configured the SSAS service account with an SPN and enabled delgation to those data sources (they must be in same domain) or use a SSS account or default account.
• In your workbook try setting authentication of the data source to "none", this will force the refresh to use the Excel Service account to connect to data source (make sure it has permissions to it. )
• Change the connection provider from SQL Native Client to SQL OLEDB provider
• Make sure PowerPivot account has access to the library where the workbooks are saved (this is for scheduled refreshes, manual refreshes don't save the workbooks).
• Check your SSAS permission, Excel account needs to be an Administrator of the SSAS instance.

As far as users not being able to publish workbooks, that just sounds like a permission problem on your library.

• Open your library, in settings look at the permissions
• If inheritance is broken than make sure your users have at least the "contribute" permissions assigned or are members of a group that have those level of permissions.
• If inheritance is not broken, then just move up to your site permission and do the same check