Caching and Cache-Warming

1. Caching and Cache-Warming Chris Webb Crossjoin Consulting Ltd chris@crossjoin.co.uk

2. Who am I? • Chris Webb (chris@crossjoin.co.uk) • Independent consultant specialising in Analysis Services and MDX: http://www.crossjoin.co.uk • Blogger: http://cwebbbi.wordpress.com • Author: • MDX Solutions 2nd Edition • Expert Cube Development With Analysis Services 2008

3. Agenda • How Analysis Services caching works • When and why Analysis Services can’t cache data • Warming the Storage Engine cache with the CREATE CACHE statement • Warming the Formula Engine cache by running queries • Automating cache warming

4. How SSAS answers queries MDX Query In Cellset Out Formula Engine works out what data is needed for each query, and requests it from the Storage Engine Cache Query Subcube Requests Storage Engine handles retrieval of raw data from disk, and any aggregation required Cache Disk

5. What can be Cached? • Analysis Services can cache individual cells containing measure values • It does not cache the result of queries • Although queries are (in part) groups of cells • It cannot cache the set expressions that are used to determine what is on rows or columns

6. Types of Analysis Services cache • Analysis Services can cache two types of value: • Values returned by the Storage Engine • ‘Raw’ measure values, one cache per measure group • Dimension data, one cache per dimension • Values returned by the Formula Engine • Numeric values only – strings can’t be cached • All SE caches have the same structure, known as the data cache registry • The FE can also store values in this structure if calculations are evaluated in bulk mode • The FE uses a different structure, the flat cache, for calculations evaluated in cell-by-cell mode

7. Storage Engine Cache • Data in the data cache registry is held in subcubes, ie data at a common granularity • Subcubes may not contain an entire granularity – they may be filtered • SE cache data can be aggregated to answer queries • Except when the measure data itself cannot be aggregated, for example with distinct count measures or many-to-many • Sometimes more data is fetched into cache than is necessary for the query – this is known as ‘prefetching’ • Usually good for performance, but can cause problems • Arbitrary-shaped subcubes cannot be cached

8. Subcubes

9. Subcube Example (Seattle, Year.Year.MEMBERS)

10. Subcube Example (Seattle, Year.MEMBERS)

11. Subcube Example ({Redmond, Seattle, London}, Year.MEMBERS)

12. Subcube Example ({Redmond, Seattle}, {2005, 2006, 2007})

13. Arbitrary-Shaped SubcubeExample

14. 2 2 2 3 4 5 6 7 2 3 4 5 6 7

15. 2 2 2 3 4 5 6 7 2 3 4 5 6 7

16. 2 2 2 3 4 5 6 7 2 3 4 5 6 7 [2, {2,3,4,5,6}] Precise solution [2, *] Non precise solution

17. Which one is better ? 2 2 2 3 4 5 6 7 2 3 4 5 6 7 [2, {2,3,4,5,6}] Precise solution [2, *] Non precise solution

18. Formula Engine Cache Scopes • There are three different ‘scopes’ or lifetimes of a FE cache: • Query – for calculations defined in the WITH clause of a query, the FE values can only be cached for the lifetime of the query • Session – for calculations defined for a session, using the CREATE MEMBER statement executed on the client, FE values can only be cached for the lifetime of a session • Global – for calculations defined in the cube’s MDX Script, FE values can be cached until either • Any kind of cube processing takes place • A ClearCache XMLA command is executed • Writeback is committed • Global scope is best from a performance point of view!

19. Cache Sharing • Values stored in the SE cache can always be shared between all users • Values stored in the FE cache can be shared between users, except when: • Stored in Query or Session-scoped caches • Users belong to roles with different dimensions security permissions • Note: dynamic security always prevents cache sharing • Calculations evaluated in bulk mode cannot reference values stored in the FE flat cache • Calculations evaluated in cell-by-cell mode cannot reference values stored in the FE data cache registry

20. Forcing Query-scoping • In certain circumstances, SSAS uses query-scoped FE caches when you would expect it to use global scope • These are: • Calculations that use the Username or LookupCube functions • Calculations use non-deterministic functions such as Now() or any SSAS stored procedures • Queries that use subselects • When any calculated member is defined in the WITH clause, whether it is referenced or not in the query • When cell security is used

21. Warming the SE cache • Considerations for warming the SE cache: • We want to avoid cache fragmentation, for example having one unfiltered subcube cached rather than multiple filtered subcubes • It is possible to overfill the cache – the SE will stop looking in the cache after it has searched 1000 subcubes • We want to cache lower rather than higher granularities, since the latter can be aggregated from the former in memory • We need a way of working out which granularities are useful

22. Warming the SE cache • We can warm the SE cache by using either: • WITH CACHE, to warm the cache for a single query – not very useful • The CREATE CACHE command • Remember that building aggregations is often a better alternative to warming the SE cache • But in some cases you can’t build aggregations – for example when there are many-to-many relationships

23. CREATE CACHE • Example CREATE CACHE statement:CREATE CACHE FOR [Adventure Works] AS'({[Measures].[Internet Sales Amount]},{[Date].[Date].[Date].MEMBERS},{[Product].[Category].[Category].MEMBERS})'

24. Which subcubes should I cache? • The Query Subcube and Query Subcube Verbose events in Profiler show the subcubes requested from the SE by the FE • This is also the information stored in the SSAS query log, stored in SQL Server • Analyse this data manually and find the most commonly-requested, lower-granularity subcubes • Maybe also query the Query Log, or a Profiler trace saved to SQL Server, to find other subcubes – perhaps for queries that have been run recently

25. Warming the FE cache • First, tune your calculations! Ensure use of bulk mode where possible • The only way to warm the FE cache is to run MDX queries containing calculations • Remember, these queries must not: • Include a WITH clause • Subselects • Also, no point trying to cache calculations whose values cannot be cached • And think about how security can impact cache usage

26. Queries to warm the FE Cache • Again, it is worth manually constructing some MDX queries yourself to warm the FE cache • Also, running regularly-used queries (for example those used in SSRS reports) can be a good idea • Can easily collect the queries your users are running by running a Profiler trace, then saving that trace to SQL Server or a .trc file • The Query Begin and Query End events contain the MDX query • Need to filter out those with a WITH clause etc • Watch out for parameterisation (eg SSRS) • Watch out for use of session sets and calculations (eg Excel 2003) • Watch out for queries that slice by Time, where the actual slicer used may change regularly • Think about the impact of dimension security too

27. Memory considerations • SSAS caching can use a lot of memory! • The cache will keep growing until SSAS thinks it is running out of memory: • When memory usage exceeds the % of available system memory specified in the LowMemoryLimit property, data will be dropped from cache • When it exceeds the % specified in the TotalMemoryLimit property, all data will be dropped from cache • We therefore don’t want to exceed the LowMemoryLimit • We also want to avoid paging • We need to leave space for caching real user queries • The FE flat cache is limited to 10% of the TotalMemoryLimit • If it grows bigger than that, it is completely emptied

28. Automating Cache Warming • We should perform cache-warming after cube processing has taken place • Remember – it may take a long time! It should not overlap/interfere with real users querying • We can automate it a number of different ways: • Running SSRS reports on a data-driven subscription • Using the ascmd.exe utility • Building your own SSIS package – the best solution for overall flexibility. • Either fetch queries from a SQL Server table • Or from a Profiler .trc file using the Konesans Trace File Source component

29. Summary • Clearly a lot of problems to watch out for! • However, some cache-warming (however inefficient) is often better than none at all • A perfectly-tuned cube would have little need for cache-warming, but... • Some performance problems we just don’t know about • Some we may not be able to fix (eg with complex calculations, hardware limitations) • Cache warming is likely to have some positive impact in these cases – maybe lots, maybe not much