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:
Result: MaxL Shell session is recorded to
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.