Last week, One of my customer had interesting issue with PowerPivot Workbooks. Here is scenario:
My customer has created a PowerPivot Model by using Excel 2013. As you may know, PowerPivot is now shipped with Excel 2013 and it is another addin shipped with Excel.
Please note that imagesbelow are illustrative images.
So Customer created very simple model
And also created Pivot Table in a workbook. Everything is working fine excel side.
As next step, we deployed our workbook to SharePoint PowerPivot Gallery and try to render workbook through sharepoint. When you first open workbook. You can see that Excel Services manages to render workbook properly. There is no problem so far. Because workbook didn't need to read data from source or doesn't need to refresh data.
So when you click one of drill down options or filter options, excel workbook needs to refresh data and also needs to load workbook into your powerpivot for SharePoint Instance. At this point we get 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.”
We couldn't find anything useful in ULS Logs. After further investigations, we have found out that 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.
Seems like only solution here to upgrade your sharePoint farm to 2013 farm or use excel 2010 to create your data models.
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.
If you want to read more about it, here is KB Article: