Forum Discussion
Premium Incremental Refresh Detect data changes (How does it identify the correct row?)
Doing Incremental Refresh in Premium
I have a last update date in my data. When I detect data changes set to on, it checks the row and if this date has changed then the record is updated.
However how does it know that the record in Power BI and the record coming in is the same record?
I have searched the documentation and cant find anything on this. Im assuming its the Primary key of the record or the key ? However this isnt set anywhere tht I can see
What about when you are loading in the fact table? The fact table contains foreign keys. Basically how doesthe incremental refresh know that this is the row that replaces the row in Power BI?
https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh#detect-data-changes Doesnt seem to answer this question
29 Replies
- Hi there
From my understanding everything is based off a dateTime column. This includes when detecting the changes.
When the incremental refresh runs, it will look at the refresh policy as well as at the detect changes column and which ever data fits into the refresh policy it will then go and get those new rows based on the dateTime of your data.This is what Im not understanding at all. You cant just base it on LastUpdatedate
Say you have the following in Power BI
Key ID Surname lastUpdatedate
1 24 Smyth 01/09/2019
2 48 Jonis 01/09/2019
And you upload this data
Key ID Surname lastUpdatedate
1 24 Smith 21/09/2019
2 48 Jones 21/09/2019
You cant overwrite the correct record simply using the LastUpdatedate. There doesnt appear to be any information on this anywhere but If it was simply down to LastUpdateDate then I dont understand how you can trust Incremental processing in Power BI?
Surely you have to supply more details like IDs or something else?
Hi DebbieE
When you are configuring your Power BI Incremental Refreshing you can set it on the following screen to detect data changes
Here are more details
https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh#detect-data-changes
Fascinating conversation. Coworker and myself have been beating our heads against the wall trying to figure out how this works - its way more complicated than necessary in my opinion. But....I think the short answer to the original question (which never got directly addressed),= is it doesn't need to know what the unique key is to find the same record. And that's because it just reloads the entire partition involved in a change. Just deletes by partition, not by key value.
But then lot of this conversation is making assumptions based on documentation rather than actual data tests. It's unfortunate that Microsoft isn't very revealing of the internals on how this works. And note...if you use the "Get the latest data in real time with DirectQuery" option then it won't load anything for the current partition - instead it's going against your source for the most recent data.
Our tests did this...our data goes back 50 years and can change at any time point in history. The reason is we merge customer records and re-arrange households and golden records on a daily basis so all of history can get a new key value in our fact tables. So our refresh and archive settings are 50 years. Detect data changes is turned on and Get latest data in real time and only refresh complete years is turned off. We set the refresh column to modified timestamp column and setup the parm/filter to business date timestamp such as an order date (converted to time via DAX function) which of course becomes the basis for the partition ranges.
We then updated the modified timestamp to getdate() for specific orders in history. We then profiled the SQL during refresh after waiting a couple of minutes. What we observed was first a query to get max(modifiedtime) for every partition. Then it does a partition range query for every partition where there was a newer modified timestamp. So how it behaves like what was said here.
The one exception was...when I also modified the date which is the basis for the partition so it should move partitions (such as order date) then nothing happened during refresh. I was hoping it was smart enough to rebuild both partitions involved but instead it did nothing at all. My assumption is it gets confused if the partitioning date changes (which they say isn't supposed to). The solution to this will be just knowing on a daily basis the data will leak these kind of changes and say every weekend do complete refresh to get these leaks resolved.
