Want to hire me? For availability and scheduling please email info@arcepm.com

18 September 2015

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.

5 comments:

Bob Rhubart said...

As a rabid SCTV fan I must correct an error in your post. That's Eugene Levy portraying the prostrate Perry in the comedy clip.

Bob Rhubart said...

I commend you, sir, on your fondness for easy listening, and your taste in comedy. As a rapid SCTV fan I must mention that the C Man was also the target of another SCTV bit, this time played by the brilliant Eugene Levy: https://youtu.be/zj5A7W-0zPY

Still Como was no Sinatra...

GlennS said...

Cameron,
I am glad you have finally seen the light and elegance of my solution. While you consider left side equations horrendous, it is only because you are left brained. Only left handed people are in their right mind. I actually think it has more to do with you being a rule follower. You were told early on that cross dims on the left hand side of equations were bad, you assimilated that and now use it as a mantra. I on the other hand embrace simplistic out of the box methods to accomplish what others create elaborate solutions and struggle with them. For comparison, you could try Edwards approach to use a single sum statement instead of my loop. It may be faster but I doubt it

Cameron Lackpour said...

Glenn,

Follower? Really? This is a blog called "Cameron's blog for Essbase hackers".

There can't be too much left brain thinking here, can there?

Now if ask me if it looks pretty, nope, I still don't think it does.

:)

But yes, MMIC, I do follow you to the best of my somewhat bad abilities. Really, I do.

I don't know what Edward's sum approach entails.

Cameron

Peter Nitschke said...

Hey,

Because I actually was interested, I did some performance testing.

Hybrid is absolutely amazing at these kinds of calculations. Limited upper level blocks means that there is limited 'noise' to work through.

The worst case I saw was the aggregation code being around 3.3-3.5x slower than the associated MDX Export and load. Given that the time difference was 2.7 secs tto 9.5 secs, that's not too terrible.

The test case was summing up a distribution from Wrkforce to GL. 5000 employees across 150 entities - with the code aggregating the distribution across 85k entity members.

So, ridiculously quick in all methods.

Cheers
P