Problems Refreshing Excel Services Data Sources with SSAS

I just spent a day looking into why Excel Services could not refresh an Analysis Services 2005/2008 external data source.

the message was "Data Refresh Failed... The data sources may be unreachable, may not be responding, or may have denied you access"

It transpired that SSAS database was set to a locale of US English and the MOSS installation English (Ireland).

I matched the languages and manually set the ODC connection string as below without a Language Identifier



Integrated Security=SSPI;

Persist Security Info=True;

Data Source=MyOLAPServer;

Initial Catalog=Adventure Works DW


Further reading on using Analysis Services with Excel Services is in this white paper

One tip when troubleshooting connectivity with Excel Services is to click on the ODC directly from the Data Connection Library as a test. In this case when I did it I got a different Error Message that led me to a KB on issues with Locales and Excel Picot Tables.