A few years ago the very very talented Mike Henderson gave a Kscope presentation entitled Power Tools: Using VAR + LOOP for If-Then Procedure Logic and ARRAY for Speed (the presentation can be found here.) First and foremost the presentation has the easiest to understand and most detailed description of how arrays work in essbase – including a perfect use case and lashings of ginger beer code!
It also describes a nifty way of building conditional IF statements, allowing differences in treatment between two scenarios, without the performance and code cleanliness hit of having to do repeated IF(@ISMBR(“Sparse”)) intersections.
The only issue I had with Mike’s proposal is that…it’s slightly confusing.
SET RUNTIMESUBVARS {
runForecast=0 Enter 1 to skip Forecast scenario ;
runBudget=0 Enter 1 to skip Budget scenario ;
};
VAR ForecastBreak = &runForecast;
LOOP(1,ForecastBreak)
[Forecast code goes here]
ENDLOOP
VAR BudgetBreak = &runBudget;
LOOP(1,BudgetBreak)
[Budget code goes here]
ENDLOOP
Basically it’s almost inverse of what you’re expecting. The variable ForecastBreak needs to be set to 1 if you want to SKIP the Forecast code. After I’d used the code in a few different places, I ended up practically shelving it because I was having to explain what the code was doing (sometimes to myself. I tried not to do this out loud.)
That said, it’s still an elegant solution to a fairly fundamental problem. Most other coding languages have the ability to do a conditional IF statement, and it definitely has use cases within Hyperion.
Fast forward a few years
A few years later I was doing some (all too brief!) work with the brilliant Danish Hotta and noticed in his code the following:
SET EMPTYMEMBERSETS ON;
FIX("Forecast" AND {Var_Scenario})
[Forecast code goes here]
ENDFIX
FIX("Budget" AND {Var_Scenario})
[Budget code goes here]
ENDFIX
Being an inquisitive fellow, and having never seen that code pattern before, I immediately asked (somewhat incredulously in hindsight) what on earth that was meant to do. He explained that it was to split the code up between Forecasts and Budgets.
I’d like to pretend that I believed him immediately and obviously didn’t have to have it proved to me…and given that this is my blog I shall take the liberty in stating that it only took around 10-15 minutes of patient explanation before I understood what it was doing.
So what IS it doing then?
Since this appears to be completely undocumented behaviour, let’s break it down to the component parts and work out what is happening.
SET EMPTYMEMBERSETS ON;
Emptymembersets is that function that you don’t need to understand, right up until it bites you and then you set it in every script.
The documentation is actually very clear as to what it’s doing:
If EMPTYMEMBERSETS is ON, and a FIX command evaluates to a (sic) empty member set, the calculation within the FIX command stops and the following information message is displayed: “FIX statement evaluates to an empty set. Please refer to SET EMPTYMEMBERSETS command.” The calculation resumes after the FIX command. If a calculation script contains nested FIX commands, the nested FIX commands are not evaluated.
However, the pertinent point is ‘what does evaluation to an empty member set’ actually mean. It is not a evaluation of data, instead, an empty member set is where the intersection of the fix statements resolves to something fundamentally invalid. Often times this will occur when using functions within FIX Statements ie:
SET EMPTYMEMBERSETS OFF; /* This is the default */
CLEARDATA "Actual";
FIX(@Children("BegBalance"))
Actual = 100;
ENDFIX
Begbalance is a level zero member, ergo it does not have children, so the reference it is an invalid. The EMPTYMEMBERSETS flag then defines the calculation behaviour, with the default behaviour completely ignoring the Fix statement and loading data to all periods.
Setting EMPTYMEMBERSETS to ON then changes this behaviour – ignoring both the fix statement and all of the calculations within it
SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual";
FIX(@Children("BegBalance")
Actual = 100;
ENDFIX
To further the point, the greatly venerated C.Lackpour wrote a very long blog post on this – going into detail about differences in behaviour of EMPTYMEMBERSETS with nested fix statements. I would strongly recommend reading it before continuing this post – I’ll wait.
Okay…you’re back
So now we know what EMPTYMEMBERSETS does – how does this help us with a conditional IF? This is where that Boolean AND function comes into play.
SET RUNTIMESUBVARS
{
Var_Scenario="Budget";
};
SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual";
CLEARDATA "Budget";
FIX(Actual AND &Var_Scenario)
AUD = 100;
ENDFIX
FIX(Budget AND &Var_Scenario)
AUD = 200;
ENDFIX
In FIX statement above, the scenarios are hardcoded, and then a scenario variable is passed through with a Boolean AND statement. In this case I’m using Essbase run time sub variables but could just as easily be done in a Planning application with calculation manager variable.
Tricka Technology
Triggering the calc above and looking first at the output in smartview, we can see that the Budget data has calculated, and the actual data has not changed.
Reviewing the log we can see the that the resolutions of Actual AND budget has resolved to an empty set, therefore ignoring the FIX statement and all of the code therein.
Tricka Technology the Remix
Changing the variable to Actual and rerunning?
SET RUNTIMESUBVARS
{
Var_Scenario="Actual";
};
SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual";
CLEARDATA "Budget";
FIX(Actual AND &Var_Scenario)
AUD = 100;
ENDFIX
FIX(Budget AND &Var_Scenario)
AUD = 200;
ENDFIX
Exactly what we’d expect to see:
The most interesting aspect of this is to look at the log. The evaluation of the fix statements actually occurs as the first step of the code – and essbase is therefore aware that this fix statement can be ignored and doesn’t even try to resolve it later. This can act as a staggering optimisation in any code because it completely skips over even the resolution of anything within the fix statement.
So there you have it
A clean and simple method to have conditional and variable driven FIX statements optimally calculating a component of a script. It works across both Dense AND sparse intersections and can be used both to standardise and simplify code – as well as providing the ability to selectively optimise any rules, particularly where you can pass through parameters from a planning dataform.
Cheers
Pete