• <1 minute

Governance setting for cache refreshes from Azure Analysis Services

Data visualization and consumption tools over Azure Analysis Services (Azure AS) sometimes store data caches to enhance report interactivity for users. The Power BI service, for example, caches dashboard tile data and report data for initial load for Live Connect reports. This post introduces the new governance setting called ClientCacheRefreshPolicy to disable automatic cache refreshes.

Built on the proven analytics engine in Microsoft SQL Server Analysis Services, Azure Analysis Services delivers enterprise-grade BI semantic modeling capabilities with the scale, flexibility, and management benefits of the cloud. The success of any modern data-driven organization requires that information is available at the fingertips of every business user, not just IT professionals and data scientists, to guide their day-to-day decisions. Azure Analysis Services helps you transform complex data into actionable insights. Users in your organization can then connect to your data models using tools like Excel, Power BI, and many others to create reports and perform ad-hoc interactive analysis.

Data visualization and consumption tools over Azure Analysis Services (Azure AS) sometimes store data caches to enhance report interactivity for users. The Power BI service, for example, caches dashboard tile data and report data for initial load for Live Connect reports. 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 Azure 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 on-premises SQL Server Analysis Services) 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.

ClientCacheRefreshPolicy governance setting

The new ClientCacheRefreshPolicy property allows IT or the AS practitioner to override this behavior at the Azure AS server level, and disable automatic cache refreshes. All Power BI Live Connect reports and dashboards will observe the setting irrespective of the dataset-level settings, or which Power BI workspace they reside on. You can set this property using SQL Server Management Studio (SSMS) in the Server Properties dialog box. Please see the Analysis Services server properties page for more information on how to make use of this property.

ClientCacheRefreshPolicy