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.
lbendlin The fact that so many people are struggling with this issue means we aren't alone. We have no data that is immutable in our organization. If you're doing householding, golden record matching/survivorship or merging customer accounts then you will have changing history so I think this has to be a very common need.
Are you saying it will also be problematic for fact updates when they move partitions? I hope not. What I read is it sounds like it's only issue with M:1 dimension to fact type of relationship. We haven't hit a duplicate error yet with fact incrementals in our testing. Not to say it can't happen however.
We will be doing full refresh weekly but daily full refresh isn't practical for us in enterprise models. The attraction to PBI datasets over SSAS (besides dumping SSAS servers) is it's built-in support for incremental and partitioning as full refreshes even with SSAS take to long with larger models.
I wish someone would do deep dive into how this really works behind the scenes. Please let me know if you have seen someone do that. Everything I've seen just talks about how to set it up but not how it really works in-depth.
We're in the same boat. Lots of data changing over time (like an opportunity status, or an order amount). The only way we have found that is halfway acceptable is to have a separate process that judges which partitions need refreshing, and then initiates these refreshes via XMLA. If this sounds like the OLAP cube refreshes of yore - yep, that's what it basically is.
To add insult to injury - if you use standard incremental refresh partition management then you are bound by regular calendar periods. We use a fiscal calendar that often results in more partitions (or parts thereof) being refreshed than strictly needed.
There is no magic to any of this, it is all tedious work.
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla Advanced incremental refresh and meta data changes
https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#override-incremental-refresh-behavior special features for incremental refresh
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-troubleshoot Troubleshoot incremental refresh
Late reply but for those that come here after a search:
I believe it needs an immutable datetime column, not necessarily that the whole row needs to be immutable. If you have an immutable column like a creationdate log column you can use that together with detect data changes on a modifieddate log column. If you set your partitions at the month level for example it will scan each month for a change in last modifieddate and then refresh the entire partition.
Martin1986 I was more talking about the general concept of incremental refresh. It works well with any data that does not change after the fact (like IoT sensor readings). It does not work well with SCDs like order line status changes etc. For that you would need differential refresh, the ability to touch individual rows in a partition. For now it is flush and fill on partition level (well, blitting).
Hi, thanks for the quick reply. I'd say that flush and fill on partition level with detect data changes is still a huge improvement on load compared to a full refresh in many cases. I wanted to state that because otherwise ppl who do not have very strict requirements might come to this thread and not try incremental refresh because their data isn't immutable. Or am I missing something except for that it doesn't handle potential deletes.
