Applies to: SharePoint 2010 Excel Services, SQL Server 2005, 2008, 2008 R2, 2012 Analysis Services
If you ever get the following error while using Excel Services to kick off a document to query the data from an Analysis Services database, you may wonder where the error come from and how to control it.
“Unable to refresh data for a data connection in the workbook.”
When the timeout occurs, the server will generate an error “XML for Analysis parser: The XML for Analysis request timed out before it was completed”. However, the EXCEL SERVICES will display a much general error message “Unable to refresh data for a data connection in the workbook…”
So then how can we find out what the timeout value is?
To start, use Profiler to trace the activities. Doing so, you will see the MDX statement along with all those connection properties sent by the Excel Services (see the screen shot below)
If you cut and past the property list out, you will see something similar like below. One of the properties is <Timeout>. There are few other properties in the list as well. However, in this blog, our focus is time out. The timeout value is set to 299 (seconds) in the example list here.