As part of our strategy to converge enterprise and self-service BI on Power BI as a single platform, we announced the public preview of incremental refresh last summer. While there is still more work to be done to get it to general availability, we have seen strong uptake of incremental refresh.
Incremental refresh is a great example of how Power BI is modernizing/simplifying complex BI implementations. It enables incremental loading of new or changed data to a BI model without needing to reload the full set of data. This can have the following benefits:
- Refreshes are faster since you don’t need to load all the data every time and more can be done in parallel. This allows for new data to be added quickly.
- Less memory may be used during processing (again because you are only loading part of the data).
- Models can grow to very large sizes.
- Old data can be dropped when not needed.
- Existing data can be updated without updating the entire model.
- Refreshes may be more reliable.
The Power BI service partitions data based on date range. This is what enables only certain partitions to be refreshed incrementally. To make this work, the partition filter conditions are pushed down to the source system by including them in the queries. Using Power Query terminology, this is called “query folding”. It is not recommended that incremental refresh is used when the required query folding cannot take place. For more detailed information on query folding, please see the incremental refresh docs article.
Given the various levels of query-folding support for each data source, it is recommended that verification is performed to ensure the filter logic is included in the source queries. To make this easier, the February update of Power BI Desktop will attempt to perform this verification for you. SQL based data sources such as SQL, Oracle and Teradata should be able to rely on this warning. Other data sources may be unable to verify without tracing queries. If Power BI Desktop is unable to confirm, the warning is displayed.
Note: if the PBIX file was authored in a version of Power BI Desktop prior to the February release, the warning will be displayed regardless until a data refresh is performed.
Here is a video you might find useful that discusses query folding for incremental refresh.
We look forward to getting back to complete the work to get incremental refresh to general availability, so watch this space!