Using Power BI datasets as a source for Power BI reports is an increasingly common Microsoft Power BI architecture. Thanks to the “Premium per user” license introduced last year, this is also a good and affordable solution for small and medium-sized businesses. In this blog post, we’ll take a look at the refresh options for a Power BI dataset.
Power BI Dataset Architecture
Before we look at the various refresh options, it’s a good idea to first examine the basic architecture of Power BI when using Power BI datasets. Below is a simple architecture that uses Power BI datasets. Whether you’re building a Power BI dataset based on an operational (cloud) database, a data warehouse, or an intermediate option, it doesn’t make much difference to this architecture. A Power BI dataset, as a semantic layer, offers not only attractive pricing with a Premium per-user license (€16.90 per user) but also a model size of up to 100 GB, which is more than enough for most organizations. Additionally, the performance of a Power BI dataset is comparable to that of Azure Analysis Service. Reports built on the Power BI dataset have a live connection to the Power BI dataset. This means that every interaction within the Power BI report triggers a query to the Power BI dataset. However, this interaction (live connection) is extremely fast because the Power BI dataset is stored in a highly compressed in-memory cache. The timeliness of Power BI reports therefore depends on the timeliness of the Power BI dataset, which is why we’ll take a look below at the various refresh options for a Power BI dataset.
Scheduled Refresh
The most obvious way to refresh a Power BI dataset is to schedule it for a specific time. This can be done multiple times a day, depending on the underlying architecture and how long the refresh takes.
Refresh via PowerShell
When Power BI datasets are populated from, for example, a copy database, data warehouse, data mart, or something similar, it is not always possible to accurately predict when such a load process will finish. A good way to optimize this process is to automatically run a PowerShell script immediately after the load process (for example, via an SQL Server Agent job) that refreshes the Power BI dataset(s). It’s best to run a PowerShell script to refresh a Power BI dataset using a service principal rather than under your own account. See also this link from Microsoft about Service Principals. Communication between a Power BI workspace and a PowerShell script takes place via an encrypted XMLA endpoint. The use of an XMLA endpoint is only possible with a Power BI Premium license. See also this link from my colleague about XMLA endpoints.
Refresh a Power BI dataset with Power Automate
Power Automate It's part of the Office 365 suite, and you can simply sign in here with your Power BI/Office 365 account. Below, I'll describe two ways you can use Power Automate to refresh a dataset.
1. Through an automated cloud stream
This is the same concept as using PowerShell. In that case, a trigger ensures that a Power BI dataset is refreshed. In the example below, a dataset is refreshed whenever a file is created or modified in a specific SharePoint folder. In this example, after the loading process, you’ll need to upload a file to SharePoint using a small script.
In addition to the graphical interface, this also has the advantage that you can easily add extra steps here. For example, if your Power BI dataset uses Power BI data flows, you can insert the refresh of the Power BI data flows as an extra step in between. Or would you like to receive an email if the Power BI dataset refresh fails? You can add this step to the flow as well. Is SQL Server your data source? Then you can also set it as a trigger to perform a refresh.
2. When the user clicks a button in a Power BI report
In Power BI, you can use the “Power Automate” visualization. This allows you to trigger an automated cloud flow. This cloud flow can then refresh a Power Dataset or a Power BI data flow. This can be useful when the Power BI dataset is based on an operational database or when there is an external source (such as Excel with targets) in the Power BI data flow/dataset that needs to be refreshed. The advantage is that the user can then determine the refresh time themselves.
Hybrid tables
For some time now, it has been possible to specify, on a per-table basis within a Power BI dataset, whether a table should be refreshed incrementally. In many cases, historical data no longer changes, and using incremental refresh can yield significant performance gains. Hybrid Tables is a premium feature that has been in Public Preview since December 2021. It allows you to use Import mode and DirectQuery mode side by side for the same table. Import mode provides fast performance, while DirectQuery delivers (near) real-time data without requiring the Power BI dataset to be refreshed. Please note: You’ll need to balance performance against the recency of the data. DirectQuery mode does not import the data into the Power BI dataset but instead executes a query on the underlying database for every interaction. This process takes more time and negatively impacts report performance. With the right partitions and indexes on the underlying database, this performance loss can potentially be mitigated. However, there is another significant drawback: the limited capabilities of Power Query transformations and DAX calculations. Therefore, a second trade-off involves choosing between (near) real-time data and a Power BI dataset that is better suited for self-service BI.
Conclusion
So there are several ways to refresh a Power BI dataset. The right choice will depend, among other things, on the organization's needs, the type of license, the underlying architecture, and performance.
