Timeout Error from Excel Services - “Unable to refresh data for a data connection in the workbook”

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.

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="https://schemas.xmlsoap.org/soap/envelope/">

       <Catalog>AdventureWorksDW2012Multidimensional-EE</Catalog>

       <Timeout>299</Timeout>

       <Content>SchemaData</Content>

       <Format>Multidimensional</Format>

       <AxisFormat>TupleFormat</AxisFormat>

       <DbpropMsmdFlattened2>false</DbpropMsmdFlattened2>

       <SafetyOptions>2</SafetyOptions>

       <Dialect>MDX</Dialect>

       <MdxMissingMemberMode>Error</MdxMissingMemberMode>

       <DbpropMsmdOptimizeResponse>1</DbpropMsmdOptimizeResponse>

       <DbpropMsmdActivityID>4118A1F1-8E5C-4F22-AAA7-D9BCBEA0FFF5</DbpropMsmdActivityID>

       <DbpropMsmdRequestID>DB8499CD-8367-4348-BF29-103B24296D2B</DbpropMsmdRequestID>

       <LocaleIdentifier>1033</LocaleIdentifier>

       <DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility>

       <DbpropMsmdSubqueries>2</DbpropMsmdSubqueries>

</PropertyList>

 

Now, we know the timeout value and it brings us the next question namely - Where can we change the Timeout Setting?

If you wonder where it comes from, open SharePoint 2010 Central Administration

Select Manage services applications

Click on Excel Services Application

Click on Trusted File Locations

Click on the location you keep your Excel services documents

In the Session Management section, you will see a Maximum Request Duration (The example here shows the setting is set to 300 second)

Increasing or decreasing the value here will change the <timeout> property sent from Excel Services. In turn, it control how long the query will run.

Key words: SharePoint 2010, Excel Services, Excel Calculation Services, MDX, OLAP, Tabular, Multidimensional, Analysis Services

C S John Lam| SQL Business Intelligence | Premier Field Engineering