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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerRon
Post Patron
Post Patron

What is the function of Detect Data Changes in Refresh?

Hi 

below is my refresh, RangeStart and RangeEnd based on creation-date, Detect Data Changes based on last-change-date

Knipsel.JPG

 

I don't understand the addition of Detect Data Changes. Did some testing.

 

TEST 1
Had two rows with creation-date and last-change-date 2022-02-04.
Change row 1: currency from EUR to ABC
Change row 2: currency from EUR to XYZ, last-change-date to 2022-02-06
Both rows were changed after refresh. Both are within the 2 months refresh, but my expectation was only row 2 would change because of changing last-change-date

 

TEST 2

Had two rows with creation-date and last-change-date 2021-12-22.
Change row 1: currency from EUR to ZZ1 and last-change-date to 2021-12-28
Change row 2: currency from EUR to ZZ2, last-change-date to 2022-02-14
Nothing changed after refresh. Probably because creation-date is not within the 2 months refresh.

 

So what then really is the advantage of having that Detect Data Changes checked?
My opinion was that it would then only detect those rows in the refresh period of 2 months, for which last-change-date changed.
But in TEST 1, row 1 is also changed without having changed the last-changed-date.

Please help
Ron

1 ACCEPTED SOLUTION

Hi, @PowerRon 

Please refer to Incremental refresh and real-time data for datasets .

The Detect data changes setting enables even more selective refresh. You can select a date/time column used to identify and refresh only those days where the data has changed. This assumes such a column exists in the data source, which is typically for auditing purposes. This should not be the same column used to partition the data with the RangeStart and RangeEnd parameters. The maximum value of this column is evaluated for each of the periods in the incremental range. If it hasn't changed since the last refresh, there's no need to refresh the period. In this example, this could potentially further reduce the days incrementally refreshed from 3 to 1.


Related  tutorial:

Power BI Incremental Refresh - Understanding Detect Data Changes 

 

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
Heniekwpracy
New Member

In your example (case 1) both rows got refreshed because the MAX(last-change-date) changed for the partition 2022-02, and so it happens that both rows belong in this partition. Partitions are defined by the column where you put rangeStart/End parameters (creation-date in your case).

 

Case 2: exactly, archived periods are not refreshed at all, only the initial load. That is why we only use this when we know that hitorical records cannot change.

selimovd
Super User
Super User

Hey @PowerRon ,

 

the idea behind this column is that you have an additional column in your SQL Server like a "Change Date" or a "Load Date". This is quite common in a data warehouse to maintain the data load to the data warehouse.

 

Like this Power BI can only monitor the "Load Date" column and load the rows that are changing in the source. For more information check the description in the documentation:
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#optional-setting...

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

Hi 
I understand, We have a creation datetime, which I use for the incremental refresh RangeStart RangeEnd, and we have a last change datetime, which I use for Detect Data Change. But If you look at my test's, why doesn't it do what I expected?

 

@soheil 

@parry2k 

Hi, @PowerRon 

Please refer to Incremental refresh and real-time data for datasets .

The Detect data changes setting enables even more selective refresh. You can select a date/time column used to identify and refresh only those days where the data has changed. This assumes such a column exists in the data source, which is typically for auditing purposes. This should not be the same column used to partition the data with the RangeStart and RangeEnd parameters. The maximum value of this column is evaluated for each of the periods in the incremental range. If it hasn't changed since the last refresh, there's no need to refresh the period. In this example, this could potentially further reduce the days incrementally refreshed from 3 to 1.


Related  tutorial:

Power BI Incremental Refresh - Understanding Detect Data Changes 

 

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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