Incremental refresh PowerBI dataset without the use of SSMS

[Titatovenaar2]

Hi guys,

I was wondering if it is possible to set incremental refesh up out of the box. Without the use of SSMS. Microsoft seems to support it if I read everything in their article, but I cannot get it to work:

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

I have my fact table and 2 parameters:

This is the entire M query behind the FACT_Sales:

let
    Source = Sql.Database("xxx.database.windows.net", "xxx"),
    dbo_FACT_Sales = Source{[Schema="dbo",Item="FACT_Sales"]}[Data],
    FilteredRows = Table.SelectRows(dbo_FACT_Sales, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    FilteredRows

Then I've setup the incremental refesh on the FACT_Sales table:

Then I saved and published the model to the service on a premium workspace.

The FACT_Sales table has 2 million records and takes about ~1-2minutes to load. But on purpose I have only a single record within the past 7 days, so I'd expect the refresh to be a few seconds, as it should only refresh the past 7 days. However, every time the refresh takes 1-2 minutes, showing that it loads the entire 2million records:

What do I need to do to make sure the refesh only works for the past 7 days, and ignores all other records? I have the feeling it is not really partitioned, but as far as I know the incremental refresh setup should account for that.

What do I miss, any suggestions are appreciated!

Kind regards,

Igor

[muhssamy]

Is this only for testing or your planning for doing this in production?

[Titatovenaar2]

Alright, I have insight now in SSMS in the partitions, and here you can see it is currently only loading that single record.

However it takes just as long (2 minutes), while 1 record should probably only last a few seconds.

When I did not have partitions, it took 2 minutes to load all the records (the ones in partition 2024Q30801).

Or it isn't really working, even though it shows correct details (since I only want it to refresh the past 7 days),

Or having partitions automatically means the scheduled refresh always takes 2 minutes, no matter if you have new rows or not.

Any ideas what this could mean?

[lbendlin]

"it is taking two minutes"  can be a lot of things, including the service being busy with other stuff etc.  You will have to provide a lot more data (millions of rows per partition) to see any meaningful effect.

[Titatovenaar2]

Alright. Reason why I was asking is that before partitioning the scheduled refresh took between 1 and 5 seconds Every single time when it had only ~5K records (~15 times tested). When it had 2 million, it Always took 2min (~15 times tested). And with partioning it still takes 2 minutes (~30 times tested). So I think just a random server things sounds weird to me here with this many tests, its getting statistically unlikely. 

So my hypothesis still stands; partitioning is nice but adds minutes delay.

Meanwhile I will create a second dataset with 100 million records and test the same. Because we do want to take this to production eventually if it works properly.

[lbendlin]

Note that every time you make meta data changes and re-publish to the workspace this will reset the partitions, and do a full refresh. Only the NEXT  refresh will then re-start the incremental partition processing.

You need to use SSMS or DAX Studio to examine the partition rows and last update date.