I have worked with many customers who are seeing refresh failures in the Excel Web Access Web Parts in their TFS Dashboard.
In most cases, this is caused by not having a Secure Store Service ID set up for Team Foundation Server and that SSS ID has not been added to the Enterprise Application Definition. Follow the steps below to configure your environment.
Set up a Secure Store Services ID. Here are the steps to create a Secure Store Services ID:
Central Administration > Manage Service Applications > Secure Store Service > New
Target Application Settings:
Application ID: tfs
Display Name: tfs
Contact E-mail: Administrator
Target Application Type: Group
Target Application Page URL: None
Field Name: Windows User Name
Field Type: Windows User Name
Field Name: Windows Password
Field Type: Windows Password
Target Application Administrators: Administrator
Members: example: “SecureStoreUsers” (This will be an AD group that you create. Many companies will create certain groups for certain resources. Example: One company may create an Application ID called "SSSAccounting" and have an AD group called "Accounting" that they make a member.)
Select the SSS ID "tfs" > Set Credentials:
Windows Name: (this account needs to have access to the backend data).
Step Two: Add the Enterprise Application Definition:
Note: For all the workbooks to be updated, you need to wait about 30 minutes. I am not sure what Timer Job runs to update there workbooks. Here is the best documentation I could find.
Team Foundation Server Timer Job for SharePoint Products
Project portals may contain Excel workbooks that have a connection to the Team Foundation Server data stores. The administrator of a Team Foundation Server instance can change the reporting configuration for the instance. For example they could enable/disable reporting, change the location of the SQL Server Analysis Services cube or change the Enterprise Application Definition for sites within a given SharePoint Web application.
To keep these connections pointing to the correct location or to keep the Excel Services Authentication Settings matching the Enterprise Application Definition, a timer job for SharePoint Products runs on each SharePoint Web application that contains project portals. The timer job periodically scans the portal sites in the Web application and updates the workbook details if it finds that the connection or authentication details have changed.
If you are adding additional workbooks to a project portal and want their connections to be updated by the timer job then you should place them in the same document library as the Excel workbooks for Team Foundation Server. Note that the name of the connection in the workbook must be "TfsOlapReport" if it is to be updated by the timer job.
Customizing Team Foundation Server Project Portals