Want to know The Truth About CPM?

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.

14 November 2012

Will the 13th be a lucky day

Nope, I am not becoming superstitious

Oh, I never walk on a crack, lest I crack my mother’s back (you have to see the pavement heaving in my town – I blame all those lovely trees), nor do I walk under ladders (having fallen off a tall one – yeah, that explains a lot, doesn’t it? – I know what is up will eventually come down, maybe on my head), and I never throw my hat on a hotel bed (when I am wearing one of my boonies, I am more likely to be near a sleeping pad and bag), so no, I am not becoming superstitious in my old age.  And 13 December is not Friday the 13th,  so really, what could possibly go wrong?  Ah, but a man's reach should exceed his grasp, Or what's a heaven for?

I am hoping for luck, however, because it will be required

Why?  Because I (I should say we, as in ODTUG) am going to participate in something new, innovative, quite possibly very rewarding, and also very possibly a bit of a stress-inducer.  What oh what oh what am I talking about?  Nothing other than the first ever ODTUG virtual experts panel.

Panel beating

I have participated in, and moderated a few Kscope panels (I leave it up to the reader to decide if yr. obdnt. srvnt. deserved to be up front – I have to say I sometimes wonder) and I know how much fun and yet informative they can be.  From a preparation perspective, all one needs to do is bring knowledge and experience, and a healthy desire to chime in – the rest is magic.  And from an audience perspective panels are an opportunity to get some (hopefully) knowledgeable opinion on technical matters from a variety of perspectives and experience.  Did I mention that moderating these things is like trying to herd autistic cats?  Fun all around.  

Kscope (like Christmas) comes but once a year, but the need and desire for panels happens the other 51 weeks.  What to do?  Enter a brainstorming IM session between John Booth and myself.  We were kicking around the idea of doing something like the show these two morons/idiots/geniuses/very funny guys do for the automotive world, but for EPM world when one of us (I know not which, but I suspect it was John) said, “Why can’t we do a panel?”  And thus the ODTUG virtual experts panel was born.  (If you follow the link, replace Chemical X with very strong coffee.)

Not just EPM

One thing to note – as John and I talked about this idea, we realized that to limit this just to EPM was silly.  You will note that two out of the four panelists are NOT from the EPM world at all, but the larger data integration and business intelligence communities.  I am particularly excited about this (and excited that we were able to go outside of the US and get people many, many, many hours ahead of the States to participate) because Oracle’s tools are crossing disciplines.  We chose Oracle Data Integrator because it is an exemplar of a tool that does just that.  Given ODI’s read-from-anywhere, write-to-anything nature, looking at ODI from an EPM perspective simply didn’t make sense.  And so we are not.

The vision

The way this is going to work is:
  1. ODTUG is going to scour the world for the best practitioners in a given field.  In our first go round, focusing on ODI, we have Matthias Heilos, Gurcan Orhan, Mark Rittman, and (somehow) yr. obdnt. srvnt, up on the panel.
  2. You, dear audience, will connect to ODTUG’s GoToWebinar session and listen to John kick off the panel.
  3. You will listen to our witty yet wise banter and repartee and this dialog will spur ODI questions.
  4. You will send questions to the ODTUG GoToWebinar administrator via G2W’s (I am not typing that out any more) chat feature.
  5. The ODTUG G2W (hi, Lori Lorusso) admin will collate the questions and pass them to John and John will ask the panel the question.  Btw, this approach is because moderating a panel and running a webinar is akin to rubbing your stomach and patting your head at the same time.
  6. Chaos/genius/a cacophony of panelist voices will ensue, and hopefully your question will be answered.
  7. This will all be a great success (bar some inevitable minor logistical SNAFUs as we climb the learning curve, and yes we have rehearsed it beforehand, but there will be stress for we panelists, not you the audience) and you will see many more virtual panel webinars from ODTUG.  Did I mention this will be great?

Be a part of it

A panel differs from a presentation in its spontaneity and improvised content.  In other words, while this webinar will be recorded, it does not follow a set path and you most certainly can and will influence what we talk about.  But of course you have to be there to impact it.

Here’s the gen

What:  ODTUG Expert’s Panel Webinar - Oracle Data Integrator (ODI)
When:  13 December 2012, 1:00 pm to 2:30 pm Eastern (US) Standard Time
How:  https://www3.gotomeeting.com/register/270041222

Join us, won’t you?