Are you skint, broke, busted, busy, unappreciated but still want to go to ODTUG Kaleidoscope?
The question
Can’t experience the full ODTUG Kaleidoscope experience because your company has no budget/that big project tragically coincides with the ConferenceoftheYear/your boss doesn’t appreciate your super genius and won’t cough up the dough?
The answer
For those short of time, budget, or both, you can now attend the ODTUG Hyperion symposium. What is it? Tsk, didn’t you read my last blogpost? Of course you did, because you are a super genius, just like me.
Returning to what you actually care about, Sunday 26 June 2010’s Hyperion symposium is the day where big Oracle acts like a tiny little startup and you’re the Venture Capitalist. Oh yes, the power feels so good.
What do I mean? That Sunday will be where Oracle gives we lucky attendees (and that can now include you for c-h-e-e-p, just keep on reading) a preview of where Oracle EPM is going, and if the magic repeats from last year, an opportunity for you to share your Essbase/SmartView/FDM/whatever opinion directly with the people who manage the products. Perhaps I’m overemphasizing the point, but Sunday may be an unparalleled opportunity for you to talk directly to the product staff. Speak now or forever hold your peace. And your office isn’t even on Sand Hill Road.
Setting expectations
Just a bit of a disclaimer – not every product presentation last year was this kind of freewheeling session, but I am hoping to gently nudge all of them into this position through the awesome reader base that this blog commands. So that would be my mother and Glenn. And I have to ask her. I know Glenn just does it for the chance to heckle me and tell me that I’m overly verbose. Which is true.
Continuing down the may-not-happen-so-don’t-hold-me-to-it theme, Oracle will state in these presentations that they are committed to nothing they present and that whatever they do present is subject to change. I like that – it’s honest and realistic. Product futures aren’t written in stone and besides, if Oracle is asking you questions, the whole point is that the direction will change. Regardless, the peek into the future makes it all worthwhile.
So, what’s your excuse?
Even Scrooge had to give Sunday’s off, and I appreciate that dipping into your pocket hurts (I know people who took vacation or unpaid time off, paid their own way, and shared rooms just to attend last year. This conference inspires sacrafice.) , but look at it this way, you’re getting access that only the largest and most important customers/partners get for the mere pittance of $325 if you sign up before 24 March 2010. And you don’t even have to miss a single day of that unbounded joy we call work. I hope to see you there.
Kaleidoscope 2010 is coming (which reminds me, I need to write my presentation, but soon, gulp) and there are two awesome prequels to the conference itself, which is no slouch when it comes to awesomeness.
Schooldays
Every Kaleidoscope I’ve ever attended – a grand total of two, but give me time –kicks off with a volunteer day. I wasn’t able to attend last year because of work pressures, but my bosses’ bosses’ boss was kind enough to take my place (thanks, Edward) so the very worthy alien plant eradication could take place.
These days are really a lot of fun (I did the one in New Orleans, so this isn’t mere puffery) because in addition to donating muscle to a worthy cause, you get to meet people you won’t be seeing during the conference proper as you obsessively shuffle between all of the cool Hyperion content here and here. You won’t be? Tsk. I will. But that’s my neurosis, so perhaps you should be grateful you don’t share it. It is a heavy burden.
Yes, Oracle seems to own other products
As hard as it is to believe, it is my understanding that there are actually other technology tracks on offer at Kaleidoscope, although why anyone would leave the warm and cozy world of Oracle EPM is beyond my comprehension.
If you are similarly narrowly focused in your love for all things formerly owned by Arbor Software, this might be your only chance to meet the great people that use those other, obscure, products like PL/SQL…I wonder what it does.
And oh yes, give freely of your time. See, virtue is its own reward, just like your parents told you.
A Descartesian Three
Nope, this isn’t the Wrong Kind of Join (see, I am not completely ignorant of the black art called CeeKewEl), instead you have three choices, all good.
Unleash your Inner Librarian on the Dewey Decimal System
Oddly, at my dear alma mater, Wossamotta U, the IT degree program was offered in the school formerly known as Library Science. I am trying to imagine a major that would be less likely to get you a date on Friday night than the double whammy of geek + librarian but I am coming up short. Of course I can be smug because I got my obsolete degree in MCIS in the business college. And because I have been told many times that I am the living embodiment of Gary Cooper, Cary Grant, and William Holden rolled into one alpha geek. At least I think that’s what they mean when I get calledWalter Mitty.
In atonement for giving librarians (they are so going to revoke my library card and *all* library privileges, forever) and fellow geeks the raspberry, I will likely be that geek librarian and help them sort their books. So now you know which task to avoid.
Or relearn hopscotch (in my case, learn it for the first time) and help paint on the blacktop the games of our halcyon youth.
You won’t regret it
It’s a great way to give back, ODTUG makes the transport easy, gives you eats, and you get to rub shoulders with people in very different parts of the technical world. What’s not to love?
And now the amazing stuff
Would you like to know where Oracle is taking the EPM stack? See the latest products and not suffer the pain of a beta? Get product managers to ask for your opinion? I can think of two ways to do this:
1) Get a job at Oracle. Get promoted at a pace so meteoric that the phrase “meteoric pace” is inadequate. Take John Kopcke’s job. Now you know all. Can you do that in one day?
2)Returning from Cloud Cuckooland, you could instead come to Sunday’s Oracle Hyperion EPM and BI Symposium and have it handed to you on a platter. No, no thanks are required, this blog is here to help.
Oracle > Hyperion
Yes, Oracle is many, many times bigger than dear old HYSL ever was. Yet somewhat unbelievably, they act like a small company, at least at Kaleidoscope.
I don’t know if it’s because Big Red really is a warm and fuzzy group of people, or that ODTUG is awfully good at getting to the right people at Oracle, or if it’s just the right alignment of stars, but the mix of altruism, self-interest, and personalities comingles into magic. By that I mean Oracle comes to show you where they’re going in the near future, where they want to go in the mid to far future, and oh by the way, what do you Kaleidoscope attendees think about it?
We (or at least I) are not worthy
Think about that – Oracle is this enormous company that is asking you what you think of their product plan. I’m not saying that they will do your every bidding, but it’s pretty astounding to me that they ask and act on comments from the hoi polloi of EPM geekdom. That’s awesome. Yes SmartView product team, I’m talking about you. Thanks again for last year’s freewheeling session. If I could just think of a word that was awesome * 2 and then apply it to last year’s symposium, I would.
Solutions != Kaleidoscope
I went to (and paid for) every Solutions conference there ever was. I can’t remember anyone from Hyperion asking me for my opinion on a product. Ever. I know that much of the product development staff is the same so I’m going to attribute this welcome change to Oracle’s culture and Kaleidoscope’s awesomeness (there’s that word again).
It doesn’t stop on Sunday
And of course Oracle’s involvement doesn’t end there – they’ll be in sessions all through the conference. About 1/3 of the presentations are given by Oracle employees.
You are going to be there, yes?
This is *the* conference to go to – amazing content from Oracle, partners, and customers, all those great (ahem) Werewolf games at night, and the chance to give back to the DC public schools. You can’t miss it.
Oracle’s Enterprise Performance Management Architect has been giving me pain, agony, and general agita because I just couldn’t get the darn thing to work. Nope, not what you think – it wasn’t what it did to a Planning database, I couldn’t even get it running enough to whine about its bugs.
Brief history – I needed to install the Oracle EPM stack – Shared Services, Workspace, Essbase, Studio, EIS, EAS, Planning, Calculation Manager, HFM, Financial Reports, and Web Analysis on my Windows 2003 Server VM. No big deal, right? Installations are much easier in 11.x, right? Even an idiot (like yr. obdnt. srvnt.) could do it, right?
Did I read Tim Tow’s blog post on how to install 11.x? Oh, yes.
Did I bug people I know and respect deeply? Oh, yes.
Did their combined wisdom help? Oh, yes.
Did I need to zap my VMWare instance about 10 or 12 times, despite reading these documents and generally annoying people to no end with questions? Oh, yes.
Some call it success
But after zapping 10 or 12 installations (oh, thank you, VMWare for not making me reinstall the OS on bare metal) I finally got the extraction order right for everything I need. Hallelujah, praise be, & c.
Except for EPMA.
Just for the record, this is one of those simple-stupid installations where I took the absolute default on everything. That means: SQL Server Express, install all of the Oracle EPM stack into a single SQL Server database, use admin/password and sa/password (yes, I know, stupid from a security perspective, remember, I used that adjective before), let the configurator do its work however it wishes. Did I mention that several prayers were sent up to the Oracle at Delphi (that’s Greek mythology, not a mix of technologies) that all would work correctly?
I suspect it was one of those a-million-monkeys-type-for-a-million-years-and-come-up-with-Shakespeare moments, but somehow, everything worked but EPMA Process Manager.
What was EPMA’s problem?
As the Great Stoneface would have said, had he talked, “Damfino”.
Here’s a screenshot of the Event Viewer error message for the Process Manager’s failed service start:
What on earth does this mean other than the @#$%^&*()_+! service doesn’t work? Damfino. Did I mention that I don’t do installs for a living?
However even I can guess that there’s something wrong with SQL Server – it’s right there in the message.
People were anxious to help
I post from time to time on OTN (this is a joke, by the way, my obsession with OTN/Network54 almost matches my coffeeaddiction), and the Great and Good John Goodwin took time out to try to answer my question on OTN to no avail. And I hijacked a thread on Network54 to try to get some help. And that help happened.
And here’s where that help happened
Henceforth I am going to have to refer to John Booth as the Great and Good John Booth. So, that would be the Great and Good Johns?
Somewhat incredibly, he took 30 minutes out of his Saturday (today, to be precise) to set up a GoToMeeting web session and fix my problem.
I’m amazed and humbled by his generous help – as you’ll see in a moment, I wouldn’t have figured this one out in, oh, eight or nine million years. He went above and beyond and I really, really appreciate it.
Gentle readers (all three or four of you, and “Hi, Mum”), think about this for a second – a total stranger – someone I’ve only bumped up against a few times on the web, helped, a lot. I think I may no longer be able to be a Card Carrying Cynic. Sniff.
So, again, John, thank you so much.
The fix
Part of me is embarrassed, the answer is so simple. But of course it’s simple when you know how to do it. In case you haven’t deduced this by now, I think Mr. Booth knows what he’s doing.
So simple a developer could do it
Tim Tow mentioned it, sort of, in the Network54 thread. Or maybe he did provide the answer, and I was too dense to understand.
The fix I was shown was – go to SQL Server Configuration Manager, open up the SQL Server 205 Network Configuration, and then right click on TC/IP.
But before you get the fix, see why Jason Jones is not the world’s greatest fan of SQL Server Express. And neither am I.
And here we come to a difference between SQL Server and SQL Server Express
By default, SQL Server Express uses a dynamic TCP port. By default, at least on my VM, the TCP ports were set to 0, which means dynamic. This is (I guess) good, because SQL Server Express can then go against any port that is open. I’m no SQL Server dba, so I’ll leave it to one of those explain why this is good.
SQL Server Express 2005
Here’s the TCP setting on my SSE install:
See the yellow highlighted lines? These are the culprits.
SQL Server 2005
I have a real copy SQL Server, not SQL Server Express, on my laptop (not the VM, but the laptop all of this madness runs on), and guess what?
Note the difference on the TCP ports – these are already set to 1433, SQL Server’s default port. And the dynamic ports are turned off.
That’s all there is to it
Yup, here it is in all its glory. Set the TCP Port to 1433, click OK, and restart the SQL Server Express service.
Launching EPMA Process Manager
John then told me to try starting EPMA Process Manager. The VM whirred and clicked and ground away and..ta da, it ran, and brought up the Job Manager, Event Manager, and Engine Manager services as well! I believe my mouth was hanging open in disbelief.
Magic
He also recommended that I restart Workspace. After I did that, we went into Workspace, navigated to the Dimension Library – and there it was, in all of its completely unused glory. It was almost, but not quite, anticlimactic.
The conclusion
Thanks again, John
So to John Booth for taking a half hour out of his weekend – thank you so much. You’re going to see links to this blog in both the Network54 thread and on Essbase OTN giving you full credit which of course you deserve 100%. I hope to see you at Kaleidoscope 2010 and buy you the beverage of your choice.
The very least all can do in the meantime is check out his latest post on how to install the 11.x Essbase add-in without the multi-gigabyte nonsense that EPM installer puts you through. Now that is a cool hack.
But no thanks to you, whoever you are
To some unnamed programmer(s) at Hyperion or Oracle – why on earth did you make EPMA communicate differently with SQL Server from the rest of Oracle EPM? Nothing else required this setting change. Once made everything worked, but why? Maybe you wanted to drive sales of the real SQL Server? Or drive Essbase hackers crazy?
The end of this post
So, hacking Essbase per the title of this blog? Not exactly, more like hacking SQL Server, but diving into this stuff is always interesting, and when you have someone who is a hacker in his own right it’s always fun.
Oh joy, oh rapture, it’s finally here. And you can preview 2010’s schedule herewith 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?
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
The data load performance is slow, which is lame.
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.
Many metadata load options are missing, e.g., allow moves, allow property changes, etc. This is lame in the extreme.
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:
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.
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.
Drive all of the MaxL parameter variables in your MaxL script with ODI variables.
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?
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
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:
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).
Create variables – I'm going to create an ODI variable for each and every one of my MaxL parameter variables.
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.
If we were running the MaxL script from the command line, the order of the strings would look something like:
Essmsh
MaxL script name
Essbase username
Essbase password
Server
Essbase object name
SQL username
SQL password
Essbase application
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.
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.
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:
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.
The path to MaxL shell (essmsh.exe) and the MaxL script (filename.msh) should be fully qualified.
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.
When recognized, ODI turns the color of the Variable to blue. This is a good syntax check.
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?
ODI is awesome
The Essbase KMs leave something to be desired
MaxL and load rules, well, rule
ODI can do anything (almost anything?) you could do in script better, faster, and sexier.
Hacking two products at the same time is more fun than one. J
This is only scratching the surface of what ODI can do.
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.
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).
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.JI’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.
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.
