Excel Services & PowerPivot for SharePoint

Troubleshooting Excel Services and PowerPivot for SharePoint rendering and refreshing in the browser.

PowerPivot for SharePoint 2010 & SQL 2012 - Refresh in browser fails due to multiple "Process" Data Models in Analysis Services.

PowerPivot for SharePoint 2010 & SQL 2012 - Refresh in browser fails due to multiple "Process" Data Models in Analysis Services.

  • Comments 2
  • Likes

 

 

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.  The
following 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 fails
http://support.microsoft.com/kb/2820918

The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
http://support.microsoft.com/kb/2772858

Comments
  • 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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment