20 November 2012

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:

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.html
My 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.





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.

11 comments:

  1. Can you use Exclude in a Business Rule? I seem to remember it only works in calc scripts.

    ReplyDelete
  2. Dear Anon,

    You can use EXCLUDE...ENDEXCLUDE in a Calc Mgr business rule, at least on 11.1.2.1.

    I recall back in the 9.3.1 days that EXCLUDE didn't work in HBR (I think I was burnt by this), but that was four odd years ago.

    Regards,

    Cameron Lackpour

    ReplyDelete
  3. Cameron, I think you have an error in Case #1

    You are @REMOVEing member '200-10' but your validation worksheet (and the outline, for that matter) don't have that member. The correct child of "Diet" would be "200-20", not "200-10" so.... you're removing the wrong member !!

    in the remaining cases, the result is consistent, remove the prototype and all the shared members get also pulled out

    ReplyDelete
  4. Santiago,

    The point behind case #1 was to show that I could remove a non-shared member from the scope of the FIX.

    When I try to do that same bit of code, but with a shared member, the code does not work. But it should, right? 200-20 should be #Missing but instead it is 100. That's the bug.

    Regards,

    Cameron Lackpour

    ReplyDelete
  5. Hello and thanks Cameron,
    In the good old days of hyperion at a time I has been told by a technical stuff member that there maybe a lot of "minor" that will have to be fixed it will be fixed by developement. However if there are bugs regarding computing inside of essbase ALL alarm bell are ringing at hyperion developement.
    Why ? Oh no, nobody working with (and relying in) essbase should ever ask these question.
    Hm - I ask myself , is Oracle seeing this the same rigourous ethos.
    What would happen when two differently formulated (but regarding the outcome technical identical) SQL statements in Oracle DB would deliver different results.
    It's quite clear this would be a desaster.
    I really hope Oracle is seeing a clear parallel here... and that is all that I can say.
    Andre

    ReplyDelete
  6. Hi Cameron,

    I had noticed similar bugs and I just saw this blog entry, which means I was not insane.

    Something that I would like to add is that this illogical behavior also happens when using Shared members and 'AND' instead of remove.
    For example if you have an alternate hierarchy and want to select all the shared members tagged with a specific UDA it will not work properly. And you cannot use EXCLUDE in this case.
    I have not had the occasion to try @INTERSECT, the new function in 11.1.2.3 but hopefully replacing 'AND' by @INTERSECT will work.

    Thanks,
    Julien

    ReplyDelete
  7. Hi Cameron,

    Do you know if this issue has been fixed by Oracle yet. I searched on Oracle support but did not find any reference to this issue.

    Thanks,
    Ed

    ReplyDelete
  8. Ed,

    AFAIK, this functionality has not been changed.

    Regards,

    Cameron Lackpour

    ReplyDelete
  9. Hi Cameron!
    Thanks for your awesome blog! I ran into this nasty issue this morning in 11.1.2.1. Neither @Remove nor EXCLUDE was working for me despite specifically listing a single ahared member that I wanted removed from a FIX. I saw this post and I added the @SHARE to my @Remove and it finally worked!
    Looking forward to seeing you at Kscope14! :) Robin Banks

    ReplyDelete
  10. This was very fascinating, Cameron. I tried using EXCLUDE/ENDEXCLUDE today with a DATAEXPORT to remove Shares (to remove the pesky derived cells "errors" in ASO loading) and did not get the results I was expecting. Only around 1.8% of the data came through. Alas, it was the end of the day and I will return to it tomorrow.

    ReplyDelete
  11. Since I had the shared member in multiple alt hierarchies I used @LIST("Member_Name", @SHARE("Member_Name")) in the @REMOVE and it worked! Thanks Cameron!

    ReplyDelete