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

  • Article

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.”

clip_image002

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)

clip_image004

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

clip_image006

Select Manage services applications

clip_image008

Click on Excel Services Application

clip_image010

Click on Trusted File Locations

clip_image012

Click on the location you keep your Excel services documents

clip_image014

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.

clip_image016

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