Power BI Incremental Refresh: The Ultimate Guide 2023

Published: March 21, 2023
Last Updated: April 30, 2024
Chief Strategy Officer at Alpha Serve
When working with a large dataset, refreshing your entire data each time can be time and resource-consuming. You won't have to deal with this if you leverage one of the key performance features that simplify the data refresh process, known as incremental refresh. This post explains all you need to know about Power BI incremental refresh and how to use it.
Integrate Jira and Oracle Analytics

What is incremental refresh in Power BI

What is incremental refresh in Power BI
Incremental refresh refers to loading only the new or updated data sets added to a data table after the previous loading cycle has been completed. Power BI incremental refresh features automate the creation of data partitions for tables that get updated with new data.

It helps to extend the scheduled refresh operations for such tables to ensure only the most recent sections are uploaded instead of the entire table.

Full Refresh vs Incremental Refresh

What is the difference between Power BI incremental refresh and full refresh? The main difference between Power BI incremental refresh and full refresh is that the full refresh reloads all data in a dataset, whereas incremental refresh updates only new or changed data since the last refresh. When doing a full refresh, all previous rows that have been uploaded, as well as any new rows, will be uploaded as part of the refresh. This process takes longer than uploading only the new rows that have been added since the last refresh, which is the case with the incremental refresh.

Benefits of Power BI Incremental Refresh

Refreshing only those data that arrived or changed after your last refresh process has numerous benefits, especially when working with large data sets. Some of the benefits of doing an incremental refresh of Power BI include the following:

Lower Refresh Times: Imagine working with a data table containing millions of rows of data that get updated frequently. Refreshing data for such a large table will take a long time. To keep your analytics workload in sync, you must refresh the data as often as possible. But with the incremental refresh, you're only loading a small portion of the entire data simultaneously. The piece you're loading is the rows of data added after your last refresh, which means your refresh is completed faster.

Improved Reliability of SQL Queries: Since queries that run for a shorter duration are less likely to lock the database for extended periods, the refresh process becomes more dependable and less prone to causing disruptions to the database system.

Minimize Resource Consumption: having fewer data to refresh reduces the overall memory and other database resources used in Power BI to complete the refresh.

It makes it easier to work with large data sets. Since there's no need to entirely refresh the entire dataset each time you want to complete a refresh operation, it's much easier to work with datasets with millions or even billions of rows.

How to Set Up Incremental Refresh in Power BI

How to Set Up Incremental Refresh in Power BI
Uploading your data with incremental refresh splits your entire data table into partitions. The quality of each cell depends on the settings you have applied at the point of setting up incremental refresh. In this section, we will cover all you need to know about setting up an incremental load in Power BI, including the requirements, steps to follow, and the limitations of this method of data refresh.

Requirements to Set Up Incremental Refresh in Power BI

To successfully set up an incremental refresh in Power BI, the explained requirements below are necessary to have:

Table with Date Field(s)
In implementing incremental refresh, your dataset must have a table with a DateTime or timestamp field. This column will mark new or updated information that needs to be refreshed. You can tell by looking at this column if you have made any edits to a table since Power BI last refreshed.

A Data Source That Supports Query Folding
Power BI's Query Folding functionality optimizes queries by offloading some processing work to the data source. A data source that supports query folding is required for an incremental refresh. Based on the date column in your database, the data source must be able to conduct queries that return just the rows that have changed since the last refresh.

Supported Data Sources
Power BI supports incremental refresh for the following data sources: SQL Server, Oracle, PostgreSQL, MySQL, and Teradata. You can also utilize additional data sources that enable query folding; however, you may have to construct the queries to use incremental refresh manually. In addition, specific data sources may necessitate the installation of a gateway to enable communication between Power BI and the data source.

Supported Plans
Whether or not you can use Incremental refresh also depends on the Power BI plan you're on. You can only use Power BI incremental refresh with Power Bi Pro, Power BI Embedded datasets, and Premium per user plan. Additionally, you want to get the latest data in real-time using DirectQuery. In that case, you must be on a Power BI Premium, Power BI Embedded datasets, or Premium per user plan.

Steps to Set Up Incremental Refresh in Power BI Desktop

The way Power BI desktop incremental refresh works, all the data in your dataset are partitioned into time slices. Only the most recent partition is uploaded anytime the data is refreshed. To set this up, you start configuring RangeStart and RangeEnd parameters on your Power BI Desktop. Here are the steps to follow to do this.

Create the RangeStart and RangeEnd Parameters
The first step is to use the Power Query Editor to create RangeStart and RangeEnd parameters and set their default values. These parameters allow you to filter the number of rows to be initially loaded into the Power BI model. To do this, select Manage Parameters in the Power Query Editor. In the window that opens, create the parameter by typing the name type, e.g., RangeEnd (case sensitive), selecting the Date/Time, and entering the date/time value in the box for Current Value.

Filter The Data Using The Parameters
Still, in the Query editor, select the Custom Filter option for the data column you're trying to set a filter for. Here you will be prompted to choose two conditions. For the first condition, select the After or Equal to option; for the second condition, select the Is before option. Alternatively, you can choose the After option for the first condition, in which case the second condition will be Is before or Equal to. Click the Ok button to close this dialog then click Close & Apply to apply the filter.

Define the Incremental Refresh Policy
In the data view tab, right-click on the table and select Incremental refresh. Next, specify all the required settings and click Apply to confirm. After completing all these steps, save the model and publish it to the service. Finally, refresh the dataset. The first refresh will likely take longer since you're importing all the data. However, subsequent refreshes from that point will be much faster since you're refreshing data for the period specified in the new incremental refresh policy you set up.

Limitations of Power BI Incremental Load for Datasets

  • Limited data source support: Incremental refresh may not be supported for all data sources.
  • One date time column per dataset: Incremental refresh is limited to only one date time column per dataset, which may not be suitable for datasets with multiple date time columns.
  • PBIX file retrieval: Once the incremental refresh is set up, the PBIX file cannot be retrieved from the service due to data partitioning.

Incremental Refresh in Power BI Dataflows

Incremental Refresh in Power BI Dataflows
The Power BI incremental refresh feature for data flows and datasets are intended to work together seamlessly. It's acceptable and supported to have an incrementally refreshing entity in a dataflow, fully loaded into a dataset, or a fully loaded entity in a dataflow incrementally loaded to a dataset.

Requirements to Set Up Power BI Incremental Refresh for Dataflows

Some specific requirements must be met to set up Power BI Incremental Refresh for Dataflows. Firstly, the dataflow needs to be located in a workspace within a Premium capacity in order to use the incremental refresh feature.

In the case of Power Apps, using incremental refresh requires either Power Apps per-app or per-user plans. It is only available for dataflows with Azure Data Lake Storage as the destination.

Additionally, whether using Power BI or Power Apps, it is necessary for the source data that is being ingested into the dataflow to have a DateTime field. This is a requirement for the incremental refresh feature to be able to filter the data effectively.

Steps to Set Up Power BI Incremental Refresh for Dataflows

Power BI dataflows typically contain more than one entity. The Power Query incremental refresh setting can be done separately for each entity. It means one data flow can have both incrementally refreshed and fully-refreshed entities. Setting up incremental refresh for data flows is more accessible than setting it up for datasets since you won't need to create separate RangeStart and RangeEnd parameters.

Configure your entity
The first step in setting up incremental refresh for dataflow is configuring the specific entity you want to refresh incrementally like any other entity.

Select Incremental Refresh
Next, click on the incremental refresh icon. It'sIt's the last icon to the right of the entity.

Turn on Incremental refresh and adjust the settings accordingly
On the next open window, turn on incremental refresh and adjust the settings. The filter-field drop-down allows you to select the field of the entity that the increment should be filtered for. The incremental refresh will only work if your entity has a DateTime field.

Power BI Connector for ServiceNow and Incremental Refresh

Power BI Connector for ServiceNow and Incremental Refresh
Those who aim to utilize ServiceNow data for analysis in Power BI or are already doing so may be aware that ServiceNow is not included among the available data sources supporting incremental refresh. Consequently, users are often tasked with handling extensive data sets and enduring prolonged periods dedicated to fully updating the data.

If you find yourself in this situation, we have a solution to offer - the Power BI Connector for ServiceNow. In addition to its various other benefits, this connector supports an incremental refresh.

Power BI Connector for ServiceNow Overview

Power BI Connector for ServiceNow Overview
The Power BI Connector for ServiceNow is an enterprise plugin for seamlessly integrating ServiceNow and Power BI platform. It allows users to export their ServiceNow data to Power BI for analysis and generate insightful reports quickly.

The Power BI Connector for ServiceNow has an intuitive interface allowing users to connect directly and seamlessly between both platforms without writing code.

The possibility of customizing data queries when using this connector also means users get complete control over the data they export. They can apply advanced filters to manage ServiceNow data to export and visualize in Power BI. Users can create multiple connectors and customize their data queries to retrieve specific data sets based on various business purposes, date ranges, status, and priority.

The real-time data refresh feature of Power BI Connector for ServiceNow ensures that users can seamlessly incorporate the latest data from ServiceNow into their Power BI reports and dashboards and generate insights in real-time.

The Power BI Connector for ServiceNow is equipped with robust security features such as user roles and permissions to ensure the privacy and integrity of sensitive data, minimizing the risk of data breaches or unauthorized access.

How to Export ServiceNow Data to Power BI and Configure Incremental Refresh

The Power BI Connector for ServiceNow not only simplifies the process of exporting data from ServiceNow to Power BI, but it also supports the use of incremental refresh.

To get started, the first step is to install the connector. You can find detailed instructions on installing Power BI Connector by following the Installation Guideline. Once the app is installed, you can proceed to create a data source and configure incremental refresh by following these steps:
1. Create a data source in Power BI Connector for ServiceNow
To create a data source using Power BI Connector for ServiceNow, users must have either a PBI ADMIN or PBI EDITOR role. Once on the Power BI Connector page, click the New button in the Data Sources table. Enter the required information into each field, including the source name, description, and set row limit.
Create a data source in Power BI Connector for ServiceNow
Next, click Add Table and search for the tables you need using the search field or scrolling down the list. After adding the table, select the fields required for the reports. Users can use various filters to narrow down the exported results. To apply any edits, click the Save button at the end of the page. The data can now be imported into Power BI for analysis and visualization.
Select Tables and Fields to export to Power BI
The data can now be imported into Power BI for analysis and visualization.

To import data from ServiceNow to Power BI Desktop, you will need to start by copying the OData feed URL from the ServiceNow instance. You can do this by clicking on the "Copy OData feed URL" button or the "Copy" button next to each data source.

Next, open Power BI Desktop and click on "Get Data." Select "OData feed" from the drop-down menu and paste the copied URL into the input field. Then, click "OK" to proceed.
copying the OData feed URL from the ServiceNow instance.
In the "Navigator" window that opens, switch to the "Basic authentication" tab and enter your ServiceNow credentials. Then, click "Connect" to authenticate your account.

In the "Navigator preview" window, you can select the tables you want to load by checking the checkboxes. Once you have chosen it, click "Load" to start the import process. After completing the import process, you can begin building dashboards based on the imported ServiceNow data in Power BI Desktop.
select the tables you want to load to Power BI by checking the checkboxes
If you require detailed instructions, you can access the complete user guide which provides a step-by-step guide on how to use Power BI Connector for ServiceNow.
2. Set Up Incremental Refresh to Update ServiceNow Data
Once you have created a data source and exported ServiceNow data to Power BI Desktop, you can proceed to configure incremental refresh using the following steps:

1. Open the Transform Data window and create two parameters: RangeStart and RangeEnd, which determine the time period used for data change monitoring.

2. In the table to be configured, select the field for which the TimeRange filter will be applied.

3. Select the Date/Time Filters option and choose CustomFilter. Set the filter as shown on the screen and click OK.
Configure Incremental Refresh with Power BI Connector for ServiceNow
4. Click on Close&Apply in the Transform Data window and wait for the filters to be applied.

5. Select the table to be configured for Incremental Refresh, open the settings menu, and choose Incremental Refresh.
6. In the open dialog, set the Archive data starting and Incrementally Refresh data starting fields, considering the TimeRange set in step 5.

7. Click Apply, save the data source report as a .pbix file, and publish it to Power BI Service.

It is important to note that the first refresh may take longer as it creates an Archived data set. In contrast, all subsequent refreshes will calculate only frequently updated data and take less time than the original refresh. Reference type fields will show live data after each refresh.

Detailed instructions on how to configure Incremental Refresh can be found here.

Conclusion

It'sIt's possible to avoid incremental refresh when working with a relatively small data model. However, incremental refresh can save you time and resources when working on large volumes of data. If your data is being sourced from ServiceNow, you can simplify the process even better by connecting Power BI with ServiceNow with a Power BI connector.

The connector facilitates data transfer to Power BI, and implementing incremental refresh within the connector further optimizes your data refresh process. And, it's not only about the time needed for bringing the new data into your Power BI datasets but also the resources needed to reprocess the whole gigantic tables.

Subscribe to the Alpha Serve blog

Stay up to date with different topics related to project management, agile methodology, and more.
By clicking the button you agree to our Privacy Policy

Related Topics


Latest from Alpha Serve