SharePoint 2010 and Excel 2013 Power Pivot
I’ve start seeing customers moving to or considering Office 2013 for self-service BI. Naturally, the end users would like to share their Excel 2013 self-service data models by deploying to SharePoint. Does SharePoint 2010 support this scenario? Unfortunately not. The user can upload Excel 2013 files and view the embedded reports. However, when he attempts to perform an interactive action (triggers all backend services involved in processing Power Pivot workbooks) he gets the dreaded error “Unable to refresh data for a data connection in the workbook”. What’s wrong in this case is that due to the Excel 2013 changes to support Power Pivot natively, Excel Services must load the data model in a different way. This is discussed in more details in the “‘Unable to refresh data for a data connection in the workbook’ error when you interact with an Excel 2013 workbook in a SharePoint Server 2010 farm” article by Microsoft. Note that the only resolution path is to upgrade to Share Point 2013.
Again, the issue is with Excel Services and not with the Power Pivot version. If the user has Excel 2010 with the latest Power Pivot bits (SQL Server 2012 SP1 version of Power Pivot), then the SharePoint 2010 integration works. To make the upgrade story short:
- Match SharePoint version with the Office edition. If you need to support Excel 2013 Power Pivot workbooks, upgrade to SharePoint 2013.
- The SQL Server instance behind Power Pivot for SharePoint must match the Power Pivot version on the client. For example, if the users have Excel 2010 with the SQL Server 2012 SP1 version of Power Pivot, the SQL Server instance installed on the SharePoint server should be SQL Server 2012 SP1. SQL Server 2008 R2 won’t be able to handle the latest Power Pivot bits.
This is definitely something to watch and plan for when considering upgrading to Office 2013.
