Incremental refresh - Table keeps loading in PBI desktop

[amir_mm]

Hello,

I defined new parameters on a date/time column in one of the tables (RangeStart and RangeEnd), and filtered the column to a custom date range, but when loading into PBI desktop the table keeps loading forever, even I filtered the date range to 1-day only, but again it keeps loading.

And even when I want to cancel the loading it stuck and I have to force close PBI through task manager.

I think query is folding (because the view native query is active and not grayed out after defining parameters and filtering date range).

This is only happening in our largest table (with 10 Million records) and other tables are loaded fine.

[lbendlin]

Aren't these a little too small for the effort you put into Incremental Refresh? How long does a regular load take?

[amir_mm]

After I defined the RangeStart and RangeEnd on both tables, it takes 2-3 min to load.

The main issue is auto refreshing, as we face memory capacity limit (we are on premium A2), so I thought maybe I can do some partitioning and refresh each partition separately.

[lbendlin]

fair enough.  Usually you start thinking of incremental refresh when you are north of 200 Million rows, or getting close to the 5 hr hard limit.

[smpa01]

@amir_mm  when you say 10M rows, that probably means it is coming from some sort database. What if you write native db queries instead of fetching the data from db using PQ ? In my case, I regularly query large data tables but always use native db query (e.g. SQL). I find no advantage in trying to oblige query folding; it is rather a performance killer, Use the SQL and bake IR on top of that. It is a killer combo.

[lbendlin]

Every time you make structural changes Power BI Desktop throws away all meta data and recollects it. Set your RangeStart and RangeEnd to two adjacent dates,  then let Power Query do its thing and do not interfere. Messing with it will make it worse.

Do not attempt anything fancy in the Power Query code - no merges or sorting!

[amir_mm]

I did the same. There is no merging, sorting or anything. Set RangeStart and RangeEnd to one day only, and again when loading into desktop it just keep loading and spinning. 
other tables work just fine. 

[lbendlin]

can you show a sanitized version of that Power Query code?

[amir_mm]

The table is connected to 6 other child tables. After removing one of the child tables in Power Query, the data loaded into PBI Desktop quickly this time. I also checked the database for constraints and indexes, and everything seems to be in good order. Could an incorrect relationship in the PBI data model have caused the issue?

[lbendlin]

If you refresh an incremental refresh partition all of its dependencies will be refreshed as well. That is especially devastating when you have Auto Date/Time enabled.

[amir_mm]

Thank you. I don't know why, but I had to apply the RangeStart and RangeEnd on both the parent table and one of the other child tables. This way the tables are loaded into PBI desktop quickly. 

[lbendlin]

What's the row count  and cardinality of these tables?

[amir_mm]

9M rows on parent table and 8M rows on child table with many to one relationship on both direction.

[amir_mm]

This is the query after defining the parameters. It just keep spinning ...