We will discuss the variations between incremental and full refreshes in this blog post, along with how to use incremental refresh in Power BI.
Full refresh and incremental refresh are the two ways you can load data from the source into Power BI. While incremental refresh involves loading only the portion of the dataset that may change and adding it to the previously existing, non-changing dataset, full refresh entails retrieving the entire dataset each time and erasing the previous data.
Refreshing your complete dataset every time might be time- and resource-consuming when working with huge datasets. If you take use of incremental refresh, a crucial performance feature that streamlines the data refresh procedure, you won’t have to bother with this.
Knowing Incremental Refresh:
The incremental refresh feature allows the service to dynamically divide and distinguish between data that requires regular refreshes and data that can be refreshed less frequently. Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd are used to filter table data.
The parameters in Power BI Desktop are used to filter only a portion of the data that will be imported into the model when you first set up incremental refresh.
After being published to the service, the first refresh operation causes the service to override the parameter values in order to filter and query data for each partition based on date/time values for each row. This results in the creation of incremental refresh, historical partitions, and optionally a real-time Direct Query partition based on incremental refresh policy settings.
Only the rows that are within the dynamically determined refresh period by the parameters are returned by the query filters with each successive refresh.
Refreshed are the rows whose date and time fall inside the refresh interval. Rows containing a date or time that has elapsed beyond the refresh period are incorporated into the historical era and are not updated.
Folding Queries and Incremental Refresh:
In Power BI, pushing data transformation steps back to the data source while a query is running is known as “query folding.” It is crucial to incremental refresh since it reduces the volume of data processed and sent, enhancing performance and using less resources.
Power BI can effectively refresh smaller areas of the dataset by utilizing push down operations and data source optimizations through the use of folding transforms. This allows Power BI incremental refreshes to happen more quickly and to use resources more effectively.
Data Sources Compatible with Incremental Refresh and Query Folding:
Relational databases such as SAP HANA, Azure Analysis Services, Oracle, MySQL, PostgreSQL, and Snowflake, as well as Analysis Services such as SQL Server Analysis Services, SAP HANA, and Azure Analysis Services, offer the features that Power BI needs to effectively fold data transformation steps and carry out incremental refresh.
How to configure Incremental Refresh:
Applying filters, setting an incremental refresh policy, and defining RangeStart and RangeEnd parameters are all part of configuring incremental refresh. Once the dataset has been published to the Power BI Service, you run an initial refresh on it. The incremental refresh policy you choose is applied during the first and subsequent refresh processes.
A. Create Parameters:
Use Power Query Editor to define the RangeStart and RangeEnd parameters in this task and set their default values. Only when filtering the data in Power BI Desktop that will be loaded into the model are the default settings applicable. You should only submit a small portion of your data source’s most current data in the values you enter. The incremental refresh policy takes precedence over these values when they are published to the service.
- In Power BI Desktop, click Transform Data to open Power Query Editor.
- Click Manage Parameters > New Parameter.
In Manage Parameters > Name, type RangeStart (case sensitive), then in Type, select Date/Time, and then in Current Value, enter a start date/time value.
Create a second parameter named RangeEnd. In Type, select Date/Time, and then in Current Value, enter an end date/time value.
Once you have the RangeStart and RangeEnd parameters, you can use them to filter the data that will be put into the model.
B. Data filtering
Apply a filter depending on conditions in the RangeStart and RangeEnd parameters after they have been established.
Choose the date column you wish to filter on in Power Query Editor, then click the filter icon > Date/Time Filters > Custom Filter.
In Filter Rows, to specify the first condition, select ‘is after or is after or equal to,’ then select Parameter, and then select RangeStart. To specify the second condition, select ‘is before or equal to’, or ‘is before’, then select Parameter, and then select RangeEnd.
Note:
Power BI will load all records between RangeStart and RangeEnd as well as records that have RangeStart and RangeEnd dates if you use “is after or is equal to” for the first condition and “is before or is equal to” for the second.
Power BI will only load the records between RangeStart and RangeEnd and not the records that include RangeStart & RangeEnd dates if you use “is after” as the first condition and “is before” as the second.
Power BI will load all records between RangeStart and RangeEnd as well as records that contain the RangeStart date if you use “is after or equal to” for the first condition and “is before” for the second. However, records that contain the RangeEnd date will not be loaded.
To close, click OK.
Click Close & Apply in Power Query Editor. After that, Power Query will load data using any additional filters you specified in addition to the filters specified in the RangeStart and RangeEnd parameters.
C. Define the policy for incremental refresh.
You specify an incremental refresh strategy after you’ve specified the RangeStart and RangeEnd parameters and filtered data according to those criteria. Only when the model has been published to the service and a manual or scheduled refresh operation has been completed is the policy enforced.
- Click Incremental refresh after bringing up the table’s context menu in Data view > Fields >.
- Choose the table, confirm, or choose the table under Incremental refresh and real-time data. The table you choose in the Data view is the one that appears in the Select table list box by default.
- List the prerequisite settings:
Click the slider to the On position under Set import and refresh ranges > Incrementally refresh this table. If the slider is not in use, then there is no filter based on the RangeStart and RangeEnd parameters in the Power Query phrase for the table.
Choose the historical store period you wish to include in the dataset under Archive data starting. Unless there are other filters that apply, all rows with dates within this timeframe will be added to the dataset in the service.
Choose the refresh duration under Incrementally refresh data start. Every time a manual or scheduled refresh operation is carried out, the dataset will be updated to include any rows with dates inside this time frame.
- Specify any optional criteria:
To incorporate the most recent data changes made at the data source following the most recent refresh period, choose Get the latest data in real-time with DirectQuery (Premium only) under Choose optional options. A DirectQuery partition is added to the table by the incremental refresh policy as a result of this setting.
To update only entire days, select Only refresh complete days. Rows for the entire day are not refreshed if the refresh procedure determines that a day is not finished. If you choose Get the most recent data in real-time with DirectQuery (Premium only), this option is activated immediately.
To designate a date/time column that will be used to detect and update only the days when the data has changed, select Detect Data Changes. At the data source, a date/time column is typically required for auditing purposes. When using the RangeStart and RangeEnd parameters to segment the data, this column shouldn’t be used.
In the incremental range, the highest value of this column is assessed for every period. The current period is not refreshed if it hasn’t changed since the last refresh.
Depending on your configuration, your policy ought to resemble this:
After checking your options, click Apply to finish the refresh policy. This step does not load the source data.
D. Store and post content to the platform. After completing the filtering, refresh policy, and RangeStart and RangeEnd parameters, make sure to save your model before publishing it to the service. Make careful to enable the Large dataset storage format before triggering the service's initial refresh if your dataset grows to a significant size.
E. Refresh the dataset
Refresh the dataset within the service. During the first refresh, historical data for the whole store period will be loaded in addition to newly updated data for the refresh period. This may take a while, depending on the volume of data. Because the incremental refresh policy is applied and only the data for the period provided in the refresh policy setting is refreshed, subsequent refreshes—manual or scheduled—usually happen significantly faster.
Conclusion:
In order to wrap up, Power BI’s incremental refresh capability is a useful tool that makes it possible to efficiently and optimally refresh big datasets. It minimizes data transport and processing, resulting in better performance and less resource consumption, by splitting data into smaller chunks based on a partitioning column and using query folding.
An efficient incremental refresh approach is ensured by putting best practices into practice, such as choosing a good partitioning column, streamlining data loading, and creating a workable refresh schedule.
Incremental refresh offers additional benefits when Power BI Premium capabilities are regularly tested, monitored, and used. Power BI customers may benefit from quicker refresh times, more effective resource management, and timely data updates for their reports and visualizations using incremental refresh.
Share your valuable comments on this blog, and show your support by liking, sharing, and subscribing.
Thank you