When opening a workbook after a Schedule Data Refresh, the workbook does not open in Excel Services:
This is because after a Scheduled Data Refresh, the “Refresh data when opening the file” is checked. This is by design, because after a Scheduled Data Refresh, the workbook’s Data Model is updated with new information and the workbook is re-published to the SharePoint Library. To ensure that you are getting fresh data in the browser, the “Refresh data when opening the file” is checked. (I will get to the reason why it is spinning later under the Cause section).
If we uncheck “Refresh data when opening the file” and open the workbook in the browser, the workbook opens, but if we click on a slicer (a workbook refresh is triggered), the error “Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. Thefollowing connections failed to refresh: PowerPivot Data” is thrown:
This is caused by a Bug. When the Scheduled Data Refresh runs, a Process cube is created under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases. The Process cube should be deleted, but it is not. Therefore many (not sure I can call them “duplicate” since they have a unique GUID so I will use the term “multiple”) “multiple” Data Models are created and not deleted. When the workbook is refreshed, the workbook looks for the Data Model associated with that workbook under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases. As you can see there are many “multiple” Data Models and the workbook refresh either hangs (spinning when using “Refresh data when opening the file”) or fails when clicking on a slicer (error “Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data).
To resolve this, you will need to upgrade all servers running PowerPivot to SQL SP1 (11.0.3000.0) and apply the Cumulative Update 4.
Microsoft® SQL Server® 2012 Service Pack 1 (SP1)http://www.microsoft.com/en-us/download/details.aspx?id=35575
Cumulative update package 4 for SQL Server 2012 SP1 http://support.microsoft.com/kb/2833645/en-us
FIX: PowerPivot database is not deleted from SSAS memory when PowerPivot database processing failshttp://support.microsoft.com/kb/2820918
The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was releasedhttp://support.microsoft.com/kb/2772858
hi, IT told me that they were running sharepoint 2008 R2 and that they had no planned update scheduled due to cost and user issues. but they said that they were going to install powerpivot 2012 with SQL Reporting Services 2012. will that solve issue?
SharePoint 2010 with farm SQL 2008 r2 + PowerPivot sql 2012 services. Refresh history page shows all success but refresh data is not reflecting and getting same error message when refreshing from connection (PP gallery).
Please help here how to resolve the issue. I am not finding any SP error logs
RODCOT: Installing PowerPivot 2012 will also resolve this issue.