Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Titatovenaar2
Helper III
Helper III

Incremental refresh PowerBI dataset without the use of SSMS

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:

Titatovenaar2_0-1724762457281.png

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:

Titatovenaar2_1-1724762574075.png

 

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:

Titatovenaar2_2-1724762708169.png

 

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

5 REPLIES 5
muhssamy
Resolver I
Resolver I

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

Titatovenaar2
Helper III
Helper III

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

Titatovenaar2_1-1725290935016.png

 

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).

 

Titatovenaar2_2-1725290987538.png

 

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?

"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.

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
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.