Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
looking for some advice on which column(s) I should be using when setting up incremental refreshes for my data set.
I am currently pulling data from a claims database and i have a column for a claim called InsertDate, which is when the claim was created in the system. I also have a LastUpdateDate, which is when the claim was last updated. Each claim has 1 row in the system and only the LastUpdateDate gets changed when the claim is updated.
Now, users can go in and update a claim from 30 or 40 years ago. We have claims that go back to 1970 and sometimes these old claims are still being updated due to subrogration and other reasons. Therefore, i would need to make sure that i capture all these changes in my incremental refresh period in order to not miss anything.
I was originally thinking of using the InsertDate as my incremental refresh date for historical (first time load) and incremental load and then using the LastUpdateDate for the Detect Data changes field. However, in order to capture all of the changes in teh system that could happen, i would need to go back 50 years everytime i do a refresh. For example a claim from 1970 could get updated today and the incremental refresh would have to check all years going back to 1970 in order to capture just the change from today. I feel like this is not really helping me much since all years will have to be queried in the source database which seems to defeat the purpose of the incremental refresh. Am i correct in my thinking or am i missing something?
Hi @scabral ,
Hope the following documentations can help you:
Power BI Incremental Refresh - Understanding Detect Data Changes
Power BI tutorial- Data refresh and scheduling (Part 3)
Best Regards
Rena
@scabral ,
You need to use lastupdated column in the dataset, below is a clear explanation of how incremantal refresh works.
https://www.youtube.com/watch?v=-pjmKI66iRQ
@scabral , Typically we use last update date as an incremental date. The only thing we need to take care the last update date is not null for the new record.
https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only
https://thinkaboutit.be/2020/02/how-do-i-implement-an-incremental-refresh-in-power-bi-free-or-pro/
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.nabler.com/articles/power-bi-data-refresh-and-scheduling/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
https://www.fourmoo.com/2020/03/11/how-to-configure-incremental-refreshing-in-power-bi-with-datekey-...
do not use the Detect Data Changes option? Just use LastUpdateDate as the Incremental Refresh Date? It will never be null, it defaults to the insert date the first time.
thanks
Scott
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |