Want to know The Truth About CPM?

15 December 2009

ODTUG 2010 is here! (Well, almost, but you can dream, yes?)

Introduction

Oh joy, oh rapture, it’s finally here.  And you can preview 2010’s schedule here with the proposed speakers.  Enjoy the incredible (Really, it is incredible, and if you’re reading this blog, you will enjoy it.  I promise.) quality and diversity of sessions. 

The sessions are fixed; the speakers assigned are provisional pending their agreement to present.  Speakers – you proposed these presentations, now you have to live up to your promise.  I guess I’m trying to say, the speaker names may change, a little, but hopefully not too much.

Thanks

How did this amazing schedule happen?

Well, there’s this guy named Edward Roske (who has this blog you may have heard of) the ODTUG Hyperion SIG’s content leader (I think I got that right) who literally worked through the night to put together a draft schedule, and of course the rest of the board that endlessly (or wait, was that me who wouldn’t shut up?) questioned, probed, kicked the tires, and generally massaged the schedule into the awesomeness you see now.  Being part of it was really exciting, especially hearing the passion and excitement that all board members brought to the process.  The quality of the board members (guys, I am not listing names here to preserve privacy – I may revise this blog and put in names if I get your okay) is incredible and it shows in the quality of the schedule.  Yes, of course without the speakers and their abstracts, there would be no schedule, but there were many submissions, and few spots to present.  Choosing who would actually present was not an easy task as the quality of the abstracts was so high.

NB – Edward, you are too public to get the privacy waiver, and besides, you did way to much work not to get the credit.

Abstract submitters -- thanks goes to all of you for submitting your ideas.  Please know that the quality of the presentations gets better and better each year and the number of submissions gets higher and higher.  If you didn't get in this year, please don't be discouraged.  There's always next year and it would be a shame if those who were rejected never tried again.  FWIW, I'm on the board and only one of my abstracts out of three got accepted and it got merged with another presenter.  So much for my cronies on the board.  :)

Next?

Okay, you’ve got the schedule.
 
Dream away.  Figure out what sessions/how you’re going to pay for/where in Washington, DC you’ll stay when you attend 2010’s ODTUG
conference.

Isn’t this the best Winter Solstice present ever?  Ever?

01 November 2009

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.

08 October 2009

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.