OData from MS Dynamics - performance issues

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