PowerBI Service error: OData: Unable to read data from the transport connection:

[Shaishavm]

Hi, 

Our Power BI reports fetch data from Dynamics 365 Business Central using the native connector. However, since the Business Central version was updated to 20.0 (Release Wave 1 2022), refresh is failing. We tried both Power BI Service as well as Desktop.

Issue was escalated Microsoft Power BI Support, but they seem clueless. 

Refresh runs for about 3 hours and terminates with below error;

OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

Appreciate any help!

Regards,

SM

[Kurbis]

I am experiencing this error in Power Query in Excel. Come to find out that a merge of tables is the root of my error. Trying to load the merged tables first, then merge didn't help. I had to delete the merge-steps, load the tables and then establish a connection and merge them with a RELATED() column in the data model in Power Pivot

[grantbrimhall]

Why do you think that helped? Kurbis?

I work for an MS partner. For the last 8 weeks, and for other 4-8 week periods throughout the past 3 years, we have MANY customers at once all experiencing this issue. They have power query steps both simple and complex.

some make some adjustments like you have. Others can't and just suffer... and then magically, after several weeks, having done nothing to fix the issue, their queries finishe successfully again just like they had before the problem period. (During the problem period they will fail anywhere from 50-100% of the time varying by client. ... but some do experience some success in having their data returned.)

We are about at the end of the "fall equinox 2023 power query sucks cycle". Most of our clients are working again. None having done anything significant or material to their power query steps.

what was your data source? All the clients I am speaking on behalf of are using Microsoft business central. 

the cycle seems to repeat itself just before business central releases new waves. (Mid September and mid March).. but I know there are several Microsoft technologies that use the same 2x annual release cycle. So may be more tied to that than BC releases.

if your data source is outside of Microsoft then this ruins all my hair brained theories.

[Kurbis]

Why do I think it helped? Because it did (for me anyway). With a merge I get the error, without I don't. 

Do I think it's ideal? Absolutely not. The error occurred a couple of weeks ago, hence might be with the September update. Of course it is annoying, didn't know though that that's a Microsoft thing. 

And yes, BC is my data source 

[grantbrimhall]

thank you for sharing your experiences. I've been dealing with this on/off for 3+ years. And I just can't make rhyme or reason to anything. I have read that merges and other similar power query steps can cause some problems; exactly what you experienced. But why a "read data" problem? and who is the "remote host" forcibly closing the connection? And why does rewriting your power query steps (after the data has been retrieved into PowerQuery) influence anything?

Microsoft support has been, unfortunately, incapable of performing their stated duties. I was able to produce fiddler logs for some clients when this happened 12 or 18 months ago. But nothing ever came of it. Now the oddest thing about it is that today (Nov 5) all but 2 of our clients are back to normal. No material change in their query steps pre-September to now. They just suffered through it for 8 weeks. But the 2 that aren't back to normal have this experience now:

- they turn on fiddler to hopefully catch the https webservice calls in the act to see which is/are failing and why? and it the data returns to excel just fine; no remote host forcibly closing any connections.

- so we think, YES!, they're through the quagmire and back to normal ... but NO!, they turn off fiddler and the errors resurface.

Well I guess we wait until March 2024 and try to get support's attention again ...

[Dayveed_Ade]

This is what I tried that seemed to do the trick.

After you 'Close and Apply' from the Power Query window, when the data tries to load you get this error. Cancel this dialog box. After this you should still have the warning that you have pending changes at the top. Don't apply or discard the notification. Simply ignore, then kick off a full refresh of the entire file (without applying the changes popping up at the top). Once the refresh is complete, now apply changes and it should work just fine.

[seimor]

Currently experiencing this issue now. We are upgrading our NAV2013 on prem to Business Central (Cloud) and for reporting, this has been a tedious experience using web service. The issue seems to be the communication to BC and the time it takes to retrieve the data before timing out or the error the author is receiving. The other issue is from Power BI where it takes ages to apply steps in Power Query. We created custom pages with filters to reduce rows, removed calculated columns from the pages, and reduced queries in power bi as well. We also tried Azure Synapse Link using virtual tables from BC to Dataverse but ASL doesn't support virtual tables. Not sure if Power BI Premium Capacity will help or not and that costs a lot of $. Anyone found a solution or workaround? 

[mdkhemka]

Suddenly same error started on data referesh, which was working from long time. 

OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host 

[Shaishavm]

Hi,

Just an update.

We reduced the calls to Business Central by removing some of the tables, this appears to have fixed this issue.

However, my concern is we are only in 3rd year of Business Central and data will definitely grow year on year. Microsoft is marketing Business Central and Power BI as enterprise class application, restrictions on number of calls per session should be lifted.  

Hope there is someone in this forum who can convey consumer feedback.

Business Central connector to Power BI has many bugs, some of them were highlighted as early as 2020. Both being Microsoft products, its a shame that bugs still exist.

[jepp5872]

Hi,

We have also been experiencing this mysterious and frustrating error over the past few months, with several clients. While we haven't been able to find the exact cause (other than that it seems to occur with heavy data load or too heavy queries), we have found a workaround that so far has stopped the errors.

We created a dataflow where we import all the tables we need from BC. Then we applied as many query steps as possible (non premium capacity doesn't allow merging or appending), to reduce the steps needed in Power Query. In the pbix file we then connect to the dataflow and load in all the partially complete tables. For each table we then created a new query, referencing these "import" tables as source, and applied the rest of the necessary query steps. So for example we might have a "SalesImport" table from the dataflow and a final "Sales" table to be used in the model. We then turned off load for the import tables, leaving just refresh on. 

Finally, we scheduled the dataflow to refresh 1-2 hours prior to the report refresh, to make sure that the dataflow is always done before the main report loads from it. With this setup, we haven't had any more errors. Refresh times can still be long (anywhere from 10 minutes to an hours in our case), but it always completes.

Hope this can be helpful for the rest of you too 🙂 

[Anonymous]

Why do we always have to find a workaround for Microsoft crap products! They push all this "awesome" software in our faces and then leave us to fend for ourselves when it breaks!  Shame on them.

[Shaishavm]

Hi, 

We are again facing this issue. Power BI service is failing for data refresh with the same error.

There is no change at our end. 

Hope someone can advise a solution. 

[IvanS]

Hi @Shaishavm ,

I am facing the same issue. Did you find out what is causing this error?

Thanks

[Shaishavm]

Hi @IvanS,

Case is opened with Microsoft, both Power BI and Business Central teams are currently working to resolve.

I will post the update here.

Incase anyone has any other suggestion/findings please share.

Regards,

SM 

[IvanS]

Hi @Shaishavm ,

did you manage to get any reply from Microsoft teams?

Thank you

IvanS

[grantbrimhall]

You haven't tried installing 21.0 yet I presume ... our experience (as a Partner) is we have a few clients that are heavy ODATA/PowerBI users ... they all seem to complain about the same time. All have been silent for 3-4 months. But if your post is a good harbinger of what is to come, I imagine the chatter will likely pick up again soon.

[Shaishavm]

Hi @grantbrimhall we are on 20.5. Issue started last Friday, 7th Oct (SGT). We were already on 20.5 for more than 10 days. Not sure what was the trigger, but there was no change in the report or business central environment.

[IvanS]

I have tried to reduce the number of imported rows (1 company instead of 3) and it is working without issues. Then I tried to prolong the timeout time on my original dataset but it is still failing.

So it seems that my issue is related to too much data being handled via OData feed.

[Shaishavm]

@IvanS when we had the issue, we were issue the cause of too many queries on a single table. Apparently Business central seems to have some cap on the number of queries. We optimized our reports to reduce the number of calls. This time, we are not sure what triggered the issue. Waiting anxiously for response from Microsoft.

[rrcastillo]

Hello. I have s same problem with several reports from business central update. We are with powerbi an business central team support and they don't find the error.

If anyone find the solution, please, tell me.

Regards, 

rrcastillo

[Shaishavm]

Hi Thanks for the replies.

We are on version 20.1, and we still have this issue. The issue is reduced to 2-3 times a week. Power BI support has told me that the issue is caused by large volume of transactions, caused by DimensionSetEntries table. We have optimized it and issue reduced, evern before Business Central was running version 19. 

I am not sure yet, what is the cause and how can it be resolved.

We have been having problems since Jan 2022 with no solution in sight.

A bit disappointed, as one of our reasons to migrate to Business Central and Power BI, was the promise of seamless integration between these apps.

Thanks!

SM