"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

Applies to: Excel 2013

Symptoms

Consider the following scenario:
  • You install Microsoft SharePoint Server 2010 Enterprise Edition together with Microsoft SQL Server 2008 R2 on a computer. Or, you install SharePoint Server 2010 Enterprise Edition together with SQL Server 2012 PowerPivot for SharePoint on a computer. 
  • You verify that the deployment is operational by uploading a PowerPivot workbook that is created by using Microsoft SQL Server 2008 R2 or by using SQL Server 2012 PowerPivot for Microsoft Excel 2010.
  • You use Microsoft Excel 2013 to create a workbook that uses an advanced data model.
  • You upload the workbook to the SharePoint Server 2010 farm and try to interact with the workbook in the browser.
In this scenario, Excel Services in Microsoft SharePoint Server 2010 does not load the advanced data model. Additionally, you receive the following error message: 
Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: ThisWorkbookDataModel.

Cause


This issue occurs because Excel 2013 uses a different method to maintain data models in workbooks than does SQL Server 2008 R2 or SQL Server 2012 PowerPivot for Excel 2010. Excel 2013 uses an internal connection for communication between the workbook and the embedded PowerPivot data. This internal connection does not reference a Microsoft OLE DB provider for Analysis Services (MSOLAP). Whereas PowerPivot for Excel 2010 uses an MSOLAP connection to load the embedded PowerPivot data model from a custom XML part in the workbook.

Excel Services and PowerPivot for SharePoint 2010 load PowerPivot data models from a custom XML part in the workbook. Therefore, this technology is incompatible with internal connections that do not reference an MSOLAP provider (such as the connections that are used by Excel 2013).

Note PowerPivot functionality is not supported by Excel 2013 workbooks that use data models in SharePoint Server 2010 environments.

Workaround


To work around this issue, upload the Excel 2013 workbook to a SharePoint Server 2013 farm.

Excel Services and PowerPivot for SharePoint 2013 load Excel 2013 workbooks that use advanced data models and PowerPivot workbooks that are created in PowerPivot for Excel 2010.