Conditional FIX statements

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.

emptymbrsets_off_log
emptymbrsets_off

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

emptymbrsets_on_log
emptymbrsets_on

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.
budget_output
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.
budget_log

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:
actual_output

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.
actual_log

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

Sub Variables, and a Glorious Dataform Hack

G’day All,

I’ve been a bit lax with regard to posts – apologies – but Cameron harassed me to post this up after he’d not heard of it. To be honest, this is one of those ‘been in my back-pocket for so long I just assumed everyone knew’ kinda features (is there an acronym for that?)

Basically I was asked if anyone knew a way of showing only certain periods on a dataform…without having to update the form every month. This is one of those things that feels like it should be possible (nay easy) but…well…isn’t. Hence this hack.

Where did this all come about…

One of the common problems with Planning dataforms is a fairly hefty limitation in how they can be structured. This has general led to a number of obscure and imaginative hacks to show the data that you want. Significant examples of limitations include:

  • No UDAs available to dynamically filter member selections
  • No range substitution variables
  • Limited member selection functionality

Oh for the want of a cloud…

For those of you already heavily using PBCS you’ll note that a number of these functions have been significantly improved. Including:

Member Exclusions
01-exclusions
Significantly More Member Selections
02-new-member-selections

This gives you the flexibility to build dataforms however you like. In our case, it allows you to selectively show Right or Left members of a member.

So setting a subvar to &Period_Plan = Nov and defining a column header as IRSIBLINGS(&Period_Plan) will show just November and December!
irsiblings

Terrestrial Based Development?

On-premises you’re more limited. The functionality to allow for member selections is basically only Level-0 references and children – difficult if you wish to selectively show certain periods based upon a sub variable.
20161113_dataform2

So what are our options? Interestingly what you would ‘expect’ to work doesn’t. Defining “Nov,Dec” as a sub variable will not allow it to be used – see both ActPeriods and Period_Act_Range for examples of what I tried (although they are obviously for the Actual periods!) Using these, saving the dataform and attempting to open it will just throw a default ‘dataform is invalid’ error message.

20161113_subvars

What’s this about Merging SubVars then?

One of the interesting default behaviours of Planning dataforms is to ‘merge’ the same member into a single column. If you have a column or a row selection with the same member listed sequentially (ie: Actual, Actual, Budget, Forecast) it will merge the Actual columns together rather than duplicating the column.

Therefore we need to work out a way that we can use and abuse that behaviour to suppress the columns that we don’t want to show. In order to do this, we create 12 sub variables.
subvar_definition

The variables are then updated with the ‘same’ value for the first month to be shown. In this example I’m showing just Nov and Dec, but with 12 variables it could obviously be extended to a full year. Obviously in a case like this you’re going to want to automate the setting of sub variables… but I’ll leave that as an exercise for the reader.

Building the Dataform

Then our dataform becomes easy – the period dimension is defined as all 12 variables.
20161113_dataform

The closed variables then merge together on the dataform and we are left with only the plan periods. Simple!

20161113_dataform_updatd

Obviously we’re all hoping for the next round of 11.1.2.4 patches to bring some of this PBCS functionality back to on-premise (my kingdom for on-premise smartpush!) – however for the moment it’s a very effective, if a touch inglorious, hack!

Cheers
Pete