Stupid Programming Tricks #14 -- SET EMPTYMEMBERSETS ON doesn't work they way you might think

I love/hate it when I learn something new

I always love learning something new – it really makes my day.  No kidding.  I can be in the middle of the very most boring project/meeting/design session/whatever and if I figure out, or have pointed out to me, something that I didn’t know before and it impacts my life – I am 100% happy.  

But I hate it when I think I know something, do things (like, oh, write calc scripts) and then learn that I didn’t know that something, or at least not all of it.

So this post has to be one of those love/hate kind of intellectual moments – I am very glad I learnt it, but how oh how oh how did I not know it?  Please don’t answer.

What is SET EMPTYMEMBERSETS?

Before I go any further into this, let’s get a definition of what an empty member set is, and why you care.  

To quote my very favorite Oracle Essbase documentation set, the Technical Reference Guide:

"EMPTYMEMBERSETS stops the calculation within a FIX…ENDFIX command if the FIX evaluates to an empty member set."

Just to be clear, an empty member set is the result of a FIX statement that does NOT return any members.  It’s valid syntactically, but just doesn’t work.  One example given is FIX(@DESCENDANTS(“100-10”)) – this can’t work in Sample.Basic because 100-10 is already a level zero member and thus there are no descendants.

What happens if you don’t use SET EMPTYMEMBERSETS ON in a calc script?  Every member in that dimension is going to get selected if the FIX is on an empty set.  Don’t believe me?  Take a look at this code:

And this result:

The FIX for @DESCENDANTS of East worked (note that East is not valued), but every single product at all levels got set to 1.  

Clear out Sample.Basic, and try this again, but now with SET EMPTYMEMBERSETS turned ON:

And what does Essbase (in this case through the MaxL shell) tell us?

And in fact nothing is valued in Essbase.

Just as I thought it would work ever since I first stumbled across the command.

But wait, there’s more, and it doesn’t work the way I thought

Check out this thread over on Network54:  http://www.network54.com/Forum/58296/thread/1351216021/Fixing+on+an+empty+set%2C+Fix+ignored

And then pay close attention to Javier Fernandez’ Sy-Quia (I think that is the Javier in question – someone, I imagine Javier (yes he did as you can see through the strikethrough), will correct me if I am wrong) post.

Javier wrote:

Remember to use nested FIX statements.

fix (mbrList1, mbrList2)

does not work the same way as

fix (mbrList1)
   fix (mbrList2)

In the first instance, the calc will run if EITHER mbrList1 OR mbrList2 does NOT return an empty member set. In the second case - nested - the emptymembersets setting will kick in as long as one of those member lists return no members.

Hmm, I wonder what that means.  I always, but always thought that if you threw a SET EMPTYMEMBERSETS ON into a calc script, anything that was empty, like that FIX(@DESCENDANTS(“100-10”)) would cause everything to stop.  Nesting, smesthing, who cares?  

After all, isn’t this:

Which results in this:

Functionally the same as this:

That results in this:

In fact, there’s a thread over on OTN where the issue of nesting (Kyle Goodfriend) versus non nesting (Tim German, but with qualificatons and yr. obdnt. srvnt.) where we essentially say it’s a matter of preference.  Or is it?

Get rid of the nesting

You are not going to like this.  Well, if you are anything like me, i.e., someone who thought there was no difference between nesting and non-nesting FIX statements and blithely wrote scads of code using SET EMPTYMEMBERSETS ON in non-nested FIX statements.

What do I mean?

Let’s modify GoodFix.csc to this:

All I’ve done is put the two dimensions, Market and Product, onto a single FIX statement line.  There’s no other functional difference and I’ve proven that when empty sets are not a consideration, there is no difference between nested FIX statements and combinted FIX statements.  What oh what oh what do you suppose happens?  :)

Just like not having SET EMPTYMEMBERSETS ON in the calc script at all.  Oops. :(

Take it one step further

Let’s make both dimensions resolve to empty member sets.

What happens?  Nothing.

Javier had this right – if ONE of the dimensions on the combined FIX statement does NOT return an empty set (like @DESCENDANTs of East and @DESCENDANTs of 100-10, the first is valid while the second is not), SET EMPTYMEMBERSETS ON is ignored.

And if all of the dimensions return empty member sets (like the @DESCENDANTs of New York and the @DESCENDANTs of 100-10), SET EMPTYMEMBERSETS ON does work.  

And what have we learnt?

1. It’s a good thing I wrote here that I promised not to be right all of the time.  This was an easy one because it is so true.  :)
2. Use nested FIX statements each and every time you use SET EMPTYMEMBERSETS ON because that works.
3. Do NOT use single FIX statements and SET EMPTYMEMBERSETS ON when at least one of the dimensions has an empty member set condition because that does not work.
4. OTN and Network54 are crucial sources of knowledge.
5. I learnt something new, so I am happy.  I wish I had known it before, so I am sad.  Such is the duality of man, or at least Cameron.
6. It would be kind of nice if this distinction was in the documentation.  I will bug my documentation contact at Oracle with this blog post.