I will be available on 8 May, 2015. ODI forever! Or something like that. Contact me on LinkedIn if you're interested.

11 April 2015

Stupid Programming Tricks No. 23 -- UNC, Sub Vars, and DATAEXPORT

Is The Stupid is spreading?

Nope, this Stupid Trick is pretty good.  I am however continuing my pattern of stealing good ideas highlighting all that is best in the Essbase world with this ostensibly-simple but quite useful Stupid Trick from Martin Slack.

Migration, drives, and DATAEXPORT

I and many others have covered using DATAEXPORT as a way to get data out of BSO cubes.  There are quite a few issues when writing to relational tables (it always appends, performance isn’t all that great, batch inserts amazingly do not work with 64 bit Essbase, etc.) so the most common way is to write to flat file.  As one might guess, writing to a flat file usually (I suppose one could write to a default location but who does that?) requires defining a drive name with syntax that looks something like:
DATAEXPORT "File" "," "c:\\tempdir\\samplebasic.txt" "#Mi" ;

And yes, before anyone gets too excited about me using MVFEDITWWW, aka Sample.Basic, as an example for this, this technique works with Really Big BSO Databases (so RBBD?) as well.

Moving on from my justification of using Sample.Basic, can anyone spot the issue with the above?  I’m sure the more perceptive of you note that I am pointing to an explicit drive (C:) and directory (tempdir).  What happens when I migrate this awesome bit of code from my VM to something real?  Or, more to your real world needs, what happens when you write something like this on your development server, which uses the C drive (‘coz you are nuts – who uses that drive for anything data-related, but I digress yet again) and move it to something more real, like the D or E or F drive over in QA and Production.  And what if you’re exporting data not to a local drive but to a network mapped drive (the purpose of exporting data is to make it available for some other process, likely on another server, to transform)?

You change the code.  But of course you shouldn’t.

Here’s the What You Shouldn’t Do approach:


NB – I am using UNC names as I may very well want to push this output to another server.  At least if I hard code this I can, with only a bit of change, flip from one server to another.

Some of us devil-may-care Essbase hacker types believe (in the immortal words of a consultant I knew long, long ago), “There’s no test like production” and, I suppose in an insane way that’s true.  However, this blog’s audience is of the Best and Brightest and we B&Bers know that one never, never, never does this.  Right?  Right.

So with this in mind, what’s the obvious way to get round this?  Essbase has this cool bit of functionality called an Essbase Substitution Variable.  One could hard code the explicitly defined file name (full path including drive, folder path, and file name) to a Sub Var, but then the issue would be that if you have more than one calc script writing out a DATAEXPORT, you’d then have to have those explicit path Sub Vars for each one of the files.  And even worse, if the different environments have different mapped drives or different local drives that bump up against the development drive definitions it all gets a bit horrific.
Wouldn’t it be better to not hardcode much of anything except the file name and maybe part of the path?  And get away from explicit drive names?  And even explicit folder names?  Yes.

Sub Vars with a twist

So if the obvious (isn’t it) way to solve hardcoding of path names is to use Sub Vars, and the even better way to avoid hardcoding path names is to use a Sub Var that only specifies the drive and the path, how oh how oh how to do it?  It isn’t obvious.

Start with easy

The most straightforward approach is to simply put the entire path into a variable as per below:

And then in the code reference it like so:

Does it work?  Oh yes:

That’s great but as noted to use this approach for more than one DATAEXPORT target, one would have to have multiple variables and that’s messy.

So if we think about strings, and how they can be concatenated, it seems reasonable that a concatenation of a variable that specifies a drive and folder path with the file name should provide the string that Essbase needs and be flexible at the same time.

What to do with the double quotes is a bit of a puzzle.  The non-variable approach is to wrap the path and file name in double quotes, the HardcodedPath variable has that explicitly named file in double quotes in the variable value thus passing the quote delimters.  The question then becomes how do you handle the leading and trailing quotes when part of the path name is a variable and the other a hard coded file name that requires that terminating double quote?

No Joy in Mudville

Let the name of the variable below be a clue.

Oddly, on validation, it passes:

The doom and gloom text box is not from EAS but instead yr. obt. svt.’s idea of humor such as it is.

So, it syntax checks but when executed, nada:

We now know that, at least so far, a variable with the double quotes as part of the variable value works, but when those double quotes are wrapped around the variable value (which does not have those quotes) it fails.

NB – For those of you who might think the file was written out with the name &MappedDriveFails\tempdir\samplebasic.txt because everything within the double quotes is treated as a literal, I am here to tell you that it is not; it simply doesn’t get written to disk.

We are now at the Stupid Trick

Before I go any further, here are some further oddities.

Can you get round double quotes altogether?  It validates…
But it doesn’t work.

And you cannot hardcode the path and then terminate with a double quote (not totally sure why you want to do this, but I note it for the sake of completeness).  It does validate…

But doesn’t work.  I wouldn’t put a lot of stock into Essbase’s DATAEXPORT validation process.

The not so obvious answer

Really, this is not obvious, at least to me.  Hopefully not you, either, else I will feel even more stupid than I usually do.  Which lately is quite a bit.  

Here’s the approach as taught by Martin:  put a double quote at the beginning of the variable value and then do not use a double quote to terminate the file name in the calc script.  It sounds and looks really wonky but it works.

This bears repeating

Don’t put a double quote on the end of the file target.  Let me restate that – the variable value has a leading double quote – "\\epm11123\c$ but the terminating file name does not have a terminating double quote.  It looks something like this:
DATAEXPORT "File" "," &MappedDriveWorks\tempdir\samplebasic.txt ;

Again the variable value is "\\epm11123\c$. That’s a leading double quote.  Pay Attention Because This is Important.  As is the (how many times have I stated this?) the fact that there is no trailing double quote.  See, I told you this was a Stupid Trick.  And I think I told you twice.  At least.

Let’s look at the results

Here we are in the EAS variable editor:

And then, in the calc script, do not use a trailing double quote (so the third time I mention this?).  

Here’s the proof (I of course expect you, Gentle Reader, to believe absolutely none of this so Happy Testing):

Ah, you say, surely this can be dealt with by putting a double quote into the calc script itself and then using &MappedDriveFailes which does not have a leading double quote.  That mimics what’s done above (at least in theory), right?
Alas and alack, although of course it validates it doesn’t work.

Going back to what works, how the calc script editor handles the lack of a leading double quote is a bit amusing:
So the approaches that don’t work do validate, do color code commented lines as green, but again don’t work.  The approach that does work does validate (although again this isn’t much to write home about), doesn’t handle color coding, and then does work.  Terrific.

This is what I love about Stupid Tricks

What’s the criteria?
  1. It is pretty useful.
  2. It is not obvious.
  3. It is clever.
  4. It really doesn’t make sense, but it works.

I think this use-double-quotes-here-but-not-here approach hits the mark on all four points.  Going forward, I won’t specify DATAEXPORT paths any other way.

Thanks Martin for letting me steal your idea.

Be seeing you.

02 April 2015

A really, really, really interesting guest blog on parallelism and its impact on database design

This is your captain speaking

I am very happy to inform you that I have once again suckered convinced another very clever and insightful Essbase hacker to write for me.  Peter Nitschke of M-Power Solutions  has written an absolutely fascinating analysis on BSO database design and its impact on parallelism.  I haven’t seen anything quite like this and the results are, to put it mildly, intriguing.  Without exaggeration, I think this is going to make people think, a lot, about how they design their databases.  

You will note British/Australian/Commonwealth spelling below.  Peter’s from Godzown and it is his post, and the States are the only country not to follow the Queen’s English, so I’m leaving it as is.  Thankfully, there’s nothing metric in here as I would have to convert that to real money.

Ahoy hoi,

This will be a reasonably long and technical one - so it comes with a recommendation to read this in the morning with a strong coffee, or alternatively in the evening with a good red wine. Actually, if you're doing the latter you may have a problem

24 March 2015

The new (and improved) EPM documentation portal

In the beginning

In my experience (or at least memory, which, as you will read below, is perhaps not iron trap-like) there have been three major iterations of the EPM documentation portal under Oracle’.  Arbor and Hyperion Solutions had documentation before that, but recalling documentation portals from before 2004 (2006?) is simply beyond me.

Here’s the history, in Living Color.

Life under HYSL

Yes, really, thanks to the web’s Wayback Machine and a search for http://dev.hyperion.com a pretty good feel for what documentation used to look like..  What, you don’t remember Hyperion’s stewardship of Essbase?  Then you are a n00b.  No worries, it wasn’t all that much fun, as you’ll see below.  Let’s experience the horror together.

One other note – this was a search of 2004’s information.

And yet another note – it’s a bit unfair to judge an old portal by today’s standards, but as you’ll see, documentation has moved on quite a bit.

In the beginning with Essbase 6

It ain’t pretty, is it?  To be fair (there’s that concept again), as much as it pains me to try to be even handed, some of the ugliness of this is because the Wayback Machine isn’t perfect with grabbing all graphics.  Regardless, I have my reasons as will be revealed below.

With that, welcome to web design 10 years ago:

EPM?  What’s that?  We’ve got Essbase, and that’s all we need.  

Essbase was quite a bit simpler back in the 6.0.0 days.  Even yr. obt. svt. who is, to be incredibly charitable, a complete infrastructure idiot, could install Essbase.  It all got more complicated very quickly.  Let’s take a last fond look at the Good Old Days.

Essbase Administration System Console?

The late lamented Application Manager is no more.  Folks, it ain’t 1993 anymore (Essbase’s debut although I believe App Man didn’t come till Essbase 2.0) but I do miss the tight functionality with Windows.  Did Hyperion really think Essbase developers were using X Windows?  There were some weird product decisions back then, so who knows.  Note that EAS existed but isn’t available on the Essbase page.  Why?

Not too much change although I think that’s an artefact of the date of this web page.

Why the ire and bile about Hyperion, given that so many of its employees now work for Oracle?
Essbase XTD Analytic Services?  What?  Hyperion never quite knew what to do with Essbase and tried to bury the thing.  One of the first acts Oracle did with Essbase was change it back to its original name.  In my opinion, that’s the difference between an applications company (Hyperion) and a database company (Oracle).  It’s not that there’s a whit wrong with applications – I bought my house on the back of the money I earnt from Hyperion Planning – it’s just that Essbase is so awesome it really frosted my cookies when Hyperion started to deemphasize it.  All EPM product management needed was to be free from their chains.

With that fairly epic rant over and done with, Essbase documentation is beginning to look a little more it is today.  Here’s the Tech Ref:

View it all in PDF format

As imperfect as web pages are displayed, you can see that the pdfs are all there.  Wonder when ASO was introduced?  Was it 7.0 or 7.1?  Take a look at the New Features Guide:
Thus began ASO which begat Hybrid and the future of Essbase.

Life under Oracle

System 9

Ah, much better.  Oracle put some money into it and it shows.

11.1.1.x

Pretty much the same thing, but you can see how the product is growing.  There were some turkeys amongst the gems.  Does anyone remember Smart Search?  The .NET builder?

11.1.2.2

We are now in the modern era.

New and improved

The actual content of the documentation has of course been updated as the tools expand in breadth and depth, but the documentation UI has for the most part been old wine in new bottles since Oracle’s acquisition.

That is no longer the case both for 11.1.2.3.x and 11.1.2.4.

The Oracle documentation team have come up with a much cleaner interface and improved content.  Some might argue that a UI for documentation doesn’t really matter but I think that discounts how a clean and responsive design can impact quickly finding information.  The point after all behind documentation is trying to find the answer to a problem/understand a new technique/get the job done as easily and quickly as possible.  Documentation, while incredibly valuable, is not something yr. obt. svt. (and I suspect you as well) want to spend any more time reading than absolutely necessary.

So what does it look like?  Oooh, pretty colors that match either the Stars and Stripes, the Union Flag, or the Tricolor.  Wait, there’s the Russian and Dutch and I’m sure a few other flags I have missed.  Oracle is nothing if not international in its outlook.


Big deal you say.  Colors, schmolors, what does this actually do?  Well, one thing it does is dynamically resize the web page based on window width.

The above I sliced off on the right (this is a 1600x1200 desktop display) to get the Essbase page to display in a readable manner.

Here’s the reorganization within a smaller window.  It took me a few tries at it to notice this – it just works and makes finding links within the Essbase docs easier to find.  This sort of seamless productivity enhancement is the sort of detail that makes great UI.   The little things count.

Errors, what errors?

Oracle have also changed most of the Error Message Reference guides.  Looking at Foundation Services, the Workspace error list now looks like this:

Alas and alack, the Essbase error guide still has the old look, but upgrading it is on the radar:

Again, you may think of these as incremental improvements but I view these improvements as making my life that much easier; if I’m looking at the Essbase error message list, life is bad, bad, bad.  Anything that makes that bad go away quickly is good in my book.

More than an incremental improvement

The EPM Cumulative Feature Overview has been around for quite a while.  I can remember looking at it during an 11.1.2.0 implementation and that was 2011.  It has heretofore been an Excel workbook that was only available on My Oracle Support.  I’m a Gold Partner (and yes, it is eye-wateringly expensive for a one man shop’s access to MOS but at least I don’t have to be Platinum) so I can download it when needed but for customers or consultants who are not on their firm’s Happy Support List, getting it meant begging and borrowing a copy of the file.  I try not to think about whether that violates licensing or not.

In any case, Oracle have now made it a web page.  Oh happy day.

It works as before.  

Pick a from and to release level:

NB – the version numbers go up to the highest level, e.g., 11.1.2.5.400 is visible but it does not apply to any EPM product other than Smart View as of this writing.

Pick your version, select your technology (I am only selecting Essbase but multiple selects are possible):

And Bob’s your Uncle, here it is:

Do you see the bit in the red box?  You can either print this or save it to an Excel file.  Nice.

It’s as easy as 3.1249:

And here it is in all of its Excel-y glory, ready for you to send off to world+dog:

Isn’t that nice?

Yes, it is, and in fact it’s very nice.  These are all incremental improvements but those improvements add up to a much easier and handier interface and thus quicker access to the information that we all need.

I know I sometimes sound like a paid shill for Oracle but I can assure you no bribes (alas, this is not terribly likely, but if it does come to pass, please make it small unmarked bills in a manila envelope) were involved.  Those of you who know me privately are aware of my very critical, nay cynical, nature as evidenced by my continual pokes at Hyperion Solutions.  The flip side of that is when something or someone impresses me, I am a huge fan.  And I am a huge fan of what the Oracle EPM documentation team has produced.

Be seeing you.

19 March 2015

Can you take over the OTN ArchBeat Podcast?

Are you an Architect?  Have something to say?  Want to work with OTN?

You likely are, you almost certainly do, and really should.  It’s also dead easy.

The OTN ArchBeat podcast has been around since 2008 and is a showcase for members of the OTN architect community and everyone else to speak in a relaxed, informal, and unstructured way about the topics, techniques, and tools that make Oracle – and for the purposes of this blog, Essbase and Planning and HFM and ODI and FDMEE and whatever else floats your EPM boat -- hackers, well, something more than hackers.

Yr. obt. svt. has participated in multiple podcasts, and as many of you are already thinking, “If Cameron can do it, surely I can as well.”  You’re right, you can and should.  Some other EPM examples are upgrades and ODI.  I like to think that the latter ODI podcast helped the Great Cause of getting Oracle to support Hyperion Planning 11.1.2.4 KMs in ODI 12c.

Who does this?

Bob Rhubart runs the ArchBeat program within OTN and he is actively canvassing participants for ArchBeat podcasts.  As both Uncle Sam and Lord Kitchener said, Bob wants you.  

In fact, he’s looking for more than participants, Bob’s looking for guest producers.  What’s a guest producer?  It’s someone who picks the topic and selects the panelists for the podcast discussion.  Yes, you get a fabulous Tinseltown title.  I can’t say if you will look like this after the podcast but anything is possible.

What do you do?

Simple, simple, simple:
  1. Pick a topic
  2. Recruit fellow speakers
  3. Submit your podcast abstract to OTN
  4. If approved, come up with a few questions/topics to get the conversational ball rolling
  5. Get on Skype

Can it get easier?  Thought not.

Think of this podcast as a conversation, not a presentation.  We talk/complain/gossip/educate about EPM topics all the time – you’ll do the same in a podcast only it will be recorded for world+dog.

The best part

The very best part is that you only need be generally (or at least putatively) brilliant in your architect-y discussion of whatever EPM content tickles your fancy.

OTN does all of the hard logistical, organizational, and technical work.

How does one get the ball rolling?

Simply submit your idea here.

That’s it.

What are you waiting for?

OTN ArchBeat podcasts and their reach

I should note that the ArchBeat podcast is one of the top three podcasts Oracle have on offer.  Think about that for a moment – ArchBeat is pretty much a grassroots operation (one man does most of the work) and it meets or beats the many sales-related podcasts Oracle produces.  It seems reasonable to assume that something that actually sells software licenses has quite a bit of organizational support (read $, ¥, or £).  This isn’t to take anything away from Bob as he does an excellent job with evangelizing Oracle, but the popularity of these podcasts is testament to the strength of Oracle’s development community.  You are part of that community and the ArchBeat podcast is your chance to share your knowledge.

Be seeing and hopefully hearing you.