Auto Page Refresh in Power BI – Everything you need to know!

Table of Contents

Automating tasks – that’s something we all like! And, Power BI does excellent in that regard. Even though some of these automated things can come back to haunt you, such as Auto Date/Time option, luckily there are many more to make our lives easier.

I’ve recently played around with the Automatic page refresh feature, and in this article, I’d like to shed more light on this capability – especially regarding the best practices and limitations for specific use-cases.

What is Automatic page refresh?

In a nutshell, as its name suggests, the Auto page refresh feature lets you set the scheduled refresh of the specific page in the report! So, don’t mix this feature with report scheduled refresh, as Auto page refresh is a whole different story. The first crucial difference is that the Auto page feature works only for DirectQuery storage mode (and Live Connection to some extent). No Import mode is supported, keep that in mind!

The next important consideration is which type of Auto page refresh to use…Oh, yes, there are two different types, so let’s examine both of them in more depth.

Fixed interval

This is the basic way of scheduling an automatic refresh of the page. You set the desired interval (ranging from 1 second to X days), and all visuals will be refreshed when that interval is reached. This means, every single visual will generate a separate (direct) query and fire it to an underlying data source. But, more on that later in the article.

Change detection

In essence, it works similarly to a fixed interval type to some extent. You’ll again set the interval, but this time, different things happen in the background. The key difference compared to Fixed interval is, you have to specify a measure that’s going to be monitored (think of it, like checking if there are new records in the underlying table) – after the defined fixed interval, Power BI will generate ONE SINGLE query to check if the specified measure changed – if yes, it will execute remaining queries to refresh all the visuals…If not, nothing happens with the report page!

Let’s now see what happens under the hood when using each of these types.

Configuring Auto page refresh in Power BI Desktop

Once in Power BI Desktop, and using a DirectQuery storage mode, I can configure a Page refresh property:

I’m using a Contoso database, and for the sake of simplicity, I’m connecting only to a DimCustomer table. As you see, I placed three visuals on the report page, and set Auto page refresh type (Fixed interval), with 2 seconds refresh interval.

With this setup, Power BI Desktop should fire three separate queries (one for each visual) every two seconds! Let’s open SQL Server Profiler and check what’s going on in the background:

As expected, there were three queries ran against the DimCustomer table, and this was repeating every two seconds.

Let’s switch to a Performance analyzer in Power BI Desktop and check specific metrics behind this setup:

I’ve sorted results per Action order, to be able to distinguish between different runs. And, you may see that our table visual each time needed more than 2 seconds to render (values in Performance analyzer are in milliseconds, just to be clear)! And, this brings us to one important takeaway:

If you notice that the visual needed more time to render than the value you specified in the Auto page refresh settings, you should increase the value for refresh interval! Honestly, what’s the point of refreshing data every two seconds, if the visual needs more time than that to render in the report?!

Change detection in Power BI Desktop

Let’s now set the Change detection type. It’s a little more complex than the Fixed interval, as you need to apply some additional configuration steps.

I’ve created a very basic measure to count the total number of customers:

Total Customers = COUNT(DimCustomer[CustomerKey])

Essentially, once we set up our auto-refresh this way, what’s going to happen is that, every 2 seconds, Power BI will fire a query to check the value of the Total Customers measure. If the value of measure changed, visuals will be refreshed – if not, nothing will happen! Let’s confirm our assumption:

Just as we thought: every 2 seconds, a query to check the value of the Total Customer measure was executed on the data source side, and since no changes were detected, the report page wasn’t refreshed. Let’s now change the value of the measure, by inserting a new row in the DimCustomer table:

USE [Contoso]
GO

INSERT INTO [dbo].[DimCustomer]
           ([FirstName]
           ,[LastName]
           ,[Gender]
           ,[GeographyKey]
	   ,[CustomerLabel]
           )
     VALUES
           ('Power'
           ,'BI'
	   ,'?'
           ,1
           ,112233
		   )
GO

This is the T-SQL code used to populate a new row in the table. I’ll now check how Change detection behaves once the underlying data changed:

Yes! Just look carefully in the animation above: the query was checking and checking, and nothing was happening. Then, I’ve inserted a row into a DimCustomer table, Change detection caught this modification, and then all three queries were executed to populate our visuals. After that, again one single query was executed to check if there were new changes in the measure.

A key takeaway from this test: if you expect that your source data is changing on a frequency lower than the value you specified in the Change detection, you should increase the interval for the change detection.

Configuration in Power BI Service

I’ll now publish this report to MyWorkspace in Power BI Service.

Wait, what?! This is not going to work! As you may see in the message on the top, the Change Detection type, unlike Fixed interval, which will work in all capacities, requires a Premium license.

General considerations

Based on the conclusions from tests performed above, here is the list of general rules you should keep in mind when using the Automatic page refresh feature:

• If you expect data from the underlying table to change every 5 minutes, don’t set the Auto page refresh interval lower than 5 minutes
• When you set refresh intervals to low values, like 2 seconds in our example, consider the workload your queries will create, especiall with Fixed interval type. To illustrate, if you have 20 concurrent users running this report, and we had 3 visuals on the report page, that’s 20×3 = 60 queries every 2 seconds! Now, imagine the impact if you have 10+ visuals on the page…
• Automatic page refresh is supported only in DirectQuery storage mode, or Mixed mode containing at least one DirectQuery data source. You can use Fixed interval with some limitations in Live Connection mode too
• Change detection type requires Premium capacity
• You can set Change detection type only on one measure per dataset
• You can use maximum 10 data models with Change detection type in Power BI tenant

Now, if you are curious to find out what will happen if your automatic page refresh workloads interfere with the regular workloads – as per Microsoft’s official documentation, auto page refresh queries have lower priority.

Another legitimate question is: what happens with the query if it takes 5 seconds to execute, and I’ve set the refresh interval to 2 seconds? Power BI will not run the next cycle until the previous one is completed. Simply said, Power BI will wait 5 seconds for the query to complete, and then execute the next run.

Finally, I was curious to know if Automatic page refresh queries can leverage the results from cache. The answer is NO, as those queries are not being served from the cache.

Conclusion

Automatic page refresh is a very powerful feature in scenarios when you need to monitor some critical events in real-time and make decisions based on a quick insight provided by the Power BI report. However, be extremely careful when to use this feature and to what extent.

Thanks for reading!

Last Updated on January 20, 2023 by Nikola