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.

Two Calc Man and ASO Essbase webinars in one

Use Calc Man and ASO?  Then you should watch us

Ludovic de Paz, Paul Hoch, and I are combining ASO Calculation Manager presentations tomorrow, 7 October, from 12:00 pm to 1:00 pm, Eastern.

What you’ll see is a fairly rare multiple consulting company (Ludovic and Paul work for TopDown Consulting, I work for me) webinar.  I like to think I inspire an ecumenical atmosphere amongst competing firms but probably I am a threat to no one, so companies allow their consultants to work with me.  See, weakness can be strength.

What you’ll also see, and why you’ll care about this webinar, is the distillation of our respective Kscope14 presentations on how to get the most out of Calc Man and ASO databases.  Ludovic and Paul presented on this at Kscope14 (alas, I got to attend exactly two presentations that weren’t my own and I think one of my sessions coincided with theirs),  the bit I’m presenting is a part of the ASO Planning: Don’t Do That, Do This presentation I gave with Tim German of Qubix International at Kscope14.

The really interesting stuff

As I read through Ludovic and Paul’s slides, I realized that they came up:

• A good overview of Calc Man and ASO Essbase
• A review of what’s right and wrong with Calc Man and ASO Essbase
• A really cool hack to run Calc Man rules to get round these problems

I’m contributing my hack (what good is a presentation if you don’t get a few completely unsupported yet effective hacks?) on getting round the non-empty issue with ASO calculations.  I should mention that I learnt about this from Joe Watkins although it turns out that Steve Liebermensch has been spreading this technique since the Year Dot.  Which I have apparently missed since the Year Dot.

I have high hopes for a guest blog by Ludovic and Paul as they combine their hack with mine to really and truly get round ASO procedural calc issues.  I think it will be like peanut butter and chocolate. This webinar won't be half-bad either.

And how to get it

Click on the description below, click right here, just click and sign up.

Be seeing you tomorrow, 12 pm Eastern.

Calculation Manager, BSO Planning, and ASO Planning combine for an awesome ASO Essbase procedural calculation hack -- Part 2

Introduction

This is part two of a three part series on Calculation Manager, ASO Planning, its relationship with BSO Planning, and Essbase ASO procedural calculations.  Part one covered all sorts of interesting things you can do with, oddly enough, BSO + Calculation Manager.  There is a reason for introducing that first, and then this post on making ASO procedural calculations fast.  I’d encourage you to stick around for part three which ties them both together in a most unusual way.  With that, let’s get into the details of making ASO procedural calcs fast, fast, fast.

Joe Watkins’ genius

How often do you get called genius?  If you’re anything like yr. obt. svt. it isn’t all that regular of an occurrence unless that term is within the context of, “You have a genius for screwing things up” or “You’re a genius at prevarication” or “I’ve met geniuses and you are no genius”.  

Joe Watkins (with whom I have only ever “met” via email and Network54) is a genius, at least when it comes to ASO Essbase, because the approach Joe came up with is…genius.

Why do I use the word genius?  Simply because:

1. His approach is 100% undocumented.
2. It is not an intuitive solution at first glance, but on examination it is not only obvious, it’s !@#$ing awesome.
3. It solves a problem you could drive an HGV double-articulated lorry through.
4. It is fast, fast, fast, fast.
5. It is a total hack.

And oh yes, it is part two of my three part series on Calculation Manager, BSO Planning, ASO Planning, and ASO Essbase procedural calculations.

While this blog post stands on its own for Essbase-only practitioners for the technique alone,  I will explain why you will at least want to combine it with the CDF information I gave in part one even if the words “Hyperion Planning” never cross your lips.  Hyperion Planning geeks will have to read all three parts to get all of this hack (and yes, I contributed something to this, so it isn’t all a case of steal from others to get a good solution).

The problem(s) with ASO Essbase procedural calcs

It’s really very simple and very devastating at the same time – ASO procedural calculations do not ignore empty level zero member intersections but instead consider all of them.  Where’s there constituent data, the Essbase values the result; where there’s none, Essbase leaves the result blank.  For us BSO geeks, this is 100% not the way BSO Essbase works by default; in BSO, no blocks = no result unless we force Essbase to do so.  If only there were a way to make ASO Essbase behave the same way…

What this ASO Essbase behavior means is that procedural calculations, unless they are very tightly constrained in scope, can be agonizingly slow.  And even that tight targeting of the calculations can be a roadblock – do you always know where in a database a calculation should occur?  Maybe you could write a series of small scope calcs in a reporting application, but that would be very difficult to do if there is an element of budgeting to the application.

And in fact, I’ve understated the problem – even in a pretty small ASO database a procedural calculation can take a very, very, very long time.  Proof?  Read on.

The database

I stumbled (as is my usual wont) into this as part of a presentation.  I was trying to write a currency conversion calc to mimic, sort of, what happens in BSO Planning as part of an ASO Planning Kscope14 presentation I gave with Tim German.  I should also mention that Dan Pressman was a big help in the building of the dimensions.

The dimensions

By ASO standards it isn’t much.  Of course, by the lights of BSO, it’s huge – more on that in a moment.

The logic

Oddly, ASO Planning does not create all of the dimensions required for a multiple currency database.  They expect you to do so.  No problem, I thought, I’ll simply create similar dimensions to what exists in BSO Planning and go from there.  

Dimensions

Here’s how the fx relevant dimensions look when compared to a standard Planning-derived BSO fx plan type:

BSO	ASO
HSP_Rates	Fx Rates
Account (HSP_RateType and children)	Account (Fx Rate Type and children)
Currency	Currency
Product (Entity)	Product (Entity)
N/A	Analytic

For you Planning geeks, the Product dimension is the Entity dimension, Currency was automatically created by Planning (although corresponding fx conversion logic was not), and Analytic, and Fx Rates are custom dimensions I created to support ASO fx.

Account

Accounts stored the difference between Average and Ending rates.  This is just like BSO Planning.

FX Rates

I don’t have a HSP_Rates dimension but this is the same thing, mostly.

Currency

This dimension came directly from Planning itself.

Product (Entity)

The concept of tagging the members with UDAs is the same as BSO Planning.

BSO code

Want the code?  Create a multicurrency Planning app and then have Planning generate the calc script.  I’m just showing the screen shot to give you a feel for the logic.

All that the code is doing is:

1. FIXing on level zero members
2. Testing for UDAs assigned to the Entity dimension
3. Multiplying the Local input value by the fx rate / by the USD rate (which is always 1)

As this is BSO, after the fx conversion, an aggregation is needed but not shown.  Of course ASO won’t require that aggregation code as it does that aggregation on the fly.

ASO first attempt

I wrote this in Calculation Manager (remember, I was trying to do this for Planning) but the logic is exactly the same in MaxL.

Execute calculation

Note the SourceRegion – in this case it’s all individual members because I was trying to calculate just one rate.  I would at least have to open up the fx member set if I were to calculate more than one.

fxtest.csc

This is a one line currency conversion formula.  

This whole approach is ugly and not easily exapandable, but it serves to illustrate an almost literal translation from BSO to ASO logic.

Success, if you can call it that.  

After entering the rates (sent from a Smart View ad-hoc sheet into the Essbase database as ASO Planning doesn’t push rates the way BSO Planning does), I entered one data value to be converted from Sterling to Dollars as per the above code.  That is one as in a whole number, greater than zero, but less than two.  How long do you think it took to run?  A second?  Half a second?  Something else?

How about 6,000 seconds?  Let me repeat that in words, not numbers: six thousand seconds or sixty hundred seconds or one and two thirds hours.  To convert one data value.  See the problem with ASO procedural calculations?

How long did the same database (just about) with the same amount of data take to run in BSO?  The total elapsed calculation time was 0.025 seconds.  So much for the might power of ASO compared to poor old obsolete BSO.

The fix

The key to BSO’s speed is that BSO does not consider all of the possible level zero member intersections, it only considers the sparse member combinations that have data.  In ASO terms, it only calculates based on the non-empty member intersections.  There are NONEMPTYTUPLE and NONEMPTYMEMBER commands in MDX but unfortunately they are not part of the execute calculation and execute allocation (the two and only two ways to run ASO procedural calculations) grammar.

NB – Oracle say this is coming to Essbase but when is tbd.  That will be (some day) great for those of us who are on the latest release, not so much for everyone on 11.2.3.500 and before.

So how can we get NONEMPTY functionality in ASO if it’s not part of the commands?  Enter, finally, Joe Watkins’ technique again.

The problem with NONEMPTYTUPLE

NONEMPTYTUPLE (I used that instead of NONEMPTYMEMBER) can only be used in an outline member formula.  Member formulas are (quite naturally) in the outline.  The member formula fires at all levels, and in the case of an fx rate calculation, is only actually valid at level zero.

This is a bit of an impasse – we know the problem with procedural calcs is the NONEMPTY issue, fx rate calculations only make sense at level zero, MDX has a keyword to address this, but only in member formulas and member formulas fire at all levels, not just level zero.  What to do?

Back to the Genius of Joe Watkins

Instead of trying to fight Essbase, Joe came up with a really clever way of using existing ASO functionality.  I read about his approach to fx over on Network54 in the beginning of 2013 and, since I was doing BSO Planning (that’s all there was) at the time, filed it away for future reference.  I also thought he was nuts for saying things like, “This is the future of ASO.. BSO will be dead in 5 years.. (my prediction)....”  Now I’m not so sure.

What he did

Joe:

1. Created a member formula that contained his fx logic
2. Stuck a NONEMPTYTUPLE keyword at the top of the formula
3. Ran an ASO procedural allocation (not calculation) that 100% copied his member formula to a stored member thus harnessing ASO’s fast non empty performance but keeping the data calculated only at level zero
4. Enjoyed success, the good life, and that warm glow inside that only comes from helping others

I may be slightly exaggerating number four, but one through three are the truth.

NB – The example here is a fx calculation, but this approach works for any and all level zero calculations.

Here’s how I did it

Additional member

In the Analytic dimension, I created a calculate-only member called MTD USA.  It contains the member formula to calculate fx conversion.

MTD USA’s member formula

Note the NONEMPTYTUPLE command that makes the member formula only address non empty data.

The CASE statement is a MDX version of the BSO currency conversion calc script.

Execute allocation

It’s all pretty simple from here on, thanks to Joe.  All I need to do is kick off an execute allocation in MaxL, set up my pov aka my FIX statement, identify the source (Local) and target (USD).  By not defining a spread range other than USD, Essbase copies everything from MTD USA in Local to MTD in USD.

Did you see the $5, $6, and $7 in the code?  If it’s MaxL, it can be driven through parameter variables.  Think about how you might use that in Planning given the last post’s review of @CalcMgrExecuteEncryptMaxLFile.

How fast is it?

On my VM with a limited set of data (I have finally ordered that 1 TB SSD but have yet to install it so I am constrained for space) I observed the following calculation times:

Process	BSO	ASO	X Fast
Allocate	106	3	35
Fx	400	1.2	333
Aggregate	1,772	N/A	N/A
Total	2,278	4.2	542

The allocate and the aggregate times are interesting, but the biggest overall difference is in fx – it’s over 300 times as fast as the equivalent BSO outline and data.  Look at that, ASO is faster than BSO, if it only considers non empty data.  

And now, thanks to Joe’s technique, it can.  A hack, most assuredly, but a glorious one.  I have an upcoming ASO budgeting application that I was dreading because I couldn’t figure out how to quickly do its rate calculations (no fx involved).  Now I know how to do it, and quickly.

This technique is in a word, awesome.  Yeah, I take some stick for overusing that word, but 300 times the speed of BSO is sort of remarkable.  All of us who use ASO procedural calcs owe Joe a huge round of thanks.

So what’s left?

Part three of this series will bring together:

1. Running MaxL from BSO while in an ASO Planning application with Calculation Manager
2. The fast ASO procedural calcs you just read about
3. How to use this in Planning (and even Essbase)

I know it’s all been a bit long but there’s a lot of information to impart and it took me freaking forever to figure out how to tie it all together – there’s no reason to see why explaining it should be any faster.  

:)

Be seeing you.