Passing 300

It all began one summer

It seems so long ago (2,770 days or 396 weeks or 91 months or 7.6 years – but who’s counting?) that I first put pen to paper – Yes, I did.  Really.  I’ve now moved on to word processors for drafts and am thus so 21st century. – and started this blog.  And why the (re)counting?  Because this little corner of EPM inanity has hit 300 posts.  That’s an average of 39 posts of Stupid Programming Tricks, Compleat Idiot, Stupid Shared Services Tricks, Stupid Planning Tricks, and other sundry bits of EPM frivolity per year.  I pity you for reading this dreck.  Come to think of it, I pity myself for writing it at such a pace but on balance I think I feel worse for you.  

But it is a landmark of sorts and an opportunity to reflect on why this blog continues when so many contemporaneously launched blogs are moribund or nearly so.

So yes, 300 posts and yet some of you are still here.  Why?

Don’t know much about Essbase/PBCS/Planning/FDMEE/etc.

I seem to be forever chasing Oracle’s EPM seemingly ever-expanding products – how do I do X, how did someone else do Y (and how can I “borrow” their approach), why doesn’t that !@#$ing Z work?  Some of my fellow EPM practitioners seem to glide from tool to tool and solution to solution with nary a show of effort (Glenn, Celvin, TimG, TimT, Dino, and Pete I’m looking at each and every one of you.  With envy.).  I assure you that yr. most hmbl. & obt. svt never, ever, ever gets from A to B without a fair amount of pain.  Solving the problem is always fun, staring at it (best of course when in front of other people, the more senior the better) in complete incomprehension not so much.

So are you this?

Or this?

Everything I've Got Belongs To You

There are the greats in this industry – any industry really – and then there are the rest of us.  Is that so bad?  We’re not the smartest guys in the room but at least we get to be in the room.  Yes, I think I just insulted every one of you, Gentle Readers, but my point being that this blog’s primary purpose to help you and me get from A to B.  Maybe the fact that you read work-related blogs (obv. not just this one), read EPM books, follow EPM geeks on Twitter, and read and post on messageboards means that in fact you’re amongst the smart set.  Surely the smart ones use resources to solve their problems; surely the dumb ones don’t.  See?  I just rescued myself from having exactly zero readers.  Hopefully.

All kidding aside, this blog as it exists today would be pointless without you.  Thank you for putting up with what has been described as an idiosyncratic (read:  long winded with detours into obscurity) approach.  I hope you take the time to click on all of my laboriously-gathered links.  Goal one of this blog:  make you better EPM geeks.  Goal two of this blog:  make you all wish it was 1967 aka peak American popular culture as it’s a giant wasteland after that.  Let’s turn the clock back.  At least you’ll appreciate what your parents or grandparents (or in some cases great-grandparents) grooved to.

I’ve got your number

Google (Blogger and Google Analytics) is funny and by funny I mean inconsistent.

Here’s Blogger’s numbers:

Huzzah!  I’m closing in on a million page hits.

And then there’s Google Analytics:

Not-huzzah because it’s telling me that I’m closing in on half a million page views.  

It’s a riddle

A couple of interesting notes about the above:

1. People don’t read this blog around Christmas.  Not a huge surprise there.
2. My readership is going – slowly – down.  Why?

For the first, it’s nice to know that people have lives.

As for the decline (and it is real, alas) is I think based on two things:  number of posts per year (I hit my high in 2014 of 52 posts and readers vs. 40 the year after – less new content = less readers) and competition from other posts as well as Twitter and other social media.  I haven’t tried to count the number of EPM-related blogs extant today but it surely has to be about 50.  When I started it out the number was more like 10 although as noted most of those are dead, dead, dead.  YouTube, Facebook, and Twitter are yet more avenues for those who want to learn.

Or this blog sucks and is getting worse all the time.  You decide.

Why shouldn’t I

I like to think that actually the blog is getting better.  I’ve purposely hit on a combination of series posts such as the Compleat Idiot series on Planning in the cloud, Programming Stupid Tricks for unrelated Essbase, Planning, whatever-they-are tips and tricks, and community outreach posts such as live (sort of) blogging of Kscope, OpenWorld, and now meetups.  

You may have noticed that I’ve switched to a longer and more in depth approach in my Compleat Idiot cloud series.  There’s an awful lot to learn about Oracle’s cloud products.  Lots of innovation, yes, but also lots of work learning the tools and then keeping up with them.  I can’t think of how to do this except through this detailed way as so much innovation is coming out of the movement to the cloud.  Love the cloud or loathe it, money is being poured into the products in a way that simply hasn’t existed before.  That means the products change and expand constantly and that likely means the Compleat Idiot series won’t either.  That also means my life won’t get a lot better because some of these posts are over 50 pages when written in Word.  Ouch for both you in the reading and me in the writing.

While solutions to problems are what we’re all after, there is more to life and a career than code.  I’ve used this blog as a soapbox to encourage you in the strongest terms to get involved with our little community.  As an example, my involvement with ODTUG has utterly transformed my professional and personal life.  If it happened to me, it can happen to you.  Grasp the ring.  Reach.  Blow your horn.

Where I can, I’ve tried to also impart what little wisdom I’ve picked up in 20+ years of consulting in a 25+ year EPM so-called career.  Sometimes I shake my head at the folly of others when it comes to solutions (hubristically complex), code (ugly, hardcoded, slow, wrong – sometimes all four at once), and even social interactions (Is there anyone more awkward than a geek?  Thought not.) and then realize that I almost certainly did the same thing at one point or another.  Smart people learn from others’ mistakes.  Think of this as a plea to be smart and occasionally listen to me as I’ve made every mistake there is.  

The other bit of advice I’d give you is don’t be afraid to be a contrarian.  That of course doesn’t mean you’re always right, but reflect on why people say what they say.  Is a technical recommendation for the good of customer or is for the benefit of the speaker?  Is product X the solution that everyone follows because a vendor is pushing it or would some other simpler and cheaper approach work just as well?  

In a word:

 

Try to See It My Way

Have I fulfilled this blog’s mission?  Here’s what I wrote on 10 May 2009:

What about the “hacking” in the name of this blog? Hacking can mean all sorts of bad things and that’s what villains do. Good hackers are more interested in taking an ordinary tool (but so cool) and doing out of the ordinary things in a geek chic way.

To that end, I’m going to try to share with you some of the dumb things I’ve done and how you don’t have to do them, how to make Essbase do things it “can’t” do, and generally make Essbase dance.

Lastly and most importantly, I’ll also share code/techniques/approaches. I welcome your comments (constructive please, I have an average ego and it is bruised when pummeled) and most of all your suggestions for improvements. I’ve never written a piece of code that hasn’t been improved through examination by a fresh set of eyes and as a consultant if I can’t fix where I wrote it, I’ll make it better next time.

And, despite the title of this web site, I won’t limit the scope of my postings to Essbase. I’ll include anything else that touches Essbase, from Planning to Dodeca, to who knows what.

That, for good or ill, is pretty much what this blog is all about.  Through the passage of time I’ve forgotten about “geek chic” and shall henceforth casually drop it into conversation.

All kidding aside, I’ve tried very hard to live up to my vision of education and outreach and I think on balance I’ve managed to do it.

Watch what happens

So where does this blog go from here?  Will there be another 300?  Will I lose my ever-lovin’ mind and actually do this again?  Maybe.

So long as I’m involved in this little industry, I feel I have no choice but to keep learning.  Whether that’s through this blog, speaking at conferences, writing books, or in some other completely-monetarily-uncompensated form, I’ll keep on learning and sharing.  One day, hopefully not too (actually, yes, hopefully given what that entails) long from now, I’ll retire and this blog will come to an end.  I’m not dead yet and I’ve got a lot of livin’ to do so expect more of Cameron in one form or another.

Because of you

So yes, this blog exists because I use it as a mechanism to teach myself but making it public with a readership that rounds down to zero would be pointless.  Thank you for your support, your comments and corrections, and your continued readership.

Call me

Want to see a topic?  Have a question (hopefully) answered?  You can reach me care of this blog or via Twitter or via LinkedIn or reach out to me in person at meetups, Kscope, and Open World.

Be seeing you.

The Compleat Idiot’s Guide to PBCS, No. 18 -- Create Essbase Cloud Services with PBCS

There’s an Essbase Cloud Service in your future

Waiting for EssCS just like I am?  You betcha.  But what’s a geek to do when it’s not actually available (as of the middle of November 2016)?  Roll your own.  How oh how oh how?  With PBCS, natch.

What’s that you say?  PBCS != Essbase.  But it is, under the covers as is any Planning application.  And you can create a poor man’s EssCS by using PBCS’ Plan Types for fun and profit.  Okay, it is fair to say that the approach I’m going to outline might be viewed as bit excessive for a single application but remember that PBCS supports multiple Planning application types:  Simple and Standard and if you use the latter application type, there are multiple Plan Types and thus – at least potentially – multiple Essbase databases in the cloud.  It ain’t perfect and EssCS will in future eclipse this approach but for the here and now this approach is what it takes to get Essbase in the cloud.

Arguably, and only if you’re licensed for it, there’s also EPBCS although that last type of PBCS application type is actually not terribly suitable for what I’m about to show as its value is in predefined Plan Types.  

In any case, to keep things simple I’m going to use PBCS’ Lite application type.  A Lite application supports but one Plan Type and it is ASO-only.  The planning and budgeting use case for this has to be vanishingly small – a pure rack-and-stack level 0 only budgeting application.  I guess it’s possible but given the limitations around ASO and Planning (Tim German and I presented on this at Kscope14) as the sole budgeting tool (I am not counting reporting cubes built into Planning as part of a larger mixed BSO and ASO implementation)it seems unlikely that many customers would use the Lite application type.  There must be someone, somewhere, who asked Oracle product management for this; I’d like to meet him.

However, the purpose of this post is to show how PBCS can be used for analytical, not budgeting purposes and for that the Lite application has the benefit of being a good fit cloudifying my secondmost MVFEDITWWW aka ASOSamp.Sample.

I should note that it’ll also talk about a very cool way to seamlessly and quickly export data from ASO in column format.  I need to write about this in a separate blog post but this will have to do for now as an introduction.

If you have access to an Essbase server, you almost certainly have ASOSamp.  Notwithstanding Jason Jones’ announcement that The Beverage Company has gone tits up, Essbase’s sample applications can actually be quite useful when testing out hypotheses and I believe TBC has emerged from bankruptcy court.

And with that, off we go to 38 pages of blather blog genius…

Moving to Planning

How does one move an on-premises Essbase database to the cloud?  An Essbase database is comprised of its dimensions, calculations, and data.  Planning – on-premises or Cloud – requires dimension (including member formulas) and data extraction from Essbase and conversion to Planning’s format.

Dimensions go Up, Up and Away

Many, including yr. obt. svt., have written about Olapundergroud’s (really Applied OLAP’s) Next Generation Outline Extractor (NGOE).  Tim Tow tells me that it’s been downloaded over 80,000 times (yes, really) and the most common domain name (you must register to download it) is oracle.com.  In other words, NGOE is, or should be, a standard part of any Essbase hacker’s toolset.

Given that, I’m not going to walk you through how to extract dimensionality from ASOSamp and selecting the Hyperion Planning 11 Text Writer option.  Just know that this is the way to get dimensionality out and (mostly) in perfect shape for importation into either on-premises Planning or PBCS.

Just to prove that NGOE does export in Planning format, see below:

The output for all dimensions plus the fruit of the next step in this process:

And as extracted:

Note that there are three Plan Types in columns in O through Q.  NGOE doesn’t know how many Plan Types there are so defaults to the three out of the box generically named ones.  I had to change Aggregation (Plan1) to Aggregation (Sample) and get rid of the Plan2 and Plan3 columns.

But what about data?

Essbase does many things wonderfully well but unfortunately exporting data, particularly from ASO, in a nice neat columnar format is not one of them.  Gary Crisci has written about using MDX to get data out of Essbase, yr. obt. svt.’s most popular blog post ever is all about data extraction.  All of these approaches are painful although in BSO-land things are easier thanks to the calc script command DATAEXPORT and of course MaxL’s (and EAS’) option of exporting in columnar format.  The fact remains that ASO simply doesn’t export in columns although again MDX or Essbase Report Scripts can suffice for a limited data set.  NB – MDX has a technical limit of the number of addressable cells and Report Scripts have a real world performance imposed export size.  In other words, getting that data out is a PITA.

What happens when we export in native format?

Ah, that sucks.  It isn’t fun to read and even more importantly PBCS will not import this.

This is what we need (this is what the sample PBCS application Vision requires but you get the idea):

What to do, what to do, what to do?

That is a tab delimted file with data at the end.  As noted, a Report Script or maybe MDX can extract this, maybe, but there is another way for ASO:  Harry Gates’ cubeSavvy utility will extract ASO data in column format and it’s fast, fast, fast.   It is, without exaggeration, a kind of a holy grail to many of us.  I will pass over why Essbase doesn’t have something like this in-built and leave that puzzle for you, Gentle Reader, to ponder.

After downloading it (registration required) upon installation and execution the following appears.  Note that there are two tabs that are relevant or at least seem relevant:  Outline XML Parser and ASO Export Parser.  The latter allows custom MDX to define the export while the former deals with all of the database through an exported outline in XML format.

NB – As I wrote, using this XML format outline file is the fastest way to extract dimensionality in the NGOE.  I cant’ think of a reason to use any other approach.

Because I am an infrastructure idiot/I am too lazy to set up my path for everything to work, I had to use the following command line to run cubeSavvy.  May you be better at this than I, but just in case we’re in the same boat, Gentle Reader, here’s what I used:

c:\oracle\middleware\jdk160_35\bin\java -jar cubeSavvyUtilities.jar

Once, somehow, executed, cubeSavvy appears in all its glory.

Happily, this is easy to get via MaxL:

With the native export file and outline xml defined within cubeSavvy, all that needs doing is providing an output location for the columnar export.

And of course click on the “Parse on columns” button:

10 seconds.  That’s all it took.  Brilliant!

And that looks just like the data import format PBCS requires in all of its tab-delimited glory:

Gonna build me an application

Now I have dimensionality and data.  Let’s go build that application in the sky.

Remembering not to be tempted beyond human limits by that bright shiny FCCS button on the right, creating a “Lite” application is easy so long as one remembers that this a single plan type Planning application.

Name it whatever you like, so long as it’s eight characters or less.  Planning (and PBCS) != Unicode-enabled although the Essbase databases and even the data are Unicode.  Curious but there it is..

Going through the motions

PBCS requires a wizard whose settings are super-duper important in on-premises but in PBCS not so much.  Or is it?  Inquiring minds want to know.

It alwasys takes soooo long.   Working, working, working

Whirring and clicking complete.

If this is all done, why am I reviewing it?  It’s almost like these settings matter.  But they don’t and I only highlight them to show how meaningless they are.

Why the cavalier attitude?  Those mandatory dimensions we know and love (or know and hate) are no longer mandatory.  Don’t believe me?  See below.

Years?  Who needs ‘em?

Version?  Why bother?

Entity?  Only if you want to.

Even Period

I am going to use this and I will be sorry.  But it isn’t necessary.

Even that Pretty Blue Eyed Account dimension isn’t needed

And so it goes.

And on-premises?

Is this true for on-premises?  Not it is not.

And again.

It just isn’t fair.  #OnPremisesMatters but such are the fortunes of war.

The thing to take away from all of this is that PBCS is wide open:  What dimensions do you want to use?  What “required” ones do you want to reuse?  What functionality do you want to keep?  It’s all yours to decide.

Load ‘em up

I’ve shown in previous Compleat Idiot posts how to load metadata and data.  One of the joys (?) of PBCS is that its interface is in seemingly endless flux.  It does get better but if you aren’t constantly in the tool things get confusing.

I gave thought to (and took a bunch of screenshots for) giving you a screen-by-screen overview of this but given that it’s been covered and given that this post is closing in on 40 pages in MS Word and per the comment about the UI changing, I’m going to ask you to consider that as read and move on to new bits of functionality.

As review though, here are my NGOE->csv files->PBCS dimensions:

One oddity I found was that on importing dimensions and creating them is that PBCS doesn’t assign a default hierarchy type to dimensions or even subhierarchies when manually creating them.  This is odd.

And, if I’m replicating ASOSamp’s period to date calculations, fatal:

This, unfortunately, won’t be the last time I made this mistake and it is germane to this post.  It’s really easy to do when creating those small dimensions.  Stay tuned as I did it again, and again, and again.

Where’s my EAS?

It’s nowhere to be found, fortunately or not.  Obv. ASOSamp is so small it doesn’t really matter but typical practice is to have at least some of an ASO database aggregated through views.  Dan Pressman, I’m sorry.  Perhaps it’s my poor designs that require this?  

Given that there’s no EAS and for that matter no MaxL, how do aggregate views get applied?  Through Calculation Manager, sort of naturally.  

Database statistics

I can see that there are no aggregate views.

Calculating those aggregate views

There’s no command line way of doing this, but at least we can apply aggregate views.  In fact just about all of MaxL’s execute aggregate selection is available.

It’s nice to see that alternate hierarchies can be aggregated as well.

All done although of course for ASOSamp this is more than a bit of overkill.  Regardless it’s there for those real Essbase-via-PBCS applications.

This is all great, but…

I have now built ASOSamp, loaded data, and even applied aggregate views.  For the most part PBCS’ ASOSamp returns the right results.  Think about that last sentence for a second, reflect how many people say this in regards to EPM systems, and tremble.

Moving on past scary stories, this is a simple Essbase (see, I didn’t even think of writing PBCS) database and one that is being converted to boot, so this should be right first time round.  But it isn’t.  

See % of Total in Essbase:

And in PBCS:

Note the data everywhere else is wrong too.  It’s not right anywhere.  

Bugger.

Focusing on the specific aka one disaster at a time, the puzzling bit is that the formula is oh-so-simple, especially when I’m just copying it from Essbase to PBCS.

That’s what someone long ago and far away in Hyperion Solutions, Inc. once wrote.  I like to make my MDX a bit more formal.  Heaven knows I need the help.  Regardless, the functionality (and result) is the same.

The same thing in PBCS doesn’t work.  Why?

Time is a problem

The answer is right here.  And yes, that is PBCS’ ASOSamp in 11.1.2.4 on-premises Essbase.  I’ll get to that in just bit.

BegBalance is the villain

I unwisely chose to use PBCS’ native Period dimension.  Because I did that, BegBalance is the first member in the hierarchy and although I’ve written about how to modify the Period dimension in the past, getting rid of BegBalance or even moving it is impossible.  I’m keeping Time as a Multiple Hierarchies Enabled to be consistent with Essbase’s ASOSamp.  This is a mistake because Time in % of Total now comes across #Missing.   Life is complicated.

If MTD is what’s really needed (remember, there is no BegBalance in Essbase’s ASOSamp and that value is shown when Time is pulled because of Time’s Label Only property), simply change the formula:

Problem solved?  No.

It’s at least better as I now see data in % of Total but there’s still that bigger problem in that data at Income Level and Under 20,000 is the same for all of the other Measures.  Whoops.

What happened?  I goofed when I created Income Level by not tagging the dimension hierarchy type as Stored when it should be stored.  Goofus and Gallant know better.

I finally figured this out when I pulled PBCS’ outline back into EAS.  It’s much easier to see here than in PBCS’ interface.

What happens is:

1. In EAS, the default dimension type is Stored.
2. In PBCS, creating a dimension does not require selecting a hierarchy type.
3. When a dimension type isn’t selected, Multiple Hierarchies Enabled is selected.
4. When MHE is used, Income Level is a Label Only member.
5. When Income Level is a Label Only member, the first child, Under 20,000, is returned when Income Level is retrieved.
6. Whoops.

It’s not like creating dimensions happens all that often but make sure give it more thought than I did.

Success!

Once I had that sussed out, everything worked.

After 32 pages of text and screenshots, we have come to a stopping point of sorts.

A few thoughts:

1. Pushing an on-premises ASO application from on-premises Essbase to PBCS isn’t all that hard but as always the Devil’s in the details.
2. It’s difficult to benchmark this in a development environment.  Is PBCS faster than my VM on my laptop?  Absolutely.  Is it faster than an SSD, fast, 32 core server?  Almost certainly not.  Only you can decide if that matters or not.
3. Control over the Essbase environment as is common in on-premises, e.g. Essbase.cfg, location of tablespaces, etc. isn’t possible.  That’s per design but a bit jarring.
4. PBCS (and Planning) management isn’t nearly as seamless as EAS.  Such is life and EAS never was Planning’s (and PBCS’) goal.
5. PBCS doesn’t support really large dimensions that one might typically see in an ASO database or even the number of them.
6. Although I didn’t call this out, Planning’s Smart View ad hoc is painful compared to Essbase.  Sorry Charlie, but in speed, flexibility, formatting, the lot, Essbase’s connection is better by miles.  The good news is that Oracle knows this and is actively working on the issue.  The bad news is the issue has been around for the proverbial Donkey’s Years.  Only you can decide if working with a Planning connection in a pure ad hoc mode is worthwhile.

The important thing, at least as far as this blog post is concerned, is that we can have Essbase Cloud Services now.  I used the Lite application for the purpose of this blog.  In the real world, I’d use a Standard type and have just as many Plan Types I want including BSO as well as ASO ones.

We’re all waiting with various levels of anticipation (my level is DEFCON 1) for Essbase in the Cloud.  Hurry Oracle, hurry.

Be seeing you.

An addendum – PBCS Migration aka Shared Services LCM but better

My younger, smarter, taller, from completely different parents, brother Celvin Kattookaran noted that PBCS’ version of LCM can now export Essbase artifacts and when it does that it in essence writes out all of the Essbase objects be they load rules (yes, really), .otl files, and the data files themselves.  As always, Celvin says things worth listening to, especially when Yr. Obt. Svt. is trying to figure things out.

Here’s the contents of the Sample folder:

Once I have that, I can move the .otl file over to an Essbase instance.  In fact if I were ambitious I could likely move the .dat files over as well.  Interesting.

Note that I am not including the Planning, Calculation Manager, etc. artefacts to keep things simple.  This is, again, something that on-premises cannot do.

Pretty cool stuff.