Dashboard tiles based on import mode datasets are refreshed when the dataset itself is refreshed in the Power BI service. For dashboard tiles based on DirectQuery or Live connection datasets, however, the Power BI service maintains a scheduled cache refresh process for updating dashboard tiles. The purpose of this cache is to ensure dashboards are loaded extremely quickly since, as described in Chapter 13, Designing Power BI Dashboards and Architectures, many users, such as executives, exclusively rely on dashboards.
By default, the dashboard tile cache is refreshed once every hour. As shown in the following image, owners of these datasets can configure this refresh process to occur as frequently as every 15 minutes or as infrequently as once per week:
In the preceding example, a Power BI dataset (AdWorksEnterpriseDQ) containing a DirectQuery connection to an on-premises SQL Server database has been published to an app workspace in Power BI. The Scheduled cache refresh option will not appear for datasets that import their data—these datasets can use the scheduled refresh dialog described earlier in this chapter. Per the settings dialog, the dataset is associated with an On-premises data gateway (Frontline Gateway), thus allowing queries from the Power BI service to reach the on-premises database.
Switching the Refresh frequency to 15 minutes causes the queries associated with each dashboard tile dependent on the DirectQuery dataset to be submitted in 15-minute intervals, as shown in the following image:
In addition to the Last Refresh and Next Refresh columns, dataset owners can also access the Refresh history from the same dataset settings menu used to configure the Scheduled cache refresh.
Power BI reports (.PBIX files) containing either a Live connection to an Analysis Services server (AAS or SSAS) or a DirectQuery connection to the data source will be represented as distinct datasets in the Power BI service. Power BI reports created based on these published datasets will utilize the scheduled cache refresh configured for the given source dataset. The Live connections to Power BI datasets section in Chapter 11, Creating and Formatting Power BI Reports contains details and examples of these reports.
The optimal cache refresh frequency will depend on the business requirements for data freshness, the frequency with which the source database is updated, and the available resources of the source system. For example, if the top priority of the source system is OLTP transactions and the dashboard queries are resource intensive, it may be preferable to limit the refresh frequency to once a day. However, if the very latest data updates are of top value to the business users and ample resources are available to the source system, a 15-minute cache refresh schedule may be appropriate.
Unlike dashboards, a cache is not maintained for Power BI reports based on DirectQuery or Live connection datasets. These reports issue queries as the user interacts with report visuals and therefore can result in some latency. The degree of this latency depends on many factors, including the hardware resources of the data source, whether Power BI Premium capacity has been provisioned, and the complexity or density of the report visuals being analyzed. Chapter 19, Scaling with Premium and Analysis Services is dedicated to scalability and performance-related topics.