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

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.


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?

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 and

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 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., 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 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.

09 March 2015

Stupid Programming Tricks No. 22 – Making Applied OLAP’s Outline Extractor fast, fast, fast

A note before the introduction

MMIC, aka Glenn Schwartzberg, wrote a post on this tool last week.  While I would love to identify a vast ACE Director conspiracy because that would mean I was interesting, or a brilliant criminal mastermind, or at least sneaky, alas, the truth is that it is none of these things and instead a figment of my febrile (or is that feeblie?) imagination and the similar subject is simply coincidence.  Never fear, Gentle Reader, I have a different take on the tool than Glenn.  Whether it is a better or even worthwhile take is of course up to you.


I have known Tim Tow, owner of Applied OLAP, since 1995 when he came to my-then employer to teach us the Lex Excel dashboard framework for BI.  For those of you old enough to remember Excel 95 (I think that was the release), it came with a sample BI dashboard – I suppose Microsoft thought people would use Excel as a database, calculation engine, and dashboard.  Actually, they were spot on and their current desktop BI approach (btw, they jettisoned MDX in MSAS which caused a fair amount of heartburn) continues that philosophy.

Regardless of the Great Enemy’s approach (dear Microsoft Legal, I kid, I kid) twenty years in technology is basically a career and I work today with people who have five years in harness; I should note that I was but a callow IT youth of five years myself when I met Tim.  My long friendship with Tim is one of the reasons I am such a fan of his company’s products, including its flagship product, Dodeca, but I am also continually impressed with the free utilities Applied OLAP creates for the EPM community.

Probably the best known tool is the Outline Extractor aka the Next Generation Outline Extractor aka NGOE.  Tim took this product over from a skunkworks Hyperion project to get Essbase outlines out to text long ago and shepherded it through its VB API genesis to today’s Java API tool.  There have been thousands and thousands of downloads of this product – practically every Essbase practitioner I know has one or more versions of it installed on his server or PC.  It really is that useful.

NB – Tim tells me that I am using the first generation of the NGOE and that there is a second version that does not require an installation of the MaxL client. I don’t have that version installed.  For those of you that have that release, one of the techniques I illustrate below will be impossible.

A note of thanks

Tim wanted me to note that Harry Gates’ MaxL->file-readable-by-humans utility was the inspiration for creating a MaxL-driven version of the NGOE.  

So what’s not to like?

There have been two main complaints about the product:  consumption of Essbase ephemeral ports causing OE to timeout and wait for the ports to be freed up and a performance deficit when it comes to large outlines.  With the release, that issue of port exhaustion has been resolved, although not documented except by Tim.  However, the issue with performance remains with very large databases.  One could argue if the outline took a long time to build, it should take a long time to extract, but we are all (or at least most of us and especially me) an impatient lot.  What to do?

The pain and not the glory

Let me give you an example with the Hybrid Essbase database Tim German and I created for our Kscope14 presentation on Hybrid.  It’s pretty big, at least for a BSO database; I expect to see more and more of these databases as Hybrid becomes common practice.  Yes, this is small to middling in an ASO database but bear with me and I will show that even this database tests OE.

For those of you not familiar with the latest and greatest iteration of OE, what I’ll be showing is the GUI interface.  Nothing spectacular bit it is very functional and straightforward.  Are you using this tool for the latest faux Cloud interface or to get a job done?  Thought so.

The example you are about to see is running on my VM.  Stay tuned for my exercise in frustration/possible triumph as I install on a new VM in a future blog post, but I digress.

Here I am logging into my VM and selecting the T3_Hybrid.T3_Hybrid database.

Picking my dimensions – I’m actually not going to select the last two attribute dimensions but am too lazy to retake the shot.  So sue me.

And picking the first (and generally standard) Essbase API selection:

Choosing the Load File format – I think this is the most common of all:

Defining the options.  Tsk, tsk, it is better practice to use the | character as a delimiter but I was in a hurry:

Take the default timeout, which can be troublesome but I am going for bog standard examples:

3, 2, 1 liftoff:

I hit execute, and…go have dinner, exercise, watch some TV, and go to bed only to wake up and find:

For those of you math challenged even more than I, that is 6 ¾ hours from start to finish.  The Postcode dimension took almost three hours.  Ouch.  

This time is acceptable, albeit slow, for the purposes of a blog but can be a real deal breaker when the extracted outline is needed during a work day.  It can be even worse, unusable really, when going over a VPN to a client site.  Hours to extract a single dimension, and not a very large one at that, are not unheard of.  What to do, what to do, what to do

From the ashes of disaster come the roses of success

I had just such an occurrence recently and I despaired of ever getting the outline extracted.  Then it hit me (right between the eyes) that there were three options in the extract that I had never used.  Why, why, why?  Laziness, or a lack of imagination, or most like stupidity is the answer.  Am I being hard on myself?  Read on, Gentle Reader, and I suspect you will find yourself in agreement.

With the self-flagellation out of the way, what are those Paths Not Taken?  Why it’s good old MaxL.  I am somewhat familiar with the MaxL outline extract output – it is in XML format and unless you are uncannily good at reading XML, it requires a parser to something mere humans can readily comprehend.  Do you suppose that OE has just such a parser?  Could be, rabbit.  

Let’s first let OE run the MaxL extraction:

Don’t try this at home folks

Running the OE MaxL extractor without the correct pathing to MaxL provides this lovely Java stack dump:

The product does provide a hint – NGMaxl.cmd died.  Why?  OE cannot find essmsh.exe.  This is an easy fix – go find the NGMaxL.cmd file and look for the “set ESSBASEPATH” line – it will be commented out and defaults to the C: drive; in my case this was fine as I am running on a simple VM – you may need to change this depending on your install.

Uncomment (or in my case, copy and leave the commented line alone because I am chicken) and verify that indeed essmesh.exe is where the ESSBASEPATH variable points:

The sweet smell of success

Run the process again, picking the same options and we see that the outline extract ran in 9.54 seconds.  Whaaaaat?

The proof is in the pudding, and I love that sweet at the end of a meal

That export process is so fast, it is frankly unbelievable.  Looking at the times in Explorer shows it starting at 8:16 and finishing within the same minute.  Given that the process took less than 10 seconds one can hardly characterize that as surprising.

One of these things is not like the other, one of these things does not belong

Hmm, the file size for some of the dimensions are a wee bit different.  Could the MaxL approach have incorrectly or incompletely extracted dimensionality?

The Essbase API:

Or, if you prefer in text format:


Again, in text:

The only difference is the number of UDAs.  Is this a problem?  If you look at the two screenshots, you will note that the API approach has extra columns for UDAs which happen to be blank other than the header.  Yes, yr. obt. svt. could not believe his eyes and double checked.  Nope, nothing there.

So why the extra columns?  It turns out that these are UDAs assigned to the dimension but not actually assigned to any member.  In essence the member information is exactly the same – the slight difference in header is responsible for the difference size.


One other note:  in this Network54 thread, there was a comment that the MaxL approach does not capture solve order.  I’m here to tell you that in fact the MaxL export references both solve order and levels considered for aggregation.

Try this on for size

If the OE can run MaxL, surely I can too right from the MaxL shell.  What kind of MaxL grammar should I use?  Well, I could go off and look at the Tech Ref, or I could just steal the existing MaxL that the OE created.  As noted, I am lazy (as all good programmers are, although in this case it is a different, Not Good, kind of laziness on my part) and so I eschew rewriting something already done.

Take a look in your TEMP folder and search for a file with a .mxl extension.  Yep, there it is in all of its MaxL glory.

And now replicate in the MaxL shell.  You would think, given that I am copying someone else’s code, that I could get it right.  But noooooo:

I do eventually get it right.  See, I show you my Ultra Super Bad Typing Skills – there are no coding secrets on this blog.

Note that as with the API approach, I am not extracting all of the dimensions, hence the list dimensions syntax.

I eventually got it right.  Again, so fast that MaxL doesn’t even log it.  But did it work?  Oh yes:

Wow, a 32 MB xml file.  Who would want to read this?  Happily for all of us, or at least me, I can use OE’s XML outline parser to read in the file and spit out

This time, I will use the manually extracted outline XML file:

And run the extraction:

This is pretty good – I have almost halved the time.  Of course we are considering this within the context of a 10 second OE-only approach and of course there was my manual extraction via the MaxL shell, but still, that is some pretty amazing performance.  Less Than Five Seconds.  Phowar.

The numbers

The numbers are a bit unbelievable but you can do the math if you don’t believe me:

Time in seconds
X faster
Var % to API
Essbase API
Cameron MaxL

You read that right – 2500 (twenty five hundred or two thousand five hundred) times as fast as the API.  Who knew?  And why would anyone ever, ever, ever use anything else?  Beats me.

 I am sandbagging this one a bit as it does not count the time to extract the data from the MaxL shell but the numbers are all kinds of amazing and awesome.  But still.

It took you this long, Cameron?  Why?

Sigh.  I wish I knew.  I really, really, really wish I knew.  As I wrote above, mental sloth is likely the root cause.  Hopefully you are smarter than I and already knew all of the above.  I only wrote this because I wanted to save other, misguided, Essbase hackers.

This approach makes the OE moar bettah, much moar bettah, than before, and it was already pretty awesome.  Henceforth, I shall look askance at the API extract.  I really cannot think of a reason to use it.  I am sure the Best and Brightest will correct me on that but for the time being, that is my position.
Thanks again Tim, and the rest of the crew at Applied OLAP, for making such a fantastic product and thanks doubly for providing such a fast, fast, fast way to extract the dimensionality.

Be seeing you.