Correctly configuring the data refresh in Excel Services in Microsoft SharePoint Server 2010 can be challenging.  You need to make sure that Excel Services data is available to only those authorized while ensuring an incident free refresh of the data when opening a workbook.

It is helpful to know what is going on behind the scenes in Excel Services at each decision point.  Each path leads to a choice. How are you authenticating?  Are you using Windows or SharePoint Secure Store?  Does an ODC exist? Is it in a trusted location?

Several articles give great guidance on how to correctly configure data refresh, but now we can add one more tool to assist you in the troubleshooting of established Excel Services. 

Click here to download the "Excel Services data refresh flowchart".

This downloadable flowchart (available in .pdf or Visio) shows the sequence of events that occur when Excel Services renders a data-connected Excel workbook. Use it to troubleshoot specific issues or to get a better understanding of what is going on behind the scenes in Excel Services.  Rather than showing user steps on how to configure, it shows the decision points and the underlying outcomes based on settings either in the workbook or within Excel Services.  It also includes information about errors you may encounter.  I think you will find it a valuable tool!   Check it out.

Other articles that address Excel Services in Microsoft SharePoint Server 2010  and data refresh:

 

Let us know what you think!