Part of a data disappears in the dataflow with incremental refresh

[denis163]

Hi All!

We use dataflows with incremental refreshes in PBI Service as a primary datasource for our PBI reports - these dataflows are connected to our local MS SQL server with hundreds tables and millions rows in it.

We set incremental refreshes for dataflows with almost the same configuration - store 2 year and refresh last 7 days. Most of them work well without issues, but some of them don't load the whole data from the SQL server - for example, one of the dataflow every day skips data from 8:00 PM to 12:00 AM, exactly this period every day and without any failures in scheduled refreshes. Only when we disable incremental refresh for this dataflow - it starts load each row from the SQL server without any misses.

And because we don't have any failures in scheduled refreshes we can't identify the main reason of this issue - so the questions are:

1. Is there any type of diagnostic for the incremental refresh that can help identify reasons of these incorrect behavior, if we don't have failures with a description?

2. Do we have any other options for dataflows that are connected to big data SQL (other than incremental updates) that can help maintain fast updates for dataflows and its stable operation?

Thank you in advance!

[lbendlin]

We use dataflows with incremental refreshes in PBI Service as a primary datasource for our PBI reports - these dataflows are connected to our local MS SQL server with hundreds tables and millions rows in it.

That's not what dataflows are for.

for example, one of the dataflow every day skips data from 8:00 PM to 12:00 AM,

Did you enable "Load only full periods" ?  Note that the Power BI Service runs on UTC.

1. Is there any type of diagnostic for the incremental refresh that can help identify reasons of these incorrect behavior, if we don't have failures with a description?

There is currently no way at all to troubleshoot dataflow incremental refresh. It's been a painful black box for a while. No bootstrapping, no selective partition refresh, nothing. Quite a contrast to the wonderful world of dataset incremental refresh.

 Do we have any other options for dataflows that are connected to big data SQL (other than incremental updates) that can help maintain fast updates for dataflows and its stable operation?

Consider improving the performance of your SQL server. Use datasets. I will not say "use datamarts"  because I think datamarts are an abomination  (personal opinion). YMMV.