Row-driven Focused Aggregations in Planning, finally

It couldn’t be done, but has

And apparently by someone quite a bit more inventive than me just playing around.  Who would have thunk it?  Not me.  In fact, quite explicitly not me, as I have told multiple clients (I think two, maybe three) that this very thing cannot be done.  Sigh.  But thanks to this thread on Network54, it most definitely has been done.  All thanks must go to Christian M. (who oh who oh who is Christian M.?  Christian, if you’re coming to Kscope14 I want to buy a beer.  Several, in fact.) as he figured this out.  So this is most definitely not something I have invented – I am just expanding on Christian’s discovery.

Why this matters

Yes, I know, I haven’t exactly spilled the beans on what this is, but let’s set some background so you can understand why this is so important.  

The technique that I call Focused Aggregations is a way to read a Planning form and only aggregate the bits of the hierarchy that are germane to the form.  Calc Mgr/HBR can read POV and dropdown values from Run Time Prompt (RTP) variables and “faking” the aggregation process (really, it’s just aggregating as if a CALC ALL or AGG (dimnames) was issued but it only does the aggregation for the relevant hierarchies all the way to the top of the dimension).   This approach is fast because instead of aggregating an entire Entity or Product or Employee or whatever dimension, much of which is not going to be relevant to the data entered on the form, only the hierarchies that matter get aggregated to the dimension top.  

This approach is fast, fast, fast (it isn’t magic, but only aggregating the bits you need versus an entire dimension can make “too big” BSO Planning applications perform acceptably) and I have written about it here in Hyperion Business Rules, Calculation Manager, and Dodeca.  The Dodeca post is (or was) an important one within the field of Focused Aggregations because focused row-based aggregations are possible as Dodeca can provide the row contents.  That’s what a Planning form cannot do.  Or at least it couldn’t, until now.  Hopefully you now understand why I am so excited about this.

Why Calculate Form isn’t good enough

Some (like Celvin Kattookaran, my coopetition on this subject) have suggested that maybe the in-built <Form Calculate> might do the same thing and then why oh why oh why would anyone bother with focused aggregations?  Here’s why this approach doesn’t make the grade and Celvin mostly hit on the reason why in his post.

A bit of review

Just in case you’ve forgotten what a <Form Calculate> Business Rule looks like, here it is in 11.1.2.3’s form designer.  Note that this is not a rule that you can look at (maybe it’s buried in the EAR file that makes up Hyperion Planning but it is not something you can casually examine).

Note that I’ve moved the <Calculate Form> rule over to the “Selected Business Rules” listbox and ticked the “Run on Save” box.

Going into Smart View, I can open up a form (note the dynamic user variable selection of E032 – that will become important a little later on), and enter in values of 100 and 200 into the January column.

When I click on Submit, I see the following in the form:

Very nice, right?  There’s 300 in E032.  Is this enough?  Nope.  

The problem with Form Calculate

The issue with Form Calculate is that it only aggregates the descendants of the topmost member in the form.  As E032 was the form’s top of the Entity dimension, all of the intermediate members were aggregated.  That is a Good Thing.  But what Form Calculate does not do is aggregate the ancestors of the topmost member in the form.  Note that E03 does not equal 400 (and it should).  And TotalGeography does not equal 1,173 (ditto).

Here’s what the totals ought to look like:

So unless you live in some weird Planning world where looking at top level numbers isn’t important, I’d say that Form Calculate is nice, but not enough.  Remember, for anyone looking at data above E032, it’s as if the Form Calculate didn’t happen.

The traditional way of aggregating the form dimension

It’s really simple:  

But remember, Entity could be a really big dimension with lots of lower level blocks and thus  pretty slow performance.  Are you sure you want to aggregate that entire dimension?

Enter Christian M.’s most awesome hack

What would be nice would be a way to read the dimension, or even more to the point the topmost form member and then aggregate the descendants (like Form Calculate) and the ancestors (which Form Calculate cannot do).  Until now, this is what could not be done.  

Christian’s hack still can’t read the Planning form’s row definition, but what it can do is read a Planning User Variable.  And if a form’s row definition is driven off of a Planning User Variable, then you have squared the circle because that Planning User Variable can (somehow) be read into Calculation Manager.  Huzzah!  Hurrah!  Hot diggity-dog, etc.

Here’s the technique in summary:

1. Create a Planning User Variable
2. Create a Planning form that uses the User Variable to drive the row contents
3. Create a Calculation Manager Variable that is explicitly assigned the Planning User Variable
1. The Calc Mgr Variable type must be Members, not Member, and no, I don’t know why, and yes, Members versus Member doesn’t make sense but why are we complaining about a great hack?
4. Use the Calc Mgr Variable in the standard Focused Aggregation code approach
5. Deploy the rule and assign it to run on save in the form.
6. Hope that Oracle doesn’t “fix” this hack.

Let’s now go through this in detail.

Create a Planning User Variable

Simply go to the Administration->Manage->Variables and create a new variable.  I like to sort of mnemonically name them, so my Entity dimension variable name will be varUVEntity.

Create a Planning form that uses a User Variable

I simply modified a form in the Planning sample application and in the row definition selected the descendants of the user variable:

The My Region variable comes with the sample application.  I don’t like spaces in variable names and I don’t like variables that aren’t immediately identifiable as such, hence the name “varUVEntity”.

I wanted the user to be able to change the row contents, so I made sure that the user variable was selected in the Other Options form design tab and I selected “Enable dynamic user variables”.  That last tick box means that the user can change the form rows as required so long as he stays within his Entity dimension metadata security.

So far all of this is bog-standard Planning.  We’re about to enter the undocumented hack area.

Create a Calculation Manager Variable that reflects the Planning User Variable

This is the bit where I really have to take my hat off to Christian.  I am pretty sure I would have come up with this approach in, oh, never.

Go into Calculation Managers Variable Designer and create a variable of type Members.  Assign (you are going to have to remember the name of your variable as it isn’t going to pop up in Calc Mgr) the name of the Planning User Variable to the Calc Mgr Members variable’s Default Value.  Be sure to prefix it with a “&” symbol.  And yes, that is sort of like an Essbase Substitution Variable, but isn’t.

You will also need to select a Dimension Name (in this case, Entity), make sure it’s a RTP type, and even enter in a RTP Text message.  Remember, it’s the Default Value with an ampersand before the Planning User Variable that does the passing of the Planning User Variable’s value to Calc Mgr.

NB – It seems logical to me to use a Calc Mgr variable type of “Member” instead of “Members” and in fact it works, but when using that variable type a web browser will force a dialog box.  Oddly, Smart View does not do this.  

Don’t try to find reason where I suspect none exists – none of this makes any sense.  I’ve done the pig headedness bit for everyone, so just go with Members.

Write a Focused Aggregation Business Rule in Calculation Manager

Calc Mgr script

Please disregard the lack of variables in the FIX statement – you can add that in later.  The important bits are the @IDESCENDANTS and @ANCESTORS statements.  

In the first sub FIX statement, the code fixes on a Calc Mgr variable called varSegments.  This is just a standard RTP Calc Mgr variable.

What’s inside the FIX({varSegments}) is what’s important and heretofore impossible.  The code does an @IDESCENDANTS of the Planning user variable varUVEntity as passed to the Calc Mgr variable varBRUVEntity to mimic the Form Calculate functionality.  The next line issues an @ANCESTOR calculation of that Planning User Variable via a Calc Mgr variable to aggregate up to the top of the dimension.  Ta da!  We have just hit the super cool hack.

The second FIX statement uses that same Planning User Variable as expressed in a Calc Mgr Variable but now selects the relevant bits of the Entity hierarchy and then does an @ANCESTORS aggregation of the Page dimension Segments.

Double Ta-Da!  We have just squared the row-based Focused Aggregation circle.

Calc Mgr rule

Stick the script into a rule by dragging and dropping it into the rule itself.  

NB – If you do not have Flash 10.x installed on your machine (in my case, a Windows 2008 R2 VM) you won’t be able to do this.


When you do this, be sure to go to the Variables tab and tick the “Is Hidden” boxes for the variables.  This will ensure that neither RTP pops up when the rule is run on form save.

If you click on the Script tab you will see something kind of interesting:

Instead of varBRUVEntity the default value, &varUVEntity, which does look awfully Essbase Substitution Variable-ish, shows up.  

If in fact you were to go to the Errors & Warnings tab and have Calc Mgr analyze the script, you would see this bogus warning:

Interesting, isn’t it?  But it still works.

Deploy the rule

But don’t validate

As Celvin pointed out in his blog post, don’t bother trying to validate the rule.  That error message in the Errors & Warnings tab will surface:

So just deploy.  And fix the code yourself.  C’mon, if you’re writing code like this you can do syntax checking in your head, right?

Deploying is easy

Hook it up to the form

We are back to bog standard Planning.  Simply attach the deployed Calc Mgr rule to the form:

As always, make sure it runs on save, uses the members on the form to drive the POV/Page dimensions, and hide all RTP prompts.  That’s it.  Let’s now go prove that it works.

Hmmm, I love pudding

The proof of the pudding is in the eating.  

Of course we cannot eat a Planning form, but we can:

1. Change a dynamic user variable
2. Enter in data values
3. Save the form
4. Prove that it aggregates all the way up to the tippy-top of the dimensions

Let’s do just that.

Change a dynamic user variable

Select E01_101 as an Entity member.

Clicking on OK and then doing a form refresh will change the form selection:

Enter values and Submit Data



Review the fully aggregated data


And here’s the data aggregated all the way up to the top of the hierarchy in an Essbase ad-hoc retrieval:

Magic.  Thanks again, Christian.

So why is this faster?

It’s all in the amount of data that Essbase has to slog through in its aggregation.

I turned SET MSG DETAIL on in the script (and got rid of the CALC DIM of Accounts and Period as those should be fully dynamic – ask Oracle why the sample app isn’t set up that way, not me):

[Tue Oct 15 12:10:53 2013]Local/SampApp1/Consol/hypadmin@Native Directory/1792/Info(1012672)

Calculator Information Message:

Total Block Created: [0.0000e+000] Blocks

Sparse Calculations: [2.2500e+002] Writes and [8.5500e+002] Reads

Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads

Sparse Calculations: [1.0303e+006] Cells

Dense Calculations: [0.0000e+000] Cells

[Tue Oct 15 12:10:53 2013]Local/SampApp1/Consol/hypadmin@Native Directory/1792/Info(1012550)

Total Calc Elapsed Time : [0.059] seconds

I then created a calc script that used an AGG for Entity and Segments:

And ran it with the following results:

[Tue Oct 15 12:11:38 2013]Local/SampApp1/Consol/hypadmin@Native Directory/360/Info(1012672)

Calculator Information Message:

Total Block Created: [0.0000e+000] Blocks

Sparse Calculations: [2.8690e+003] Writes and [1.0772e+004] Reads

Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads

Sparse Calculations: [1.3137e+007] Cells

Dense Calculations: [0.0000e+000] Cells

[Tue Oct 15 12:11:38 2013]Local/SampApp1/Consol/hypadmin@Native Directory/360/Info(1012579)

Total Calc Elapsed Time for [BRAggTst.csc] : [0.434] seconds

Some analysis

Agg	Focused	Variance	Variance Percent
0.434 seconds	0.059 seconds	-0.375	-86.406%

Nothing to sneeze at here – that’s a decrease of 86% in the amount of time the calculation took to run.  Worthwhile when applied to your real world application?  I think so.

Let’s look at the number of transactions.

Approach	Sprase writes and reads	Sparse cells addressed
AGG of Entity and Segments	2,869 writes, 10,722 reads	1,313,700
Planning User Variable Focused Aggregation	225 writes, 855 reads	103,030

There’s an enormous difference in the number of writes, reads, and overall sparse cells.  No wonder a Focused Aggregation is so much faster.

Where do we go from here?

Well, as far as this blog post is concerned, hopefully nowhere.  This is quite the post (19 pages in MS Word which is where I draft all of my blogs – that’s quite enough of the Cameron Effect for one day) but I think it was important to showcase how absolutely brilliant Christian’s hack is, and how important it can be for the Focused Aggregation approach which can now finally apply that technique to rows.

I am going to reach out to the Calculation Manager Product Manager (Sadly, I really only have the emails to a couple of people within Oracle – I lay that at the feet of being an independent with just about zero impact on sales.  Or maybe I am just lazy and don’t work the contacts the way others do.) and beg, beg, beg that this functionality not be removed.  Ever.  In fact, it would be super if this functionality was formally incorporated into the tool so we didn’t have to play games with default values and ampersands.  I am going to guess most of the functionality is already there within Calc Mgr so I am hoping we’ll see it soon, officially supported.

One last time – Christian, thanks man.  You have no idea how happy this has made me.

Be seeing you.