Why I hate (and love) Business Rules, part 2

The Two Minutes' Hate is over – the below is a paean of love to HBR.

Prequel to the cool stuff
So in part 1, I reviewed how to create a HBR, substitute in local variables to read a Planning form’s Run Time Prompts (RTP), and suggested that it might be worthwhile to read this post.

The code sample I used doesn’t do any fancy allocation or calculation. It just aggregates the database using form POV members.

You may be scratching your head, thinking, “Why on earth is he banging on and on about this? Just issue an AGG(“Entity”, “Segments”) statement and be done with it.”

Zoiks! If I wrote one line of code to consolidate a database I would be out of a blog post topic – This Is A Bad Thing.

Secondly, Why Would I Want To Do That? (Ex-Comshare employees/customers/partners know of whom I speak when I write that.)

Because thirdly, and most importantly to you, I can make that database aggregate much, much more quickly.

The Trouble with AGG
Other than sounding like a misquote from a sketch in “Monty Python and the Holy Grail”, what is wrong with an “AGG” when applied to the Entity and Segments dimensions?

Think about it in the context of the form as shown below:

If I change any of the data values on this form, I am only changing them for DVD Recorders in Pennsylvania. New Hampshire isn’t in the POV. Neither are eight-track cassettes.

But if I wrote:
AGG(“Entity”, “Segments”) ;
I would be also be aggregating a New England state and a totally obsolete electronic media, if there was any Gross Profit data for those two members. After all, AGG is kind of a blunt axe.

(For you Intelligent Calc lovers out there, yes, I could use the blocks’ clean or dirty status to not calculate upper level data that already exists, but good luck doing that in a real world Planning application. There will be clean/dirty blocks all over the place and it is very difficult to keep this approach from going pear shaped.)

Having disposed of the Intelligent Calc option, while an AGG statement will certainly come up with the right result, Why Would You Want To Do That when you only changed data at the PA and DVD Recorders intersection? Why aggregate data combinations for all of the other level zero Entity and Segment combinations that haven’t even changed?

(These questions do have a point, so bear with me.) You might answer, what else am I to do? How do I consolidate my dimensions if I don’t use AGG or CALC DIM?

Tricky, innit?
Here’s the trick – you don’t need to calculate the dimension, you only need to calculate the relevant hierarchies. That’s what the calc script above shows and what your HBR can do for your forms.

@IANCESTOR is your friend
@IANCESTOR is your BFF? That I couldn’t say. But it’s going to be your HBR pal from now on because, if you think like Essbase, you can make Essbase only calculate what you want. *This* is hacking Essbase.

Let’s review the way Essbase aggregates a database, per our dear friend, the Database Administrators Guide (DBAG). For the below section we’re going to ignore the Consol database to go along with the DBAG. Just substitute Entity for Product and Segments for Market; they are the first and second consolidating sparse dimensions.

How Essbase aggregates a database
Per the ever-scintillating DBAG, Block Storage Option (BSO) databases calculate dimensions in the following order (we are only going to concern ourselves with sparse, aggregating dimensions), “Sparse dimensions (in the order they display in the database outline)”. See http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_dbag/dcacaord.htm and the “Member Calculation Order” section if you need to cite chapter and verse.

Okay, we know the order that Essbase is going to calculate the dimensions. What about the order of the members within the dimensions? This is answered by the “Block Calculation Order” section :
“Essbase calculates blocks in the order in which the blocks are numbered. Essbase takes the first sparse dimension in a database outline as a starting point. It defines the sparse member combinations from this first dimension.”

Using My Very Favorite Essbase Database In The Whole Wide World (MVFEDITWWW), i.e., Sample.Basic, this means that:
“In the Sample Basic database, Product is the first sparse dimension…Product has 19 members…Therefore, the first 19 data blocks in the database are numbered according to the calculation order of members in the Product dimension.”

The DBAG goes on to say:
“The other sparse dimension is Market. The first 19 data blocks contain the first member to be calculated in the Market dimension, which is New York…The next member in the Market dimension is Massachusetts. Essbase creates the next 19 data blocks for sparse combinations of each Product member and Massachusetts.”

This is the important bit:
Essbase continues until blocks have been created for all combinations of sparse dimension members for which at least one data value exists.

Guess what, we have just reviewed how Essbase calculates sparse dimensions from the first sparse dimension to the last one, by dimension and within each dimension. In essence, block by block.

In plain English:
1) Product is aggregated for every level 0 Market member (where data exists in Market – Essbase is smart enough not to calculate combinations that don’t exist).
2) Then Market gets aggregated by every Product (that exists, there’s Essbase being smart again).

Okay, but so what?
If we jump back to the Consol database, you may recall that I claimed you don’t need to calculate all of Entity, do you? Nope. Just the member in the form POV and its ancestors.

You also don’t need to calculate all of Segments. No, sir. Just the Segment that is in the POV and its ancestors.

This is the trick/gimmick/optimization/clever bit/thing you maybe already knew long ago and are now totally disappointed by. Sorry if you’re in the last category – I swear the next post will be better.

How do you do this?
Simple. Let’s assume that the Entity is PA and the Segment is DVD Recorder. Remember, you only need to calculate the relevant branches of the hierarchy. The other level zero/upper level members haven’t changed, so there’s no profit in recalculating them.

Use @IANCESTORS in combination with FIX statements to make Essbase aggregate:
1) The PA ancestor tree for DVD Recorders.
2) The DVD Recorder ancestor tree for the PA ancestor tree.

The results of the above versus that AGG/CALC DIM is exactly the same, only the focused aggregation does it in less than a quarter of the time.

Correction

My good Hyperion buddy Joe Aultman pointed out an error, or at least a redundancy with the above code.

Basically, the @IANCESTORS within the FIX statement isn't needed as the members themselves don't need to be aggregated, just their ancestors.

The code as posted will result in the right value, but will be that fraction of a second slower as it is addressing two more blocks. We don't want that, right?

I don't know why I wrote it in this blog with the @IANCESTOR within the FIX as that isn't how I do it at my clients. Overthinking it, I guess.

I have inserted a snippet with the correct, @IANCESTOR-in-the-FIX-only approach below. This code also shows HBR local variables for the form's run time prompts.

Back to our regular programming

That’s it. Skeptical, are you? Can’t be that easy? The proof of the pudding is in the eating. Let’s trace the data by only changing Operating Revenue for January through March.
1) Here’s the form with the original data:

2) Let’s round the numbers up and send to Essbase:

3) Switching to Excel, the blue cells show the data that should be updated. This sheet is easy peasy, as it just shows aggregated dynamic Accounts.

4) And here it is where we expect to see it aggregated one level up, by Entity and Segment.

5) And now let’s look at the when the focused aggregation HBR is run.

Notice that it doesn’t matter if we do or don't aggregate MA, NY, DVD Player, Portable DVD, and DVD/VCR combo. Only PA and DVD Recorders changed, so only their parents need be aggregated. In other words, only aggregate parents whose children's values change, and leave the rest be.
6) Here it is rolled up by Entity.

7) And rolled up by Segment.

The Payoff
What does this mean from a performance perspective (faster=better)?

Looking at the Plansamp.log file, we can see that plain old AGG took almost 7 seconds:

Where the focused aggregation didn’t even make it to 2.5 seconds.

In the Real World

Just yesterday I benchmarked a real Planning HBR with the AGG versus focused aggregation approach. How about 180 seconds versus 24 seconds? Now we’re talking 1/8 of the time. This is powerful medicine.

YMMV; these are the results I got for my client’s Essbase database. Every database is different, so my performance improvement won't necessarily map to your application. Regardless, the above technique can make “big” Planning applications fast and give you the run on save performance your users demand.

See, I love Hyperion Business Rules.

See you next time.

Why I hate (and love) Business Rules, part 1

Just a quick note -- this is a two part (hence the title) post as the content was just too long. Trust me, the next post is worth waiting around for.

The trouble with Business Rules
Hyperion Business Rules (HBR) really has a number of petty annoyances for those of us forced to use its editor:

1) It loses its connection with Planning/Essbase on a whim.

2) The editor converts all of those lovely tabs you used to delimit your FIX and IF statements in the calc script editor (any editor, actually) and turns them into spaces, which then makes everything hard to read. Ah, but it's a tease, and HBR doesn't do that when you paste it, but when you save the rule.

3) It uses strange colors to identify keywords, and yes, they’re different than the calc script editor. And they’re unreadable. Yes, you can modify them, but still…

4) In HBR’s Bizarro World, it makes sense that while you can create a rule, you can’t run it until you’ve granted yourself the ability to do so, although you couldn’t grant yourself the ability to do so unless you could create it in the first place. Or can you? HBR has all sorts of byzantine roles that hardly anyone bothers with (has there ever been a real HBR Interactive User?) – in the real world (usually), you either write ‘em or run ‘em.

5) Oh, I forgot to mention the above whine was about the decent HBR editor – this is not the well intentioned (?), yet utterly misguided graphical business rule editor. I’m complaining about the “Enhanced Calc Script editor”. The other one…well, it’s been financially rewarding fixing the code generated from that beast. :)


Learning to love Business Rules
Despite all of the above whinging, HBR has one saving grace – it can read a Planning form Point of View (POV) through hidden Run Time Prompts (RTPs). And that means the calc script/business rule you wrote can be focused on the members in the POV. Everyone who does Planning knows this, right?

Actually, I lie, that isn’t really the reason I love business rules, although there’s a kernel of Business Rule Love there.

Boring stuff that you (probably) already know
You can’t build a house without setting a foundation. The below steps are what just about everyone does and is the basis for the cool stuff. Read it to refresh your memory, but the hack occurs in the next post – this delay is either going to intrigue or annoy you. I am hoping for the former, not the latter, but Que Sera, Sera.

The below example aggregates the Planning Reference Application’s Plan Type/Essbase database “Consol”.

Don’t pay too much attention to the logic of the calc, just follow the technique. The logic will be explained below.

Do pay attention to the below form. The two dimensions we will concentrate on are Entity and Segments which you can see in the form dropdown controls.


The basic steps I follow:

1) Write, test, and validate the calc script in a real editor (TextPad – my favorite, UltraEdit, EAS calc script editor or whatever) with POV replacement in mind, i.e., the Planning application has 100 products, you know that they will be in a dropdown control in the form POV, with a single product in your FIX. You’ll be replacing that hardcoded member name with a variable later on, but you’re trying to get the logic right first.


2) Create a new business rule in EAS.

3) Select the Plan Type that this HBR references. NB – It will have to be a Planning data source as you are going to point this Business Rule against Planning sooner or later.

4) Before you do much of anything else, give yourself rights to Validate or Launch the rule. Otherwise you’ll just do all of the following steps and find out that you can’t run it. Which can be fixed at the end, but is still a pain.

DOH! Caught you out, didn’t it? Does it to me every time.

5) First you assign the location, then you assign Validate or Launch rights. Why does it have to be this order? I think understanding this is to dream the impossible dream, but I digress.
6) Make it an Enhanced Business Rule by clicking on the Source tab, and then typing a single letter (your choice, actually any character on the keyboard). Business Rules will ask if you want to take the daring step of abandoning your beloved graphical business rule. You do.
7) Paste your calc script into the HBR editor. I’ll explain in the next post (there’s that tease, again) why a plain old AGG(“Entity”, “Segments”) statement wasn’t used.
8) Create HBR variables to map your POV dimension members (Entity and Segments) if hardcoding (yes, sometimes that is perfectly acceptable) a member doesn’t make sense. Make them Run Time Prompts for a single member. Only varEntity is shown below -- it would be the same for varSegment.

9) Flip back into the HBR source code, select the member name in whatever FIX statement makes sense (usually a single member; although more than one member can be selected that isn’t going to work with the single member coming off of a dimension in the POV) and right click to insert the variable.

10) Save the HBR.
11) Validate it – you will be prompted for whatever dimensions have prompts. When you saved the variable/RTP, you specified that the sample value you enter is not the default, right? It is highly likely that what works for you as a default is nonsensical to almost everyone else in the Planning application. If the code doesn’t work, revise till it does (you knew this, too). I often run the HBR at this point to ensure I haven’t inadvertently introduced an error during the copy, paste, and HBR variable substitute process.

12) Edit the Planning form in question, go to the Business Rules tab, assign your freshly baked HBR over to the righthand pane, and then click on Properties. Select the “Run on Save,” “Use Members on Data Form,” and “Hide Prompt” checkboxes and then save the rule. This is the magic that moves RTPs across from the form POV to the business rule. Save that rule.

The above twelve steps (not substance abuse) are nothing special you say, every Planning developer does this. All you’ve seen is the simple substitution of hardcoded member names for Planning form POV member names. This is actually (for those of us who remember a time before the above was possible, pre Planning 4) pretty big stuff, although nothing new, for veteran System 9/11x developers.

Spot the Clever Bit?
Here’s a test for you – do you see the Essbase hack in the code above? All will be revealed in the next post.

Stringing you along
Hopefully I’ve piqued your interest; the second installment of this post will explain why I wrote the above HBR the way I did. As I wrote before, I decided to split this subject in two because the length was getting ridiculous.