I’ve been reading the SSAS Multidimensional Operations and Performance Guide, which is honestly the best reading on Analysis Services I’ve ever done. A major area Thomas Kejser and Denny Lee focus on is performance requests and bottlenecks. When an MDX query is written and executed, it does a lot more than you’d tend to think. There are a lot of places it passes through before it just magically returns that result back to you on your screen. From a high level, the general flow from the client application to retrieval looks something like the below:
In this post, I’m going to focus specifically on the formula engine. Over the next several days I plan on blogging about the others, so by the end of the series each piece will have it’s own post. The formula engine comprises of two parts: the query processor and the formula engine cache. The query processor is the piece that takes your request, generates an execution plan, finds the best place to find it, and returns the cell set. It’s very similar (ideologically, anyway) to the database engine query processor. However, unlike the database engine, the Analysis Services query processor has 2 types of execution plans that it uses: cell by cell or block mode computation. These are important as they have a determining factor on performance. As the storage engine doesn’t speak the same language, the query processor has to translate the request into something that it will recognize. If we think about how partitions and dimensions are actually stored on disk in the storage engine, this makes sense, as they’re organized into logical sub cube structures that the storage engine organizes.
As the query processor gets these responses back from the storage engine, it organizes and stores them in the query processor cache (also known as the formula engine cache). A handy tip that you can do to improve performance is do something called “warm the cache” where you warm this formula engine cache as the cube processes. See this post by the one and only Chris Webb for more information on that here. As Analysis Services technically has 2 locations, the formula engine and the storage engine, both caches could theoretically be warmed to improve performance, but that’s another post for another day.
Furthermore, the query processor cache has 3 distinct contexts that it uses to determine how often that value can be used in the cube, which are query, session, or global. It’s easiest to explain these via MDX examples.
Query context caches apply only to the duration of that query. This is very similar to a query scoped variable in a tsql query. Using an MDX function like WITH Member, where you are creating a calculation only for that specific query, is a query context example.
Session context applies to the length of your session. MDX queries like ALTER CUBE, CREATE MEMBER, or CREATE SET are session context scoped and apply for the duration of your connection.
Global context applies to your cube at a much larger level. Unlike the other two, which apply only to the one user who is writing the statement or executing the query, global caches can be used by multiple users, under the condition that the users share the same security role. Furthermore, the Global cache context is completely disabled for the entire cube if cell security, or functions like UserName, any of the “StrTo” functions, or LOOKUPCUBE functions exist anywhere in the cube.
Now, clearing up cell by cell versus block mode. Cell by cell mode (also called naive mode) are evaluated in a turn by turn style approach. What this means is that for each record that needs returned under your attribute hierarchy, the query processor looks at it, and returns every row, before it evaluates for what is not empty. If you have a cube that sparsely populated, this can lead to significant performance issues as it’s returning large amounts of values that are null (and will always be null). Additionally, if the data is populated in every intersection of the points, the engine is spending additional time identifying that same filter for every attribute, when it’s already a given what that filter is (year, for example)
Using subspace mode corrects this behavior by having the engine work its way down an execution tree identifying what needs to be filled in the first place. However, this can only be used in certain situations, especially given that Analysis Services, unlike SQL Server, will spend more time figuring out the execution plan than it will actually retrieving the data.