Edit

Query caching in Power BI Premium or Power BI Embedded

  • Article
  • 2 minutes to read

Organizations with Power BI Premium or Power BI Embedded can take advantage of query caching to speed up reports associated with a dataset. Query caching instructs the Power BI Premium or Power BI Embedded capacity to use its local caching service to maintain query results, avoiding having the underlying data source compute those results.

Important

Query caching is only available on Power BI Premium or Power BI Embedded, for Import datasets. It is not applicable DirectQuery or LiveConnect datasets that use Azure Analysis Services or SQL Server Analysis Services.

The caching is performed the first time a user opens the report. At present, the service only does query caching for the initial page that they land on. In other words, queries aren't cached when you interact with the report. Cached query results are specific to user and dataset context and always respect security rules. The query cache respects personal bookmarks and persistent filters, so queries generated by a personalized report are cached. Dashboard tiles that are powered by the same queries also benefit once the query is cached. Performance especially benefits when a dataset is accessed frequently and doesn't need to be refreshed often. Query caching can also reduce load on your capacity by reducing the overall number of queries.

You control query caching behavior on the Settings page for the dataset in the Power BI service. It has three possible settings:

  • Capacity default: Query caching Off
  • Off: Don't use query caching for this dataset.
  • On: Use query caching for this dataset.

Query caching dialog box

Considerations and limitations

  • When you change caching settings from On to Off, all previously saved query results for the dataset are removed from the capacity cache. You can turn off caching either explicitly or by reverting to capacity default setting that an administrator has set to Off. Turning it off can introduce a small delay the next time any report runs queries against this dataset. The delay is caused by those report queries running on demand and not applying saved results. Also, the required dataset might need to be loaded into memory before it can service queries.
  • The query cache is refreshed when Power BI performs a dataset refresh. When the query cache is refreshed, Power BI must run queries against the underlying data models to get the latest results. If a large number of datasets have query caching enabled and the Premium/Embedded capacity is under heavy load, some performance degradation might occur during cache refresh. Degradation results from the increased volume of queries being executed.

Next steps

What is Power BI embedded analytics?