cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Koense
Frequent Visitor

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!