When data is LOADED, it is incrementally refreshed, which implies that only the new or updated data is loaded. Power BI Incremental refresh provides automated partition formation and administration for dataset tables that often load new and updated data, extending scheduled refresh procedures. This is typically one or more tables, such as a fact table in a relational or star database structure, that store transaction data that changes frequently and can grow rapidly.

Understanding Power BI Incremental Refresh

Incremental Refresh works by partitioning the data in the service’s dataset. Time slices are used to create these partitions and only the data in the most recent partition is refreshed once the data has been imported into the dataset. Two parameters: RangeStart and RangeEnd, must be configured in Power BI Desktop to do this.

Setting Up Incremental Refresh in Power BI Desktop

You will be able to set up incremental refresh in Power BI desktop by connecting to data from a variety of sources using Power BI. The home ribbon is used to connect to data. Select the Get data button label or the down arrow to bring up the most common data types menu. 

Show the most common data types menu and click more to open the Get Data dialogue box. By selecting the Get data icon directly, you can open the Get Data dialogue box without having to go through the most common menu.

Enable the Preview Feature

The first step is to use Power BI Desktop’s Preview capabilities to enable the Incremental refresh Policies.

• Navigate to File -> Options and Settings -> Options in Power BI Desktop to enable the preview option.
• Then select Incremental Refresh Policies from the Preview features menu. After that, Restart Power BI Desktop.

Img Source: Powerbiusers.com

Configuring the DateTime parameters

• Open Power Query Editor in Power BI Desktop by clicking Transform data.
• Select Manage Parameters > New Parameter from the drop-down menu.
• In the Power BI Desktop, right-click on the table and choose Incremental Refresh.
• You can choose the table first in the Incremental Refresh settings window. You won’t be able to do the setting for the table if it doesn’t have the two parameters of RangeStart and RangeEnd used in the filter criterion.

• In Manage Parameters > Name, write RangeStart (case sensitive), then pick Date/Time from Type, and then enter a start date/time value in Current Value.

• Add a RangeEnd parameter to the mix. Select date/time as the Type, and then enter an end date/time value in Current Value.
  Now that you have RangeStart and RangeEnd parameters, you can use them to filter the data that will be put into the model.
• The incremental refresh is simple to set up. You just set the number of rows to store (load once and save) and the number of rows to Refresh (re-load every time)
• Connecting the data source with Power BI Desktop

Filtering the fact table

Each table in the new dataset has a single partition when you publish a Power BI Desktop model to the service. All of the rows for that table are contained in that single partition. If the table is large, such as with tens of millions or even billions of rows, a refresh might take a long time and require a lot of resources.

The service dynamically splits and separates data that has to be renewed regularly from data that can be refreshed less frequently with the incremental refresh. Power Query date/time parameters with the stored RangeStart and RangeEnd are used to filter table data.

Defining Refresh Policy in BI

Once filters have been implemented and a set of data has been imported into the model, users will define an incremental refresh policy for the table. The service uses the policy to construct and manage table partitions and conduct refresh actions after the model have been published to the service. The Incremental refresh and real-time data dialogue box will be used to create the policy, allowing you to provide both necessary and optional settings.

Verifying Incremental Refresh: How to check if it working?

You can test whether incremental refresh is working or not by deploying it to a table and comparing it to a table that does not have incremental refresh deployed to it. The table with incremental refresh will have multiple partitions for current and previous years, whereas the table without incremental refresh will only have one partition. Precaution to take before performing action

Here are some prerequisites for using Power BI’s incremental refresh feature:

• Import Data Mode: In Import Data mode, the data should be imported into the Power BI data model. The Direct Query mode does not support incremental refresh.
• Table with Date Field: You’ll need a table (or more) containing date field(s) in order to set up the incremental refresh. The date field is the one that will be affected by the partial data refresh.
• Licensing Requirement: You’ll also need a Power BI Premium subscription. You won’t be able to use this feature if the file is not in a workspace with premium capacity when you publish it to the service if you configure without the subscription.

Note: Incremental refresh without query folding, according to Microsoft, can result in worse performance.

Publish to Power BI Service Schedule Refresh Frequency

You publish the model to the service after defining the incremental refresh policy. After you’ve finished publishing, you can refresh the dataset for the first time.

If you suspect a dataset will grow beyond 1 GB or more in a workspace with Premium capacity, you can increase refresh operation efficiency and ensure the dataset doesn’t exceed size limitations by enabling a large dataset storage format before completing the first refresh operation in the service.

Limitations: Things to Know Beforehand

After setting up incremental refresh in Power BI, you will no longer be able to get the PBIX file from the service because the data has been partitioned. It also makes sense because the data is probably too large to be downloaded.

Natives Queries or ODBC with Incremental Refresh in Power BI

What is Query Folding and Why Should You Care About It?

Query folding is critical for the incremental refresh, to the point where Power BI will notify you if it can’t be done. It won’t break your incremental refresh “per se,” but without query folding, it won’t serve its main purpose – reducing the amount of data that has to be refreshed in your data model — because the Mashup engine will have to recover all data from the source and thereafter apply subsequent steps to filter the data.

Given the different levels of query-folding support for each data source, it’s a good idea to double-check that the filter logic is included in the source queries. The ideal method to see if a query folds or not is to right-click on it and look at what the View Native Query option looks like: This step (probably) does not fold if it is greyed out. When the View Native Query is greyed out, your query probably doesn’t fold, but it’s not 100% correct. 

If, on the other hand, you are able to select this option, your query will most likely fold again. You might be perplexed by the word: Probably. But that’s the proper word, as you can’t be 100% sure that if the View Native Query option is disabled, that query doesn’t fold.

Keep The Existing Data in Your Power BI Dataset and Add New Data To It Using Incremental Refresh

1. Turn on the “Incremental refresh” slider.
2. Select Days from the menu under “Store rows in the last” and enter the number of days you wish to store data for. Data that is older than the specified number of days will be removed from the dataset.
3. Select Days from the menu under “Refresh rows in the last” and enter 1 under “Refresh rows in the last.”
4. Leave the boxes for “Detect data changes” and “Only refresh complete day” unchecked.

Power BI Incremental Refresh with Web API Data Source

1. Use the Web to query the API data source. The Query argument is used in the Contents function, which is very significant.
2. Create a URL dynamically, including the query string parameters. Using the Query argument allows you to dynamically add the parameters while retaining a valid base URL, which is critical if you want Power BI to check the connection before refreshing the data.
3. The query string arguments are added to the base URL when the Power BI dataset is refreshed.
4. You’ll be able to construct a table in your Power BI data model that will be configured for an incremental refresh after the function has been created.

Conclusion

In Power BI, setting up incremental refresh involves loading only a portion of the data on a regular basis and keeping the consistent data. This procedure will significantly reduce your refresh time; however, it does have some prerequisites. A date field is required in your table, and there is now a licensing requirement for Power BI premium to do so. This setting can be done on a Power BI dataset or in Power BI dataflows. If you do this with a dataset, you won’t be able to download the PBIX file once it’s been published.