Snappy title, huh?

This post is a quick note on a powerful but hopefully non-obvious feature of MDX against Essbase, inspired by an interesting thread on the Network54 Essbase board.  That discussion is about implementing custom groupings from the reporting layer – i.e. being able to retrieve roll-ups that don’t exist in the underlying cube.  For example, the ASOsamp.Sample Products dimension looks like this:

Suppose that you want to do some reporting on the total of [Personal Electronics] and [Home Entertainment], for which there is no roll-up in the cube.  MDX can do the summing, of course.  But won’t that mess up any ratio- or variance-type measures, by adding the values for [Personal Electronics] and [Home Entertainment] together?  As it turns out, the answer is ‘not necessarily’.

Here is an MDX query to retrieve the values for [Personal Electronics] and [Home Entertainment].  It also retrieves the measures [Units], [Transactions] and [Avg Units/Transaction].  The latter is a ‘ratio-style’ dynamic member with the formula ‘[Units] / [Transactions]’:

SELECT {[Personal Electronics], [Home Entertainment]} ON COLUMNS,
       {[Units], [Transactions], [Avg Units/Transaction]} ON ROWS
  FROM [ASOsamp.Sample];
 Axis-1                   (Personal Electronics)   (Home Entertainment)
+------------------------+------------------------+------------------------
 (Units)                                    184091                    30226
 (Transactions)                             181180                    42903
 (Avg Units/Transaction)          1.01606689480075        0.704519497471039

Note that [Avg Units/Transaction] really is equal to [Units] / [Transactions] for each member of Products.

The next query shows how MDX can meet the custom roll-up requirement. The WITH clause is used here to create an on-the-fly, ‘custom member’ in the relevant dimension; this member exists solely within the scope of the query:

  WITH MEMBER [Products].[PE+HE] AS '[Personal Electronics] + [Home Entertainment]'
SELECT {[PE+HE]} ON COLUMNS,
       {[Units], [Transactions], [Avg Units/Transaction]} ON ROWS
  FROM [ASOsamp.Sample];

 Axis-1                   (PE+HE)
+------------------------+------------------------
 (Units)                                    214317
 (Transactions)                             224083
 (Avg Units/Transaction)         0.956417934426083

Note that [Avg Units/Transaction] is still equal to [Units] / [Transactions]!  That’s very helpful, but how does it work?  The key is solve order.¹  In the ASOsamp.Sample database outline, [Avg Units/Transaction] has a solve order property of 10.  By default, members defined in a WITH clause have solve order of zero.  This means that Essbase is really, truly treating [PE+HE] as a member in the cube (with solve order zero) for the duration of the query, and consequently performing the sums of [Personal Electronics] and [Home Entertainment] prior to executing the [Avg Units/Transaction] formula.

To reinforce the point, this query sets the solve order of [PE+HE] to a value of 20 – note the SOLVE_ORDER syntax in the WITH clause:

WITH MEMBER [Products].[PE+HE] AS '[Personal Electronics] + [Home Entertainment]',
          SOLVE_ORDER = 20
SELECT {[PE+HE]} ON COLUMNS,
       {[Units], [Transactions], [Avg Units/Transaction]} ON ROWS
  FROM [ASOsamp.Sample];

 Axis-1                   (PE+HE)
+------------------------+------------------------
 (Units)                                    214317
 (Transactions)                             224083
 (Avg Units/Transaction)          1.72058639227179

Now, Essbase is performing the [Avg Units/Transaction] calculations first, then summing [Home Entertainment] and [Personal Electronics].  This is exactly what we (generally) don’t want to see.

The above feature is a great reason to use MDX for this type of operation, rather than retrieving the individual product components then doing the arithmetic in Excel.  Only MDX can preserve all the formula intelligence that has been built into the cube itself.