Dataflows being inconsistent (Business Central data)

[frank14]

We have dataflows (GEN 1) running at 2 different companies to help us with extracting data from Business Central using our custom API. This has always worked fine for us (this has worked for 3+ years). These companies have nothing to do with each other. 

Since April 1st we have noticed the dataflows getting very inconsistent with the time they need to refresh, and they often wont refresh due to errors. The weird thing is that the error occurs at different tables each time and the dataflows will work sometimes. 

Also the error message is very unclear as the message is empty.

I was wondering if there where more people wo are experiencing this and if anyone here knows a way to fix this?

Thanks in advance. 

[v-menakakota]

Hi @frank14 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @frank14 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @frank14 ,

I hope the provided  information by @ibarrau is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[ibarrau]

Hi. I haven't experienced a different behaviour. In order to keep all data movement or transformation ok, you can check it desktop. The best way to check errors, messages and almost "debug" (kind off), is copying all the dataflow to a Power Bi Desktop and refresh it. That will show better results. Keep in mind that the desktop "Warnings" could be errors at dataflows, so make sure you check all tables errors because messages can be confusing. That should make the dataflows prevent any manual or transformation detail.

Also, keep in mind that 3 years can be a lot of data in many industries. If it just takes more time, it would be natural.

I Hope that helps,

[frank14]

Hi ibarrau,

Thank you for the reply. The weird thing is that the error occurs in different tables in the dataflow each time. These tables will work with the next refresh and then a error occurs in another table if i check the refresh log (so the problem does not seem to be in the tables themselves). Also what stands out to me is that the issues started on the same date at both the companies.

Maybe the issues are caused on the Business Central side, so i might have to post this on a forum there aswel. 

As of for now i am very clueless of what it might be. If i may ask, do you have dataflows running on business central or are they connected to another source? 

[ibarrau]

Yes it's weird, but start with the debug to make sure it's not the code. This can be timeouts or issues at data sources. When a datasource can't reply in specific time it can be cancelling the request and it changes which table is failing depending on the reponse time. When code is ok, then sources can be the next step. The sources processing time or timeouts for connections/commands.

I hope that make sense

[frank14]

Hi Ibarrau,

I have tried copying the queries into Power BI desktop. When loading i now get the following error message:

OLE DB or ODBC error: [DataSource.Error] OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..

I have been looking around but cant really find anything on how to fix this in our use case. Do you maybe have any clue as to how we can fix this?

[ibarrau · Accepted solution]

Are you connecting with OData or ODBC? if odbc, have you installed the appropiate driver? if you have, it looks like the source is not leting you in to read data. To proceed, ask a IT or DB admin to help you read data from the machine you are running the Power Bi Desktop. It could be that they are preventing connectiongs or requests/queries and that's why it changes messages each time it refreshes (one time it fails with table 1, then with table 3, other day with table 5)

I hope that make sense