Forum Discussion
Best practice for Incremental Refresh
I have a simple sales fact table in power bi ,
the columns have Document Date, Last Modified DateTime , CreatedDateTime
I would like to understand which will be the best practice:
Practice 1:
In power query, i can set my parameter based on Last Modified Datetime
In the model, i will setup incremental refresh without Detect Data Change feature (past 60 days)
Result: will always refresh all records of past 60 days based on Last Modified Datetime
Practice 2:
In power query, i will set my parameter based on CreatedDateTime
In the model, i will setup incremental refresh with Detect Data Change feature based on Last Modified Datetime
Result: will always refresh records only if maximum value of LastModified Datetime is changed
I adopt 2nd practice as the best practice due to it will utilize the detect data change feature, however if i set "Archive data 5 years before refresh data", and incrementally refresh data "1 days before refresh date with Detect Data Change" on Last Modified Column.
Will my detect data change only detect 1 days of data change on Last Modified Column? or if there are any change within 5 years of full data in any rows of LastModifiedDatetime, they will be refreshed as well?
So this basically mean, my report will only add 1 day of new data based on Created DateTime for every refresh, but will also refresh data changes if Last Modified Column has any changes from 5 years of data?
(because this doesnt seem like the case with Power BI tooltip during setting incremental refresh, indicating that "only refresh data in the last 1 day if the maximum value of this datetime column changes" )
Thanks in advance
Hi soso7668 ,
Practice 2 is indeed the better approach and aligns with Power BI's best practices for incremental refresh. By partitioning data using the CreatedDateTime column and enabling the Detect Data Changes feature on LastModifiedDateTime, you're able to append new data while selectively refreshing updated rows.
However, it's important to note that the "Detect data change" feature only evaluates changes within the defined refresh window for example, if your refresh window is set to the last 1 day, only changes in that 1-day period will be detected and refreshed, even if your archive spans 5 years.
This means that any updates outside the refresh window will not be picked up unless you manually trigger a full refresh or expand the refresh window. So in your scenario, Power BI will load one new day of data based on CreatedDateTime, and only reprocess that 1 day partition if a change is detected in LastModifiedDateTime it will not scan or refresh data from previous years, even if changes exist there.
5 Replies
Practice1 is incompatible with Incremental Refresh as it will result in data duplication across partitions. Your partition definition field must be immutable.
Detect Data change is a triple edged sword. You don't really want to have to use that. Read about Canary datasets...
Hi soso7668,
May i know has your issue been resolved? If the response provided by the Super user lbendlin, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.
Thank you for your understanding!
Hi soso7668,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi soso7668,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Hi soso7668 ,
Practice 2 is indeed the better approach and aligns with Power BI's best practices for incremental refresh. By partitioning data using the CreatedDateTime column and enabling the Detect Data Changes feature on LastModifiedDateTime, you're able to append new data while selectively refreshing updated rows.
However, it's important to note that the "Detect data change" feature only evaluates changes within the defined refresh window for example, if your refresh window is set to the last 1 day, only changes in that 1-day period will be detected and refreshed, even if your archive spans 5 years.
This means that any updates outside the refresh window will not be picked up unless you manually trigger a full refresh or expand the refresh window. So in your scenario, Power BI will load one new day of data based on CreatedDateTime, and only reprocess that 1 day partition if a change is detected in LastModifiedDateTime it will not scan or refresh data from previous years, even if changes exist there.
