Gonna build an outline, from a little file

I’ve shown how easy it is to build an Essbase SaaS database from an existing outline .otl file. Read about my glorious experiences doing just that (and using LCM as well) here.

That’s all well and good but what if I want to do something other than migrating an outline? I thought at first that I’d have to create the smallest Essbase outline in the world ever and then import that, adding dimensions as required. Ugh. Regardless of the ugh, the ick, and the yuck, I wrote about just that approach.

I should have been more patient, because now Essbase SaaS (Have you noted that I call this product what it is – Essbase as a Service? You should.) can load from dimension files. Patience is a virtue but he who hesitates (or wants to write about something most ricky tick) is lost. I suppose that means that I am without virtue because I don’t hesitate to act before knowledge mysteriously comes my way. It was ever thus with Yr. Obt. Svt. But no matter, onwards.

Gonna build an outline, at least I hope I will

I have a preternatural love of Sample.Basic. Some (heh, maybe no one) have wondered what on earth I see in it. Beyond my notorious penchant for laziness, I like working with The Very Greatest BSO Essbase Database In The Whole Wide World because it encapsulates everything (just about) there is to know about how Essbase BSO works. Really and truly it does.

I even – in one of my sadder and yet greatest-ish moments – built all of it, data, formulas, the lot – in Excel to understand its calculation order when Tim German and I deconstructed Hybrid way back in (Gah. When? 2015? Probably.) the misty past.

Seriously, as far as BSO outlines go, it’s got all the basics (I simply could not resist) and more. So totes obvs I need to build it in Essbase SaaS because that will be an exemplar of how we’ll build Essbase databases in the future (and, if you’re lucky, right now).

Gonna build an outline, gonna build it high

Hmm, Cameron, you say you have an Essbase outline that you want to load via dimension files that come from Sample.Basic and yet you don’t want to just use Basic.otl to load it.  Pray tell, where will you get files to do such a thing?

To answer the question, I shall simply use Applied OLAP’s Next Generation Outline Extractor (NGOE). This has to be the number one Essbase utility extant. Tim and his team keep this product updated for free, gratis, so little cost it rounds down to zero. And guess what – it (amongst many other formats) exports Essbase outlines to generic Planning dimension build files. You don’t have to create them from scratch.

NB — You all know that Applied OLAP is the software company behind Dodeca, right?

You say you don’t have the NGOE? Really? Go to Applied OLAP’s site, click as below:

You’re going to need a username/password but as the registration is free (yes, Tim really is that awesome), this isn’t the world’s greatest hurdle. Once in, simply download and install and you’re off to the races.

Here’s my install on my ancient 11.1.2.4 install running – gasp – Windows 2008. Yes, ancient, about seven years old I believe. Eeek.

Once configured, running it is just a series of GUI prompts. I wanted to show you this bit in case you’re unaware of it:

And here we are: all of the lovely dimensions:

I don’t know how I’m gonna do it, I only know I’m gonna try

I will confess that I spent a bunch of time trying to get these files to work. Why? Because they were wrong? Nope. Because Yr. Hmbl., Obt., & Mst. Fthfyl. Svt. sort of kind of forgot that the Outline Extractor assumes three Plan Types and Essbase SaaS Free Form Planning applications only have one? Yup. How many times did I make this mistake? And how simple is it to remember that this is an issue? Many and very. Ugh.

I want to make this superduperpooper clear: the errors in loading dimensions are not the fault of NGOE because it is (thankfully) providing Planning-ready dimension import. Of course it has no idea (heh, not that I have any idea either) what the target application looks like. Did Essbase SaaS exist when the very first OLAP Underground Outline Extractor was created at Hyperion? And then taken over by Tim? And then expanded to support Planning? No (OMG it was in something like 2005), and no (I cannot remember when this happened – when Oracle bought Hyperion?), and no (Sorry, I can’t remember this one at all, either. It was some time around Planning 11, I think, but I’m not sure).

What that all means is that NGOE provides a starting point but you (and I) are going to need to modify the files. For those of us who wallow in laziness, this is rather a bother but c’mon, what did you expect? My repeated stupidity aside, this isn’t all that hard.

There were some other horrors oddities with the files like 03-25-1996 being replaced with 3/25/96 in Excel and then saving that to a .csv file when I edited it and other bits of madness. Oh Excel, how I love and hate thee.

And oh yeah, other bits of stupidity where I’d try to load a dimension with attributes and then find out that whoopsie, not only does the attribute dimension have to exist but it has to have the members in said attribute dimension before they can be assigned to the base dimension. Do you ever learn, Cameron? Ever? Thought not.

Gonna build a daydream, from a little hope

But all of that is prologue. Let’s see what it’s like to start this from scratch. Yes, you’ve seen this before but it’s different.

‘Cos, when I click on Start:

Yes, I can still select an .otl file but I’m more interested in building it for real and for true.

Gonna push that daydream, up the outline  slope

Essbase SaaS allows you to predefine your database with Account, Entity, and Period dimensions but only if you wish. If these are not selected on application creation, they can be selected later. Nice.

A summary page:

Ta da:

And here’s the Period dimension, naked and alone.

Gonna build a daydream, gonna see it through

A Period dimension with no BegBal? Is it possible? It is.

Also, I am on an (gasp and actually we are doing totally cool things, I mean OMG geeky cool) on-premises project so I don’t get to spend time looking at the new-to-me dimension editor. Oooh, pretty.

Gonna build an outline and a daydream , gonna make ’em both come true

Here’s the Next Generation Outline Extractor’s Measure dimension:

And here it is as loaded to SBasic.Sample (Hah! I used an Essbase naming convention. Why not? It’s Essbase after all.)

Gonna build an outline, from a little hell

A note about how NGOE pulls attribute dimensions: it prefixes the attribute member with the dimension name. Given that Population is numeric, we get a KABOOM!

Gonna build an outline, and I know darned well

Easy peasy to fix, from this:

To this:

If I build my outline, with a lot of care

After much toing and froing which again mostly consisted of me making a series of never-ending and sadly-repetitive mistakes, it works!

I’m not totally sure why I should be surprised but it doesn’t take much for me to be excited. I need to get out more, which is probably true given that I’ve spent the last five weekends covering much of my lawn with mulch.

Planning ad hoc:

Enter data, just like in Essbase…

And submitted.  Ah, Essbase heaven.

And take my daydream up the outline, heaven will be waiting there

I know you’re all desperately curious about the state of my lawn during the Time of Plague. No? Pity, ‘cos here it is.

Here’s one of the three deliveries of five cubic yards of mulch. That’s a lot of mulch.  That’s a mountain of mulch.  OMG, I slay myself.  For the love of all that is good, start clicking on the section links and you’ll get the joke.  The one at the tippy top is a good place to start.  I’m a big Matt Monro fan; you should be as well.

One of two front gardens. Note the gnome sentinels:

Left garden, pre mulching. Note the gnomes. I love gnomes. I have no idea why.

That’s right in front of my office. Alas, these are small ones so I cannot see my gnome vassals from my desk. But I know they’re there, guarding, watching, waiting in their gnomish way.

Another one of the many gardens. Yes, I need to get the dead grass off of the lawn.

The front yard with the mulched maple tree and a bonus gnome view of the right front garden from behind:

Oh. Wait. You’re not interested in my gardens/gnomes/mountain of mulch. You want to know what passes for semi-informed opinion on Essbase SaaS and for that matter NGOE.

That’s easy: they’re both awesome. Why? Because the latter feeds the former with almost no (putting aside Cameron Who Must Try Harder) work and because the former allows Essbase (okay, okay, Planning) practitioners to build dimensions and thus Essbase applications just as we need them, cf. the death of BegBal.

I hope that you can tell how excited I am by this tool. If you’re an EPM cloud customer, why aren’t you using Free Form Planning?

Be seeing you.

P.S. Oh yeah, this coming weekend? The last bit of the last five yards awaits.  At least it’s just a hill, not that mountain.