Automatic Page Refresh for Import Mode
A guide to combine Automatic Page Refresh with Import Mode.
Introduction
Yes, you read the title correctly: there is a way in which we can utilize the powerful feature of Automatic Page Refresh in combination with the Import Mode storage mode - which is still the recommended and most powerful type of storage mode for Power BI datasets.
In this article, I will provide a quick recap of Automatic Page Refresh and storage modes below and then expound on how to utilize Import Mode for Automatic Page Refresh.
Important note: this article pertains to non-streaming datasets.
Automatic Page Refresh
You’ve likely heard of a powerful feature that the Power BI team added to Power BI reports: it’s called Automatic Page Refresh.
Here’s the idea behind it: when users open up a Power BI report (not a dashboard, but a report) in the Power BI Service, Automatic Page Refresh can trigger the visuals on the report canvas to actually update and actually change autonomously.
There are two different types of triggers to invoke this behavior:
Specifically, I want to focus on the fixed-interval use case in this article.
Power BI Storage Modes
Recall the crucial concept of storage modes in Power BI and how they allow users to interact with data. There are three main storage modes, namely, Import Mode, Direct Query, and Live Connection.
Only Import Mode actually caches your data in the Power BI Service while the other two modes do not. Instead, the other two modes send ad-hoc (as needed) queries directly to a visual’s datasources as that visual is interacted with - either by a user interaction on the canvas or by a trigger from Automatic Page Refresh.
The Achilles Heel of Automatic Page Refresh
While Automatic Page Refresh is a great feature, here is one of its greatest limitations: regardless of your licensing type in Power BI, it will not support Import Mode Power BI reports - not even for Premium Per Capacity users. You can see this for yourself in the official Microsoft documentation. Not one combination of licensing types and triggers supports Import Mode - end of story.
And yet, again, Import Mode is the default and recommended storage mode when creating Power BI datasets.
So the question is this: how do we achieve - or at least mimic - the behavior of Automatic Page Refresh without having to sacrifice Import Mode data modeling capabilities?
The answer lies in the Power BI JavaScript SDK - the same SDK that I’ve used to power my custom Power BI software for helping people build and display advanced slideshows and digital signage, Displagent.
The Power BI JavaScript SDK
This incredible JavaScript library provided by Microsoft allows a Power BI developer to embed Power BI content onto a web page. In my case, I embed my customers' Power BI reports and dashboards onto an HTML page inside an ElectronJS desktop app - pretty awesome technology.
In particular, when you embed a Power BI report onto a web page, you have a host of JavaScript methods available to you from the aforementioned library. For instance, there is a JavaScript method to force the report or dashboard into fullscreen mode as well as a JavaScript method to show a context-menu when a user right-clicks a report.
In particular, there is one very special method that comes as a part of the Report module - the refresh method. This specific method functions in the following way with respect to storage modes:
- If the storage mode is Import Mode, then the method checks the cloud-stored cache in the Power BI Service for new data. If new data is found, it forces every visual on the report canvas to update. If no new data is found, no problem - the report canvas doesn’t change, no harm done.
- If the storage mode is Direct Query or Live Connection, then the method sends ad-hoc queries to all of the datasources for all visuals displayed on the report canvas; again, every visual on the report canvas is updated.
And here’s what’s even better: when paired with a JavaScript setInterval loop, this method can be executed up to every 15 seconds. Combine this functionality with an automated refresh pipeline for your Import Mode dataset (e.g. a Power Automate Flow or a Powershell job), and now Power BI packs a serious punch for time-sensitive, non-streaming operational data.
Operationalize Power BI
Think about the vast set of operational data scenarios that you could satisfy with this functionality:
- Sensor metrics from manufacturing machines
- Website traffic metrics
- Inventory transactions in a warehouse
- Production scheduling capacity
This is where the very-real needs of near real-time data meet the powerhouse of Import Mode data modeling. In this instance, you don’t have to sacrifice your data model’s capabilities to cater to Direct Query or Live Connection, you can operationalize your Power BI data, and you can actually force the visuals on a report canvas to update automatically - all at the same time. Sometimes you get to have your cake and eat it, too - with a bit of software development on the side!