Power BI — Incremental refresh
When we deal with large datasets, a data refreshing process might take a long time. No wonder, Power BI refreshes an entire data set, even if any record hasn’t changed. So, we can use an incremental refresh functionality.
You can find how to configure the incremental refresh in the Microsoft Docs. It is pretty simple.
We compare time of refreshing a dataset without the incremental refresh and with the incremental refresh.
My test dataset contains 6.5 million rows and 3 columns and it is stored inside a SQL Server database.
A refreshing process of the dataset took around 40 seconds.
Next, I’ve set the incremental refresh like bellow. A filter is set on a [Created] column.
Two full years are stored and two complete months are refreshed.
I’ve inserted around 1 million rows with [Created] column values set to ‘2020–11–01’. So that, it is in a range of a incremental refresh rule.
A refresh process of the model took 55 seconds.
Afterwards I added 300 rows to the source table.
A process took 12 seconds. The difference is visible. Instead of updating an entire dataset you can add only new records.
Here is a summary:
At the end, I’ve set a Detect data changes feature and updated 300 rows.
It took 14 seconds.
As you see the Incremental Refresh saves a lot of time and resources. In this scale the difference is small, several dozen seconds. But when you store hundreds millions or billions rows it makes the difference.