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: tfsDisplay Name: tfsContact E-mail: AdministratorTarget Application Type: GroupTarget Application Page URL: NoneNext
Field Name: Windows User NameField Type: Windows User NameMasked: No
Field Name: Windows PasswordField Type: Windows PasswordMasked: YesNext
Target Application Administrators: AdministratorMembers: 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.)Next
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 Portalshttp://msdn.microsoft.com/en-us/library/ff678492.aspx