Why I hate (and love) Calculation Manager, part 2

You had your (mostly) two minutes of hate.  Now what?  Ah, the love.

Honestly, I don’t hate Calculation Manager (CM).  There are some differences to HBR but what should anyone expect with a new product?  Of course it’s different.  And an awful lot of CM, from the Planning rule perspective, is quite nice.  So it’s time for love.

So is there anything new on the calculation front?

There sure is.  I’m not going to cover why a focused aggregation makes sense – you already know that because you either figured it out yourself (It came to me on a project in Binghamton, NY like a bolt of lightning and no, I have no idea why.  I wish I did because then maybe I could use that thinking process to come up with other good ideas which are, if I am being honest, far and few between.  A man can dream, can’t he?) or you read this post.  The theory still holds true.

There’s an issue with what I wrote almost three years ago (It is hard to believe it’s that long ago, but it is.)  What happens when there’s a shared rollup?  In this example, I have not so modestly named one “Cameron’s favorites.”  

We’ll get to that shared hierarchy but first a review of the focused aggregation approach.

Let’s review

Let’s take a look at the basic form (oooh, no more HTML – Smart View rocks!):

What the CM rule needs to do is only aggregate the ancestors of DVD Recorder and PA.  What does that code look like?

This is the same code as before.  I created application level variables for:  Year, Version, Scenario, Entity, and Segments.  The last one was a bit tricky but I described (erm, I stole it from another blog) above how to make it happen through creating a dummy variable, exporting it to xml, modifying the xml, and importing it back in in the previous CM post.

I deployed the rule from CM to the Planning app, and then assigned it to the form.  Move along folks, nothing to see here.  Okay, just for those who forgot, or haven’t bothered to read my two old HBR posts, I set the rule to Run on Save, Use Members on Data Form, and Hide Prompt to drive the run time prompts in my CM rule:

Remember those variables I set up in CM?  They are going to read that form.  Does it work?  Yep.

Here’s what the parent of DVD Recorder looks like in Planning ad-hoc mode:

I enter 1999 into the form and click on the Submit button.

And oh yes, I had one of these as a wee lad.  Looking back, I was (am) such a geek.  Destined for EPM, I think.  Clawing back to relevancy, let’s see what happens when I click on that Submit button.

And here’s what the data looks like after the save:

Does 287854 – 285855 = 1999?  Why yes it does.  

And how long does it take?  For those of you without the eyes of eagles, that’s 0.038 seconds.

How long does a CALC ALL take?  

 

Quite a difference, eh?  Even this limited calculation is quite a bit slower:

Again, for those who need spectacles, that’s 0.61 seconds.   

The focused aggregation takes 0.038 seconds.  That’s just over 6.2% of the time it takes to do the limited calc and just 0.36% as long as CALC ALL.  Pretty neat, eh?

But what about shared members?

This approach, alas, doesn’t work when you’re working with a shared rollup.  Not important you say?  Why not?  If you’ve entered data either in a shared rollup or in the base hierarchy, don’t you want to see the shared parent(s)?  @ANCESTORS and @IANCESTORS do not, unfortunately, handle this.  Let’s take a look at that approach.  

I modified the form so that only “Cameron’s favorites” shows up because I am a megalomaniac moron:

I then enter 1066 into the grid and click the Submit Data button:

And the result is:

Uh oh, the value should be 1,159,269, but Essbase/Planning returns 1,158,203.  What we need to do is bring in the new for 11.x @ALLANCESTORS and @IALLANCESTORS functions to the code.  Isn’t progress wonderful?

And what do we see?

The parent “Cameron’s favorites” now works.  Isn’t that nice?  But what about the real parent, “Electronics”?  Oh yes, it works as well.

And just a little bit slower – 0.056 seconds.  Which makes sense as more blocks are being calculated.

It’s all in the blocks

Or the number of blocks, to be more correct.  The fewer that are processed, the better, because disk access to read or write blocks is where an aggregation slows down.  Less blocks = less time, more blocks = more time.  Even I can follow this kind of logic.

For giggles, I used the SET MSG SUMMARY setting in the rules and then grabbed the following statistics from the application log.  Pretty eye opening, eh?  

Calc All

Total Block Created: [0.0000e+000] Blocks
Sparse Calculations: [2.3357e+004] Writes and [1.1164e+005] Reads
Dense Calculations: [1.0968e+004] Writes and [1.0968e+004] Reads
Sparse Calculations: [1.0695e+008] Cells
Dense Calculations: [2.6093e+007] Cells

NB – Ignore the dense calculations – that is because the sample application has an odd view of a BSO dense dimension (why exactly there are dense stored calculations is not known to me).  I (and you should, too, if you don’t already) only build fully dynamic dense Account dimensions.

Sparse calcs = 23,357 writes and 111,640 reads
Sparse calc cells = 106,950,000

FIX on AGG

Total Block Created: [0.0000e+000] Blocks
Sparse Calculations: [2.0330e+003] Writes and [6.8710e+003] Reads
Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads
Sparse Calculations: [9.3091e+006] Cells
Dense Calculations: [0.0000e+000] Cells

Sparse calcs = 2,330 writes and 6,871 reads
Sparse calc cells = 9,309,100

Focused Aggregation

First FIX

Total Block Created: [0.0000e+000] Blocks
Sparse Calculations: [8.0000e+000] Writes and [6.8000e+001] Reads
Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads
Sparse Calculations: [3.6632e+004] Cells
Dense Calculations: [0.0000e+000] Cells

Sparse calcs = 8 writes and 68 reads
Sparse calc cells = 36,632

Second FIX

Total Block Created: [0.0000e+000] Blocks
Sparse Calculations: [5.4000e+001] Writes and [2.0700e+002] Reads
Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads
Sparse Calculations: [2.4727e+005] Cells
Dense Calculations: [0.0000e+000] Cells

Sparse calcs = 54 writes and 207 reads
Sparse calc cells = 247,270

Total sparse calcs = 62 writes and 275 reads
Total sparse calc cells = 283,902

What’s my point?

To roll up a form, your code could process 23,357 writes and 111,640 reads (those are blocks, btw) or you could process 62 writes and 275 reads.  Which do you think is faster?

Reusable code

CM has a whole series of standard templates, mostly for use in graphical rules.  Actually, the graphical nature of a rule (you drag and drop objects into a flow) means that rules are a bit more like HBR Sequences although in fact CM has its own version of Sequences called RuleSets.  But tell me; isn’t this essentially stringing together multiple rules?  

Regardless, writing once, using many times is a good thing (unless you are in love with repeating the same code and maybe getting it wrong).  Let’s review how this is done in HBR before we go into CM and see what’s different.

Macros and Templates

HBR Macros

In HBR, there’s a (not used all that often from my experience) way to reference common code called a macro.  I like putting the focused aggregation script into a macro (or a template) as that code tends to get called again and again.  Putting it into a callable procedure means I can focus the unique code in each form and do the routine stuff simply by calling code.  

Here’s what it looks like in HBR:

The macro mcrConsHBRConsolFocusedAggTemplate is just the focused aggregation code:

Parameters
If I want to get fancy, it’s easy to pass positional parameters to a HBR macro:

On the receiving side of this macro it looks like this:

CM Templates
In essence, HBR macros are customized by you, the developer.  In CM, there are, as I mentioned, lots and lots of standard templates that do all kinds of interesting things.  Which I am going to ignore as you can go read the documentation far better than I can repackage it.  I will focus on Custom Defined Templates, or just plain old templates because I am just plain old Cameron.

Things are definitely a bit different in CM.  

Here’s what you can NEVER do in CM.  Or can you?

Being the generally ignorant kind of guy that I am (I have been called worse), my first instinct with a template was to drag it into the CM rule script.  Uh oh.

 Oh, that’s a nasty error.  But here’s the funny thing – if you deploy the rule, despite the nasty message, which looks just like the one where the RTP prompts weren’t valued, Failed to validate against Essbase. One or more unresolved RTPs found" [ID 1235676.1], it works.  I’m probably breaking all kinds of rules when I just drag and drop it but it works.

The approved way

Boo!  Hiss!  Who wants to do something the right way when hacking is available?  Oh, you mean people who don’t want to place their necks right on the chopping block and ask the executioner to swing the axe?  Nope, living out episode two of The Death of Mary Queen of Scots on Radio 4 isn’t a good idea.  So what is the right way?

It’s all graphical

It’s really a colossal pain, in my opinion, because you have to set the FIX statement in the first script.

 Then insert the template.

And finally insert the end FIX.  Not so easy to read as it requires a lot of clicking about.  But it does validate.  And I will note that this concept of using a flow chart to do this kind of thing is 100% the graphical CM way, so I guess I shouldn’t be surprised.

And one big thing you CAN’T do

I have to say there aren’t that many people out there that use parameters, but I have used it to drive rate types in HBR macros that calcuate currency conversion (one of these days I will blog that as well) for Planning applications.  

Remember this?

And this?

That ability to pass parameters simply can’t be done in CM.  My advice:  lobby Oracle to expand the functionality/don’t write code that works that way.  Such is life.

What’s your story, morning glory?

So is Calculation Manager (see, I am writing its full name for the end) a bust?  Nope, not at all.  Think of all of the Oracle components it addresses:

• Financial Management
• Essbase BSO
• Essbase ASO
• Oracle General Ledger (for Essbase)
• Planning

That’s a fair bit more than HBR ever talked to (Essbase BSO + Planning).

And of course you can actually generate decent code with this graphical interface as opposed to the awful stuff that came out of HBR.

Did I mention that there are tons of predefined templates?

Did I also mention that if you use EPMA for Planning, you must use Calc Manager?  But if you’re using EPMA, you knew that already, and are using it.

While only time will tell if this is true or just a random rumor I picked up, it sounds like there’s a good chance EPM 11.1.2.2 is going to force you to move to Calc Manager even if you use Classic Planning.

And of course there certainly are a whole bunch of cool things in Calc Manger – I am a big fan of the way it organizes rules, variables, and just about everything else in a hierarchical manner,  of course no more inability to select an outline as HBR has suffered literally for years and no more insanely long Hungarian notation naming conventions as in Hyperion Businsess Rules.

All in all Calc Manager does bring quite a few things to the table.  I hope you’ve enjoyed this trip down Calc Manager lane, and maybe even learned a few things or two.  We even got a few unauthorized hacks in there as well to make life interesting.

And yes, I lurve you, Calculation Manager.  :)