Power BI Live Connect reports allow the dataset to be stored in Azure Analysis Services (Azure AS) or SQL Server Analysis Services (SSAS) while the report itself resides in the Power BI service. Report interactivity can be enhanced by Power BI maintaining caches of dashboard tile data and report data for initial load.
However, enterprise BI deployments where semantic models are reused throughout organizations can result in a great deal of dashboards and reports sourcing data from a single Analysis Services (AS) model. This can cause an excessive number of cache queries being submitted to AS and, in extreme cases, can overload the server. This is especially relevant to Azure AS (as opposed to SSAS) because models are often co-located in the same region as the Power BI capacity for faster query response times, so may not even benefit much from caching. This post discusses the options to disable automatic cache refreshes including the new governance setting called ClientCacheRefreshPolicy.
Scheduled cache refresh setting
By default, the Power BI service polls the AS model every hour to see if the data has changed since the last cache refresh. This can be changed in the dataset settings in the Power BI service to a less frequent polling period; perhaps once a week. The following image shows the Scheduled cache refresh setting as it’s displayed for a Live Connect dataset.
The above setting may depend on report authors being conscious of the Power BI cache refresh behavior and taking the time to set it. An administrator can set it for each individual dataset one by one, but it does not prevent new Live Connect reports from being uploaded with the default 1-hour frequency.
ClientCacheRefreshPolicy governance setting
The new ClientCacheRefreshPolicy property allows IT or the AS practitioner to override this behavior at the AS server level. All Live Connect reports will observe the setting irrespective of the dataset-level setting, or which workspace they reside on. It works the same way for workspaces on shared capacities (Pro) and those on Power BI Premium dedicated capacities.
Currently the setting is only for Azure AS, not SSAS.
You can set this property using SQL Server Management Studio (SSMS) in the Server Properties dialog box.
ClientCacheRefreshPolicy is an integer with the following allowed values.
· 0: Discourage all background cache refreshes
· -1 (default): Allow all background cache refreshes
Setting the property to 0 does not disable caching of tiles and report data altogether; it just means they are refreshed on demand. Power BI still polls the AS model at the interval specified in dataset settings and invalidates stale caches, but they are not refreshed automatically. Instead, caches are created on demand based on user interaction.