Forum Discussion
Helper IAnalysisServices: The connection is not open.. Microsoft.Data.Mashup.ErrorCode = 10478
Hi Max_WH
My support case for this error message is almost ready to be closed. I thought I'd let you know. It turns out there was a bug in the way the PQ mashup engine interacts with the underlying connections to the PBI dataset. This behavior should be documented by Microsoft in the near future, and I'll post a link here.
I opened two SR # for this bug:
2503220040000275
and2504010010001220
The second was a unified/premier case.
I'm not totally sure, but I think your case and my case were both associated with the same internal IcM (Incident-603360591) You should be able to confirm that with your Mindtree engineer, if you provide my IcM number.
I have an RCA and a longer explanation can be found here:
https://community.fabric.microsoft.com/t5/Service/DataSource-Error-AnalysisServices-The-connection-either-timed/m-p/4702221/highlight/true#M276330The RCA for my case is like so:
I don't know if that helps. At the end of the day, the workaround is pretty straightforward. You have to wrap any queries for the AnalysisServices connector, and put Table.Buffer around them. This ensures that connections won't be held open for long periods of time resulting in socket exceptions. In my case the offending connection was being held open for internal reasons that are NOT in my control. It has something to do with the fact that we are using one query as the driver to loop/execute some nested queries.
Hopefully this is makes sense. Your best is to test with Table.Buffer and see if that avoids the error. IMO this is an obvious bug, but the ASWL PG doesn't currently see it that way (possibly because the bug isn't theirs but belongs to a related product team - PBI integrations or whatever).
Memorable MemberHi Max_WH and Anonymous
It has been three weeks since you first mentioned this bug . Did you open a ticket (SR with Mindtree)?
I'm about to open a similar ticket. Would be nice to know your SR number or ICM number.
Did you expand the refresh history to find the multiple failures, per refresh attempt? I think this is a problem that originates with the mashup engine. I think there is some sort of self-inflicted timeout and cancellation. There is exponential backoff as well. I totally HATE the way Microsoft implements retries to hide their own bugs. This troubleshooting is taking many hours longer than it should, simply because of the mandatory retries that are doomed to fail. (Doom is a good word for the feeling, whenever I'm working with these PBI bugs.)
Just as with OP, my queries run fine on the local PBI desktop but not in the service. They fail comparatively quickly in the service.
Before failure I am seeing lots of successful activity in the sql profiler trace (image below). However after a period of time, the queries stop executing, presumably because the PQ mashup engine commits suicided. Notice that the queries are hitting my capacity from a PQ mashup engine running in azure (north central)
The dataset refresh is moving data from one dataset to another via a "cloud connection", rather than a gateway. Like so:
Any information would be appreciated. It feels like the type of ticket that will take far too long (3 weeks)? I'm hoping you can help me avoid some of that delay.
Helper IYes, it has something to do with the mashup engine. Ticket opened with Microsoft, but till now they are still investigating the issue. what's a pity!
- dbeavon3
Hi Max_WH
In your repro, are you running TONS of queries, or just a few? Is the workspace on premium or fabric capacity? Did you enable "Large semantic model storage format"? How certain are you that the mashup engine is responsible (what evidence)? The error sounds more like a problem that the AS team needs to look at. (Sometimes they will deflect to other teams.)Can you post your query here? Do you know how many rows it is likely to generate? Just because the data size is small doesn't mean that the results won't be enourmous - it depends on the query.
Did the engineers open the ICM with Microsoft yet? Can you share the SR or ICM number here?Is your engineer from Mindtree ("v-" in front of the name of the email address?) Have you spoken with the ops manager yet about moving the ticket to Microsoft? Usually after three weeks the ops manager will start to take notice, and either will encourage his Mindtree engineers to send the case to Microsoft, or close the case.
Did your engineers look for the failure mesages in their logs, (based on the request ID)? What did they see? Are there socket exceptions? Memory issues? Is the AS service crashing?
Based on the error message, I think the mashup engine is the last place the problem could be originating from. The only fault in the mashup engine is that it isn't relaying a very good error back to us, and will result in the need to spend way too much time on support tickets.
- dbeavon3
Hi Max_WH
My support case for this error message is almost ready to be closed. I thought I'd let you know. It turns out there was a bug in the way the PQ mashup engine interacts with the underlying connections to the PBI dataset. This behavior should be documented by Microsoft in the near future, and I'll post a link here.
I opened two SR # for this bug:
2503220040000275
and2504010010001220
The second was a unified/premier case.
I'm not totally sure, but I think your case and my case were both associated with the same internal IcM (Incident-603360591) You should be able to confirm that with your Mindtree engineer, if you provide my IcM number.
I have an RCA and a longer explanation can be found here:
https://community.fabric.microsoft.com/t5/Service/DataSource-Error-AnalysisServices-The-connection-either-timed/m-p/4702221/highlight/true#M276330The RCA for my case is like so:
I don't know if that helps. At the end of the day, the workaround is pretty straightforward. You have to wrap any queries for the AnalysisServices connector, and put Table.Buffer around them. This ensures that connections won't be held open for long periods of time resulting in socket exceptions. In my case the offending connection was being held open for internal reasons that are NOT in my control. It has something to do with the fact that we are using one query as the driver to loop/execute some nested queries.
Hopefully this is makes sense. Your best is to test with Table.Buffer and see if that avoids the error. IMO this is an obvious bug, but the ASWL PG doesn't currently see it that way (possibly because the bug isn't theirs but belongs to a related product team - PBI integrations or whatever).- Max_WH
Thank you for the update!
- 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.
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.