It’s not too late but it almost is

The set up

I know you’re all coming to KScope12, right?  Right.

Why?  Simply because it is the best combination of deep technical content, training, and networking the Oracle world has ever seen.  (Some call me biased, I call myself well informed, at least on this particular topic.)  I hear the parties are pretty good, too.

What, you’re coming to KScope12 and you haven’t signed up yet?  For shame.  
The horrible, horrible shame
The real shame is, by not signing up BEFORE 26 March 2012, you will have to pay an extra $300.

Who would want to do that?  No one I know.  C’mon, if you’ve got an extra $300, give it to me.  :)

The resolution where you, the hero of our story, are fully redeemed (and quite a bit richer)

Big secret – use my buddy/mentor/geek idol Tim Tow’s company code, AOLAP, and get another $100 off this week.  That’s $400 off the list price.  

Now are you going to sign up?  I hope so.  $400 is not exactly chump change and like I said, if you feel like $400 is insignificant to your pocketbook, take advantage of the early bird sign up and send me the dough, moolah, cabbage, dosh, etc.

As you’re all too smart for that, I’ll expect you’ll simply save yourself the unnecessary expense and enjoy Kscope12 that much more.  See you in San Antonio!

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.  :)

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

Hate takes too much energy

And besides, it’s difficult to hate a software product, unless its name is HAL.  :)

Love?  

When it comes to technologies, I love Essbase (and in fact I was the editor in chief and contributed to a book all about it – read about it here), but love for Calculation Manager aka Calc Manager aka Calc Mgr aka CM?  Love may be a bit of an overstatement but it certainly has its interesting bits as I shall endeavor to show you.

Why Calculation Manager now?

I have resisted to date the siren song of Calculation Manager (there is no way I am typing that again and again and again – CM it is henceforth)  as I tend to do Classic Planning applications that utilize Hyperion Business Rules.  However, time marches on, EPMA uses CM exclusively, and I have read (although I have to say this is 100% unsubstantiated) that 11.1.2.2 Classic Planning applications will no longer support HBR.  

In any case, this is a good opportunity to revisit what continues to be some of this blog’s most popular posts, Why I hate (and love) Business Rules, part 1 and 2,  This post is similar, but for CM and I will throw in a few twists that I didn’t (couldn’t actually) cover in the HBR posts.

If you haven’t yet dipped your toe into CM, check out this good introductory series of blog posts on the Edgewater/Ranzal blog:  Parts 1, 2, 3, and 4.  One difference between what you will see below and their series – I don’t use the graphical module.  If you are in love with dragging and dropping you will have to look elsewhere.  If you love graphical rules, I still suggest you read on because I cover quite a bit that is common to both approaches.

What’s different?

Navigation

Oh, everything as far as the interface is concerned.  CM is no longer a module in EAS but instead is its very own subsystem in Workspace.  Here’s how you get to it:

Code organization

Once in, the interface is certainly different looking, but if you think about it, not all that different from the way HBRs look in EAS if you use projects to manage them:

One thing that I like about CM is that I no longer need to come up with rule names like AppName.PlanTypeName.ScriptName to keep them sorted correctly in HBR’s Repository View.  I use Projects in HBR to make my life a little easier – CM just does it out of the box through its hierarchical nature.  Score one for why I love CM.

I will also note that there is no more malarky around having to set locations to be able to validate the rule I just wrote (that one still trips me up) as in HBR.  Another one for CM.

Variables

Variable creation is certainly different as well, and there are more places to create them.  

Just to review, in HBR, variable creation is either Global or within the rule itself (Local):

In CM, variables are created by going to CM’s Tools->Variables and then by expanding the technology/application:

Interestingly, there are additional levels of variables:  Global, Application, Plan Type, and Rule.

For us HBR types, Rule = Local Variable and Global = Global Variable.  So what about CM’s Application and Plan Type?  What about Global?  Does it work the same way?  This is where it gets a little interesting if completely undocumented.

Through trial and error (Am I smart enough to learn any other way?  Nope.), I have figured out the following:

1. Global really is global to the entire server.  This is just like HBR Global variables.  So far, so good.  
2. Application level variables don’t really have an analogue in HBR.  They are global to the application.
3. Plan Type level variables also don’t have a corresponding type in HBR.  They are global to the Plan Type.

Just as with HBR’s Global Variables, I can see a use case for creating variables for all common dimensions for use in Run Time Prompts (RTPs) and sticking them at whatever level makes sense.  Most of the time, I think that would be at the Application level.

But there’s a catch – you can only create Global or Application variables for common dimension types.  In other words:  

Btw, the label to that dropdown (actually dropup) is Dimension Type, not Dimension:

Fooling Calculation Manager
So what happens when you want that Global or Application variable for a Custom dimension?  You can’t.  You can’t even create one at the Rule or Plan Type level and copy and paste it – if the dimension type isn’t one of the required dimensions, it doesn’t work:

NB – If the dimension type is one the seven required dimensions, the copy and paste does work.

Definitely not a plus for CM.

However, the great False God Google came to my rescue once again with this wonderful
blog post.  What you do is:

1. Create a Global or Application variable with an allowed dimension type.  
2. Insert it into the CM rule
3. Export the rule
4. Edit the rule in a text editor and replace the dimension name with the custom dimension.
5. Import the rule back into CM.
6. Ta-da, your custom dimension variable is now at the Global or Application level.

Let’s take a lookie-loo at the XML of an exported rule with global, variable, and :

The addition of the application node makes the variable a…wait for it…application variable.  The notes for application and plantype equals a Plan Type variable.  It would be nice if CM just handled this out of the box.

Just in case you think I am completely nuts (oh, many do, and they’re likely right), check out this from Oracle Support:  When creating Calculation Manager Global Variables, not all Hyperion Planning Dimensions are available [ID 1272807.1].  

I agree with their comments about Global variables – if a dimension is only in one Planning application or Plan Type, it really isn’t Global, is it?  

However, to preserve what little sanity I have left, I do like the idea of creating an Application level variable and will use Celvin’s approach going forward.  We all owe a big thanks to him.

Default values in Run Time Prompts

Another oddity in CM that doesn’t manifest itself in HBR is validating a rule with variables.  In HBR, when you create a Member RTP variable, be it Local or Global, you have the option of not providing a default value.  This is, to my mind, a Good Thing as there could be a failure to feed the form POV values to the RTP.

By default, it’s blank in HBR:

And in CM. So far, so good.

In HBR, when you validate a rule that uses variables that do not have a default value, you are prompted to select values.  You’ll only get prompted once if you don’t select this tick box:

I tend to select that as I want to be DoublePlusSure that the form drives the value.  

Here’s what HBR pops up on rule validation:

In CM, when the same approach is tried:

What’s going on?  Our friends in Oracle Support (they don’t invite me over to their respective houses for a kaffeklatsch, but boy oh boy do they answer a lot of questions) have this article:  Failed to validate against Essbase. One or more unresolved RTPs found" [ID 1235676.1]. 

Here's the relevant passage:

As Calculation Manager does not provide an interactive interface to enter RTP values during validation, the validation relies on the RTP's default value. If at least one RTP does not have a default value set, the validation fails with above error.

To recap:  you can create a RTP variable, you can insert it into a rule, but then that rule will never ever validate.  Or, you can put in default values that don't match what a given user might select, but then you can validate.  Hmmm.  I guess I will put in default values.

Another minus for CM.  

Oh well, rant over, I will henceforth put in default values for RTPs:

Success!  Boil in bag!

You must deploy

One big difference between HBR and CM is related to the issue above.  You must deploy the CM rule to the Planning application to run the rule.  There is no run-from-Calc-Manager option.  All rules get run from Planning itself.

The rule can be deployed from within CM by clicking on the Validate and Deploy button within the rule.

Or by selecting Deploy or Deploy All from within the System:

Or by clicking on the Planning application and selecting Deploy.

Deployment viewing
What needs to be deployed?  CM can give you that information by going to the View menu when in the System View tab and selecting Deployment View. 

You can even deploy individual or all rules from this report:

You can also go to the View menu and select List View:

Which will let you filter what comes back from CM:

Which results in this report.  Pretty slick and several +1’s to make viewing this easier.  Remember, with a handful of rules, navigation is a doddle.  When many start to appear, understanding what is where and why gets quite a bit more complicated.

Deleting rules

The Deploy moves the CM rule from CM to your Planning application.  A very important thing to note is that deleting a rule from CM does NOT delete it from Planning.  What that means is that you must select Deploy All or Deploy at the Planning application level to remove rules from Planning.  This is totally different than HBR where, when you delete a rule, you’ve deleted it.  I’m not going to give this one a plus or a minus, just a note that it doesn’t behave the same way.

Fixed in Calc Manager

How many times in HBR have you seen this when trying to create a HBR in EAS:

The “fix” for this is to go into the Planning application in question, open a form, return to EAS/HBR, close the rule, open the rule back up, and select the Planning Plan Type which should (hopefully) be available to you now:

CM completely does away with this requirement as the rules can only be created at the Planning application level.  Definitely a big +1 for CM.

Stop for now, but the balance next week

So there we have it:

• Big changes in the navigation of rules, for the better, really.
• The removal of some of the odd things traditional HBR required such as writing the rule but not being able to run it till the localtion and rights are assigned and that trick with opening a form before being able to select an outline.  
• A different way of navigating variables with of course a hack to make it work the way we want (that is sort of the definition of this blog, right?)

There’s another big interface change but I will cover that in the next and final part of this series (hey, this thing came to 37 pages in total – that’s too much for one post) and of course a semi-interesting twist on how to calc Planning forms that is different (and better) than what I did in HBR.