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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Only refresh changed rows possible with Scheduled Refresh

Hi Community,

 

Need some help once more since I'm struggeling at one of my projects.

The situation is as follows. We have a big amount of sales and purchase order lines combined in to 1 table. All data we get from SQL Database. We currently do not have a DateTimeLastModified field on the rows but this could be added if it is a solution.

We use Import and have 8 scheduled refreshes during working hours. 

This takes up alot of resources however with only a handful of rows changing between each refresh. I've looked into the possibility of using scheduled refresh functionality but I'm not 100% sure that this works as a solution in our case. Since all the rows in our dataset are subject to changes and never 'final'.  It seems that with scheduled refresh you only set a timeframe in which records should be refreshed. So if I set RangeStard to 9-4-2021 and RangeEnd to 10-4-2021, only these records are refreshed:

 

Koense_0-1628771011310.png

 

Or could we just add the DateTimeLastModified to the rows, then set up RangeStart as minimum date and RangeEnd as maximum date and set the Detect changes field as the DateTimeLastModified?

Koense_1-1628771186766.png

 

Koense_2-1628771227051.png

 

Please let me know what the best way to go is. 

1 REPLY 1
lbendlin
Super User
Super User

Incremental refresh is not entirely suited for what you are trying to achieve.  What you are looking for is differential refresh (what Qlik calls CDC).

 

Absolutely DO add the DateLastModified column to your data, it is extremely useful.

 

BUT!  You cannot specify the same field for RangeStart/RangeEnd  and for Change detection.  These have to be fed from separate fields.

 

What we ended up doing was to use a hybrid approach where we would fetch the data in reasonable chunks (for example fiscal quarter, or "transactions created in the last three months"  , with the assumption that the data would have calmed down after that period,  and then flush and fill only these chunks as needed. 

 

We do use incremental refresh, but our RangeStart/RangeEnd is based on the chunk dates, not the data dates  (we don't have the luxury of the DateLastModified field in our data)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.