Summary for "Cache–Warming Strategies for Analysis Services 2008" by Chris Webb

The following is a summary for the very good session held by Chris Webb for SQLBits and Cache–Warming Strategies for Analysis Services 2008:

• First he described how AS answers queries, and used the following chart:
  
  And it goes like the following:

1. When you run a query it goes in to the Formula Engine (FE).
2. The FE looks to the query and says what data needed to answer this query, so it slice it down to a number of requests then send them to the Storage Engine (SE).
3. All what SE does is receiving requests for data at a single granularity and it gets that data and it returns it back to the FE.
4. The SE looks to three possible was to answer a request: First of all it might have the data already in its cache (because it caches every data that already brought back). If it could not answer the request from its cache (which is the fastest way) it will look if this request matches exactly an aggregation, if not it will try to drive it from an aggregation. In the worst last way it will read the data from the raw partition of the data then aggregate it to the requested level of granularity.
   After that the request will be sent back to the FE, the FE then stitches all back together, it also does the required calculations (if there is). FE as well has a cache where it caches the result of the calculations. 
5. FE then put it all together in a cell set, and finally send it back to the user.

• SE caches data from measure groups as well as dimension data, and there is one cache per measure group and also one cache per dimension.
• FE caches only numeric values which are the results of an individual calculation… Strings can’t be cached.
• Basically when FE perform its calculations, there are two basic ways or types to do the calculations: If FE can look into your calculations before it evaluates them and know in advance what data is needed to do them, then before it does the calculations, it will bring all the needed data back all together and does the calculations, that is called the bulk mode, and it is the better way. The worse way is the cell-by-cell mode, an example of this mode happen when you have something like turning string to member names in your query.
• All SE caches have the same structure, known as the data cache registry, which hold sub-cubes and slices of data from the cube. The FE can also use that data cache structure (cache registry) if the cached data was calculated in bulk mode, if it was in that cell-by-cell mode the FE uses a different type of cache structure called Flat cache.
• SE cached data can be aggregated to answer FE requests, except when the measure data itself cannot be aggregated, for example with distinct count measures or many-to-many. And this is in fact one of the reasons why it a good idea to separate distinct count in its measure group, because as soon as you have a distinct count in a measure group the whole SE cache for that measure group will be non-aggregatable and the query performance will suffer.
• Sometimes more data is fetched into cache than is necessary for the query, this is called ‘prefetching’.
• Arbitrary shapes can only use the query cache if they are used in a subselect, in the WHERE clause, or in a calculated member. An arbitrary shape is any set that cannot be expressed as a crossjoin of members from the same level of an attribute hierarchy (means, not on a single level of granularity). For example, {(Food, USA), (Drink, Canada)} is an arbitrary set.
• To find what is going on with the cache things, run SQL Profiler and add the following events:
• Query Being/End: and these the events that fired when we start to run a query.
• Execute MDX Script Begin/End: events that fired when the MDX itself is evaluated, and this is for example when building all the calculated members and evaluating any name set..etc.
• Get Data From Aggregation: appears if an aggregation was used.
• Get Data From Cache: appears if data was fetched from cache.
• Query Cube Begin/End: fired when the query itself is evaluated.
• Query Subecube verbose: gives us a look to the actual granularity of the request made by the FE to SE.
• Progress Report Begin/End: when AS actually begins and ends getting data from the disk.
• There are three different ‘scopes’ or lifetimes of a FE cache:
• Query: for calculations designed 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, or Writeback is committed.
• If you want to get the best performance out of the FE cache, then you want it to cache values as long as possible, which means you should declare your calculation so they live as long as possible. In general, try to avoid declare your calculations in WITH clause of the query, instead put them in the cube, so in most cases Global scope is best from a performance point of view.
• 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 that dynamic security always prevents cache sharing.
• Calculations evaluated in bulk mode cannot reference values stored in the FE flat cache. The same for calculations evaluated in cell-be-cell mode cannot reference values stored in the FE data cache registry.
• 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, a client tool that always use subselect is SSRS, so try always to manually write the MDX query there.
• When any calculated member is defined in the WITH clause, whether it is referenced or not in the query.
• When cell security is used.
• We can warm the SE cache by using either:
• WITH CACHE: to warm the cache for a single query … not very useful.
• CREATE CACHE command.Remember that building aggregations is often a better alternative to warming the SE cache.
• For the FE cache, all what we can do is to run queries, there is no equivalent CREATE CACHE command. You can easily collect the queries users are running by using Profiler trace, save the trace to SQL Server or a .trc file, then rerun them, but you have to filter out those queries with a WITH clause, and watch out for parameterized queries, you have to replace the parameters with real values before rerunning them. Also watch out for queries that slice by Time, where the actual slicer used may change regularly.
• 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 percentage of available system memory specified in the LowMemoryLimit property, data will be dropped from cache. When it exceeds the percentage specified in the TotalMemoryLimit property, all data will be dropped from cache. We therefore don’t want to exceed the LowMemoryLimit, want to avoid paging, and 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 will completely be emptied.
• We can automate cache warming in different ways:
• Running SSRS reports on a data-driven subscription.
• Using the ascmd.exe utility.
• Building you own SSIS package (The best solution for overall flexibility) that either fetches queries from a SQL Server table, or from a Profiler .trc file using the Konesans Trace File Source component.