If you’re familiar with Dan Pressman’s work on ASO internals, you’ll know that he sometimes talks about the number of “bitmap passes” required to answer a particular MDX query.  I don’t want to rehash everything Dan says here, but the following Kscope 2014  presentation (free “associate membership” required) contains a good explanation: How ASO Works and How to Design for Performance.  Dan’s chapter in “Developing Essbase Applications” touches on the same concepts.

In brief, what Dan says is that a given MDX query may require Essbase to run one or more “stored queries” under the hood.  He makes a pretty convincing case, using the analysis to show why (for example) a stacked hierarchy provides better query performance than pure MDX to calculate YTD.  However, I don’t think anyone has pointed out that MaxL exposes a measure that helps prove that this is really what happens: “kernel_queries_tracked”.

The statistic is passed back from the following MaxL command (“cube_size_info” doesn’t seem the most logical place to have put it, frankly):

query database ASOsamp.Sample get cube_size_info;

The “kernel_queries_tracked” statistic is only gathered when query tracking is enabled.  So we can count the number of “kernel queries” required for a given MDX query with code similar to the following (fair warning — running this script will discard any query tracking data gathered to date, so use caution in production!):

alter database ASOsamp.Sample disable query_tracking;
alter database ASOsamp.Sample enable query_tracking;

SELECT {[Units]} ON AXIS(0),
       {[YTD(Jun)]} ON AXIS(1)
  FROM [ASOSamp].[Sample];

query database ASOsamp.Sample get cube_size_info;
alter database ASOsamp.Sample disable query_tracking;

In the output from the “query database” statement, we see (amongst numerous other statistics):

kernel_queries_tracked
+---------------------+
                     1

Now try again, querying [YTD(Jul)]:

alter database ASOsamp.Sample disable query_tracking;
alter database ASOsamp.Sample enable query_tracking;

SELECT {[Units]} ON AXIS(0),
       {[YTD(Jul)]} ON AXIS(1)
  FROM [ASOSamp].[Sample];

query database ASOsamp.Sample get cube_size_info;
alter database ASOsamp.Sample disable query_tracking;

This time, we see:

kernel_queries_tracked
+---------------------+
                     2

Why does retrieving [YTD(Jun)] require only one kernel query, while retrieving [YTD(Jul)] requires two?  The answer lies in the formulas on those members:

Notice that the formula for [YTD(Jun)] points to just one member in the primary time hierarchy, [1st Half].  [YTD(Jul)], on the other hand, sums two members from different levels in the primary time hierarchy, [1st Half] and [July].  Evidently Essbase is, internally, having to perform separate queries to extract these two values.

So, is this going to be another post tagged “esoterica” with little practical application?  Hopefully not!  It turns out that the above technique can provide genuinely useful information.  I’ve sometimes run into situations where two MDX formulas do exactly the same thing functionally, but have noticeably — sometimes wildly — different execution times.

As an example, I added a classic “stacked YTD” stored hierarchy to ASOsamp.Sample, then created two new members in the “Measures” dimension, [Units YTD A] and [Units YTD B].

Both formulas “redirect” from the base (MTD) period member to the “stacked YTD” hierarchy; in other words, ([Units YTD A ], [Jun]) and ([Units YTD B ], [Jun]) both return the same value as ([Units], [JunYTD]).  This is the type of formula more typically created in an analytic dimension; ASOsamp.Sample doesn’t have one of those and I didn’t want to mangle it too much.  In any case, the point here is not what these particular formulas do, but the fact that they both do the same thing — redirecting from a period to its YTD equivalent — in slightly different ways. Examining the kernel queries statistic for each via the above technique produces the following results:

SELECT {[Units YTD A]} ON AXIS(0),
      {[Jan]} ON AXIS(1)
 FROM [ASOSamp].[Sample];

kernel_queries_tracked
+---------------------+
                    12

SELECT {[Units YTD B]} ON AXIS(0),
       {[Jan]} ON AXIS(1)
  FROM [ASOSamp].[Sample];

kernel_queries_tracked
+---------------------+
                     1

Amazingly, the “Case” technique for YTD redirection decomposes into 12 stored queries, while the “substring” technique is executed as just one.  My guess is that all 12 possible “Then” values in the “Case” statement are being evaluated, although I can’t tell for sure.

Ceteris paribus it’s fair to assume that fewer kernel queries are better, but (sadly!) the count of kernel queries is not the whole story with respect to query performance.   The “cube_size_info” clause also returns a value called “total_query_cost”.  The definition of “cost” is not given, but it doesn’t seem that the 12 kernel queries of [Units YTD A] produce a cost that is 12 times higher than that of the single  [Units YTD B] kernel query.  Interpreting the “total_query_cost” is material for a future post.

These statistics are definitely no MDX “EXPLAIN PLAN”, but they’re a start!