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.

An even better focused aggregation with Dodeca

The obligatory disclaimer

I gave a presentation on this very subject at the Hyperion SIG Open Microphone Night.  I am not entirely sure that a formal (well, informal, but it was PowerPoint) presentation is 100% a spontaneous thing, but they asked, I double-checked, they agreed, and so it was.  

The disclaimer?  I am a cheerleader for Dodeca, but not a salesman, not an employee, not a shareholder, etc., etc., etc.  Here’s the who-is-this-nut-in-front-of-the-room graphic for those who doubt.

Also, for the record, there were alcoholic drinks there but there was also…chocolate milk and regular milk.  2% and skim.  And cookies.  Yum.  It is somewhat incongruous to admit this at my age, but I really like the taste of milk.  I like mixing chocolate and regular milk (so I guess I like watered down chocolate milk) even more.  What an awesome idea for snacks.

Back to Dodeca – I like the product, a lot, I don’t do very much with it (if I could sell it, I would, but I can’t very much so I don’t), I do consider Tim Tow both a friend and a mentor, and that’s it.  If this post reads like an advertisement, it isn’t.  It’s just what the very last bullet point says it is – an interesting way to make processing faster.

Whoops, one more thing.  It would be awesome if Planning could pass the contents of the rows and columns to Calculation Manager.  Awesome.  As you will see if you but continue reading on, gentle reader.   

The good and the bad about focused aggregations

In Planning-land, focused aggregations off from forms, at least in the BSO Planning world almost all of us still live in, is the only way to fly.  You can read all about it here in detail.

Very, very briefly, the big win is only to calculate the ancestor blocks that are affected by the form.  In other words, instead of aggregating an entire dimension, including lots and lots of hierarchies that are not affected by the form input, just aggregate the hierarchies that matter.  It’s that easy.

As an example, let’s take a look at the Planning sample application and compare the calculation time of a CALC ALL (not something you are likely to do, but still a valid data point), an AGG of just the two sparse dimensions, and then of course a partially focused aggregation.  Remember, this is with Entity in the form row and Segments in the page dimension so Calculation Manager can read Segment via a Run Time Prompt variables but not Entity.

Approach	Seconds	Percent
Calc All	5.766	3696%
Agg of Entity & Segment	0.375	240%
Agg Entity and focused Segment	0.156

The blocks tell the tale

When we look at how many reads and writes occur, and how many cells are touched via a SET MSG DETAIL statement, it’s easy to see why a focused aggregation is so fast – it simply touches fewer data points.  To quote Ludwig Mies van der Rohe, “Less is more”.

Approach	Sparse writes and reads	Sparse cells addressed
CALC ALL	22,236 writes 107,570 reads	101,820,000
Agg of Entity and Segment	2,869 writes 10772 reads	12,592,000
Agg of Entity, focused Segment	888  writes 2,960 reads	3,897,400

Last bit of review

The code looks like ugly but this is how BSO Essbase walks the hierarchies when it does an AGG of sparse dimensions.  Weird, it’s true, but it works.

That was the good, here’s the bad

But focused aggregations in Planning have a problem – while they can read the point of view dimensions and drop down or page dimensions, whatever dimensions are on the sheet, and those dimensions’ members are terra incognita to Calculation Manager.  

Why care about row and column dimensions?  If the dimensions are dense, and if I’ve designed the Planning app, I don’t care – everything is dynamic.  But if the dimensions are sparse (and if I’ve designed the Planning app I will have fought this tooth and nail but sometimes that’s just the way people interact with data), I do care because that means any aggregations off of the form will require a full AGG.  Not so focused, is it?

What oh what to do?  In Planning – there is not a blessed thing.  However, what if the application in question was Dodeca?  

What Dodeca can do

The problem with the focused aggregation approach in Planning is as I stated – I don’t know what the sparse dimensions on the sheet are, nor do I know the scope of the member selections.

If I did know that, why then I could write focused aggregations up to the top of the dimension and down to any subtotals.  In other words, a super focused aggregation with hopefully super fast results.  How oh how do we do this?  It’s actually quite easy.

Dynamic Rowset to the rescue

All I need to do is modify the approach I wrote about back in December of 2012 on how to do a dynamic rowset report in Dodeca and change the database from Sample.Basic to the Planning sample application.  And once I do that, I then need to take advantage of the selected Entity and insert it into my Dodeca-specific calc.

To review:  a Dyanmic Rowset report takes a user selecting in a dimension, figures out what the descendants (or children or siblings or whatever) are, and then sticks all of them on a sheet via an Essbase report scritpt, MDX query, or delimted list.  Dodeca does all of this through something called a Workbook Script – sort of Dodeca’s enlightened approach to coding.

Set up a few ranges on the sheet to indicate where the retrieve range is and where to insert rows, and then “code” by modifying the below Workbook Script that fires when the workbook itself opens up, but before it retrieves.

1. BuildRangeFromScript – I chose the the EssbaseReportScript type; there are many ways of doing this including MDX (cannot be seen because this is an older release):

2. ScriptText – Code to actually build the rows.  Tokenized to push the value of the select from the dimension treeview.

3. StartCell – Range name of the repeated rows that are tied to the output from ScriptText.  This is the green range.
4. Rows – This report has dynamic rows; it could just as easily be columns.
5. EnterNumbersAsText – Just in case member names such as 100 are used, treat them as text.
6. CopyFromRange – The name of the range to be repeated.  This is the blue range.
7. Insert – Set to TRUE as I want the output of ScriptText to be reflected in the sheet.
8. OutputRangeName – The name of the rows that are built during the insert process.
9. OutputMap – The column that receives the output of ScriptText.

When all of that is built, and the rows are placed onto the sheet, Dodeca does its retrieve.  Magical, isn’t it?

A short note about tokens

Do you like Planning’s Run Time Prompt variables because of their utility in Calculation Manager?  If so, you will really like Dodeca’s tokens because they are even more powerful.  Tokens work in report scripts,  calc scripts (just like RTP variables in Calculation Manager), and they work in retrieves as well.  

You saw above how a token with a Workbook Script function to force the valuation of the token worked in getting the content’s of the rows

Within a report (aka an Excel sheet) before:

And after in Dodeca:

And in the calc script itself:

Did you catch the clever cannot-be-done-anywhere-else bit?

It’s as easy as 1, 2, 3.

1 – After fixing on whatever the Segments dimension member is, calculate the inclusive descendants of the Entiy dimension that drives the rows.

2 – Within that same FIX, calculate the ancestors of the Entity dimension.

3 – Then FIX on the inclusive descendants and the ancestors of the Entity dimension that drives the rows and aggregate the ancestors of the Segments dimension.

We’re done.  It was easy, and awesome.

What’s the payoff?

Time

The payoff is speed, a lot of it.  Let’s review the speed again, but now with the above row-based focused aggregation.  The difference is dramatic.  The Dodeca focused aggregation is 93 times as fast as a Calc All, 6 times as fast a dimension aggregation, and 2.5 times as fast as a partial focused aggregation.  Would you like to speed up your budgeting aggregations by a factor of 6?  Or 2.5?  You bet you would.  And you can, quite easily.

Approach	Seconds	Percent
Calc All	5.766	9300%
Agg of Entity & Segment	0.375	605%
Agg Entity and focused Segment	0.156	252%
Dodeca agg of focused Entity and Segment	0.062

Blocks

We can look at time or we can look at the blocks.  If time = money, then time also = blocks.  The fewer are most definitely the better.  The percentage improvements in sparse cells touched by the aggregations mirror the time pretty closely.  

Approach	Sparse writes and reads	Sparse cells addressed	Percent
CALC ALL	22,236 writes 107,570 reads	101,820,000	7733%
Agg of Entity and Segment	2,869 writes 10772 reads	12,592,000	956%
Agg of Entity, focused Segment	888  writes 2,960 reads	3,897,400	296%
Dodeca focused Entity and Segments	300 writes 1,000 reads	1,316,700

Takeaways are not necessarily fish ‘n chips

1 – Focused aggregations are good because they can make your aggregations quick.

2 – Planning can’t give you the rowset for a sparse aggregation although it’s way better than nothing.

3 – Dodeca can give you the rowset for a sparse aggregation through a combination of Workbook Scripts and Tokens.

Try it, you’ll like it.  :)