Stupid Programming Tricks #15 -- @SHARE the pain with FIX

What is the deal with shared members?

Have you ever been to a conference or seminar or webinar or meeting or whatever, and asked a seemingly simple question and just stumped the presenter 100%?  Some blather on forever hoping that the noise will confuse you, others pause, look at you with that I-hate-you-because-you-have-uncovered-a-chink-in-my-intellectual-armor-but-I-cannot-show-that-my-ignorance-is-causing-me-angst look (What, you’re not familiar with that? – go on, stump a self-important consultant <grin> and see what he does.), and will then say, “That’s a really good question.  We’ll cover that later.”  FWIW, I have noticed that later never comes.  <even bigger grin>

Adventures in deflating self-important people are always fun, except of course when you are the target, or at least are collateral damage.  What I’m talking about is this thread over on what-is-possibly-my-favorite-board-in-the-whole-wide-world, Network54:

http://www.network54.com/Forum/58296/thread/1351709026/Exclude+Shared+members+in+FIX-IF+statement+%28Re-posted+with+correct+title%29

Anyway, I am a consultant, hopefully not self-important, and I was 110% (Sammy Davis, Jr., one of my favorite singers – yeah I know what you’re thinking, but check out his older stuff, no Candy Man for me – always gave an impossible 110% effort when entertaining, so he is my blog idol) stumped by this question.  

Later is now, at least for shared members.

Back up a bit

A super quick review – shared members are members that are referenced in more than one hierarchy, but only stored once.  A simple example of this is Sample.Basic’s Product dimension which has a Diet parent and three shared children as shown below:

Shared members + @REMOVE + FIX = huh?

Check out this thread.  Passing over the fact that I 100% (or should that be 110% in keeping with my Sammy Davis, Jr. theme?) forgot that I participated quite extensively in that thread, I proved that FIX statements with @REMOVE and shared members do not work.  Do not work?  Yup.  

Case #1 – First you don’t see it

All I am doing is removing the member 200-10/Old Fashioned from the FIX scope.  The database is empty, I am forcing the creation of blocks on sparse calculation, and my expectation is that Sales in Jan, Actual, and every product but 200-10 will have a value of 100.

Yup, just as expected – 200-10 is #Missing which is just what the code said to do – all level zero Products except 200-10.

Case #2 – And now you do

If I clear out the database, and then change the scope so that the FIX now removes 200-20 – that is the shared Diet Root Beer, the code looks the same except for the member name.  The result should be the same as the above, but just for a different member.

And what do we get?

Whoops.  That wasn’t what was suppsoed to happen, was it.  And of course we now see it twice, once in the stored location as a child of 200 and again as a shared child under Diet.  Huh?

Don’t believe me?  Remove the entire Diet hierarchy from your copy of Sample.Basic – 200-20 will then not have a value of 100, it will be #Missing.  Shared members are behaving in an interesting, yet confusing manner.  Hang on, it’s about to get more confusing.

Case #3 – Trying yet again, the same but differently, and getting different results

What happens if I change the hierarchy a wee bit?  In the below example, I have created a new parent, Total Product, and pushed products 100, 200, 300, and 400 underneath it.  Diet is now a sibling to Total Product.  What oh what oh what do you suppose happens when the same code as above gets applied to this?

The code in all its glory.  I have expanded this to all three shared members but really, the logic is the same.

A great big heaping bowl of whaaaaaaaaaat?  Doesn’t taste very good, does it?

Or maybe it’s just an acquired taste?  How oh how oh how did 100-20, 200-20, and 300-30 not get valued?  That is what we wanted, right?  Do we have an answer?  We are getting close.

Case #4 – Variation on a theme by Shared

What happens if I go after “Diet”?  FWIW, I also stuck a @LIST around Diet’s @RELATIVE – no difference in behavior.

I would love to write that the below is a joke, but it is not.  Sigh, we’re right back to the beginning.

Case #5 – Throwing a spanner into the works

No, not this one:

Although this post is now at the Lost and Foundry stage.  

I am using a monkey wrench called “UDA”.  Throw a uda called “SharedandRemove” against those shared members:

And then change the code to this:

And we get…

There is no Ghost in the Machine, that’s exactly like using @REMOVE with @LIST(“100-20”, “200-20”, “300-30”).

Case #6 – Back to square 1, year dot, zero, etc.

But, if I remove Total Product and make the Product dimension look the way it “should”:

And change the code to just refer to the top of the dimension:

We get…

As Charlie Brown would say, “AUUUUUUUGGGGGGHHHH!!!!”  Back again to the beginning.  What is going on?

What can we conclude at this stage, and where do we go from here?

The following can be observed:

1. Case #1 – @REMOVE against non-shared members works
2. Case #2 – @REMOVE with shared members does not behave the same way
3. Case #3 – When @REMOVE is used against shared members with a different parent and those shared members are explicitly listed, @REMOVE against shared members does work
4. Case #4 – When @REMOVE is used against shared members with a different parent and those shared members are referred to by a parent function, @REMOVE against shared members does not work
5. Case #5 – When @REMOVE is used against shared members with a different parent and those shared members are referred to by a UDA function, @REMOVE against shared members does work
6. Case #6 – When @REMOVE is used against shared members with a common parent and those shared members are referred to by a UDA function, @REMOVE against shared members does not work

From this, I think we can accurately surmise that:

1. If you want to remove shared members from a FIX range, at the very least the shared members and must not have a common parent.  Sticking Total Parents into the hierarchy and making it a sibling to Diet allowed Case #3 and #5 to work.
2. Even though it is completely non-intuitive, the way the members are referenced changes Essbase’s behavior.  Even in a non-common parent scenario as I outlined above, when @RELATIVE(“Diet”, 0) is used to remove the shared members, the @REMOVE does not work.  But a @UDA or an explicit list does.  So weird.

So then the question is – is there any function in the really rather large BSO calc script function set that might make any difference.  Why yes, there does seem to be one.  Or <insert corny ghost music right here> is there?

Can @SHARE make things better?

There appears to be a function that deals with shared members directly.  Could the name possibly be @SHARE?  Why yes it could.  Hopefully it fixes everything.  I am the optimistic sort, aren’t I?

What do the docs say?

If you want to get it from the horse’s mouth, go here:

http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/share.htmlMy summarization

Right at the very tippy top is the bit that piqued my (And maybe yours?  Surely if you have read this far) interest:  

“Checks each member from rangeList to see if it has a shared member and returns a list of the shared members it has found.”

And my question

Does identifying shared members through the @SHARE function make their selection any better?  One is given hope through this bit of the documentation:

To remove a specific member from the Product dimension, you can use @SHARE specifying the shared member to be removed:

@REMOVE(@DESCENDANT(Product),@SHARE("100-20"))  

Now that looks like good old Sample.Basic aka MVFEDITWWW (somewhat unbelievably, that bit of doggerel can be searched on so go ahead and find out what it means).  Channeling W.S. Gilbert, maybe not.

Case #7 – Time for some disappointment

So what happens when we use the above code with a mild modification to change the @REMOVE to go after level zero members only?  Should be good, right?

Oh, bugger.  Btw, I am going for definitions 9 and 11 in that link.  Why?  ‘Cause @SHARE is just as bad as before, if before means use cases #2, #4, and #6.  Put on a sad face.

Even with Tony Benett singing the above is bad.  But at least we have the consolation of 33 & 1/3 and Hi-Fi.  

Case # 8 – Meet the new boss, same as the old boss

Any better with a different parent?  Uh, no, not really.

Oh, bugger yet again.  @SHARE is more than a bit pointless if you ask me, at least within the context of a FIX.

Case #9 – One last try

Nope, neither Conrad Birdie nor Dick Van Dyke nor Janet Leigh (phwoar) nor Ann-Margret (double-phwoar) can save this, but we can try.  And yes, you can likely tell that 1965 is my cutoff date for movies worth watching.  Moving beyond my antediluvian movie/music (mostly)/literature tastes, Kyle Whigham on that original Network54 board post noted:

Looks like I encountered a similar issue to yours. I was able to resolve by including the stored member and the dhared (sic) member in the list for members to be removed.

i.e.

@Remove("Products",0, @List("Member 1", @Share("Member 1")))

Hmm, the above is shades of the explicitly listed members.  So what happens with something like this?

Honestly, what is the point of using @SHARE if you have to explicitly  list the members to be excluded?  None, actually.  We are back at use case #3, but at least this time we don’t have to worry about having a separate non-shared parent.  Not that the other shared members are excluded as one might hope.

Bye-Bye, @SHARE

Given the almost unmeasurable improvement of @SHARE in FIXing on members, I think we can safely say @SHARE is a bit of a damp squib.

Get that bad taste out of your mouth

The awesomeness that is Essbase gives us a way out:  EXCLUDE...EXCLUDE.  This isn’t exactly a fix for FIX (oh, I kill myself) and shared members, but it is surely a way round the problem, at least in Sample.Basic.

Huzzah!  Success!  Boil in bag!  Btw, do you see the impossible code above?  It validates and works – I love

finding stuff like this.

I love a happy ending

Could @SHARE work with EXCLUDE…ENDEXCLUDE?  Why yes it could.  And this time no bug in the code.

Happy happy, joy joy.

The above success of @SHARE within EXCLUDE…ENDEXCLUDE (we will leave aside the EXCLUDE…ENDFIX bug) and its failure within FIX…ENDFIX makes me think all the more that the way shared members work within FIX are some bigger bug.  Why, why, how, and most importantly, when will it get fixed is 100% (or is that 110%?) out of my bailiwick, but it is interesting.

Is there a moral to this story?

All of my stories end with a moral, or at least a conclusion, I hope.  And what is that finding?  If you must use FIX...ENDFIX  It is this:

If you want to remove shared members, and do not want to reference specific members, your best chance is use case #5, where you stick those shared members under separate parents and select them through UDAs (or attributes).

And if EXCLUDE...ENDEXCLUDE tickles your fancy, then @SHARE works perfectly, but the outline has to work out that way.  A great reason to get all calculation requirements before database design commences. 

Btw, if you wonder why I spent this much time working on a seemingly simple topic in a supposedly dead language (BSO ain’t Latin, yet, and one wonders sometimes except there are all of those BSO apps out there), it’s because I really think this one (two, actually) is a bug.  I leave it to someone working at a platinum partner (they can raise Service Requests and yr. obdnt. srvnt. does not rate that) or a customer (you must have a valid maintenance contract) to log this one with Oracle Support.  And when you do, please tell me the bug number.  

Be seeing you.