Incremental Update Power BI: It allows you to load only the changes made to the data, rather than replacing all the data with each refresh. This saves time and resources. Learn everything you need to know about this feature!"
There are two main ways to transfer data from one location (A) to another (B). The first involves clearing all data from location B and loading all the data from location A.
The second method is incremental loading / Incremental Update Power BI. After initially transferring all data from location A to location B, this approach involves loading only the changes made to the data from A to B.
Data in location B is never erased or replaced. Only data that has not yet been loaded from A to B is transferred.
By default, when refreshing data in Power BI, the first method is used. To use incremental refresh, it must first be configured.
This approach was formerly available only in ‘import’ or ‘dual’ mode, but the ‘composite tables’ introduced in December 2021 changed everything.
Now, with this new type of tables, incremental loading is available for a portion of the table when a specific partition is in ‘Direct Query’ mode while other partitions are in ‘import’ storage mode.
Furthermore, Incremental Update Power BI was previously available only for Power BI Premium accounts. Since February 2020, this option is also accessible for Power BI Pro accounts with certain restrictions.
However, composite tables are currently available only for Power BI Premium and Premium Per User (PPU) accounts. Power BI Pro accounts cannot take advantage of this feature.”
Advantages of Incremental Update Power BI: What are they?
Incremental refresh offers several advantages, especially for large tables with hundreds of millions of rows. Firstly, it allows for much faster data refresh compared to the truncate and load method since only the incremental range of data is refreshed.
Additionally, the incremental data refresh process consumes significantly fewer resources than refreshing all data. This refresh is less expensive and easier to maintain.
For massive datasets with billions of rows, incremental refresh is essential. Power BI Desktop utilizes local machine memory to process data, and it is unlikely that a standard computer would have enough memory to import such a volume of data.
How does Incremental Update Power BI work?
After publishing the model to Power BI Service, the service creates multiple partitions on the table with incremental strategies based on year, month, or day.
Depending on the defined incremental strategy, these partitions are automatically refreshed as long as you schedule automatic refresh. Over time, some of these partitions will be dropped, and others will be merged.
After configuring incremental refresh in Power BI, you have two data ranges: the historical range and the incremental range. The historical range contains all data processed in the past, while the incremental range contains the data currently being processed.
Only data from the incremental range is affected by incremental refresh. Historical data will not be modified. New rows can be inserted, updated, or deleted from the table.
When defining an incremental strategy, you set a date range, which is the historical range. Data between these two dates will not change. Over time, some old partitions will be dropped, and other partitions will move to the historical range.
How to implement Incremental Update Power BI ?
Incremental refresh can be configured in Power BI Desktop and in Dataflows within a Premium workspace. After implementing it in Power BI Desktop, the model is published to Power BI Service.
The first refresh takes longer since all the data is transferred for the first time from their sources to Power BI Service. After this initial load, all future refreshes will be incremental.
Implementing incremental refresh is a two-step process. First, you need to prepare the prerequisites in Power Query and define the rules in the data model. The next step is publishing the model to Power BI Service and refreshing the dataset.
To prepare the prerequisites in Power Query, start by defining two parameters with the DateTime data type in Power Query Editor. These parameters, named RangeStart and RangeEnd, are used to define the rules for incremental refresh.
Next, filter the table by a DateTime column using the RangeStart and RangeEnd parameters when the DateTime column value falls between RangeStart and RangeEnd.
After completing the initial preparation in Power Query, you need to define the incremental refresh strategy in the data model in Power BI Desktop. Then, simply publish the model to Power BI Service to begin the incremental refresh of the dataset.
Optional settings for Incremental Update Power BI
The window for configuring incremental refresh contains a section dedicated to optional settings. Here’s an overview of the available features.
On a Premium Power BI account, you can get the latest data in real-time with DirectQuery. This feature allows the most recent data partition to connect to the source system via DirectQuery.
Another optional setting is to include complete periods in the refresh. These periods can be years, months, or days, depending on the refresh range chosen in the previous settings. During the refresh, only the data from completed periods will be included.
The last optional setting is for data change detection. In many data integration or Data Warehousing processes, audit columns are added to tables for useful metadata such as the last modification date or the user who initiated it. Data detection in Power BI is very useful if your table has a DateTime column indicating data changes.
This option allows you to select the desired audit column, which cannot be the same column used to create partitions with the RangeStart and RangeEnd parameters. For each scheduled refresh period, Power BI considers the maximum value of the column within the incremental range to detect changes that occurred during that period.
Publish the model on Power BI Service
Publishing the model to Power BI Service involves uploading the Power BI Desktop (PBIX) report file containing the data model and any reports to Power BI Service.
There are several methods to accomplish this. The most common one is to click the “Publish” button from the Home tab, select the workspace where you want to publish the model, and then click the “Select” button.
Typically, automatic data refreshes are scheduled from Power BI Service. Incremental refresh is especially relevant when data needs frequent updates.
Updating the dataset on Power BI Service
Now that the model is published, you need to go to Power BI Service to refresh the dataset. If you used an on-premises data source, you must configure the On-Premise Data Gateway.
Next, open Power BI Service and navigate to the desired workspace. Select the dataset and click the refresh button.
After the initial dataset refresh in Power BI Service, it’s no longer possible to download the report. This limitation makes sense because a local machine wouldn’t be able to download the billions of incrementally loaded data rows in the table. It would require several hundred gigabytes of RAM.
To make further changes to the data model, you must use tools other than Power BI Desktop to deploy the changes to the existing dataset. Examples of such tools include Tabular Editor, ALM Toolkit, or SQL Server Management Studio (SSMS). These tools need to be connected to the dataset using XMLA endpoints, which are available only on Power BI Premium and PPU.
Hybrid boards for Incremental Update Power BI
If you need near-real-time or almost real-time updated data on a large table, you can use hybrid tables. Such a table can accommodate both imported and DirectQuery data.
This allows for real-time data refresh while benefiting from maximum performance on Power BI.
Hybrid tables are partitioned. Their most recent partition is a DirectQuery from the data source, and their historical data is imported from another partition.
It’s not a Dual storage mode but a table where one part is imported and the other is DirectQuery. The hybrid table option can only be applied to a table with incremental refresh enabled.
To activate it, a Power BI Premium or PPU license is required, and the dataset must be published to a Premium or PPU workspace. Select the “Get the latest data in real-time with DirectQuery” option. Afterward, you’ll be able to see the real-time data range and a timeline diagram indicating the chronology.
How do I master Power BI?
Incremental Update Power BI is one of the many lesser-known features of Power BI. To learn how to master this software and all its nuances, DataScientest is the place to be.
Our Power BI training is divided into two parts: beginner and advanced. The first part is for beginners looking to discover the tool and learn how to use features like DAX and Power Query. It is completed in 24 hours, spread over 3 days.
The second part is designed for advanced users and allows you to delve deeper into the use of each Power BI feature. You will explore complex concepts such as incremental refresh and DataFlows. The program is completed in 14 hours, spread over 2 days.
If you are a beginner and want to master Power BI thoroughly, you can choose the Complete Mastery curriculum, which includes both parts of the training. It takes 38 hours, spread over 5 days.
As a Microsoft Learning Partner, DataScientest prepares you for the PL-300 exam and helps you pass this test to obtain the Power BI Data Analyst Associate certification.
