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.

Stupid Programming Trick No. 25 part C -- Hybrid allocations purely in BSO

The high, the low, and the Perry Como

Sometimes I wonder why I make these parenthetical references when it seems all too likely that Mr. Smooth and Calm aka Mr. C is an unknown quantity to anyone under the age of, say, 70.  And me.  In any case, for those of you brave enough to click here , here, and here you will now have at least some appreciation for music of a calmer nature.  Isn’t life hectic and unpleasant enough?  Wouldn’t some nice, gentle, soothing singing, akin to a Nice Cup of Tea, be warranted?  

Of course, if you can’t stand the above,  check out Eugene Levy's (special thanks to Bob Rhubart for getting the name right) absolutely devastating parody on SCTV.  And yeah, SCTV is also something no one under the age of 40 would likely even know unless you are a fan of Canadian humour (see what I did there?).  Of course if you’re really a fan of all things Canadian, and in particular a fan of The Maritimes, and a fan of brotherhood, and a fan of a pretty high level of slapstick, and a fan of general insanity, I suggest you check out Trailer Park Boys.  My yet-another-brother-from-completely-different-parents and fellow ODTUG board member, Martin D’Souza, hates that show.  How can he be Canadian as it is a national institution?  Perhaps because he has a brain that he actually uses?  You decide.

Perry Como?  What about Gaius Julius Caesar?

Did you know that Caesar has a Twitter account?  Never discount the descendant of Venus, conqueror of all Gaul, dictator for life.  Follow him on @Julius_Caesar.

   

Oh, and since I have bastardized his immortal words during the triumvirate of this series on allocations in Hybrid Essbase, check out this fairly awful Latin quote:

Hybrid Essbase serpit sunt divisa in partes tres, quarum unam incolunt horrendis file parsing, altera CDFs, qui in sua lingua vetus figuratus calc scripts noster BSO calc cumulus script, odiosis tertia. Haec inter se differunt secundum formam aditu codice, et complexionem. Horum omnium fortissimi sunt BSO calc scripts sunt simplicissima.

I pinky promise that this blog will henceforth have a much smaller smattering of Latin.  Like none.  It was fun while it lasted but I think I’ve exhausted this particular vein.  Or…

I russam defricare proponis blog erit deinceps regia a multo minore smattering linguae Latinae. Tamquam nihil. Pugnatum erat fun dum puto me hoc exhaustis venae.

:) or maybe that should be :P

As always, check for the translation at the bottom but in the meantime Read The Whole Thing.

Enough of the palaver, on to the show

Sorry for the above, but I’m working on four hours of sleep because of a project – sleepwise, this is like Kscope but without fun – and yr. obt. svt.’s writing filter is firmly turned off.  

What is switched on is the realization that you have seen two approaches to Hybrid allocations:  mine, which makes pigeons weep because its horrific nature, and Peter Nitchke’s that uses ultra uber super duper cool Calculation Manager CDFs in an Essbase calc script (NB – Oracle’s Sree Menon is a friend to all Essbase and Planning practitioners for his creativeness and enthusiasm in working with the non-Oracle world) which is…complicated albeit cleaner.  

This is a post that sits somewhere in the middle – not awful, not cool, but instead, like Perry Como, simple, straightforward, and not a challenge to your brain.  Think of this as the middlebrow approach approach.  Did I mention it’s also pretty fast.

Accumulate the positive, eliminate

Satish M over on Network54 noted that he used an accumulation approach to getting totals to do allocations and it was blazingly fast.  My older, smarter, undoubtedly cooler, and completely not actually related older brother, Glenn Schwartzberg also mentioned this after the debacle of my first approach.  

What does this look like?

Easy peasy, lemon squeezy

It really couldn’t be easier.  Use what Glenn likes to call a loop to iteratively add, only in the BSO layer of a Hybrid database, the totals of Market and Product into those same No Market and No Product members.  Yes, it uses a mildly horrific cross dim on the left side of the the equation and yes it has to happen within a member formula but it’s actually pretty straightforward.

The code itself

Let’s take it apart bit by bit.

1. Before this runs, the target No Market, No Product, Actual Budget v must be cleared out or each subsequent run will double, triple, quadruple, etc. count the totals.
2. Loop the level zero members to touch all of the existing members.
3. Define a member formula block to allow the left hand cross dimensional indicators.
4. Write that ugly cross dim to stick the totals into No Product, No Market, Sales, Actual.
5. In No Product, No Market, Sales, Actual, add each member’s value as the loop of members occurs.
6. Allocate the Distribution Pool to level 0 Budget Products and Markets.

Ta da, it’s done.  Pretty easy, no?  I sure wish I had come up with this initially but you have to admit, the other approaches are pretty epic hacks.  

Wot’ll she do, Mister?

Pretty fast, is what.  Tim German aka @CubeCoderDotCom tested this on the same Hybrid cube we used in our Kscope15 calculation aproach scorecard presentation and found that it ran in about eight to nine seconds.  Comparing that to my insane six second export, parse, import process we can observe that Hybrid’s ASO aggregation engine is faster than BSO – no real surprise there and the promise of Hybrid.

And there you have it:  three different approaches to allocations in Hybrid.  What does yr. obt. svt. like the best?

1. The extract, parse, import and calculate process was fun, kind of, to write but it should really never, ever, ever be used.  It fails.
2. Peter Nitschke’s approach is far, far, far cooler.  I don’t have exact timing on this as it hasn’t been tested on same database, but I expect it to be blindingly fast because it too uses the ASO aggregation engine.
3. This last accumulation approach is the most understandable pure Essbase way of doing things.  While slower, I think it’s likely the way most people will approach this use case.  Having said that, I encourage you to use Peter’s way because of its performance.  And utter badass coolness.

And now the last bit of semi-prententious Latin translation

Hybrid Essbase spreads are divided into three parts, one of which is inhabited by horrendous file parsing, the other CDFs, those who in their own language are old fashioned calc scripts, our BSO calc accumulation script, the boring third. All these differ with one another in the form of approach, code, and complexity.  Of all these, the BSO calc scripts are the simplest.

Videat, tibi.

CalcMgrExecuteMaxLScript and RUNJAVA

Introduction

This is just a super (well sort of) quick post on the @CalcMgr CDF and running MaxL scripts.

You saw that I previously discussed the @CalcMgrExecuteEncryptMaxLFile command and showed how both to use that calc script function as well as the RUNJAVA equivalent.

Over the weekend, mostly because we are sad individuals, Peter Nitschke, Tyler Feddersen, and I were going over how to use @CalcMgrExecute in conjunction with MaxL shelling to run batch code.  Fascinating, eh?  Maybe.

In the course of that, I stumbled my way through the RUNJAVA syntax (which, of course, is maddeningly different than the RUNJAVA commands for running the encrypted MaxL file and no, not in the way you would think) and came up with one or two interesting twists on passing both member names and just plain old parameters to a MaxL script.

One note – I am not going to go into how to use shelling in MaxL scripts, but am just going to cover the basics and one or two odd things in running MaxL scripts in this post.

What am I trying to run?

Here’s the MaxL script noencrypt.msh:

The username, password, servername, and a single member name must be passed as parameters.

RUNJAVA

When it comes to using the Calc Mgr MaxLFunctions CDF with RUNJAVA the important bit to understand is that it is position dependent.  By that I mean the code that runs encrypted MaxL scripts and the code that runs non-encrypted MaxL scripts has no explicit flag that tells RUNJAVA to run them one way or the other.  Instead, it is the presence of the –D and encrypted keys (or maybe it’s just –D – dunno on that one as I try to have some kind of a life) that forces an encrypted script execute.  

And the absence of that information makes the RUNJAVA invocation of the CDF run the MaxL script as non-encrypted.  Interesting, eh?  I have to look into other commands but again, I sort of have a life, or at least I try to.

Remember this about RUNJAVA – it does absolutely zero syntax checking.  You could stick NowIsTheTimeForAllGoodMenToComeToTheAidOfTheirParty and EAS wouldn’t throw an error.

Non-encrypted MaxL file

If you want to do it the right way, see the below:

One thing that is nice about this is that you do not have to pass the username, password, and servername to the MaxL script unless you wish to as the RUNJAVA parameters you see there are just that – parameters.  And just like running a MaxL script from a command line, what gets passed, if anything, is up to you.

Another thing to note is the double quotes, double backslashes.  I’ve used double quotes and single forward slashes – it never occurred to me that my advice on MaxL scripts quoting and escaping also applied to RUNJAVA.  Thanks, Peter, for pointing that out.

Encrypted MaxL file

This is in contrast to the way an encrypted MaxL script is called via RUNJAVA.  I suppose one could argue that this is not all that unreasonable – when an encrypted MaxL script is called from the command line it must receive the decrypt flag and the public key.  What the @CalcMgrExecute (or its RUNJAVA) equivalent also requires is the username and password in encrypted for even though the called MaxL script has that information.  Weird.

After the encryption information, you can optionally pass more parameters such as the server name and a member name.

@CalcMgrExecuteMaxLFile

If RUNJAVA isn’t your cup of tea, you could always use @CalcMgrExecuteMaxLFile.  The nice thing about this command is that it will syntax check your code.  I actually used this technique first to back into what I would need for the RUNJAVA equivalent.

Remember that you must have a block to run this in (it is, after all, a calc script function), and only select one block in your code unless you want to run this multiple times, once for each block.

Non-encrypted

Note that parameters must be enclosed within a @LIST function.  You will also note that only the member name Inventory has a @NAME function surrounding it.

Encrypted

For a point of contrast to RUNJAVA, note that this approach does NOT force you to pass the encrypted username and password – just the public key.  No, I have no idea why the two are different since they call the same function within the CDF.  Some Things Are Not Meant To Be Understood.

Would you believe?

In both encrypted and non-encrypted MaxL, note this odd bit:

Note the @NAME() around the username.  This will syntax check and run quite nicely.  But remember, hypadmin is a username, not a member.

Alas and alack, but not all that surprisingly, when I remove @NAME() from around the member name Inventory, EAS pukes:

From this, I take it that the CDF doesn’t care if you wrap non-member names in @NAME() but does care if you leave that off of real member names.  Weird but there it is.

And of course RUNJAVA could give a tinker’s damn about @NAME – it takes parameters as you pass them and off it goes.

Addendum

Peter had shown me a technique that ignored the username and password early in our email chain.  For whatever reason, I could not get it to work.  And then, as so often happens in at least my life, I told him that, and then I tried it again, and then…it worked.  Arrrgh.  So here’s the code, thanks to Peter:

 

Just in case you missed the syntax for not passing the username and password, it is:

"",""

That is doublequote-doublequote-comma-doublequote-doublequote.  I swore that I tried exactly that in my tests but given that it failed and then worked, I got something wrong. 

I did ask Peter how he figured that syntax out as it is not documented (well, that is sort of the purpose of this post, but still).  His reply, “I'm lazy? *grin* Wanted to see how little I could put in a piece of code and have it work.”  The sign of a true hacker.  Thanks again, Peter.

Conclusion

The Calc Mgr CDF is really powerful, hardly used (although that seems to be changing), and is still not documented.  Play around with it, look for that essfunc.xml file on your server to give you a few ideas, and have fun.

Be seeing you.

Thanks again to Peter and Tyler on this – always fun, if a bit geeky, to share interests in code.