[DataSource.Error] AnalysisServices: The connection either timed out or was lost.

[btfergie]

I am getting this message on Refresh or Apply Queries:  OLE DB or ODBC error: [DataSource.Error] AnalysisServices: The connection either timed out or was lost.  This happens about 90% of the time.  I am able to successfully refresh about 10% of the time.

This happens with a refresh on the Desktop or in the Service.  I use an Analysis Services query to connect to a SSAS model.

Perhaps this is happening when the SSAS model is doing its own refresh.

I am using the latest on premise Gateway and Desktop.'

Please help with any ideas.

Thanks.

[dbeavon3]

Hi @btfergie and @GilbertQ 

I just spent three months on a support case with Mindtree/Microsoft.  I think I was encountering the same bug as the one you were discussing. 

I believe Microsoft has promised to create some public-facing document about the behavior/bug.  

The problem occurs for us when there are multiple tables connected to the same underlying AnalysisServices data source.  For example if we are looping thru rows in one PQ table, and generating a series of related queries to fetch AnalysisServices data for another PQ table.  Whenever this sort of thing happens, the underlying client driver (ADOMD client library) gets confused ... because a connection resource is shared for multiple tables in the mashup.  The shared connection does not appear to be properly disposed between requests.  At some point the connnection will get locked up, especially if the PQ takes longer than a few 100's of seconds to execute. 

If/when Microsoft shares public documentation, it should look similar to the following.

Currently we are not able to support 5 minute silence in reading rows in all cases. While we have application protocol to deal with keep-alive, in this case, we do not wait for last 64KB of write on the server side. So if the client didn't drain the last 64KB, there is no way for application level code to recover the connection. The configuration of 64KB buffer applies to system level HTTP stack and it is not something Product team prepared to manipulate just for this case.

As a workaround, the recommendation is for customer to make use of Table.Buffer function of M to materialize the outer joins.

Hopefully this is clear.   My strategy for using the AnalysisServices connector will basically involve lots of calls to "Table.Buffer".  I don't think I will ever trust that connector without wrapping it with another function.  

I don't think the "5 minute" timeout that they referenced is accurate.  In the service the timeout is far shorter than that.  In my experience the failures occur in only three minutes or so.

It should be noted that the error, "AnalysisServices: The connection either timed out or was lost",

... represents a socket exception under the hood (an unusual type of error).  So if you beleive you are doing something fairly normal and do NOT believe a socket exception is warranted, then it is VERY likely that you are encountering this bug as well.  The ultimate test is to publish the PQ mashup and related dataset into the same workspace on the same capacity, and see if that configuration will encounter these so-called "socket exceptions".    There is really no good reason for a socket exception when the client and server are in such close proximity, so it is easy to rule out a "proper" connectivity issue.  In this configuration, it is more likely that you are encountering a well-known bug.

As-of my unified support case this year (May 2025) the PG claims this bug was not previously reported to them. They also don't seem to think it is encountered very frequently.  However my gut tells me that it is a common bug, albeit one that can be confused with a connectivity issue.

Thankfully there is a workaround, and I hope that will be shared by Microsoft in the near future as well.

[dbeavon3]

The ASWL team eventually agreed to create documentation about this.  It is confusing since a problem that is not a socket exception is being misrepresented as such.

https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-troubleshooting-refresh-scenarios#co...

For google/AI:

The Analysis Services connector may encounter the error The connection either timed out or was lost. This error is usually a transient error when the network connection fails, and a retry will succeed.

In some circumstances, this error can be more permanent when the results of the query are being used in a complex M expression, and the results of the query are not fetched quickly enough during execution of the M program. For example, this error can occur when a data refresh is copying from a Semantic Model and the M script involves multiple joins. In such scenarios, data might not be retrieved from the outer join for extended periods, leading to the connection being closed with the above error. To work around this issue, you can use the Table.Buffer function to cache the outer join table.

[dbeavon3]

Here is what the behavior looks like when encountering this bug in a dataset mashup (one dataset connected to another dataset).

Notice that there is a rapid fail/retry loop and all of the failures claim to be repeated socket exceptions, after only about 100 seconds.  It certainly doesn't take wait for five minutes, before failing.

[GilbertQ]

Hi @btfergie 

What I would suggest doing is having SQL  Profiler running when the refresh is taking place. You should be able to see if there is an error in your SSAS and what is the cause.

It could be memory or CPU pressure that is happening at the same time you are trying to refresh.