Issue
As we all know :) with SharePoint 2013, manual data refresh in the browser (Data > Refresh All Connections) for PowerPivot workbooks will refresh data all the way back from the original PowerPivot data source, which is specified in the PowerPivot workbook's PowerPivot connection. This functionality is different with SharePoint 2010, where manual data refresh in the browser (Data > Refresh All Connections) will refresh data only from the cube (in its current state) that exists for that PowerPivot workbook in the PowerPivot instance of Analysis Services.
The Issue: With SharePoint 2013, we have found that a PowerPivot workbook with a PowerPivot connection to a SharePoint list cannot be refreshed manually in the browser (Data > Refresh All Connections) using Windows authentication in SharePoint 2013.
Many have tried to get this refresh functionality working using a number of different Service Principle Name and constrained delegation settings and configurations, and so far efforts have been unsuccessful with getting delegation to work.
Workaround
In this scenario, the workaround is to use a Secure Store ID for the workbook list connection, instead of using Windows authentication.
Of course, the SSS ID (mine was PowerPivotUnattendedAccount) will need to have credentials set for it that have permissions to the SharePoint list that is being refreshed from.
Issue:
Users server has SQL Server 2014 version of PowerPivot
User creates a PowerPivot workbook with Excel, and saves it to SharePoint.
User creates a .bism file that points to the PowerPivot workbook and saves the .bism file to SharePoint.
User creates second workbook that uses the .bism file to point to the first PowerPivot workbook as a data source.
User views the workbook with Excel Services and tries to slice the data.
User gets error "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh."
There is a software defect that prevents .bism files from working for this use case.
Workaround:
Instead of creating a .bism file, create a .odc file and use that.
How to create .odc file and data connection library
http://msdn.microsoft.com/en-us/library/office/ms772101(v=office.14).aspx
You may see a "Call to Excel Services failed" error when trying to perform a scheduled data refresh using PowerPivot for SharePoint on a workbook in SharePoint 2013 that has a Power Query connection to a data source. At this time, Power Query is not supported by SharePoint 2013 on-premise. It is only supported by Power BI for Office 365 (SharePoint Online).
http://blogs.msdn.com/b/powerbi/archive/2014/05/02/scheduled-data-refresh-for-power-query.aspx
As a workaround, you can connect to the data source without using Power Query by creating a data connection using PowerPivot. To determine whether or not the workbook currently has a Power Query data connection, look at the connection string in Excel by going to Data > Connections > Properties > Definition. If the Provider is Microsoft.Mashup.OleDb.1, this indicates that the data connection was made using Power Query.