Stupid Programming Tricks No. 30 -- FIXing Stupid Programming Tricks No. 15 with @SHARE and @REMOVE

For the love of Mike

I just wrestled with an Essbase calc script that humbled me because:  I couldn’t figure the !#$%ing thing out and I already dealt with this FOUR YEARS AGO (just about) in November 2012.  And I forgot that I did.  And I found out only when I searched for it on the web.  How embarrassing on several levels.  I am however happy to relate that I did figure this out (hence this post) and would ask you what exactly you were doing professionally four years ago.  Share your immediately referenced memories with me care of this site and please include estimated time between the previous sentence and recollection.

Yr. Obt. Svt. at work and play

So what didn’t work?

As I wrote back in Stupid Programming Tricks No. 15 @SHARE the pain with FIX, there is a bug (feature?) with FIX statements when allied to @REMOVE and shared member hierarchies.  To wit:  @REMOVEing shared members from within a FIX simply doesn’t work.  

To recap, here is Good Old Sample Basic aka MVFEDITWWW completely cleared out:

What I want to NOT do is write to the level zero descendants of Diet:  100-20, 200-20, and 300-30 but nowhere else.

One would think using FIX, @REMOVE, and @RELATIVE as follows would provide the desired result.  The idea being that if I use a @REMOVE function to remove the leaf Diet members – which are all shared – I will write to everything but 100-20, 200-20, and 300-30.  I will be disappointed.

By disappointed I mean this:

Ah.  That isn’t exactly what I wanted:  Essbase just wrote everywhere.  What went wrong other than it obviously isn’t working?

Why it went pear shaped

Rahul S. figured this out in his blog post FIX, REMOVE, and CLEARBLOCK, You may end up with No data ;).  Essentially (basically?) trying to remove shared members from a FIX statement using @RELATIVE results in a union of the two halves of the statement, i.e:  FIX(@REMOVE(@RELATIVE("Product", 0), @RELATIVE("Diet", 0)).  The first @RELATIVE selects all of the level 0 descendants of Product, the second @RELATIVE selects the shared 100-20, 200-20, and 300-30.  When these two @RELATIVE functions are surrounded by the @REMOVE the second set is removed from the first and then unioned with the now-reduced first set.  In other words, the shared members are removed and then they are added back in.  Go Read The Whole Thing but at the end you’ll join me in a despairing “Bugger”.  Double Bugger because this isn’t in any way, shape, manner, or form how Essbase deals with @REMOVE when the scope doesn’t address shared members; that works exactly as expected.  Triple Bugger.

As I noted in my blog post four years ago, there are three ways round this:

1. Explicitly list all of the members to be excluded in that second set.
2. Use a UDA to exclude the members in that second set.
3. Use the EXCLUDE..ENDEXCLUDE grammar.

I’m rejecting all three approaches on the grounds of OMG-you-have-to-be-kidding-me (do I really need to detail why doing this for three members is annoying but for 30 or 120 is utter madness?), OMG-this-is-redundant, and OMG-it-doesn’t-freaking-work.

In order:

1. Listing shared members in code is just dumb on effort, maintenance, and ascetic grounds.
2. Assigning members to an alternate hierarchy and then assigning a UDA to those members as well is also dumb from an effort, maintenance, and ascetic perspective.
3. EXCLUDE..ENDEXCLUDE doesn’t work.

The first two points are arguably subjective but the issue around EXCLUDE..ENDEXCLUDE one is not.  I am here to tell you that it flat out doesn’t work when applied to large data sets.  Don’t believe me?  What does this tell you when EXCLUDE..ENDEXCLUDE is applied to a real database?

Full system utilization or crap code?  I vote for the latter and the blame is squarely laid at the feet of EXCLUDE.  Oh EXCLUDE..ENDEXCLUDE, don’t you know you’ve broken my heart?  Such wasted promise.

So what are my choices?

1. Give up.  All life is an illusion.  Seemingly insoluble FIX logic binds one to the Wheel of Things.
2. Figure out another way round this.  Surely there has to be a way.

Fix it or ignore it

It occurred to me that if FIX was the issue, there might be another way to address members.  Of course there is but it is not typically used in the context of selecting sparse members:  I am speaking of an IF formula in a calculation block.  Before you start with, “Always FIX on sparse, IF on dense” I’m here to tell you that particular rule of thumb sucks eggs.  The only things that matter are:  does it work and the how does it perform.  The latter measure in BSO is always driven by the number of blocks accessed (assuming EXCLUDE..ENDEXCLUDE isn’t driving you round the bend).  Fast code that doesn’t work is trumped by maybe-slightly-slower code that does.

Let’s look at my “wrong” code:

The code still focuses on level 0 members of Product but instead of using FIX to identify members in the Diet hieararchy I’ve used an outline test to see if a member is a descendant of Diet or not.  To that point about an IF statement on sparse being bad by addressing every block without constraint this code doesn’t allow that because the containing FIX on level 0 Product defines the absolute limit of members to be tested; the IF statement just performs an outline query within that FIX.

And what do we get?

Ah, relief.  This really does the trick:  exclude the shared members within the hierarchy Diet from operations against the overall hierarchy of Product.

Sample.Basic is one thing.  What about a real database?  I am happy to share that it is fast as it doesn’t significantly address more blocks than the hoped-for @REMOVE statement would.  Yes, it is arguable that FIXing on everything and then testing for inclusion in a shared hierarchy is slower than never touching that hierarchy’s members but cf. that comment about fast code that doesn’t work.

What, if anything, have we (I) learnt?

1. Try to remember what you did in the past.  I actually (now) remember writing that post.  It was a real stinker figuring out what didn’t work.  
2. Has this unintuitive behavior been documented by Oracle?  Has it been fixed (this is not what any reasonable geek expects)?  Has EXCLUDE..ENDEXCLUDE been changed to actually work?  No, no, and no.
3. Don’t focus on, “It has to work, it just has to.”  No it doesn’t.  Think Of Another Way.  There often is one in Essbase-land if not always in life.
4. Never give in.  

Also, I have solved this !@$%ing problem.  And closed down one of my more painful Stupid Trick posts.

Be seeing you.