Forum Discussion
Premium Incremental Refresh Detect data changes (How does it identify the correct row?)
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
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: @NickyvVIm so sorry Im really really struggling to understand this concept.
Going from the beginning
Switch Incremental Refresh policy on in Options
Power Query Editor. Add RangeStart and RangeEnd with dates for that will be overwritten so it doesnt matter what you put here
Q What constitutes a range here? Im not sure what this means
Filter your data in desktop using RangeStart and RangeEnd so you dont have a massive model within your desktop
Set your Incremental Refresh policy
Storing 60 months of data (LastUpdateDate is in the last 60 months)
Refreshing 10 days of data where LastUpdateDate is in that 10 days
Todays date is the 28th January
All the data was loaded Q Do you have to do this before you set up the inremental refresh?
In Power BI
Key ID Surname lastUpdatedate
1 24 Smyth 15/01/2020
2 48 Jonis 15/01/2029
And you upload this data
Key ID Surname lastUpdatedate
1 24 Smith 27/09/2019
2 48 Jones 27/09/2019
What would happen in the above situation?
It says that anything in the last 10 days gets deleted. And anything in the last 10 days coming in gets loaded. But because the last updateDate in Power BI is over 10 days old, surely these will remain and the new 2 records will be loaded meaning that you now have duplicates?
How does the incremental refresh know to delete Where Key = 1 and then Add Where Key = 1 if its just based on the date?
I dont really understand how RangeStart and RangeEnd fits with what Im doing either at the moment.
And because I dont understand the basics Detect data changes has really confused me
DebbieE no problem, let me try to clarify.
I'll start at your last question, because I think that holds the most important point/answer.
In the Filter date column updates it says:
Incremental refresh isn't designed to support cases where the filtered date column is updated in the source system.
So the scenario you sketch is not possible with Incremental Refresh, because your LastUpdateDate changes. In the example an OrderDate is chosen, which typically never changes for a given order.
Regarding your other question:
The first refresh operation in the service loads historical data (up to the 60 months of data in your example). Subsequent refreshes are incremental and loads 10 days of data where LastUpdateDate is in that 10 days.
So you'll have to choose a column in your fact table that holds a (functional) date that does not change for a given fact.
Does this clear things up?
