OData from MS Dynamics - performance issues

[IvanS]

Dear all,

since Monday (10th of October), my dataset refresh has started to fail with the following 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.

I have noticed already several users complaining on this forum and it seems to be a bug (or feature?) on Microsoft side. Most likely it is related to having too many queries on a single table. I have tried to prolong the timeout time however this did not help. Then I tried to keep only 1 company instead of 3. Then the adjusted report refreshed without issues.

Now the question is if I should wait until response from Microsoft if this is a bug or permanent solution or start to optimalization of dataset. When it comes to optimalization, I am merging 3 major tables in one (with append query) and the volumes are as per below:

I can accept to have history of last 2 years but this will remove only small part of the rows from the tables (to be specific 150k from 1 mil.) as I expect to have 600k+ entries in 2022 until year end.

As the data are invoices/payments, once they are booked in system, they do not change and therefore they do not need to be refreshed on daily basis. I was thinking about storing the data somewhere and only add the new data. I red about Incremental refresh but I did not find info if this refresh applies also for Power BI Service.

If you have any other solutions, please share your thoughts. I would appreciate any idea!

Thank you

IvanS

[v-yadongf-msft]

Hi @IvanS ,

You can try to use incremental refresh. Incremental refresh also applies for Power BI Service.

If you want to know how incremental refresh works, please refer to:

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

If you want to deploy incremental refresh, you can refer to the detailed steps in this article:

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Learn

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.