Forum Discussion
Issues in Incremental refresh
Hi, Anonymous
Please refer to the following solutions to see if they are helpful to you.
Problem: Refresh fails because of duplicate values
Cause: Post dates have changed
With a refresh operation, only data that has changed at the data source is refreshed in the dataset. As the data is divided by a date, it’s recommended post (transaction) dates are not changed.
If a date is changed accidentally, then two issues can occur: Users notice some totals changed in the historical data (that is not supposed to happen), or during a refresh an error is returned indicating a unique value is not in fact unique. For the latter, this can happen when the table with incremental refresh configured is used in a 1:N relationship with another table as the 1 side and should have unique values. When the data is changed (for a specific ID), that ID then appears in another partition and the engine will detect the value is not unique.
Solution: Refresh specific partitions
Where there is a business need to change some past data from the dates, a possible solution is to use SSMS to refresh all partitions from the point where the change is located up to the current refresh partition, thus keeping the 1 side of the relationship unique.
Please refer to the following documents.
https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-troubleshoot
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
"detect data changes" is a myth. you can't really do that, so you resort to smoke and mirror. That means you clandestinely load data into a shadow dataset (the canary) and then compare against what the user sees (the original dataset). Once a difference is found you then alert the user "hey, I detected a data change".
You can see that when looking at the DMVs, they will mention "canary". These artifacts also show on Dataflows.
Think of Incremental Refresh as a (convenient) corner case for partition management. You can create, fill, and merge your own partitions via XMLA, regardless if you need this to be incremental or not.
My understanding of 'detect data changes' is that it simply queries your source for the MAX ModifiedDate logfield you set as column to detect changes on and compares it with its previous value. It does this for every partition. So if you have 24 months of refresh scope it would do 24 queries for each start-end date per partition and retrieve the max modified date. So my understanding is that as long as you have such a log field in your source it would work quite well? I would use the same logic to detect a change in data myself to be honest if I were to script it.
I also have set a custom polling M query so that it can accept a different field to monitor. For example I use it with an API which can't be queried for MAX modifieddate as it doesn't fold, but I entered a custom M query that calls the API to sort on modifieddate and return first row.