Forum Discussion
Community ChampionPremium Incremental Refresh Detect data changes (How does it identify the correct row?)
Super UserFrom 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.
- DebbieE
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?
- GilbertQ
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
- nickyvv
Most Valuable Professional
Hi DebbieE, I understand you concerns, but let me clarify:
Because you speak about a fact table I assume you have basic knowledge of a DWH scenario. Otherwise please let me know and I can explain this further.
In a DWH you would have a Slowly Changing Dimension (type 2 for example) on the fact table usually with valid_from and valid_to columns (or similar). When processing changes these columns get updated with new values and new rows are inserted when necessary.
In Power BI we have the RangeStart and RangeEnd parameters, which you define on a column in your (fact) table. An important difference however: this parameter is NOT defined on the SCD-2 columns in your fact table, but rather on a functional date column like OrderDate or TransactionDate. Incremental refresh isn't designed to support cases where the filtered date column is updated in the source system.
After defining your parameters and filtering on them in your model, the ranges you set in the refresh policy are working as follows:
- You define a range to store the amount of rows (e.g. 5 years of data)
- you define a refresh range to refresh to amount of days/months/years (e.g. refresh rows in the last 10 days)
The important clarification is this: the refresh will do a delete-insert rather than an update of the last 10 days of data (based on your parameters defined on the functional date column. So this means: the whole set of 10 days is deleted from your dataset, and refreshed with the new rows of 10 days worth of data.
From the documentation:
An update is interpreted as an insertion and a deletion, not an actual update. If the deletion occurs in the historical range and not the incremental range, it won’t get picked up. This can cause data refresh failures due to partition-key conflicts.
I hope this clarifies your concern a bit. If you have any questions don't hesitate to ask them!
Did this help you or did I answer your question?
Then please give kudos or mark my post as a solution!
My blog: nickyvv.com
Twitter: @NickyvV
