First published on MSDN on Aug 07, 2013
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="http://schemas.xmlsoap.org/soap/envelope/">
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://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