Developing Essbase Applications now available for purchase

It’s here, it’s here, it’s finally here

For those of us who love books, or whose ego demands a hardbound copy to place on a bookshelf so he can read his name on the binder, you (and I) can now buy Developing Essbase Applications: Hybrid Techniques and Practices and get it, get it, get it in our hot little hands.

Yup, for real and for true, Amazon now has it.

I’ve preordered it and hope that it will be waiting on my home office desk when I get home.

As of noon-ish today, there were only 18 copies left.  Be the one who makes me happy and order 19.

All kidding aside

We – John Booth, Tim German, William Hodges, Mike Nader, Martin Neulip, Glenn Schwartzberg aka MMIC aka the-older-brother-from-other-parents-who-now-hates-me-for-leaving-him-off-this-list-sorry-Glenn-sorry, and yr. obt. svt. – wrote this as a labor of love.  There’s no real money it, just the satisfaction that we’ve spread knowledge and hopefully improved the Essbase world a little bit.  We hope you enjoy reading it as much as we did in the writing.

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.

Developing Essbase Applications: Hybrid Techniques and Practices is now available on Kindle

Huzzah!  It’s finally for sale!

Thanks to Anonymous’ comment (thanks, Anon) on the prepublication blog post for DEA Hybrid, world+dog now knows that the book is finally, finally, finally available for purchase so long as the reader isn’t tied to  dead trees and ink.

Click here or on the big pretty cover below to buy, buy, buy.  Buy enough of these and there will be a third DEA book.  Don’t and CRC Press will avoid me like a deadbeat dodges the bill collector.

Limit one per customer, but only for the hardbound book

Imagine my surprise when I tried to preorder 10 copies of the book as I must somehow boost sales.  Unpossible!

If you have a Kindle, would you mind sending me a picture?

I have a halfway decent cellphone with a camera, but I don’t have a Kindle.  Would someone, anyone who knows my Twitter or email address, please take a snap and send it to me?  I’ll insert it into this post and give you credit.  Thanks in advance.
  
Edit for Kindle
And now, thanks to John Booth, I now have a (stolen) image of DEA:  Hybrid.  John, thanks again..

Be seeing you.

Stupid Programming Trick No. 25 part B – Hybrid Allocations the CDF Way

Pig Latin and Essbase

This series of Hybrid allocation approaches started out as a two part approach, but Peter Nitschke aka @essbasedownundr came up with a better way.  So, in the spirt of the before-there-was-a-Descartes-there-was-a-Caesar-or-a-horse is now in three or:

Hybrida Essbase prouinciis sunt divisa in partes tres, quarum unam incolunt file parsing alter CDF, tertiam qui ipsorum lingua Java nostrum BSO calc script appellantur. Haec inter se differunt secundum formam aditu codice, et complexionem.

Good stuff if you’re about to invade Gaul and make it a province of Rome.  And a good way to describe the three different ways of approaching Hybrid Essbase allocations.

Skip to the end (but absolutely Read The Whole Thing) for a translation into something a bit more understandable although I suspect you’ve guessed at the meaning.

And with that, heeeeeereee’s Peter!

The agony and the ecstasy

Upon first reading Cameron’s blog post on allocations in Hybrid I was horrified, disgusted, appalled, gobsmacked bemused regarding the machinations required to achieve something so simple. I believe there may have also been the words, “VBA??? That is the sound when doves cry.”, although to be honest I haven’t directly heard that but imagine that would be the sound of doves in their cognitive programming agony if they could code in VBA.  But I digress.  Surely, there is a better option!

Here are the issues to consider:

1. All upper level members of a Hybrid cube are dynamic
2. If we calculate against those upper members, we will throw the cube out of Hybrid mode. and into traditional BSO sparse dynamic calculations…at which point we’ll probably beat it in calculation using an abacus.  Or Roman numerals.  Or counting matchsticks.  You get the idea.  This is a Bad Thing To Be Avoided.
3. Therefore we have to work out a method of getting the calculated upper level data to a stored level zero member.
4. The only way to pull the data out at the upper level of a Hybrid cube is MDX.
5. MDX is…not very elegant when run out using MAXL (hence Cameron’s everything-is-a-nail-if-VBA-is-your-hammer strategy).  Cameron notes that this analogy is applicable to screwdrivers, sledgehammers, and ODI when used to build Planning dimensions.

Back to the Future I – A history lesson

Okay – so the underlying problem is pretty simple. Let us now jump back to an earlier time, an easier time, a simpler time. Hyperion version 11.1.2.2. Before all of this Hybrid nonsense. When BSO cubes ruled the world and performance was lacklustre but we liked it that way.

One of the brand new and world-breaking features in 11.1.2.2 was the advent of the @XWRITE statement. The counterpoint to the @XREF – this would allow us to write data from one cube to the other and had some amazing potential! Its benefits were easily recognisable – because you were only writing from active blocks, performance was significantly increased, and @XWRITE also had one key advantage in its ability to create the blocks on load! Gone were the days on needing to put CREATENONMISSINGBLK above your @XREF statements and waiting hours.

One of the items slipped in the documentation for this function is as follows: “For @XWRITE only, a reserved keyword @LOOPBACK can be used to write to the same database.” Curious – but actually had some utility in its ability to create blocks. The very talented David Ambler turned this into an elegant strategy to create blocks for a balance sheet in future years – pushing opening balances from the closing balances of the prior year.

Now I bet I know what you’re all thinking – that’s great Peter, so you’re just going to use @XWRITE from the parent to write to the stored level-0 member. Well done for reusing a four year old function. Except...that doesn’t work. @XWRITE is one of those top-down formulas that doesn’t work in Hybrid (and I did try). The cube drops out of Hybrid mode and we’re back to the abacus.

Still it’s a nice idea. Use a function to write back to the database directly, rather than export the data, map it and reload it. Now all we need is a function that natively uses MDX.

Back to the Future II – Where’s my hoverboard

Fortunately Oracle has provided one! The new (from 11.1.2.3.502 at least) CDFs of MDXDataCopy & MDXDataExport. Perhaps even more fortunately, Celvin Kattookaran has already written up how it all works! (whom despite never having met, I feel indebted to a significant number of drinks for all of the assistance he’s provided over the years) .

So, we’ve got a function that can export data using MDX thus keeping the database in Hybrid mode as well as the ability to map data from one member to another.

Where it’s at

Using good ol’ Sample.Basic, albeit a Hybridized/bastardized version of it, the code is as follows:

//ESS_LOCALE English_UnitedStates.Latin1@Binary

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy

"whynotzoidbergasAESkey==" /* key */

"pemO6ZElxHY7m570TfvhDB8H4WMcxP53wjWPlB26SwETEj/so3WuzB8ZBjnJBhUJ" /*user*/

"nqkqpUXwhhAywRPnn/ZFxOy5kKpb7iZoZWf6bA1NOJVR1hBzj15k2hpzjcgIwidC" /* password */

"Hybrid" /* from application */

"Hybrid" /* from database */

"Hybrid" /* to application (can be the same as the source) */

"Hybrid" /* to database (can be the same as the source)*/

"{[Scenario].[Actual]}" /* MDX that defines the column members */

"crossjoin(crossjoin(crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]})"  /* MDX that defines the row members */

"Sales,Market,Product" /* source member mappings, can be empty */

"Sales Driver,No Market,No Product" /* target member mappings, can be empty */

"" /* Target POV columns, members from dimensions that do not exist on the source*/

"-1" /* rows per page */

"e:\\Ifthisworkscameronowespeterabeer.log"; /* log file , can be empty */

Just one beer?  Surely more.  Alas, Americans only have Fosters, which is Australian for piss, so it’ll have to be an American microbrew .  Note that Australians are deeply ashamed of Fosters and wish that Castlemaine were on offer outside of Godzone so the world may understand the Awesomeness of Australian Beer.  Alas and alack, for unfathomable reasons it is not to be. Actually in truth: XXXX is Australian for piss. Fosters is merely Australian for Export Only. Still, it’s better than sex in a canoe.)

Breaking it Down

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy

Using the RUNJAVA command because we only want to run this command once and it’s all defined. There is also the function @CdfMDXDataCopy which could be used, but is not necessary here.

"whynotzoidbergasAESkey==" /* key */

"pemO6ZElxHY7m570TfvhDB8H4WMcxP53wjWPlB26SwETEj/so3WuzB8ZBjnJBhUJ" /*user*/

"nqkqpUXwhhAywRPnn/ZFxOy5kKpb7iZoZWf6bA1NOJVR1hBzj15k2hpzjcgIwidC" /* password */

This command requires a fully encrypted username and password and an associated AES key. In order to encrypt the key you need to use the calcmgrCmdLine.jar utility which can be found Essbase server. The command is as follows:

java -jar calcmgrCmdLine.jar -encrypt -key <key> <user>

java -jar calcmgrCmdLine.jar -encrypt -key <key> <password>

I had a number of problems with this because the error handling of the AES string length isn’t...great. Basically you need a 24 character key with enough entropy in it – it doesn’t actually matter what it is (see my example) but you’ll need to test a few options (or just hammer the keyboard to get 24 chars).

"Hybrid" /* from application */

"Hybrid" /* from database */

"Hybrid" /* to application (can be the same as the source) */

"Hybrid" /* to database (can be the same as the source)*/

This one is pretty self-explanatory. The standard use of this function is to write to other databases – but as you’ll see, you can replicate our @LoopBack function from earlier and write back to the source.

"{[Scenario].[Actual]}" /* MDX that defines the column members */

"Crossjoin(Crossjoin(Crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]})"  /* MDX that defines the row members */

Okay: so this is one of the limitations of this process – the MDX query is simply a Column and Row definition. No Where clauses etc. Still, for what we’re trying to do here it’s reasonably functional.  

"Sales,Market,Product" /* source member mappings, can be empty */

"Sales Driver,No Market,No Product" /* target member mappings, can be empty */

Here is where some of the fanciness lies. Really simple mapping (it’s actually just a basic string replace function) but allows us to write from a parent to a child to move the data to a stored member.

"" /* Target POV columns, members from dimensions that do not exist on the source*/

This is blank because we have no additional dimensions (the target IS the source). However, this is potentially useful when you’re mapping to an ASO reporting with additional dimensions.

"-1" /* rows per page */

As described in the documentation – this is a function to allow for a limiter of queries. If it’s a positive number only the number of rows listed will be extracted from the source and mapped. Useful only if you’re concerned about an out-of-control MDX query. Setting it to -1 will extract and load everything (and is obviously recommended

"e:\\Ifthisworkscameronowespeterabeer.log"; /* log file, can be empty */

A log file that will be generated on the Essbase server. Incredibly useful for troubleshooting as it actually shows the processes and the outputs!

2015-08-28 10:09 >  Used Memory (Mb):8 Free Memory (Mb):237 Total Memory (Mb):245 Max Memory (Mb):245

Query:SELECT {[Scenario].[Actual]}on columns,crossjoin(crossjoin(crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]}) on rows FROM Hybrid.Hybrid

User:admin

Source:Hybrid/Hybrid

Target:Hybrid/Hybrid

Rows Per Page:-1

Signed in User:admin

Grid Size: Rows(13) Cols(5)

                Actual   

Jan    Sales Driver    No Market    No Product    134590.0   

Feb    Sales Driver    No Market    No Product    130233.0   

Mar    Sales Driver    No Market    No Product    132922.0   

Apr    Sales Driver    No Market    No Product    128346.0   

May    Sales Driver    No Market    No Product    131673.0   

Jun    Sales Driver    No Market    No Product    135427.0   

Jul    Sales Driver    No Market    No Product    132977.0   

Aug    Sales Driver    No Market    No Product    137744.0   

Sep    Sales Driver    No Market    No Product    122903.0   

Oct    Sales Driver    No Market    No Product    127760.0   

Nov    Sales Driver    No Market    No Product    127837.0   

Dec    Sales Driver    No Market    No Product    130086.0   

Once you’ve got it working this should be set to missing for performance reasons.

So how’s it perform then?

On the topic of performance – how fast is it? On a fully loaded (admittedly a customised/hybridefied Sample.Basic) database of 9 dimensions with the biggest being 14k and 9k members and with the data logging turned on:

Total Calc Elapsed Time for [Test.csc] : [0.263] seconds

Without the data logging:

Total Calc Elapsed Time for [Test.csc] : [0.061] seconds

Given that you could put this step immediately after a data load (it’s Hybrid sonny – ain’t no aggregation time) that’s pretty ridiculous in an absolutely awesome way.

What have we learnt today boys and girls?

Four key takeaways from this:

1. MDX is an awesome language – as long as you can work out a method of parsing it, or just not needing to.
2. Hybrid is going to take some getting used to in order to come up new methods of calculating things
3. Sree and his team in the Oracle Calculation Manager group are coming up with some fantastic CDFs to drive a whole stack of functionality going forward. Definitely worth looking at some of them if you haven’t already.
4. Cameron really needs to step up his game ‘cos he ought to have figured this out on his own.

Okay, one more takeaway:  Latin may be a dead language, but when uttered by Caesar, it has a certain ring.  

In the Queen of Australia’s English

Hybrid Essbase allocations are divided into three parts, one of which is inhabited by file parsing, the other CDF, those who in their own language are Java, our BSO calc script, the third. All these differ with one another in the form of approach, code, and complexity.

The next, and last (unless someone else is disgusted by Cameron’s allocation code – there is always that chance) approach will be the far more pedestrian and frankly less exciting accumulation method but need every calculation incorporate a frisson of geeky awesomeness?  Sometimes boring is good.  And easy.  And BSO-only albeit within Hybrid.  It won’t be that bad.

Be seeing you.