Scheduled Refresh Policy

[debenaire]

Hi we have a source which is a SQL view of multiple databases. Over time more data gets added each day as well as more databases with historical data backfilled . What is the best way to setup our incremental refresh policy for Dataflows and subsequently semantic models. The data has a load date column and a date of the transaction. Should we partition our data according to transaction date then my fear is as new databases are added we will need to refresh all partitions to load the new data when all we really want is the new data. Also for semantic models this approach will timeout refresh due to the size of data. Should we use load date to partition the data? Also how should we utiliise incremental refrsh and detect changes to optimise this. Thanks.

[Anonymous · Accepted solution]

Hi @debenaire ,

Based on your description, partitioning the data for incremental refresh using the load date seems to be the most efficient approach. This method focuses on new data added to the system without regard to the transaction date, which is consistent with your requirement to only refresh new data without reprocessing historical data in the newly added database. By partitioning by load date, you can refresh only the most recently loaded data, thereby reducing the amount of data processed in each refresh cycle. And when backfilling historical data in a new database, there is no need for a full refresh because the incremental refresh will focus on the load date.
You mentioned that you want to consider using the Detect Data Changes feature to optimize incremental refreshes. This can be especially useful if the data has a column that indicates when a row has been modified. By specifying this column in the incremental refresh policy, Power BI can only refresh rows that have changed since the last refresh, thus further reducing the amount of refreshes.

You can refer to the following documentation
Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...
Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn
Troubleshoot incremental refresh and real-time data - Power BI | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly