Incremental refresh involves dividing a large data set into different parts based on periods. The renewal of the dataset can be set in such a way that not the entire dataset is included, but only predetermined parts.
The advantages of incremental refresh are speeding up refresh time, the refresh time is more reliable, and resource consumption is reduced. It can make a dataset usable again.
On the other hand, incremental refresh will have to be managed. This takes time, knowledge, and energy. Managing incremental refresh is only possible using a third-party tool*. Using third-party tools requires a premium work environment. This means that incremental refresh is only recommended in a premium working environment.
This also means that in addition to knowledge of incremental refresh, the dataset administrator must also have knowledge of third-party tools, such as Tabular editor and DAX studio.
It is important to mention that incremental refresh uses an XLM endpoint, which means that the dataset can no longer be downloaded from the Power BI service.
Incremental refresh works best on structured data sources such as a SQL database. Other data sources are also possible, but a data source must at least contain the following: A mechanism to filter by date with the date/time type.
Incremental refresh is a must for large data sets with premium capacity, but it should be considered before implementation.
*See third-party tool article
Introduction
Power BI is from origin a data analysis tool. This means that one has the option to import a dataset and then visualize and therefore analyze it.
Compared to Excel with a limit of 500,000 rows of data, Power BI does not have this limit. The data import options with Power BI are almost endless. What is stated is: The more rows Power BI will have to import, the longer the refresh time. An import dataset with 1,000 rows will load in a few seconds, but an import dataset with 1 billion rows will take over an hour.
An import time of one hour is of course not ideal. This also means that not every row of these 1 billion rows is relevant for renewal (leaving aside the first import). It could well be that a dataset consists of several years and changes are only made in the current year. The remaining years can be considered historic.
In principle, Power BI assumes a complete refresh. This means that with each renewal the entire dataset is reloaded (refreshed). This would mean that each renewal would take over an hour again. This is even though not the entire dataset of 1 billion rows needs to be updated, but only the current year. Renewing only the current year could drastically reduce the renewal time.
This is called “incremental refresh”. With incremental refresh, Power BI divides the import dataset into parts (partitions). Each part (partition) manages a certain part of the dataset, categorized by period. A refresh policy* determines how the data is partitioned and which of these partitions are updated upon refresh.
*See Incremental policy definition.
This gives the option of only including parts of the dataset in the refresh, while the rest of the data is considered historical.
Bron picture: https://learn.microsoft.com/nl-nl/power-bi/connect-data/incremental-refresh-overview.
Set up incremental refresh
M-Query
One starts by creating two parameters in the M-Query of the imported dataset. These parameters will be used to filter a data subset (in tabular form) by period. Please note: the parameters are case sensitive.
RangeStart
Type: Date/Time
Purpose: RangeStart concerns the oldest date and is therefore the starting period. When published to the service, this parameter will be overwritten by the refresh policy.
RangeEnd
Type: Date/Time
Purpose: RangeEnd refers to the last available date in the dataset. When published to the service, this parameter will be overwritten by the refresh policy.
Once you have created the parameters, you choose the table on which you want to set incremental refresh. It goes without saying that this is a fact table with a large number of rows. You choose the date column and then filter it based on the two parameters created.
Bron picture: https://learn.microsoft.com/nl-nl/power-bi/connect-data/incremental-refresh-overview
Incremental policy definition
Once the filtered table has been loaded into the model, you can define the incremental policy. The incremental policy is officially created when published to the Power BI service.
One defines the incremental policy by clicking + right mouse button on the table – incremental refresh.
- Select table
- The table on which incremental refresh will be set. Incremental refresh is per table and therefore not the entire dataset.
- Choose the import and refresh scope
- Archive the data starting … for renewal dates. In other words, define the start and end period of the historical dataset.
- Incremental refresh will begin… for refresh dates. In other words, define the renewal period.
- Archive the data starting … for renewal dates. In other words, define the start and end period of the historical dataset.
- Optional option
- Detect data changes. To configure archived data to be refreshed when the value of an additional date column (e.g. LastUpdate) changes.
Bron: https://docs.tabulareditor.com/te3/tutorials/incremental-refresh/incremental-refresh-modify.html
- Detect data changes. To configure archived data to be refreshed when the value of an additional date column (e.g. LastUpdate) changes.
Publish to the Power BI service
When publishing to the service, a manual refresh is performed on the model. This can take a long time for the first renewal. After all, partitions must be created, historical data must be loaded and objects such as relationships and hierarchies must be recalculated.
Subsequent refreshes will be faster, because the dataset will only be refreshed according to the incremental policy.
Manage incremental refresh
Incremental renewal will need to be managed. After all, it may happen that a data change takes place in the historical part of the renewal policy. Partitions belonging to the historical part will then have to be manually renewed. Another option is a password change. The references are then no longer correct, and this may possibly lead to an error during renewal. In this case, the data and partitions will need to be checked.
To manage an incremental policy, one needs access to a premium workspace. You can then use a third-party tool, for example Tabular Editor 3 (License) or management server studio, to connect to the dataset in the Power BI service. It is therefore always advised to only enable incremental refresh on a dataset if you have a premium environment.
1. Check partitions for data in Tabular Editor 3
You can open the built-in VertiPaq Analyzer* by clicking on VertiPaq Analyzer at the bottom and then collect statistics at the top right. In the partitions section you can see which partition contains data and how much data this partition concerns.
*See image at bottom 4.
2. Refresh partitions manually
First look for which partition needs to be renewed (see 1.). Once the partition has been found, navigate to the correct table in the TOM Explorer and open the number of partitions in the partitions tab. With the right mouse button, you navigate to refresh and do a complete refresh on the specific partition*. When refreshed, the partition will contain the data and the change will be visible.
*See image at bottom 4.
3. Adjust renewal policy
You can also adjust the renewal policy with TB3. When selecting the table* a refresh policy option will appear. One can adjust this, save to the model, and then refresh all partitions again. Please note that this is the same as a first data renewal and therefore takes longer.
*See image at bottom 4.
4. Adjust policy detect data change
One can also adjust the policy to detect data changes. When selecting the table, you can copy the code below and paste it into the polling expression heading. Replace LastUpdate with the desired column name. One can adjust this, save to the model, and then refresh all partitions again.
// Retrieves the maximum value of the column [LastUpdate]
// Replace LastUpdate with your own column name
// The data will refresh for any records where the value in this column
// equals the maximum value in the column across the entire table
let
#”maxLastUpdate” =
List.Max(
// Replace the below with your column and table name
Orders[LastUpdate]
),
accountForNu11 =
if #”maxLastUpdate” = null
then #datetime(1901, 01, 01, 00, 00, 00)
else #”maxLastUpdate”
in
accountForNu11
