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.