How to make Essbase Aggregate Storage member formulas faster

Ever since the Aggregate Storage option in Essbase (hereafter referred to ASO for brevity (and my sanity) sake), I have been using it quite extensively. Except when really serious calculations are required, I find ASO cubes handles everything faster, from data loads to calculations and retrievals, I can build cubes with much wider dimensionality and more members, and it has a lot less optimization complexity than block storage.

However one drawback with ASO is that in some of the more complex ASO cubes I found that retrievals could be quite slow. This is because members with formulas are always handled as dynamically calculated in ASO, unlike BSO where you can specify a calculated member to be stored. Usually this is not a problem, but if you have a very large cube, and a relatively complex piece of MDX code as a member formula, and you do a large retrieval, performance can be quite bad.

Because ASO does not offer many option for optimization, I have usually found workarounds for these cases, either by making the formulas less complex, or using much more focused retrievals. However recently a colleague of mine alerted me to a MDX function which can, in the right cases, make these type of retrievals much, much faster. So thanks to Johan Hammes, all the credit for this goes to him for alerting me to his functionality.

So what am I talking about? Two functions in MDX called NONEMPTYMEMBER and NONEMPTYTUPLE. And yes, they do appear in the Essbase help files (look in the Technical Reference under MDX Optimization properties), so I must admit I broke my own rule about first consulting the manuals.

So how do they work? Basically you put them as the first line in a member formula (of course you can use them in MDX scripts as well, but in this post I will focus on member formulas). As an argument you specify either a single member or member list (for NONEMPTYMEMBER), or a cross-dimensional member specification (for NONEMPTYTUPLE). This basically acts as a filter, which checks the specified member or member combination, and if it is empty, it stops execution of the rest of the member formula. So is almost acts like an IF statement where you test a member and just do nothing if it is empty, however it seems to speed up things a lot more than using a IF. An example of how this function looks is as follows, assume all the quoted text is a member formula:

NONEMPTYMEMBER([Sales])

SUM(Children([Entity].CurrentMember),[Sales])

So if Sales is empty, the SUM is not executed.The challenge with these functions is to choose the right member(s) for the filter, but it could make a huge difference. In one case where this was used, a large retrieval which took 10 minutes now takes 2 minutes, with the only difference being the usage of the NONEMPTYMEMBER function.

Intellient is the performance management and business intelligence company to connect with.