Excel failed to refresh from Analysis services- connection Timeout expired error

Question

• Hi Team,

I am facing excel pivot refresh issues while connecting with Analysis services Multidimensional model. Details below,

1. I am trying to drag and drop the measure value from field list which is having Distinct count as measure calculation in cube. After 40 seconds of the refresh the Excel is throwing the below error
2. Traced query which is running in the backend and ran separately in the SQL browser of the particular cube. It is running for about 9 mins and providing the results. 9 mins is acceptable during the excel refresh.

NOTE: Rarely it is getting refreshing for about 10 mins but most of the cases it is getting failed within 40 seconds with "timeout exceeded" error.

Option tried:

Provided the Timeout=360000 in the excel Analysis connection string, but no luck it is throwing error within 1 minute but I can see the queries are submitting perfectly fine in the backend but once the error thrown at the excel end it is getting stopped at that point in the profiler.

Any pointers is really appreciated.

Answer 1

Is there any actual resolution to this issue?  Setting Timeout or Connect Timeout does not solve the connection timeout for my users.  Same issue, SSAS Tabular model works in Excel 2013 but quickly (within 1 minute) timeouts on Excel 2016.

Answer 2

To fix my problem I added to the connection string Connect Timeout=1000;.  After doing this I no longer receive the error message while the file is open.

However, this seems to leads to another problem.  The Connect Timeout connection string parameter is not persisted.  Immediately after I save the connection string, if I open it up again Connect Timeout=1000 is missing.  This doesn't seem to be an issue by itself because if I Refresh, it still seems to works.  If I save and reopen the document it too seems to still work.

However when I save the spreadsheet under a new name, I get the error message again.  Also if I change the connection string anywhere else I get the error message again.

I'm sorry I didn't post my query, but in Excel it's just [Model].   More over it is clear the problem is Excel 2016, and has nothing to with my cube that was running fine (albeit slowly) in Excel 2013.

Answer 3

Hello Daniel,

Thank you for posting your query on Microsoft Community.

As mentioned in the above response, please post your query on the above mentioned forum link. The reason why we recommend posting appropriately on the above mentioned forum is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Thank you.

Answer 4

Hi I'm having this problem in Excel 2016, however this problem does not occur in Excel 2013 for the same model.  It takes about 4 minutes to process the query, but Excel seems to be quitting in a duration that does not correspond to Timeout in the connection string, or the SSAS server ExternalCommandTimeout or ExternalConnectionTimeout values.

Answer 5

Hi,

Welcome to Microsoft Community.

Since the issue is related to pivot table, your issue would be better addressed in the Excel IT Pro forums:

Click on the link below to post the query:

http://social.technet.microsoft.com/Forums/en/excel/threads

Thank you.