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.
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.
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.
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?
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.
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!