Want to know The Truth About CPM?

27 October 2016

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.

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

Be seeing you.


Bill said...

I'll play. 2012-OCT - I was working on a project in Canada. It took me all of a few seconds -- I was up there for 5 years with only a short break for a project in the US in 2010, so it was a pretty easy one to remember.

And to comment on the actual purpose of your post...

I hate it when I realize I am tackling the same problem years later. I like to think I would remember that stuff. I've started creating little files in Google Docs where I save examples of code that have annoyed me.

While I tell people the ol' FIX/IF rule, I don't always follow it. There's an "it depends" that I don't share with noobies. I like to think about it from the standpoint of how many of the members in the FIX do I expect to actually need to touch. If I plan on touching/changing 100% (or almost 100%) of the blocks identified in the FIX then I don't usually care if it is a dense or sparse dimension. This has become more and more my MO now that we can use THREADVAR/FIXPARALLEL to simplify complex (nested) IF statements.

Anonymous said...

we all go through this pain. Thanks for the post. would it not have been better to use "Actual" instead of Connecticut for your block statement, I'm sure there are less members in scenario than in market and you would be doing a dense calculation

er77 said...

did you see(test) @levmbrs function ?


Cameron Lackpour said...


Isn't Actual (strangely but not really to get the account dimension calcing right as I recall) dense? I'd then have block creation issues. The use of a sparse member such as Connecticut causes the blocks to be created.

Sorry, I should note that while I have SET CREATEBLOCKONEQ ON in the code, that's no long necessary as several have documented. Old habits die hard.


Cameron Lackpour

Cameron Lackpour said...


No, sorry, I did not try @LEVMBRS. Is there some reason you'd expect it to differ? From my experience, @LEVMBRS and @RELATIVE behave identically.


Cameron Lackpour

er77 said...

i think so to
but if you have some free time can u test @LevMbrs - because some papers sad what this function more prefer then @Relative

i am always use @merge and if , and only your this post open my mind why @remove don't work as i expected )))

Bill said...

@RELATIVE will give you more flexibility in the long run. Also, by always using @RELATIVE it is one fewer command to explain to the client. I use @RELATIVE whenever I can so that I can isolate to a small portion of a database for testing/debugging. In Planning applications, this is something that one will take advantage of for business rules that have run time prompts. You can't isolate to a subset of Lev0 members using the @LEVMBRS command. $0.02

Anonymous said...

Hi Cameron, i have a calc that i cannot figure out how to get it to work. I need all levmbrs of (org unit,2) to equal the value on the first child with data

Orglvl2 #missing
Orglvl1 =10
Orglvl10 =10

I need orglvl2 to =10 not 20 and i cant specify specifc members do to size of all the dimension involved.
Any ideas? I tried to agg using fixed and excludes but level 2 still just aggs all children