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.

Stage 4 tour of beijing

Today was another GREAT stage. Infact I think the race should be renamed the tour of the great wall as today we actually rode right through it! We have ridden past it and beside it so now having gone through it there is only one more step for the organisers to take! Ride along it!!

Stage 4 marked the first time the race would include a mountain top finish. Therefore our plan was pretty simple, get mr captain cannondale Ivan Basso in the best chance to do a good result. The other objective of the day was to ensure damiano held onto his king of the mountains jersey. At the meeting this morning the objectives were simple. Ensure Ivan starts the final climb at the front and make sure that no break away goes including any challengers to damiano's jersey. I love it when the objective are so simply spelled out for us as after that you simply have to do your job, well simply so long as your legs are happy to do what's required!

From the gun it was full blast as usual. All the team controlled all the moves taking turns in chasing breaks or simply following them. Its so much easier to do this task with a strong team as if you have to chase break after break alone you put yourself in big danger of blowing yourself to pieces in the early part of the stage. Finally a break established itself with nobody threatening damanio's jersey and we settled in for the next move. Initially the break included the eventual stage winner from moviestar but he decided to sit up and return to safety of the peleton, he was obviously confident in his chances for the stage and rightly so! That left only 3 up the road meaning there were still points on offer for the peleton. We had to be carefull of this as wez sulzberger, fellow tasmanin and good mate was only 2 points behind so he now had a great chance to challenge for the jersey. Fearing this Ratto concocted an excellent plan!

The idea was that at around 1km from the top I would attack as hard as possible which would force wez to chase. Damiano would be glued to his wheel and getting a free ride and would then have fresher legs to take the sprint. Or the other possibility would be I would stay clear and sweep up the points, in reality I knew that would not be happening, wez really wanted the jersey! The next glitch to the came with the news Micheal Bling Mathews had withdrawn from the race. Why was this significant? Well all wez's team mates would usually be saving themselves to help bling so now he would have his entire team to help him chase the jersey. None the less I was ready and set up camp right at the front. Fortunately we were not going very hard on the climb so I was happy to take the wind for 5km to ensure I was in the perfect position to execute our plan. So all was set and with around 3km from the top the green edge train rolled along side me! I looked back and damiano was glued to wez's wheel but I also realised our task was going to be a little tuffer.

I thought pretty quick on my feet and realised the green edge guys had a plan aswell. They were nodding and whispering among themselves and they were also trying to block my escape route out the front of the bunch. We were now 2km from the top and I knew we had to anticipate anything they had install and I had to launch first. I waited for a moment when there were distracted and moved aside just enough for me to squeeze through and attacked as hard as I could. I knew I had to go hard as we needed wez to use as many of his men as possible to chase me down. I did not want to leave anything to chance. After 500m full blast I took a look back now saw only 2 greenedge and behind them damiano and ratto. Perfect the odds were now in our favor. The peleton off course let us go knowing we were just chasing the points and therefore the battle was between us. I kept the pace high as possible until 500 from the top when greenedge's mitch docker give it a dig to lead out wez. When he was done wez was alone and damanio and ratto were right on his wheel. Not surprizingly the 2 of them sprinted away from wez and swept up all the points and with it sealed the mountain jersey. So that was the days first objective ticked off.

Next was to keep an eye on Ivan and ensure he took the climb at the front. This was a little more straight forward as its as you just need to keep pushing forward. To keep pushing forward you simply need to take some wind as most are saving themselves for the final climb. That's were having dedicated team mates make a big difference. A leader can ride at the front but does not have to take the wind, its a big advantage. On a day like today Ratto and Elia are absolute masters at this as a result of there excellent bunch skills in a sprint and ability to do sharp acceleration's to stay at the front. Its often the team sprinters that perform this role for the climbers as they are simply happy to arrive atop the climb and not concerned with there result. To be honest I prefer taking the wind before the climb to stay at the front as I know I will start it at the front. Then once the race heats up your already amongst the action or even immediately involved in it, this inturn gets the adrenaline flowing and everything seems a little more painless and enjoyable so for some riders if not the majority, starting at the front is a good start to giving yourself the best chance to do well on a mountain top finish. Just a little on the workings of the team and how roles change day to day.

So between the 3 of us Ivan was always where he needed to be and as we hit the final climb we had he was right on the front row. The climb was always going to be fast so there was a big advantage to be had by staying up front out of the wind and avoiding the rubber band acceleration's further down in the bunch. I was struggling a little bit by this stage, just not quite yet back to where I like to be in the climbs but certainly on the improve again. Think the vuelta and recovery from my busted rib took a little more out of me than I expected, just missing that little edge at the moment. Fortunately ratto stayed with ivan as long as possible and ensured he was safe and sound in the first positions. By days end ivan would be 9th on the stage and 9th on GC so a great showing on a climb that doesn't really suit him.    

All in all a good day for the cannondale boys. Us workers had a couple of simple objectives and our leaders finished things off as expected for us. Tomorrow will be another stage for the sprinters so we will be back at the services of Elia. He has been getting closer and closer in the sprints all week so we are pretty motivated to set him up as best as possible for the final stage here at the tour of beijing.

Race Data

151km

Time: 3hrs 46min

Ave Power: 277

Max Power: 1150

Altitude Gained: 2350m

Kcal Burnt: 3900

CJW

Sent from my BlackBerry® wireless device

Stage 3 tour of beijing

Todays stage of tour of beijing gave the field a bit of a shake up. After 2 relatively flat stages for the sprinter it was the turn of mountain men to stretch there legs. The 175km was littered around 6 climbs and while none were excessively difficult on paper there was certainly the potential for some fireworks. The race got of to its usual fast start and a breakaway formed pretty early on. Our very own Damiano Caruso was inside it and by days end had used his natural quick turn of speed and climbing skills to pull on the king of the mountains jersey. So with a rider up the road we settled into a day in the bunch and waited for the fireworks to begin.

The course was again spectacular. We did another fly by of the great wall, no matter how many times you see it your always in awe. The pace was very consistent from the get go which always adds a little tension to the peleton. Fortunately we again enjoyed excellent roads with limited obstacles which has made the race feel much safer despite the stressful moments. The organisers really have done an excellent job putting this race together. The roads are also very fast which makes holding position a little easier as its a little more difficult for people to move forward. All this makes life in the bunch much more relaxing.

So back in the bunch I took on my usual role of looking after Ivan. Blocking him from the wind and keeping him as well positioned as possible to ensure he was ready to rock and role for the finale. The only little annoyance I had today was a bit of the chinese belly! My room mate mattias has felt a bit of the past couple of days so inevitable I have picked up a bit of whatever he has been putting down so to speak! Luckily the race was a solid enough pace that pain in the legs outweighed pain in the tummy, just had a bit of a struggle chomping down my tucker. Hopefully it will pass as quick as it came. Fortunately it did not stop me from being able to do my tasks today.

So with ivan all safe and sound we hit the 2nd last climb around 50km from the finish. Sure enough AG2R decided to turn the screw and intantly riders were going everywhere! I made sure Ivan was in front of me and then just sat safely behind him. Around 1km from the top of the climb the call came over the radio that elia was just off the back and we were to wait for him.

Ratto was perfectly positioned in the front so could baby sit Ivan and I just drifted down the decent waiting for Elia. He soon arrived with micheal koch and we set about chasing the down the front group. Elia need not have worried about returning to the front of the race as koch was like a motorbike. We were never more than 10-15sec off the back so had nothing to fear but I was certainly very relieved koch was there as it made chasing back on much easier in 2 as oppose to alone. Also dalla chimed in and with his team time trial prowess we soon had elia back at the head of the race. From there we just stayed behind elia to ensure he remained in the front on the final short climb which he had no drama doing and then buckled the seat belt for the plunge down to the finish. The decent was a little twisty and turny as sure enough so gaps opened and a few of us ended up on the wrong side of it. Fortunately Elia and Ivan were both safely in the front so we had done our jobs for the day.

Elia had a good sprint today and finished 4th so will be optimistic to finish of with a bang on the final stage on tuesday. Along with Caruso taking the king of the mountains jersey it was a good day for the cannondale team. Tomorrow the race will finish atop a 12km 5% climb so is certain to shake things up even more. Ivan is growing in confidence each day so he will be our focus tomorrow. I am enjoying spending time apart of the action again. Its nice to have my confidence back on the bike after my crash in the vuelta. My body was not prepared to take risks while my broken bone healed so its nice to know that that's all behind me now and I the brakes are off so to speak. You get to know your body pretty well and the feeling of not holding back particularly in the nervous moments is the best indication that everything is back to normal which inturn has given me much more confidence to push again in the race.

To make a difference tomorrow its going to need to be a very hard stage. The climbs are not steep and should wind be blowing it will make it more difficult for the climbers to drop the sprinters. All in all that shapes up for a very exciting stage as teams will be throwing the kitchen sink at the race in a bid to split it up. Looking forward to getting a bit better night sleep tonight! Last night my tummy was singing to me!! And getting up in the morning and hearing the battle plan our team manager mario screa has install for us.

Race data

Time: 4hrs 9min

Speed Ave: 42kmph

Elevation gain: 2200m

Ave Power: 262watts

Max Power: 1050watts

Kcal Burnt: 4000

CJW

Sent from my BlackBerry® wireless device

Ferry Boats Aren't the Answer

During the recent Metro-North meltdown, at least one coastal community (Darien) thought about using ferry boats to get commuters to NYC.  Interesting idea in a crisis, but let me debunk the popular myth that the solution to our transportation woes can be found on Long Island Sound. 

Ferry boats face several challenges:

SPEED:        In open water, fast ferries on the Sound could make 30 knots (35 mph).  But if they must sail up inlets to the downtown areas of Bridgeport, Norwalk or Stamford, that speed is cut to 5 knots, adding to travel time.

DOCKING:    To keep to their competitive speeds, docks would have to be located close to the Sound.  That’s expensive real estate.  And what about parking at those docks… and travel time on local roads to reach them?  Again, more travel time.

FREQUENCY:         Metro-North offers trains to midtown New York every 20 minutes in rush hour carrying 800 – 1000 passengers per train.  No ferry service anywhere in the country can compete with that frequency of service.  Will travelers really be willing to wait an hour or two for the next boat?

COMFORT:             In nice weather, a boat ride to work sounds idyllic.  But what about in a Nor’easter?  The bumpiest ride on the train pales by comparison.

FARES:        The most optimistic of would-be ferry operators estimate their fares will be at least double those charged on the train.  And people say Metro-North is too expensive?

OPERATING COSTS:       One of the reasons fares would be so high is that fast ferries are gas guzzlers, the aquatic equivalent to the Concorde.  When the Pequot Indians built high speed catamarans to ferry gamblers to their casino in Connecticut to lose money, the service proved so expensive that the Pequot’s dry-docked the ferries in New London.

COMPETITION:      When private operators ran ferry service from Glen Cove Long Island and from Yonkers to midtown NY, paralleling routes well served by the LIRR and Metro-North, they shut down after just a few months because they couldn’t compete with the trains.  Coastal Connecticut already has (usually) fast, efficient rail service, so why duplicate what already works?

ECONOMICS:         The final reason I don’t think ferries make economic sense is that nobody else does.  Ferry operators (like the near-bankrupt NY Waterways) aren’t stupid.  They’ve looked at possible service from coastal Connecticut, crunched the numbers and backed off.  In a free market economy, if a buck could be made running ferries, they’d be operating by now.  They aren’t operating, and there are lots of reasons why, many of which I’ve listed.

The only place ferries are running successfully is where they’re heavily subsidized (everywhere), have a monopoly (for example, getting to downtown Seattle from an island suburb), don’t duplicate existing transportation routes (like from Bridgeport to Port Jefferson), or offer advantages of speed because they operate on extremely short runs (from Hoboken to midtown).  Our situation here in Connecticut matches none of those tests.

You already know I’m a train nut. (The bumper sticker on my car reads “I’d Rather Be on the Train.”).  And I do love an occasional recreational sail on the Sound.  But I just think it’s unrealistic to think that commutation by ferries is in our future.