Read this article until the end to unlock the secrets to set up incremental refresh in Power BI to reduce cloud instance compute costs and get real-time data quickly.
When working with large datasets for business intelligence analytics, updating data from its server becomes challenging. The database you’re analyzing on your Power BI app is constantly updating in the backend.
If you refresh the dataset, it might keep updating for hours and even more when it is enormous or get real-time data feeds from many automated machines, like retail POSs, online checkouts, online form data, etc.
To handle such complicated situations in data import and analysis in Power BI, Microsoft included multiple data refresh features. Once such functionality is incremental refresh, it’s quite popular for effortless real-time data import and analysis.
Read on to learn what it is, what it does, what are the prerequisites to use this tool in Power BI, and how to set it up in easy steps. Let’s get started!
Also read: Tableau vs. Power BI: The Better Choice in 2023?
Incremental Refresh in Power BI
Incremental refresh is an extension of the scheduled data refresh in Power BI. It creates different partitions of the dataset in its storage space by segregating dataset tables that get updated frequently.
Most databases that you use contain a few tables that update frequently because they contain transaction data, like sales amounts, inventory numbers, etc. These data can grow exponentially large over time, making the original Power BI file take up huge space on your cloud or onsite servers.
Here, Power BI can utilize the incremental refresh policy to partition the data table according to the RangeStart and RangeEnd parameters. It can also use a DirectQuery partition for real-time data. Ultimately, the data you query from the cloud database shrinks, so the imported dataset on your Power BI app becomes smaller and easier to handle.
Incremental refresh ensures that you’re always dealing with the latest data in a large database that contains millions of rows.
For example, you got a sales report containing 10 years of data. However, you don’t always need to update the whole dataset. You may only need the update for the last week or so.
In this case, if you perform a full dataset update, it might take hours for the Power BI to complete the task. Instead, you could just configure incremental refresh and save time and money on compute costs.
Also read: Microsoft Power BI Data Analyst Certification (PL-300): How to Get it
Why Should You Use Incremental Refresh in Power BI?
Using incremental refresh in Power BI is a smart move for three key reasons. First, it saves time and resources. Instead of refreshing all the data every time, it focuses on just the new or changed data, making your reports faster and more efficient.
Second, it’s ideal for large datasets. When you have a massive amount of data, refreshing everything can be slow and costly. Incremental refresh allows you to refresh only what’s necessary, ensuring your reports remain snappy.
Third, it keeps your reports up-to-date. By targeting the latest data, you ensure that your reports reflect the most recent changes in your data source, providing accurate insights for better decision-making.
Use Cases of Incremental Refresh
Retail Sales
Incremental refresh helps you to update daily sales data without reworking entire reports. Thus, you ensure up-to-date insights for better decision-making. It also enables you to keep track of daily engagement metrics, saving time and resources by only refreshing new data or the range you configured.
Inventory Management
If you need to monitor inventory changes in real time and optimize stock levels efficiently, you can set up incremental refreshes in such Power BI reports. It can also assist you in continuously updating web traffic statistics for up-to-the-minute insights on user behavior.
Financial Reporting
Incremental refresh helps you keep financial reports current by refreshing only the latest data regularly. You can also stay on top of supply chain updates related to finance and improve responsiveness to dynamic cost changes in the supply chain pipeline.
IoT Monitoring
Incremental refresh is particularly important in training and monitoring IoT devices. IoT devices collect and send a massive amount of data each day since they collect data by the second. If you start updating the whole database linked to a few IoT systems, it can take days to complete the process. Instead, you set up incremental refresh and only update the required chunk from the whole day.
Stock Market Analysis
The stock market is another popular source that creates massive data updates by the second. Here, you can use the incremental refresh feature of Power BI to slice and dice the upward or downward movements of several stock prices to predict your investment strategies.
Benefits of Incremental Refresh in Power BI
- It allows you to update only the new or changed data, significantly speeding up the process of refreshing your reports.
- By refreshing only the necessary data, you save on system resources and minimize the load on your infrastructure.
- When you use incremental refresh on a massive dataset hosted on a cloud computing engine, you save a lot on compute fees for constantly updating the data versus only updating a small portion of it.
- Incremental refresh is particularly valuable when dealing with extensive datasets, as it helps you avoid the need to refresh the entire dataset every time.
- You can configure incremental refresh to keep your reports up-to-date with real-time or near-real-time data up to 48 times a day.
- Incremental refresh ensures that your reports reflect the most recent changes in your data source, leading to more accurate and reliable insights.
- You can continue working with the report while the data refreshes in the background, reducing interruptions to your workflow.
- Reports load faster and respond quickly to user interactions when using incremental refresh, enhancing the overall user experience.
The benefits of incremental refresh are endless, as you can use it in different ways depending on the data science and data analytics project you’re working on.
Full Refresh Vs. Incremental Refresh in Power BI
A full refresh in Power BI involves reloading all data from the source. It’s a time-consuming and resource-intensive process for large datasets. It replaces the entire dataset, even if only a small portion of the data has changed.
On the other hand, incremental refresh updates only the new or changed data, reducing the refresh time and resource usage significantly.
Find below a tubular difference between these two refresh modes of Power BI:
| Comparing Features | Full Refresh | Incremental Refresh |
|---|---|---|
| Data update | Refreshes all data every time | Refreshes only new or changed or configured date/time ranges |
| Suitable for | Small datasets with infrequent updates | Large datasets with frequent updates |
| Refresh time | Longer refresh times | Faster refresh times |
| Resource usage | Higher resource usage | Lower resource usage |
| Historical data | Requires reloading all historical data | Retains historical data within defined partitions or archives |
| Real-time data support | Limited support for real-time data | Supports real-time or near-real-time data updates |
Prerequisites of Incremental Refresh
Though the feature is highly popular for large datasets but isn’t readily available until the Power BI plan and other dataset parameters meet certain prerequisites. Find these requirements below:
#1. Supported Power BI Plans
Incremental refresh is available for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded datasets.
For real-time data updates through DirectQuery, support is limited to Power BI Premium, Premium per user, and Power BI Embedded datasets.
#2. Supported Dataset Sources
Real-time data update to a large dataset on Power BI reports through incremental refresh is best suited for relational and structured databases like Azure Synapse and SQL Database. You can also make it work on other databases as well through custom configuration.
However, the dataset must contain or support the following:
A Date/Time Column
There should be a date or date/time column in the dataset you’re analyzing with incremental refresh. These columns help Power BI to create start and end points for the data to be updated from the source server.
Date/Time Filtering
Power BI must be able to filter the dataset by data by date, usually through a date column in the target table, which can be of date/time or integer data type in relational sources. The RangeStart and RangeEnd parameters, both in date/time data type, filter table data using the date column.
Ranged Parameters
Before you can begin adding incremental refresh events in your Power BI report, you must take the dataset into Power Query to add ranged parameters. Using the Manage Parameter command, you must add two new parameters named RangeStart and RangeEnd for the target dataset.
The Dataset Supports Query Folding
Query folding is a database optimization technique that improves query performance by pushing as much of the data processing work as possible back to the database server.
Instead of retrieving a large amount of data from the database server and then filtering and processing it in the client application, query folding enables the database server to perform filtering, sorting, and aggregation operations directly on the data source.
Single Source of Data
Suppose, you’re configuring incremental refresh to get real-time data for a large database. Or, you are creating an advanced database query system using Tabular Object Model (TOM) and Tabular Model Scripting Language (TMSL) through the XMLA endpoint. In such situations, all data partitions, whether DirectQuery or Import, must be queried from a single source of data.
#3. System Date and Time
Having access to the system date and time is necessary for Power BI to understand when to update data incrementally based on your defined policy. So, you must always set the system date and time to automatic instead of manual.
How to Set Up Incremental Refresh in Power BI
Find below the four simple steps to set up incremental refresh now:
#1. Importing Your Dataset to Power BI Desktop App
You can configure your Power BI report for incremental refresh only from the Power BI desktop app. After publishing the report, you can use the feature to get real-time data or data from a selected range in the Power BI Service (web version of Power BI) tool.
To import a dataset, do the following:
- Launch the Power BI Desktop app.
- Click the Get Data button on the Power BI splash screen.
- On the Get Data dialog, choose the database source, like SQL Server Database.
- Enter SQL server details and click OK.
- You should now see the dataset Navigator of the database.
- Choose a table from the Navigator and click Load to get data in Power BI.
#2. Processing Dataset on Power Query
Now, you must transform the dataset in Power Query to add ranged parameters and date/time filtering. Here’s how:
- On Power BI, go to the Home tab on the ribbon and click on the Transform data command.
- The dataset will now open in Power Query.
- Click the Date column.
- Now click Manage Parameters.
- On the Manage Parameters dialog, click New.
- In the Name field, type RangeStart.
- In the Current Value field, enter the start date or date/time of the incremental refresh. In this tutorial, it’s 1/1/2023.
- Click OK to apply.
- Follow the same steps to add the RangeEnd parameter with the date or date/time up to which Power BI should do incremental refresh.
You should now have three items in Power Query as shown in the below image.
Now, select the Date column and click the drop-down menu. Click on the Date Filters option and then choose Custom Filters from the overflow menu.
For the first drop-down, choose “is after or equal to.” Click the box next to it and choose the RangeStart parameter.
Then, click the second drop-down, and choose “is before or equal to”, and add the RangeEnd parameter.
Now simply click the Close & Apply button on the top left corner of Power Query.
#3. Configuring Incremental Refresh on Power BI Desktop App
On Power BI, switch to the Table view. On the right side of the dataset table, right-click on the table or dataset name to which you want to apply the incremental refresh function. Click Incremental Refresh on the context menu.
Now, do the following:
- Enable the Set import and refresh ranges tool by sliding the button.
- Enter values for the following fields:
- Archive data starting
- Incrementally refresh data starting
- Click Apply.
#4. Publishing the Report
Now go to Home > Publish command inside the Share block. Click Publish.
Power BI will ask you to save the file on your PC. Do as instructed.
Again click Publish, and choose a destination, like My workspace, and click Select.
Conclusion
So, now you know how to take advantage of the incremental refresh feature of Power BI to always get real-time data from data source servers without updating the whole data and paying huge fees for cloud computing resource usage. You also save valuable time in the process.
Follow the steps mentioned above and try incremental refresh the next time you’re creating and publishing reports and dashboards from Power BI.
Next up, Power BI: differences between report and dashboard.