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" comes with a huge penalty. You get a canary dataset semantic model in addition to your regular one. We usually recommend against that. You as the business owner should know which data has changed, and you can then refresh the affected partitions yourself.
Could you elaborate on 'canary semantic model' ?
I do not know the exact workings, but I guess it stores a table that lists for each partition the max modifieddate to compare against when it checks for changes?
And if you move to scripting such logic yourself, e.g. writing a script that checks which partitions are changed and use the Power BI API to refresh only those... then a) is the only benefit avoiding the canary semantic model? b) aren't you then already on a level of dealing with data that can handle the upserts as well and you don't need the build in incremental refresh policy settings at all?