Forum Discussion
Issues in Incremental refresh
Hi,
Our DB has only 4 days data, I have setup incremental refresh for storing 12 last days and refresh daily for last 4 days.
Here's my model:
Relationships:
1) Scores.StoreKey with Image.StoreKey( one to many)
2) Image._FileName with Flavor._FileName( one to many)
After deploying to service, refresh fails with following error:
Data source errorColumn '<pii>_FileName</pii>' in Table '<pii>Image</pii>' contains a duplicate value '<pii>POSM.jpg</pii>' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
I checked the tables in DB, there are no duplicates on the one side of relationship.
Here's my incremental refresh setup:
I have tried couple of online available solutions but to no avail.
Any help will be appreciated.
Thanks,
Nishant
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.
17 Replies
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.
Hi all. I had a similar situation. we enabled incremental refresh on a dataflow. When i go to refresh history and download it, it shows "NA" as number of rows processed. How is this even possible ?? When we run a full load or incremental load, it will process certain amount of rows right?? Any help would be greatly appreciated.
Thanks v-zhangti, great answer.
LOT of mis-information going around about this issue. I have situation where the duplicate error is on a PK INT field so obviously there's no duplicate on the source side.
So if I'm reading this correctly....when the post date changes thus causing a move in partitions, PBI "may" reload the new partition before the old thus causing the row to be present twice in the dataset model. You would think they'd delete everything from the changed partitions and reload at same time but must not be or this wouldn't be an issue.
This is unfortunate for those of us where we can't control what happens to the partitioning dates. All our data (like posting date) can change over time. Should it? No. But we have no control over what the business does in our sources. And we have a number of monster dimensions where it would be nice to use incremental refresh but alas it won't work in our situation.
What you are looking for is differential refresh or CDC. Not something Power BI is currently offering. Incremental refresh is designed to work with immutable data. If you know that your data changes post factum you will have to add a periodic full refresh to your schedule.
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.
Had the same problem.
In the Native Query I was using "Between" instead of >= and <
and as the incremental refresh was not sending 1 query for all the historical data but several small queries of partitions and putting them back together, the between statement in SQL is including both days (start and end) and that's the reason I had an overlap and a double counting.If Power BI says there are duplicates then that means there are duplicates.
NOTE: Power Query is case sensitive. Power BI is not. So while you may not have duplicates in Power Query you may have them in Vertipaq
NOTE: a value with a CR/LF at the end (like from a bad Excel copy/paste) is nearly indistinguishable from a value without that issue. Chase it in Power Query using the string length.
Hi, Anonymous
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.
Best Regards
