Essbase and ODI – A Better Way

Introduction

If you want to know all things ODI and EPM, you are, unfortunately, reading the wrong blog (I like to think this one has some value, but I may be flattering myself). The right one is John Goodwin's More To Life Than This. Click on the title or see the links of blogs I Am Barely Worthy To Link To on the right. John's is usually first.

Once you've exhausted the aforementioned good stuff, and however reluctantly circled back here because there might be something of value here, you have probably also already made the first faltering steps into the awesomeness (and scariness) that is ODI. If so, you've also begun the ponder the lameness that is the Essbase Knowledge Modules.

Lameness, a Bit of Relief, and Lameness in the extreme,

The bad bits

1. The data load performance is slow, which is lame.
   
2. Additive data loads must use a data load rule (defeating much, no, make that all, of the purpose of using an ODI Essbase KM) and thus is lame.
   
3. Many metadata load options are missing, e.g., allow moves, allow property changes, etc. This is lame in the extreme.
   
4. When using a SQL source, the Essbase IKM creates a temporary text file. No wonder the performance is slow. Hey, guess what, this is lame, too.
   

(To be fair, this is about ODI 10.1.3.5 – who knows what Essbase goodness Oracle will bake into ODI. But that is the future, and this posting addresses the present of October, 2009.)

The good bits that make up for the above

Negative thoughts

It isn't HAL. J That product had "You really wish you didn't have to use me, don't you?" right there in the application title bar so you could always see it when you used it. It was sort of taunting you with its limitless mediocrity. Yes, I hated that product.

Positive thoughts

I swear when I look at ODI Designer's application title bar I see, "Art thou smart enough to master me, unworthy knave?" (Here's a question: When supernatural characters speak in movies, why do they always revert to courtly language from the Middle Ages? Why not ancient Greek? And when the subject is Greek mythology, why does everyone have Received Pronunciation accents? Especially since England didn't exist at the time? Oh, there was Boadicea, but she fought the Romans, and besides, she was a Celt. Perhaps I was scarred by drinking too deeply from the well of Clash Of The Titans. But I digress. Again.)

To say ODI != HAL isn't fair to ODI – it's actually a tremendously powerful, flexible, and complicated (we consultants love that last bit, as it equals $$$ and geek adulation) product. You can do all kinds of magic with it. Okay, that is sort of like saying ODI = HAL * -1.

The ultimate lameness

Also, and this is the deal breaker for many, if you are a Planning or HFM customer, and got ODI for "free" along with your Hyperion-branded product, I am here to tell you that you can't actually use ODI's Essbase KMs. Nope, sorry, can do but won't, better luck next time, & c. – your license doesn't cover Essbase.

Ah, I can see you, through the power of my mind, your reaction upon receipt of this news. Sputtering with indignation, face purple with rage, fists tightly clenched as you tremble with anger, blood rushing through your temples, heart beating fast with…eh, that's laying it on a bit thick. Let's just say you're annoyed and wonder why Oracle bothered to bundle the Essbase KMs when in fact many (most?) Hyperion EPM customers can't use Essbase.

Well, you can download all of Oracle's software for free from http://edelivery.oracle.com. But just because you can doesn't mean that you're legally able to use those products in production.

Dewey, Cheatem, and Howe

Obligatory groveling before the powerful and mighty:
Dear Oracle Legal – I love lawyers, I am related to lawyers, one of my childhood friends is a lawyer (and is married to another lawyer and they are as nice as can be – I think their kids are going to be lawyers and they're peaches), and I have, on multiple occasions, have been happy to use the services of lawyers, so please don't get angry with me. Laugh, it turns those frowns upside down.

A hypothetical

What happens if you use ODI against Essbase without a valid license (I am no fool, I wouldn't do it, so this is a geek's guess) and "someone" tells Oracle?

I am envisioning a phalanx of highly motivated, supremely trained, and completely ruthless lawyers with red Oracle logos on their business cards (or would that be whatever firm(s) they have on contingency – it doesn't really matter) sending multiple, and increasingly threatening nastygrams to your firm's legal department. Which is going to bring down the Hammer of Thor on you, courtesy of your manager, your manager's manager, etc., etc. all the way up to who knows where, robustly prodded on by your firm's general counsel. Ouch, that hurt your head, didn't it? Or was that the feeling when your derriere bounced off the sidewalk in front of your office, pink slip firmly grasped in hand?

True story

Once upon a time, in a different century, I was distant witness to a Hyperion customer who thought he'd get an Essbase development environment for "free". Big Mistake. No, I did not drop the dime on him (I didn't even know about it till the deed was done, as I was blissfully developing deep in "testduction"), but the resulting monetary fine (I never did find out how much) and concomitant misery was painful to watch, if well-deserved.

Is there a way out?

Guess what, you can use ODI, and it can talk to Essbase, and you aren't going to be in violation of your Planning/HFM-derived license, and there will be no lameness involved. Of course if this post disappears into the ether, guess what – this is a violation of your license, but I don't think so because we are deep-sixing the Essbase KMs, lock, stock, and barrel.

MaxL to the rescue

Legalities aside, this approach is way more flexible because it ignores, gives the cold shoulder, and generally 23-skidoos the Essbase KMs. Instead, it uses what may, in the Essbase universe at least, be truly the Most awesome excellent Language ever, MaxL.

No, in your feverish perusal of all things ODI, you did not miss the MaxL KM. There isn't one, and besides, it isn't necessary, because you can run it from ODI's command line object.

Variability is the spice of life

And when MaxL's command line parameters, as described in my last post, are combined with ODI's Variables, you can drive any Essbase command that you want from within MaxL – it's powerful medicine.

How does this work?

Take one fully parameterized MaxL script

To take advantage of this approach, you're going to need a fully parameterized MaxL script. There's one right below:


The following values have been made into variables:

Pay attention

My meandering through this stuff isn't boring you, is it? Did you catch the important, world shattering, and moderately clever variable usage above? Did you see how the $4 refers to a dimension-specific error file, a MaxL process-specific log file, a MaxL process-specific error file, and the name of the dimension load rule itself? Two little characters replace so much hardcoding it gives me goosebumps.

This dimension load rule can be used on any server, any application, any database, any dimension and any SQL-sourced load rule. One script, many uses. My ranting may be boring, but there are isolated nuggets of real value here.

Okay, but so what?

Don't MaxL parameter variables require a command line? And how is that going to happen with ODI, which isn't exactly a command line scripting language?

To the contrary, ODI does have a command-line object – it's one of the many objects you can use in a Package (a collection of ODI interfaces and objects used for automation streams). And running a MaxL script from the command-line object is just like running a MaxL script from a real command line, only better, far better.

A combination full of potential

ODI has this thing called Variables which are, wait for it, variables that can be set from within ODI. Are you seeing the potential yet?

Let me draw you a picture using words

The idea is:

1. Use the dimension and data load rules that you have known since the Year Dot and enjoy the performance and flexibility that the rules are rightly known for.
   
2. Hard code absolutely nothing in your MaxL code other than the most general of tasks, i.e, login, load data, run a calc script, etc. Flexibility? This approach is practically slopping over with flexibility.
   
3. Drive all of the MaxL parameter variables in your MaxL script with ODI variables.
   
4. Be happy with the knowledge that you're doing things a bit differently, and dare I write, even a bit cleverly, and integrating your Essbase processes side by side with your Planning and HFM processes. You are hacking ODI and Essbase. Aren't you the clever one?
   
5. Take items one through four and then wrap even more ODI goodness around your code. Maybe thou art worthy of the tool called "ODI".
   

What is an ODI variable?

In this example, ODI variables are set up within the context of a project. There are several types of variables, in these examples they are going to be alphanumeric historicized variables.

Assigning value

Variables can get a default value when they are created. You can assign values to Variables on a Package by Package basis. That's nice, but not tremendously helpful if you're trying to use variables in automation as that would result in needing duplicate copies of Packages with differently locally assigned values. Why?

Packages within Packages

However, when Variables are used in a Package, and that Package is compiled to a Scenario, it turns out that we can value that Scenario's Variables by encapsulating the Scenario within another Package. Sounds tricky, but isn't.

Steps

1. Declare a Variable in ODI by going into Designer, opening a project (or creating one if it never existed), right clicking on the Variables node, and inserting a Variable as shown:
   

   
   

2. Make sure it's an Alphanumeric, Historicized variable. I like to name my variables with a VAR prefix so I know what it is when I view objects in a list (those icons are small).
   

   
   

3. Create variables – I'm going to create an ODI variable for each and every one of my MaxL parameter variables.
   

   
   

4. Create a Package, and drag and drop the variables into the package, making sure to connect them (order not really important).
   

   
   

   When you drag them over, do not set the Type as the default value of "Set Variable". This will allow you to define the variable values within the Package, but you want more, oh so much more than that. And, you will spend 45 minutes at 11:30 pm about two steps later in this process trying to figure out why the process doesn't work when you know it ought to. Good times, good times.
   

   
   

Instead, be sure to define them as Declare Variable. This will make those Variables drivable through a Scenario-within-a-Package. Don't worry, it's less confusing than it seems.

1. If we were running the MaxL script from the command line, the order of the strings would look something like:
   
   1. Essmsh
      
   2. MaxL script name
      
   3. Essbase username
      
   4. Essbase password
      
   5. Server
      
   6. Essbase object name
      
   7. SQL username
      
   8. SQL password
      
   9. Essbase application
      
   10. Essbase database
       

   Or something like:
   c:\Hyperion\common\EssbaseRTC\9.5.0.0>essmsh c:\temp\dimbuild.msh essadmin essbase d630 Mkt sa Password Cameron Basic
   

This string is what needs to go into the OS Command object and then get ODI Variablized. I just invented that word, please pay me 15¢ whenever you use it in conversation. This will result in ODI Variables driving MaxL parameter Variables and my eventual independent wealth and early retirement. No, no, thank you. And yes, this round is on me. Same again, barkeep.

1. Examine the properties of the OS Command Object, which you have cleverly renamed MaxL Dim Build by clicking on the object and then clicking on the edit button that looks like a No. 2 pencil.
   

   
   

2. Back in reality, like I wrote, the goal is to take the above command line string and Variablize it. It will look something like this:
   

   
   

 

Things to note:

1. The command leads off with a "cmd /c" string. Basically this is a way to get the command shell to launch and then terminate. Go to a cmd window (oh, the irony) and type "cmd /?" to get a full list of parameters. Only the /c is needed for our purposes. If you don't put it in, the command won't work. It isn't obvious to me why this is so – I can use the Start->Run->essmsh combination in Windows and the MaxL shell is launched, but this is the way it works.
   
2. The path to MaxL shell (essmsh.exe) and the MaxL script (filename.msh) should be fully qualified.
   
3. ODI Variables are referenced by placing a hash/pound sign in front of the variable name, e.g., VAR_Essbase_Username is typed as #VAR_Essbase_Username.
   
4. When recognized, ODI turns the color of the Variable to blue. This is a good syntax check.
   
5. You can either manually type in the name, or expand the Project Variables, click on the variable you want, and it's in the command line string.
   

   
   

 

Compiling the Package

For some reason, you cannot encapsulate a Package with a Package. But you can encapsulate a Scenario within a Package, which in turn can be a Scenario so it can all be scheduled or run from the real command line.

There are several ways to do create a Scenario. The method I like is to close the Package, right click on it, and select "Generate Scenario".

When complete, you will see a shiny new Scenario all ready to go.

This is what you're going to use as a target for your calling Package. Think of it as a parameterized object with the parameters being the Variables and the Object being your MaxL code. If you were sufficiently daring, crazy, wild, sloppy, etc., you could actually modify your MaxL script so long as it retained the same name and location to do different things. I wouldn't do this as creating new Packages/Scenarios isn't really all that hard and it keeps things a little more understandable.

That other Package

Now create a new Package to control the Scenario you just created. I called mine PKG_LAUNCH_MAXL_BLOG_DEMO but that's just me.

Now drag your Scenario into the Package Diagram.

When you click on the Scenario, you will notice a Properties window. Click on the tab called Additional Variables.

Click on the Add button that looks like a grid and then click on the cell in the Project column. You will see whatever project you are working on. Do the same in the Variable column. Dropdown controls live! Enter whatever value you want for the Variable itself.

Once you have assigned all of the values you are good to go.

Click on OK to apply the changes, and run the Package from the Projects pane.

Click on the OK button and MaxL is GO!

How do you know it works?

Well, if you weren't going to get any fancier, you could just check the directory of wherever the logs and error files are supposed to be.

And if I look at the log file, yup, it works:

Of course, if I were an ambitious sort, I wouldn't stop with a log file that I manually examine.

15 Years of Code Down The Drain in Eight Hours or Less

ODI's awesomeness means that you can parameterize a MaxL script in a Package, Variablize (that is now 30¢ you owe me) it, compile it to a Scenario, encapsulate that Scenario in another Package, and drive emails off of log and error file existence all in the space of one working day. I was gobsmacked when I did it.

I have a library of code that I take from client to client and to get all of the above working and tested in a new environment would take me longer than a day. And I was learning as I went along. Yes, I am a legend in my own mind, but I'm trying to impress upon you that this was all pretty easy once I figured out the theory of how this would work. I won't be using that code again. You shouldn't either.

What are the takeaways?

1. ODI is awesome
   
2. The Essbase KMs leave something to be desired
   
3. MaxL and load rules, well, rule
   
4. ODI can do anything (almost anything?) you could do in script better, faster, and sexier.
   
5. Hacking two products at the same time is more fun than one. J
   

This is only scratching the surface of what ODI can do.

Escaping MaxL quotes

How do I quote thee, let me count the ways

As I explore the terra incognita that is ODI/Oracle EPM, I've come up with an overloading technique that makes ODI's Essbase dimension build and data load functionality redundant.

How, you ask, has yr. obdnt. svnt. managed to do this?

In a single word, MaxL.  Truly the Most awesome excellent Language ever.

I'll write about how not to use the Essbase Knowledge Module in ODI, and why you shouldn't,  in my next blog (this is generally known as a tease), but first a brief dive/ investigation/rant into how MaxL handles quotes natively and when used with parameter variables as they are heavily used in the aforementioned technique.

I am afraid in this case MaxL stands for Most awful excerable Language ever.  

A foolish consistency is the hobgoblin of little minds

Was Emerson thinking about the Oracle EPM space when he wrote this famous line?  Logic says “No”, but Essbase is so awesome it’s maybe just possible?  If true, we might deduce that quote handling in MaxL is either non-foolish or, more flatteringly, the province of great minds, because it surely isn't consistent. 

If you want to get all smart on me and read the documentation, the best place to look in the Tech Ref is in Quoting and Special Characters Rules for MaxL Shell.

I think I have eluded consistency pretty well

Let’s take a little trip through the land of inconsistency with a sample dimension build statement.

import database Cameron.Basic dimensions

    connect as 'sa' identified by "password"

    using server rules_file hMkt

    preserve all data on error write to

    "c:\\temp\\hDimBuild_Mkt.err" ;

Did you catch (you’re not paying the least bit of attention if you didn’t) all the different ways to handle (or not) quotes? 

Anyone who writes code like the above doesn't stay awake at night worrying about the quality of his work; I envy him.  :)

Hardcoding and quotes

So let’s try to get to some kind of standard way to handle quotes.

The statements:

spool stderr on to 'c:\temp\DimBuild_Mkt.err' ; 

and

spool stderr on to c:\temp\DimBuild_Mkt.err ;

both work.  Why?  What is the point of the quotes?  Why even bother with them?

Maybe there is a point

Well, if you foolishly put a space instead of an underscore and end up with this in your code:

spool stderr on to c:\temp\DimBuild Mkt.err ;

When you run the script MaxL drops all of the backslashes and returns this to the console:

MAXL> spool stderr on to c:tempDimBuild Mkt.err ;

      essmsh error: Parse error near Mkt.err

That is a giant frosty, heavy, highly breakable mug of Not Good. 

The rule (not the last, there will be a summary and a short quiz at the end)

So fix strings by enclosing them in quotes.  You will then get your spaces in your file name/error label/whatever.

spool stderr on to 'c:\temp\DimBuild Mkt.err' ;

As a rule, I tend to act like it's 1974, and never, ever, ever, put spaces in file names.  I think Windows (I cannot speak for *nix) now supports the real name, spaces and all, but I was scarred by Windows 95 and FAT, and still think in the back of mind about how file names used to be stored. Scary.

An addendum to the rule

Put file names in quotes.  That’s easy to understand. 

What kind of quotes do I use?  Single?  Double?  I forget.  Let’s try double quotes because that’s what I use most of the time when quoting strings.

Unfortunately, double quotes do work, but not in a way you would want:

 spool stderr on to "c:\temp\DimBuild_Mkt.err" ;

gets translated to:

tempDimbuild_Mkt.log

That file will probably get written to the same directory as your MaxL script.  Why would you want to do that?  (Anyone who ever worked with Comshare will recognize that immortal line.)

Let’s say you are addicted to double quotes – it’s what you use in other languages, you are in love with the left shift key, it just looks like perfection – whatever.  How are you going feed that double quote monkey on your back?

The problem is escaping characters

Finally, the title of the blog comes around.

Per the excerpt I took from the Tech Ref, backslashes are special characters and get escaped differently based on context (MaxL shell versus MaxL).  That means that you can (confusingly) mix and match quoting strings, single quoting strings, and double quoting strings all within a single MaxL statement and have it syntactically correct.  Bizzare, but true and shown in the first example.

Make those double quotes work

How, oh how, will you get the double quotes to work?  Of course, you will insert double backslashes, in the file path. 

spool stderr on to "c:\\temp\\DimBuild_Mkt.err" ;

Why, oh why, does it work?  The answer lies in what is the MaxL shell and what is MaxL proper (oh yes, there's a difference).

 To quote my dear friend the Tech Ref on the Use of Backslashes:

One backslash is treated as one backslash by the shell, but is ignored or treated as an escape character by MaxL. Two backslashes are treated as one backslash by the shell and MaxL.

'\ ' = \ (MaxL Shell)

'\ ' = (nothing) (MaxL)

'\\' = \\ (MaxL Shell)

'\\' = \ (MaxL)

You’d think a friend would tell you the truth

Although I enjoy reading, on a quiet Sunday afternoon, the Tech Ref from cover to cover (no, I don’t, actually), I am here to tell you an unfortunate fact. 

It’s wrong.  Often.

This statement:

One backslash is treated as one backslash by the shell, but is ignored or treated as an escape character by MaxL.

Ain’t so.  This MaxL code line:

import database Cameron.Basic dimensions

    connect as sa identified by password

    using server rules_file hMkt

    preserve all data on error write to

   'c:\temp\hDimBuild_Mkt.err' ;

works just fine. 

As does this:

import database Cameron.Basic dimensions

    connect as sa identified by password

    using server rules_file hMkt

    preserve all data on error write to

   "c:\temp\hDimBuild_Mkt.err" ;

Both statements work, and both write a dimension build error file to c:\temp\hDimBuild_Mkt.err.

I hope you’re noticing the single backslash that is supposed to resolve to nothing as the import command is part of the MaxL language. 

This bad information is there in the 9.3.1 documentation, and in the 11.1.1 release as well.  It has been wrong, I think, since MaxL was first released to a clamoring world.

Cameron’s observation on quotes

• When you use a command that is in the MaxL shell (spool, echo, shell, and msh), enclose stings in single quotes or use double quotes but escape backslashes with another backslash.
• When you use a command that is MaxL proper (practically everything else), enclose strings in double quotes.  Just for giggles, escape the backslash with another backslash.  Yes, that contradicts my correction above which shows that single quotes work in MaxL proper, but in a little bit you’re going to see why this isn’t so when MaxL variables are discussed.

Cameron’s first rule on quotes (there will be more)

So, must  strings always be encapsulated in double quotes?  No, but really, you should follow that rule – it’s simpler to just remember to use double quotes backslashes.

A foolish consistency?  You decide. 

Remember, login, iferror, import, define label and execute calculation work fine with single quotes, double quotes, or no quote characters at all so long as there are no spaces in the name (that isn't an issue for a calc script).  But why remember?  Just go with the “double everything” rule. 

Cameron’s second rule on quotes, in four parts

If you’re not going to follow my advice re double quotes and backslashes (you would just be the latest in a long list on a whole variety of subjects), then follow the below maxims:

If there are spaces and file paths, use either a single or double quote to encapsulate the string

1)      If in the MaxL shell, and if you have an allergy to double quotes, use single quotes and single backslashes

2)      If in the MaxL shell, and if you are allergy free to double quotes, use them, and double backslashes

3)      If MaxL and you use single quotes, use single backslashes

4)      If MaxL and you use double quotes, use double backslashes

Is the horse dead yet?

I know, I know, just because you can doesn’t mean you should, but still it’s fun.  Repeating my first example, the below code works just fine :

import database Cameron.Basic dimensions

    connect as 'sa' identified by "password"

    using server rules_file hMkt

    preserve all data on error write to

    "c:\\temp\\hDimBuild_Mkt.err" ;

Again, if you think this is acceptable, are you crazy?  And if so, what medication are you taking to make it in the “normal” world, because I want some.  J  I’m pretty convinced the normal world is anything but, so maybe you’ve got a coping mechanism I should be using.

Go on, be foolish, be consistent.  Don’t write your code like the above example.

Note that the \\ in the import actually resolves to \ because import is a MaxL command, not a MaxL shell command.

Parameters make it easier?

 Oh no they don’t.  Think back to the consistency = foolish minds bit by Emerson.  We are veering off into the so unfoolish part of the pitch it’s uncanny.

I often use positional parameters to make MaxL scripts reusuable across multiple dimensions, data files, databases, etc.  It's a powerful technique but of course it crashes headlong into the way MaxL handles quotes and backslashes.  It wouldn't be fun if it was easy, right?

Hardcoding lives!

Here’s an example of a fixed MaxL script, complete with every different combination of quoting and backslashing I could think of:

/*   

      Purpose:    Load dimension in Essbase    

      Written by: Cameron Lackpour

      Modified:   5 September 2009, intitial write

      Notes:           

*/

/*    Write errors to disk    */

spool stderr on to ‘c:\temp\DimBuild_Mkt.err’ ;

iferror "ErrorHandler" ;

/*    Log into Essbase  */

login 'essadmin' "essbase" on d630 ;

iferror 'ErrorHandler' ;

/*    Write general output to disk  */

spool stdout on to "c:\temp\DimBuild_Mkt.log" ;

iferror "ErrorHandler" ;

/*    Load dimension to Essbase     */

import database Cameron.Basic dimensions

      connect as 'sa' identified by "password"

      using server rules_file hMkt

      preserve all data on error write to

      "c:\\temp\\hDimBuild_Mkt.err" ;

iferror "ErrorHandler" ;

/*    Execute calc script     */

execute calculation 'Cameron'."Basic".MktCalc ;

iferror "ErrorHandler" ;

/*    Error handler label     */

define label 'ErrorHandler' ;

exit ;

And the inconsistencies keep on coming

Why does the \\ work in the spool stderr command?  It’s a MaxL shell command and as such \\ resolves to \\ (according to the Tech Ref) which you would think would be a big no-no in Windows except it isn’t, apparently.  Nope, it’s really resolving to \.  Arrgh. 

Don’t worry, you’re following the double quote, double backslash convention, so it’s all good.

Hardcoding is dead, long live parameter variables!

Note that I’ve hardcoded the script error, script log, user name, password, server, SQL username, SQL password, and Essbase app/db.  This means that I have to set this information in every script I have.  There has to be a better way and indeed, there is as shown below.

/*   

Purpose:    Load dimension in Essbase    

      Written by: Cameron Lackpour

      Modified:   5 September 2009, intitial write

      Notes:           

      *     Parameter variable notes:

      --    Two backslashes to properly expand the parameter variables in *all* MaxL shell or MaxL statements.

      *     Variable declaration:

      --    $1    =     Essbase username

      --    $2    =     Essbase password

      --    $3    =     Essbase server

      --    $4    =     Script log, error file, load rule, and dimension load error file,

                        e.g., DimBuild_emp.log, DimBuild_emp.err, hemp.rul, and hDimBuild_emp.err

      --    $5    =     SQL username

      --    $6    =     SQL password

      --    $7    =     Essbase application and database

*/

/*    Write errors to disk    */

spool stderr on to "c:\\temp\\DimBuild_$4.err" ;

iferror "ErrorHandler" ;

/*    Log into Essbase  */

login $1 $2 on $3 ;

iferror 'ErrorHandler' ;

/*    Write general output to disk  */

spool stdout on to "c:\\temp\\DimBuild_$4.log" ;

iferror 'ErrorHandler' ;

/*    Load dimension to Essbase     */

import database $7 dimensions

      connect as "$5" identified by "$6"

      using server rules_file h$4

      preserve all data on error write to

      "c:\\temp\\hDimBuild_$4.err" ;

iferror 'ErrorHandler' ;

/*    Execute calc script     */

execute calculation Cameron.Basic.MktCalc ;

iferror "ErrorHandler" ;

/*    Error handler label     */

define label 'ErrorHandler' ;

exit ;

The Tech Ref isn’t being foolish this time, but it is being consistent

Tokens enclosed in single quotation marks

Last time we looked at the Tech Ref re backslashes, it was somewhat challenged in the veracity department.  Not this time, because the below statement is 100% correct:

Contents within single quotation marks are preserved as literal, without variable expansion.

Example: echo '$3';

Result: $3

Tokens enclosed in double quotation marks

As is this statement:

Contents of double quotation marks are treated as a single token, and the contents are perceived as literal except that variables are expanded.

Example: spool on to "$ARBORPATH\\out.txt";
Result: MaxL Shell session is recorded to c:\hyperion\essbase\out.txt.

In the code examples I’ve shown, this is crucial.

This:

spool stderr on to 'c:\temp\DimBuild_$4.err'

will create the file:  c:\temp\DimBuild_$4.err – this is A Bad Thing.

And this:

spool stderr on to "c:\\temp\\DimBuild_$4.err" ;

will create the file:  c:\temp\DimBuild_Mkt.err – this is A Good Thing.

Unfoolish consistency is no hobgoblin, or, we have a rule, let’s stick with it

Double quotes rule – they work with hardcoded MaxL shell and MaxL statements and they correctly expand MaxL variables (of any variety – parameters, environment, or locally defined, although I didn’t review the last two).  What’s not to like?

Just remember to escape all backslashes with another backslash and this particular wee bit of confusion is all taken care of.

Resolving this bit of MaxL inconsistency  sets the stage for my next post – using ODI with not even a hint of the Essbase Knowledge Module against Essbase. 

Is the above a hack?  I think so.  Is figuring out how MaxL, which after all, isn’t used for anything but Essbase, uses quotes and backslashes a hack?  Maybe not, but it falls under the heading of necessary.

See you next time.

Why aren't you reading More To Life Than This?

Oh yeah, you are, aren't you, because you get it

If you aren't, why?  The link's to your right in my blog list -- it rotates in order with the other blogs based on post activity, but it's always there.

At the risk of making this sound like an international sycophantic blog lovefest, I ask John a semi-simple question on OTN's Planning board and get the equivalent of a white paper in return.  Good grief, that's amazing generosity of knowledge, time, and spirt.  Wow.

For those of you who noticed, this blog tries to mimic John's approach of deep technical knowledge with a few detours into irrelevancy (that would be my style contribution, not his).  He is an inspiring role model.

Short and sweet

The subjects he goes after aren't simple -- what would be the point?  Perhaps, sometimes, he needs to be prodded into making his great technical leaps intelligible to the hoi polloi, but he's able to come down from Mount Olympus to bring the rest of us along.  :)  John, we are running whilst you are strolling.

So that's it, just my way of saying thanks.  John, you rock!

P.S.  My blog is worth reading too, but if I had to choose between the two, I guess I'd have to pick John's.   
P.P.S.  Mum, I know you would pick mine.  But biological fealty means you have no choice, really.  :) 

Planning security: the wrong way and right way – which way is yours?

Introduction

Yes, again, not an Essbase subject, or more precisely, not strictly an Essbase subject.

Perhaps I ought to rename this blog to, “Cameron’s Blog For Planning Hackers,” as I seem to spend a lot of time talking about Planning. So, yes, this is a big hint that Planning is on the menu.

The subject of how to best define security in a Shared Services/Planning/Essbase world keeps on coming up with clients and was suggested to me by my much missed, ex-colleague (sob) Josie Manzano. If Ms. Josie suggests something, who am I to argue? It seems like the plurality of opinion is for it, and besides, I can tell my clients to, “Read my blog,” instead of having a conversation about security and hence drive traffic to this site. :)

How not to assign security

I can think of a few ways, all bad...

Assign security directly to a user name

Pro

• It works.

Cons

• Maintenance intensive – what happens when the user gets promoted (surely all who use Oracle EMP get promotions), quits, gets hit by a SEPTA (Southeastern Pennsylvania Torture Transit Authority) bus, etc.?

Assign security to groups without inheritance

Pro

• At least you’re not assigning to usernames.

Con

• Still maintenance-intensive, and you’ll have just as much many manual assignments. What were you thinking? Oh wait, you’re a Victim of Planning 4.x and before, so it isn’t your fault. Remember, all you have to lose are your chains.

Assign security to groups with inheritance

Pro

• Ah, you’ve reached Oracle EPM security nirvana.

• Low maintenance through inherited security.

• Inheritance design allows atomic security assignments.

Cons

• More than four levels of inheritance can bring poor performance, so don’t.

• You’ve had to read my drivel (346 words, thus far) to get to this point.

How does it all work?

1. Assign an upper level group in Shared Services to your Planning application and provision the group to access the application. In Planning; this typically means the Planners role.
   
2. Create one or more subgroups that are members of the overall group you just created. Typically, this is used to assign access to the Planning Plan Type. Note that the subgroups are provisioned to the application through security inheritance – there is no need to provision access to the application at this level.
3. Create yet a third (and in this example, the final) level of groups. There could potentially be many groups here (you may define many, it could be 20, it could be 50, dependent on how focused you define security). This third level is a member of the second group. Again, no need to assign provisioning to these groups.
4. Assign Planners to the third generation groups.
   

Three layers of groups seems a bit much just to provision access to an application. Surely there must be another reason to create this many layers, yes?

Indeed there is – now these multiple security groups are going to be applied to different slices of a Planning (or Essbase) application.

A mythical Planning application

The Planning application TotPlan has two Plan Types (Planning uses strange names for concepts that have been around since the year dot in Essbase) that correspond to databases in Essbase: Consol and Workforce.

The planners in these two Plan Types are mostly mutually exclusive; there are some users active in both, just to be difficult.

In this mythical application there are seven dimensions (note the modified hourglass order with the non-aggregating sparse dimensions at the bottom -- even examples should be optimized):

1. Account
2. Period
3. Entity
4. Employee
5. Year
6. Scenario
7. Version

Please ignore this message

Some dimensions don’t have security, so we can ignore them: Year and Period (Period has access to open and closed months, but this isn't done through security, so the tile of King Pedant remains safely on my brow).

But start paying attention here

Let’s look at the two simplest required dimensions: Scenario and Version.

Within Scenario, Actual will be read-only, Budget will be read/write.

Scenario

To give all of your provisioned planners (remember the first group and and inheritance) access to Actual and Budget, assign the topmost group to Actual, and give it read access. Do the same with Budget but make the access write.

Version

In Version, there are two members: Final and Working. Final gets the read-only setting and Working is set to write.  Again, use the topmost, first generation group to assign access.

That’s it, you’ll never have to worry about base dimensions again.  Note that security was done at a high level group (the highest, really) as access is the same for all Planners.

The big guns

Two required dimensions remain: Account and Entity

Chop the Accounts

When I build a Planning application with more than one Plan Type, I like to create upper level Account parents that segregate by Plan Type. This makes security and dimension builds as straightforward as possible. Yes, this does require extra dynamic calcs in the target (really, it’s the master) Plan Type to pull the XREF’d data from the source Plan Type(s), but I think it’s a small performance penalty to pay for clarity.  I reserve the right to bin the above approach if it doesn’t work for a particular application, dear client(s), so please don’t consider the above set in stone.

Mythical application example -- Account

To do this, name and order the Accounts like the below to split security by Plan Type:

Accounts

|--Wrkforce Accounts

|--Consol Accounts

NB -- Wrkforce, the source of employee expenses, is ordered before target Income Plan Type so that there are no forward dynamic calcs.

I can assign second generation groups to both Plan-Type-by-Account-parent assuming that all Workforce planners can see all Workfoce accounts and the same holds true for the Consol Plan Type.  Do you see the matchup between the upper level Accounts and the groups?
 

How do exceptions get handled?

More restrictive 
If a Workforce Planner did not have access to a single WorkForce Account, or range of accounts, apply his third generation group and assign None access.  This assignment on top of the second generation group Write access works because lower level, more restrictive, security will take precedence.

Access to both Plan Types 

If a Planner spanned Workforce Accounts and Consol Accounts, make his third generation group a member of both the Consol and the WorkForce second level groups. No need to create a special group just for that purpose – they grow like Topsy and quickly veer out of control.

Mythical application example -- Entity

Entity is the last of the required dimensions and it too must have security. Remembering that the default access is None, this is where the third level groups come into play as there (likely) is no general access to cost centers/projects/accounting units/etc.  Assign read or write access to Entity parents or, less optimally, to individual Entity members by third generation group.

Mythical application example -- Employee
This leaves Employee, a custom dimension in Planning-speak. Is this dimension to have security?  Probably. Can you get away with the same groups as used in Entity?  Almost certainly, as Employee dimensions have a habit of mimicking Entity dimension hierarchies.

The other alternative is to not turn on security in Employee, and let the Entity dimension access drive security. This is simpler and my recommendation.

Suggested naming conventions

Mnemonic names
Name the groups something that make a little sense.  You don't have to use my naming convention, but I've not found anything that makes more sense.

Names by generation

• Top level group that provisions access to the application and is used in the Scenario and Version dimensions: appname.
• Second level group for Plan Type access via the Account dimension: appname.PTname.
• Third level group for Entity access: appname.PTname.Entityname.
• BTW, the Entity in question would (could, but ain’t necessarily so) likely not be a level 0 Entity but some upper level parent. Take it easy on yourself and just tell your business owner it can’t be done. You’ll be thanked later when security management doesn’t take over the administrator’s life.

Real (as real as a sample in a blog) world example

Let us examine planner John Q. Public. A friendly sort of chap, with few vices (chiefly an excessive predilection for coffee), and several good points (he likes this brand), John is a planner in Entity 12345 in the Income Plan Type. 12345 is (yes, I said it was a bad idea, but it’s my example, so I do as I please) a level zero Entity.

How, oh how, does this mythical man of government forms get his security? (For non-North Americans – Canada, please note your inclusion by this Yankee because of the awesomeness of Tim Hortons, but actually I have no idea if John Q. Public means anything in the land of Timbits – John Q. Public is sort of the name of the man in the street.)

Steps in Shared Services

1. Create a Shared Services group (native, because my laptop doesn’t have MSAD) named PlnLCM.
2. Create two Shared Services groups: PlnLCM.Consol and PlnLCM.Workforce. Make both groups members of PlnLCM.Consol. NB – In the absence of any security overrides at this level, these groups inherit PlnLCM’s security.
3. Create a Shared Services group named PlnLCM.Consol.12345. Make this group a member of PlnLCM.Consol.
4. Make John Q. Public a user member of PlnLCM.Consol.12345.

Steps in Planning

As application administrator:

1. Migrate identities to ensure that the new groups are pulled from Shared Services if that hasn’t automatically happened already.
2. Perform dimension security as described above (PlnLCM.Consol is assigned to the parent Account "Consol Accounts", etc.).
3. Log out of Planning, and log in as John. Remember, this is a test id you created to prove that it works; real world users will be authenticated through LDAP or MSAD, and you are unlikely to know their password.
4. Note his restricted access – this is the payoff.

It gets better
When Jane Doe is to be added to Planning, you need only create an additional third level Shared Services group, make it a member of PlnLCM.Consol (if she is a Finance Planner), and assign her user name to PlnLCM.Consol.Entityname. As Jane (and John) are inexorably climbing the ladder of corproate success because of their Planning prowess (it could happen, ya just gotta believe), you, humble Planning administrator, need only move their usernames out of the third generation groups and move in new, soon-to-be-similarly-lucky Planners who are kicking down the door to ride the Planning elevator of professional success.

Wrapping it up 
As with most things in life, a little (and really, in the Planning world, the above has to fall into the e category of little) pain and planning deliver big results.

All of the above techniques would true for Essbase (had to bring it ‘round to the name of this blog sooner or later) and filters, except that instead of the Plan Types you will deal with databases.

So, not exactly a hack, and unfortunately, not exactly brief, but definitely a technique worth pursuing.  So maybe that kind of, sort of is a hack.

See you next time.

Fixing Planning's Filters

Full disclosure and source code and of course a disclaimer
Much of the content of this post comes from a ODTUG Kaleidoscope presentation I gave in June of this year. The presentation (280 – Master Essbase with MaxL automation) should be available 90 days after the conference, so I am guessing some time in September. I will update this post when it is available for those who want to see more.

This code was sort of the capstone of the presentation and used a variety of MaxL techniques. If you look at the source code you’re probably going to wonder why I used so many different approaches to, oh say, error checking, for example – you will intuit that I was trying to illustrate the different ways it can be handled. For your sanity I suggest you pick one (like %ERRORLEVEL%) and stick with it.

The source code is available here.

Take a look at the code, laugh at it, admire its genius, use it in anger – whatever. If it causes the end of the world (your Hyperion world, at least), gets you fired because it didn’t work, or any other less than optimal outcome, well, I make no guarantee, warranty, or anything else. Use it at your own risk. It is worth every penny you spent, which would be exactly zero.

Planning’s Filter Oddity
I seem to be going on and on about Planning, not Essbase, and of course the name of this blog isn’t Cameron’s Blog For Planning Hackers, so it’s fair to ask what’s going on.

Well, Planning is a wrapper application around Essbase – at the end of the day, whether it’s dimensions, data, or Business Rules (aka calc scripts on steroids) – Essbase is the foundation that Planning rests on. And Planning drives Essbase, but oddly.

Within Hyperion Planning, metadata is filtered. That is, if you're a Planning user, and security is applied to a dimension, you can only see the bits of the dimension that you have read or write access to. (If your read access is greater than your write access, you will see more members in, say, a dimension drop down, but you will only be able to write to the members you have write access to.)

This makes sense, right? Why would you want to deal with members you can't touch?

Makes sense
Here's the administrator's view of the Entity dimension from the Planning reference application:


The Planning administrator sees all – this is appropriate, as he is responsible for all data.
And here is a Planner's view of the world (literally):
See the difference? For a user that can only see Latin America, all is as it should be.

Stop making sense
And here’s that same user looking at the same dimension in SmartView:

The rows highlighted in yellow are not readable/writeable, but are visible. While the planner can select these members, he can’t retrieve their data values. This is inconsistent, and is colloquially known as Not Good.

Why does it do what it does?
Planning performs metadata filtering within the application, but when it writes the filters to the Essbase id used to query data, it uses a READ, not a METAREAD filter.

Why? This is a mystery. Okay, lots of things in life are mysteries, but the fix from the Planning side seems so simple (use METAREAD, not READ). I’m a fan of Raymond Chandler, but I am not seeing the plot to “The Big Sleep” here. Well, maybe someone was snoozing when this was implemented.

The fix (no, not the FIX..ENDFIX, the solution)
A few years ago at Solutions in the product lab I asked a Planning product manager (I think) about using MEATREAD and he said "It can't be done."

Curiouser and curiouser. Why not? Was there something in a METAREAD filter that would cause the world to stop spinning, the magnetic fields to fade, and result in a cockroach-only world? This did seem somewhat far-fetched.

There had to be a reason; I suspected I wasn’t hearing it. This was (and is) sort of the equivalent of waving a red cape in front of a bull and it made me curious -- why not? Essbase has had METAREAD since 6 (I think, it could be earlier). Why not write a METAREAD to Essbase when refreshing security?

There was an easy way to test this – roll my own refresh and force METAREAD filters as part of the Planning refresh.

Dare I? Briefly, I considered Robert Oppenheimer’s quote of the Bhagavad-Gita at Trinity: “I am become Death, the Shatterer of Worlds,” but in the true spirit of all mad scientists, I quickly dismissed such idle thoughts. Our future as cockroaches would bring many benefits, anyway.

Diagnosing the disease
What's the problem?

The filter in EAS as written by Planning:


Easy fix in EAS
If only it were this easy -- just copy the READ row and make it a METAREAD. Since METAREAD is more restrictive (both data AND metadata) it takes precedence, so the READ line doesn't have to be deleted:
And it looks just right in SmartView (or the Essbase-centric reporting tool of choice).

One tiny problem
But when Planning does a security refresh, it's going to go right back to the bad old ways of yore, and will strip the METAREAD off the Planner's filter. Bummer.

And if you have 200 users, manually editing their filters in EAS is going to cause your fingers to fall off, or your eyeballs pop out of your head, or something else equally horrible. I understand that cockroaches can regenerate limbs, so there’s another plus towards the possible end of the world due to METAREAD.

Back to some kind of solution
By hacking (okay, I am stretching this, but hey, it's the name of the blog, so bear with me) Essbase, we can make the user experience the same between Essbase and Planning without . Never say "It can't be done" to a moderately curious geek.

What would this refresh need to do?
1) Refresh Planning dimensions and security
2) Apply METAREAD access to those filters

Pretty simple, right? It's always easy when the consultant writes it on the whiteboard...

A little more detail
We're going to combine Planning 11x's CubeRefresh.cmd, MaxL, NT Cmd scripts (I am showing my age), and VBScript (ditto) to put together a scripted approach to Planning that:
1. Refreshes Planning through CubeRefresh.cmd
2. Writes the filters to disk via MaxL
3. Reads them into memory in VBScript
4. Programmatically generates MaxL code to apply a METAREAD to each of those filters
5. Run that new MaxL script

BTW, I happen to be mildly proficient in VBScript (call me Fred) and NT Cmd scripts. You could use whatever tools you like, e.g., Powershell, or the Bourne shell, or Rexx (call yourself T. Rex), etc.

Putting it all together

This is where you’re going to want to download the source code and follow along. At least that’s what I would do, but I have been called odd (and a few other choice descriptions, but I digress).
Script components

Script name	Description
Refresh_Planning.cmd	Overall NT Cmd control script
ModCubeRefresh.cmd	Refresh Planning utility
Create_MetaRead_Filters.wsf	VBScript to read output from Write_Filters_To_Disc.mshs and write METAREAD filters
call_metaread_add_filters.mshs	MaxL shell to run METAREAD filter adds
Metaread_Add_Filters.msh	Programmatically generated METAREAD filters

A note about ModCubeRefresh.cmd
When I ran CubeRefresh from a command line (this was within the C:\Hyperion\products\Planning\bin directory) it worked just fine. However, when I called it from another batch file it stopped all execution. I tried every trick I could think of to make it work and finally just added an “EXIT” to the end of it and saved it as ModCubeRefresh.cmd.

Refresh_Planning.cmd
To get this all to work, I used the calling script to accept parameters as shown below:

Parameter	Value
Planning encrypted password file	c:\tempdir\odtug_2009\password.txt
Planning application	plansamp
Planning admin username	admin
Refresh or create	/R
Filter switches	/FS
Essbase server	%computername%
First half of private key	316108469
Second half of private key	694177571

How do I run it?
refresh_planning.cmd c:\tempdir\odtug_2009\password.txt plansamp admin /R /FS %computername% 316108469,694177571

ModCubeRefresh.cmd
The normal CubeRefresh.cmd script has seven parameters:

Parameter	Value
-f	Path to encrypted password file
/A	Planning application name
/U	Username
/C	RMI port to bind to (Extra points if you tell me who said “This is the sort of English up with which I will not put.” And why. And if he really did. Hint -- His name isn’t Gordon Broon.)
/R	Create or refresh outline
/D	Process database
/F or /FS or /FV or /FSV or /FVS	Filters, shared or not, or even validated
/L	Connect to the local bean (Is this like, "Splendid work, old bean."? Again, I digress.)
/RMIPORT	RMI port to bind to the remote Planning bean. Use with /-L option
/DEBUG	Print debug statements

The RMIPORT, Create/Refresh, Process database, Use security filters, Connect to the local bean, and Debug parameters are optional.

In my world (this is my code, you can follow or get just as explicit as you like/need/want), I chose to explicitly define: the password file, application name, username, refresh or create, filter switch (what would be the point otherwise?), and I also passed along a log file I use for error checking.

Write_Filters_To_Disc.mshs
This is simple code that writes all Essbase filters to disc. I don’t know why I did this, as the display filter command can be limited to only one database. It works in the demo world, but in a real environment it’s a distinctly lousy idea. Feel free to modify this with a MaxL parameter variable to pass only the Essbase database filters you want. Yes, this gets trickier with multiple Plan Types.

Create_Metaread_Filters.wsf
This is where the rubber meets the road – it reads the output from Write_Filters_To_Disc.mshs, converts the READ filter to METAREAD, and generates the MaxL script Metaread_Add_Filters.msh. This file will be used to set the Planner’s filters to METAREAD.

Call_metaread_add_filters.mshs
Encrypted (username and password) MaxL shell to modify filters to use METAREAD.
Metaread_Add_Filters.msh
This is the code that adds the METAREAD. It’s kind of silly to have both a READ and METAREAD in the same filter, although it works because of METAREAD’s more restrictive nature. I tried using MaxL’s replace filter command instead of alter filter but I found that it just gave the Planner read access to the entire dimension in question. It must be (I think) something to do with Shared Services and alter filter works, so I went with that. I welcome a better approach – hint – Blogspot has a comment section and I read them. I will modify this post as required.

What does it look like?
Magic – Essbase filters that come from Planning are now METAREAD filters.

Conculsion
I believe that the Earth still rotates on its axis. All is well. I do not scuttle sideways across the floor, and my trusty prismatic compass still works, so apparently TEOTWAWKI did not occur.

NB – You will have to perform all filter refreshes through this utility. Unfortunately, for ModCubeRefresh.cmd to work, this will require Planning server access, or at least remote access. There are a number of ways to do this – Google is your friend.

The future
At the conclusion of the presentation, I heard that METAREAD functionality is now in the pipeline for Planning.

However, if you’re on Planning 9x (the Planning refresh utility is a little different), or up to 11.1.2, you may want to use this code base to give your Planning users the same metadata filtering in Essbase as they get in Planning.

Another chapter in hacking Essbase under the belt.

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.