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

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.

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 🙂 

Hi Shaishavm,

This issue may be caused by some database modifications and they may bring some performance problems and prevent the report from refreshing data. Hope these official docs help you.

Enabling Power BI Integration With Business Central - Business Central | Microsoft Docs

Performance Article For Developers - Business Central | Microsoft Docs

But for such kind of issue, troubleshoot with the support engineer is necessary for the error is general and it needs further investigation. May I know whether you get any update from the support engineer for now?

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

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  

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

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. 

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 

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? 

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.

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