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:

  1. Match SharePoint version with the Office edition. If you need to support Excel 2013 Power Pivot workbooks, upgrade to SharePoint 2013.
  2. 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.

Presenting Visualization Options in Microsoft BI

If you have some time amidst the holiday rush, join me on Monday, December 9th, at the Atlanta BI meeting, where I’ll present Visualization Options in Microsoft BI.

“Confused about visualization choices for your BI solution? How should you design a dashboard to impress your management? Don’t know what presentation options exist for organizational and self-service BI? Join us to find answers to these questions and share best practices about the visualization tools in the Microsoft BI platform.”

Our sponsor will be Computer Associates and you’ll learn about the latest Erwin release.

Also, don’t forget about the user group community party on December 16th.