Kscope15 -- The Essbase sessions I want to attend

What am I talking about?

Why Kscope15, of course.  Could there be any other conference?  Thought not.  So why am I writing this?

To bore you to death share with you the sessions, events, and activities that make up the very (really) best EPM conference anywhere.  It really is that good.

I should note that these are the sessions I’m interested in.  You may have a completely different take on what’s worth seeing but I’ll bet our tastes overlap.

I should also note that I will never get to attend most of these sessions because of all of the Oracle and ODTUG obligations I have at the conference.  But a man can dream, right?

And finally I should also note that this post will only cover Essbase as it’s just too long to put all of the other tracks together into one post.  Look for multiple posts on this during the week.

OMG, one last, last note.  Check out Ludovic de Paz’ post on things to do in South Florida

when you’re not being a sad, pathetic, but awfully keen Essbase geek.  I hear there is life beyond Kscope although I couldn’t tell you what that actually is like.  Perhaps Ludovic can help.

And with that, we’re off.

All of the Essbase sessions I would like to attend but probably won’t

I seem to make a living more off of Planning and (gasp) ODI, but I do get the odd “pure” Essbase (It’s an interesting choice of words, isn’t it?  I’m not the only one who refers to Essbase that way.  Does that mean that the other tools are adulterated?  But I digress yet again.) project from time to time and I really enjoy them.  I really enjoy the puzzle solving nature of “How do I analyze this” almost as much as the “How do I optimize this” part of Essbase projects.  

Here’s what I’m looking forward to:

Monday, 23 June 2015

Unappreciated and Maligned Core Essbase Features

George Cooper , Gap

Co-presenter(s): Doug Pearce, Analysis Team

When: Jun 22, 2015, Session 2, 9:45 am - 10:45 am

Anyone who can write, “What deranged person would do that?“, has to be a good presenter.  George has been around in the Essbase world seemingly forever and really knows his stuff.

Pushing Essbase to the Limit: A Load and Stress Testing Exercise

Tim German , Qubix

Co-presenter(s): John A Booth, Infratects

When: Jun 22, 2015, Session 4, 2:00 pm - 3:00 pm

My fellow co-authors Tim and John are bringing more rigor to Essbase, cf. George Cooper’s session.  Given that so much of what we do is rule of thumb, and how incredibly inadequate that can sometimes be, I am really looking forward to this session.  I should also note that this session touches on some of the topics Tim wrote about in Developing Essbase Applications:  Hybrid Techniques and Practices.

Drillbridge: The Easy and Free Way to Implement Relational Drillthrough

Jason Jones , Key Performance Ideas

When: Jun 22, 2015, Session 4, 2:00 pm - 3:00 pm

So cool  So very, very cool.  It’s drillthrough from Essbase to relational but without Studio.  So cool.

Essbase: Advanced Design Patterns from the Trenches

George Cooper , Gap

Co-presenter(s): Bill Kamal Reddy, Analysis Team

When: Jun 23, 2015, Session 8, 2:00 pm - 3:00 pm

This is another one by George.  I’m interested in design patterns (I am trying not to write crap code; this is one way to design nirvana.) and what George has on offer looks like interesting stuff.

Tuesday, 23 June 2015

The Vision for Essbase and How It Should Already Change How You Do Things

Gabby Rubin , Oracle Corporation

Co-presenter(s): William Bradstreet, Bradstreet Business Services, LLC

When: Jun 23, 2015, Session 5, 8:30 am - 9:30 am

Bill participated on a Hybrid Essbase OTN Archbeat podcast with me and a handful of the Best and Brightest on Hybrid Essbase; he has actually for real and truly implemented Hybrid at a customer.  AFAIK, he may be the first in the world.

Enjoy Better MDX for Financial Metrics: From Design to Online Help

Ted Hollenberg , Time Warner Cable

Co-presenter(s): Ron Moore, TopDown Consulting

When: Jun 23, 2015, Session 7, 11:15 am - 12:15 pm

Ted is a real, live, honest-to-God customer who really knows his stuff.  Ron is a long time Essbase consultant and instructor.  I cannot imagine not learning something (or an awful lot) from this session.

I Develop in Essbase: Why Do I Need to Know SQL?

Cameron Lackpour , CL Solve

Co-presenter(s): Glenn Schwartzberg, interRel Consulting

When: Jun 23, 2015, Session 8, 2:00 pm - 3:00 pm

Yr. obt. svt. is presenting this along with MMIC.  If you don’t know SQL and you use Essbase, you’re really missing out.  SQL is the Swiss army knife of corporate computing.  Glenn and I have put together a moderately advanced presentation with a lot of techniques and code.  It should be good stuff, despite my participation.

ASO, BSO, and Hybrid Calculations: What's Fastest, How Hard Is It, and Which One Should You Use?

Cameron Lackpour , CL Solve

Co-presenter(s): Tim German, Qubix

When: Jun 23, 2015, Session 9, 3:30 pm - 4:30 pm

I am very, very, very excited about this one.  Tim and I are going to both present on different techniques and (and this is even more exciting) provide a method for determining what should be selected based on objective measures; there will be a subjective feasibility component as well.  It’s actual real honest-to-God consulting.  In some ways it’s an extension of the evaluation engagements I sometimes now do.  I plan on using this approach with those customers.  Come to think of it, I’m going to use it on my own approaches as well.

Customer Panel: A Panoramic View of Exalytics

Gary Crisci , General Electric

When: Jun 23, 2015, Session 10, 4:45 pm - 5:45 pm

A real, live, breathing, red blooded, Exalytics customer.  What will Gary say?  Knowing him, not a single punch or praise will be pulled.  It should be both enlightening and entertaining.

Wednesday, 24 June 2015

Designer Aggregation Scripts for ASO: Make Them Fit Without Being Too Tight

Ted Hollenberg , Time Warner Cable

When: Jun 24, 2015, Session 11, 8:30 am - 9:30 am

Ibid.

Essbase New Features and Roadmap Update: 2015 Edition

Steve Liebermensch , Oracle Corporation

Co-presenter(s): Gabby Rubin, Oracle Corporation

When: Jun 24, 2015, Session 11, 8:30 am - 9:30 am

The future, as presented by the Oracle Essbase product manager.  Do you care about the future of Essbase?  You should.

The Essbase of Things: Essbase on the Cloud, Essbase with OBIEE, and What It All Means

Steve Liebermensch , Oracle Corporation

When: Jun 24, 2015, Session 13, 11:15 am - 12:15 pm

The future of the future, as presented by the Oracle Essbase product manager.  Do you care about the future of Essbase?  You should.  Again.

Automation of Focused Aggregations Creation

Dmitry Kryuk , Independent Consultant

When: Jun 24, 2015, Session 14, 1:45 pm - 2:45 pm

I’ve blogged about focused aggregations in Planning.  Would you believe analytical and algorithmic?  Again, more rigor applied to Essbase.  It looks like I need to up my game yet again.

BYO MDX Editor: BMGS (Become More Groovy-Savvy) in the Process

William Hodges , The Hackett Group

When: Jun 24, 2015, Session 14, 1:45 pm - 2:45 pm

William’s session is based on his chapter in DEA Hybrid (I am not going to type this out again and again and again).  This is a really cool concept and if you dabble or live in MDX and Excel, it is highly recommended.

Hybrid: We Have a New Essbase Engine, and Its Fuel Is Data

John Booth , Infratects

When: Jun 24, 2015, Session 16, 4:15 pm - 5:15 pm

Hybrid, Hybrid, Hybrid.  It’s almost like Hybrid is the future of Essbase.  It is, actually.  John always does excellent work and I’m super curious to see what’s come up with, particularly wrt Planning.

Exalytics - Creating the Best Platform for Essbase, EPM and BI

Gabby Rubin , Oracle Corporation

When: Jun 24, 2015, Session 15, 3:00 pm - 4:00 pm

There’s little chance of me ever working on an Exalytics project given my company size of exactly one, but what happens in Exalytics sooner or later mostly trickles down to standard Essbase.  

Why Didn't I Think of That? How Can I Do It in Essbase?

Glenn Schwartzberg , interRel Consulting

When: Jun 24, 2015, Session 14, 1:45 pm - 2:45 pm

Glenn aka MMIC always surprises me with the weird and wonderful things he knows about Essbase.  I think he’s even sadder of a geek than me, which is saying something.  I think that’s a positive attribute.  Maybe.

A Simple Solution to Performing Complex Eliminations in Planning and Essbase

Joseph Watkins , Huron Consulting Group

When: Jun 24, 2015, Session 16, 4:15 pm - 5:15 pm

Joe does some fascinating work.  I am super interested to see how he accomplishes it-can-only-be-done-in-HFM in Essbase itself.

Advanced Calculations in Calc Manager

Ron Moore , TopDown Consulting

When: Jun 24, 2015, Session 13, 11:15 am - 12:15 pm

I know that Ron has been working with Calculation Manager product management and development.  This ought to be super interesting and a chance for Essbase rather than Planning use cases.

Thursday, 25 June 2015

Thursday Deep Dive - Essbase: What's New in 11.1.2.4?

Cameron Lackpour , CL Solve

Co-presenter(s): Gabby Rubin, Oracle Corporation

When: Jun 25, 2015, Deep Dive Session , 9:00 am - 11:00 am

I swear this is the last of the lot that involves yr. obt. svt., which given that it’s the last session of the conference (sob) is pretty predicatable.  What, you’re not sticking around for the after party?  The good news is that I’m solely the interlocutor and Gabby Rubin, Steve Liebermensch, and MMIC Glenn Schwartzberg will be actual brains.  This is similar to what I did last year with the Planning Deep Dive.  I’m very excited about this opportunity to put the screws to Oracle product management and you should be too.  :)

And that’s the end of the Essbase sessions

If you read through the notes and see that many of the sessions I want to attend are simultaneously schedule, that is the nature of a conference only a few days long.  Happily, all sessions are recorded and Kscope attendees can view them at their leisure.

My next post either tomorrow or the day after will be on Planning.

Be seeing you.

Row-driven Focused Aggregations in Planning, finally

It couldn’t be done, but has

And apparently by someone quite a bit more inventive than me just playing around.  Who would have thunk it?  Not me.  In fact, quite explicitly not me, as I have told multiple clients (I think two, maybe three) that this very thing cannot be done.  Sigh.  But thanks to this thread on Network54, it most definitely has been done.  All thanks must go to Christian M. (who oh who oh who is Christian M.?  Christian, if you’re coming to Kscope14 I want to buy a beer.  Several, in fact.) as he figured this out.  So this is most definitely not something I have invented – I am just expanding on Christian’s discovery.

Why this matters

Yes, I know, I haven’t exactly spilled the beans on what this is, but let’s set some background so you can understand why this is so important.  

The technique that I call Focused Aggregations is a way to read a Planning form and only aggregate the bits of the hierarchy that are germane to the form.  Calc Mgr/HBR can read POV and dropdown values from Run Time Prompt (RTP) variables and “faking” the aggregation process (really, it’s just aggregating as if a CALC ALL or AGG (dimnames) was issued but it only does the aggregation for the relevant hierarchies all the way to the top of the dimension).   This approach is fast because instead of aggregating an entire Entity or Product or Employee or whatever dimension, much of which is not going to be relevant to the data entered on the form, only the hierarchies that matter get aggregated to the dimension top.  

This approach is fast, fast, fast (it isn’t magic, but only aggregating the bits you need versus an entire dimension can make “too big” BSO Planning applications perform acceptably) and I have written about it here in Hyperion Business Rules, Calculation Manager, and Dodeca.  The Dodeca post is (or was) an important one within the field of Focused Aggregations because focused row-based aggregations are possible as Dodeca can provide the row contents.  That’s what a Planning form cannot do.  Or at least it couldn’t, until now.  Hopefully you now understand why I am so excited about this.

Why Calculate Form isn’t good enough

Some (like Celvin Kattookaran, my coopetition on this subject) have suggested that maybe the in-built <Form Calculate> might do the same thing and then why oh why oh why would anyone bother with focused aggregations?  Here’s why this approach doesn’t make the grade and Celvin mostly hit on the reason why in his post.

A bit of review

Just in case you’ve forgotten what a <Form Calculate> Business Rule looks like, here it is in 11.1.2.3’s form designer.  Note that this is not a rule that you can look at (maybe it’s buried in the EAR file that makes up Hyperion Planning but it is not something you can casually examine).

Note that I’ve moved the <Calculate Form> rule over to the “Selected Business Rules” listbox and ticked the “Run on Save” box.

Going into Smart View, I can open up a form (note the dynamic user variable selection of E032 – that will become important a little later on), and enter in values of 100 and 200 into the January column.

When I click on Submit, I see the following in the form:

Very nice, right?  There’s 300 in E032.  Is this enough?  Nope.  

The problem with Form Calculate

The issue with Form Calculate is that it only aggregates the descendants of the topmost member in the form.  As E032 was the form’s top of the Entity dimension, all of the intermediate members were aggregated.  That is a Good Thing.  But what Form Calculate does not do is aggregate the ancestors of the topmost member in the form.  Note that E03 does not equal 400 (and it should).  And TotalGeography does not equal 1,173 (ditto).

Here’s what the totals ought to look like:

So unless you live in some weird Planning world where looking at top level numbers isn’t important, I’d say that Form Calculate is nice, but not enough.  Remember, for anyone looking at data above E032, it’s as if the Form Calculate didn’t happen.

The traditional way of aggregating the form dimension

It’s really simple:  

But remember, Entity could be a really big dimension with lots of lower level blocks and thus  pretty slow performance.  Are you sure you want to aggregate that entire dimension?

Enter Christian M.’s most awesome hack

What would be nice would be a way to read the dimension, or even more to the point the topmost form member and then aggregate the descendants (like Form Calculate) and the ancestors (which Form Calculate cannot do).  Until now, this is what could not be done.  

Christian’s hack still can’t read the Planning form’s row definition, but what it can do is read a Planning User Variable.  And if a form’s row definition is driven off of a Planning User Variable, then you have squared the circle because that Planning User Variable can (somehow) be read into Calculation Manager.  Huzzah!  Hurrah!  Hot diggity-dog, etc.

Here’s the technique in summary:

1. Create a Planning User Variable
2. Create a Planning form that uses the User Variable to drive the row contents
3. Create a Calculation Manager Variable that is explicitly assigned the Planning User Variable
1. The Calc Mgr Variable type must be Members, not Member, and no, I don’t know why, and yes, Members versus Member doesn’t make sense but why are we complaining about a great hack?
4. Use the Calc Mgr Variable in the standard Focused Aggregation code approach
5. Deploy the rule and assign it to run on save in the form.
6. Hope that Oracle doesn’t “fix” this hack.

Let’s now go through this in detail.

Create a Planning User Variable

Simply go to the Administration->Manage->Variables and create a new variable.  I like to sort of mnemonically name them, so my Entity dimension variable name will be varUVEntity.

Create a Planning form that uses a User Variable

I simply modified a form in the Planning sample application and in the row definition selected the descendants of the user variable:

The My Region variable comes with the sample application.  I don’t like spaces in variable names and I don’t like variables that aren’t immediately identifiable as such, hence the name “varUVEntity”.

I wanted the user to be able to change the row contents, so I made sure that the user variable was selected in the Other Options form design tab and I selected “Enable dynamic user variables”.  That last tick box means that the user can change the form rows as required so long as he stays within his Entity dimension metadata security.

So far all of this is bog-standard Planning.  We’re about to enter the undocumented hack area.

Create a Calculation Manager Variable that reflects the Planning User Variable

This is the bit where I really have to take my hat off to Christian.  I am pretty sure I would have come up with this approach in, oh, never.

Go into Calculation Managers Variable Designer and create a variable of type Members.  Assign (you are going to have to remember the name of your variable as it isn’t going to pop up in Calc Mgr) the name of the Planning User Variable to the Calc Mgr Members variable’s Default Value.  Be sure to prefix it with a “&” symbol.  And yes, that is sort of like an Essbase Substitution Variable, but isn’t.

You will also need to select a Dimension Name (in this case, Entity), make sure it’s a RTP type, and even enter in a RTP Text message.  Remember, it’s the Default Value with an ampersand before the Planning User Variable that does the passing of the Planning User Variable’s value to Calc Mgr.

NB – It seems logical to me to use a Calc Mgr variable type of “Member” instead of “Members” and in fact it works, but when using that variable type a web browser will force a dialog box.  Oddly, Smart View does not do this.  

Don’t try to find reason where I suspect none exists – none of this makes any sense.  I’ve done the pig headedness bit for everyone, so just go with Members.

Write a Focused Aggregation Business Rule in Calculation Manager

Calc Mgr script

Please disregard the lack of variables in the FIX statement – you can add that in later.  The important bits are the @IDESCENDANTS and @ANCESTORS statements.  

In the first sub FIX statement, the code fixes on a Calc Mgr variable called varSegments.  This is just a standard RTP Calc Mgr variable.

What’s inside the FIX({varSegments}) is what’s important and heretofore impossible.  The code does an @IDESCENDANTS of the Planning user variable varUVEntity as passed to the Calc Mgr variable varBRUVEntity to mimic the Form Calculate functionality.  The next line issues an @ANCESTOR calculation of that Planning User Variable via a Calc Mgr variable to aggregate up to the top of the dimension.  Ta da!  We have just hit the super cool hack.

The second FIX statement uses that same Planning User Variable as expressed in a Calc Mgr Variable but now selects the relevant bits of the Entity hierarchy and then does an @ANCESTORS aggregation of the Page dimension Segments.

Double Ta-Da!  We have just squared the row-based Focused Aggregation circle.

Calc Mgr rule

Stick the script into a rule by dragging and dropping it into the rule itself.  

NB – If you do not have Flash 10.x installed on your machine (in my case, a Windows 2008 R2 VM) you won’t be able to do this.


When you do this, be sure to go to the Variables tab and tick the “Is Hidden” boxes for the variables.  This will ensure that neither RTP pops up when the rule is run on form save.

If you click on the Script tab you will see something kind of interesting:

Instead of varBRUVEntity the default value, &varUVEntity, which does look awfully Essbase Substitution Variable-ish, shows up.  

If in fact you were to go to the Errors & Warnings tab and have Calc Mgr analyze the script, you would see this bogus warning:

Interesting, isn’t it?  But it still works.

Deploy the rule

But don’t validate

As Celvin pointed out in his blog post, don’t bother trying to validate the rule.  That error message in the Errors & Warnings tab will surface:

So just deploy.  And fix the code yourself.  C’mon, if you’re writing code like this you can do syntax checking in your head, right?

Deploying is easy

Hook it up to the form

We are back to bog standard Planning.  Simply attach the deployed Calc Mgr rule to the form:

As always, make sure it runs on save, uses the members on the form to drive the POV/Page dimensions, and hide all RTP prompts.  That’s it.  Let’s now go prove that it works.

Hmmm, I love pudding

The proof of the pudding is in the eating.  

Of course we cannot eat a Planning form, but we can:

1. Change a dynamic user variable
2. Enter in data values
3. Save the form
4. Prove that it aggregates all the way up to the tippy-top of the dimensions

Let’s do just that.

Change a dynamic user variable

Select E01_101 as an Entity member.

Clicking on OK and then doing a form refresh will change the form selection:

Enter values and Submit Data



Review the fully aggregated data


And here’s the data aggregated all the way up to the top of the hierarchy in an Essbase ad-hoc retrieval:

Magic.  Thanks again, Christian.

So why is this faster?

It’s all in the amount of data that Essbase has to slog through in its aggregation.

I turned SET MSG DETAIL on in the script (and got rid of the CALC DIM of Accounts and Period as those should be fully dynamic – ask Oracle why the sample app isn’t set up that way, not me):

[Tue Oct 15 12:10:53 2013]Local/SampApp1/Consol/hypadmin@Native Directory/1792/Info(1012672)

Calculator Information Message:

Total Block Created: [0.0000e+000] Blocks

Sparse Calculations: [2.2500e+002] Writes and [8.5500e+002] Reads

Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads

Sparse Calculations: [1.0303e+006] Cells

Dense Calculations: [0.0000e+000] Cells

[Tue Oct 15 12:10:53 2013]Local/SampApp1/Consol/hypadmin@Native Directory/1792/Info(1012550)

Total Calc Elapsed Time : [0.059] seconds

I then created a calc script that used an AGG for Entity and Segments:

And ran it with the following results:

[Tue Oct 15 12:11:38 2013]Local/SampApp1/Consol/hypadmin@Native Directory/360/Info(1012672)

Calculator Information Message:

Total Block Created: [0.0000e+000] Blocks

Sparse Calculations: [2.8690e+003] Writes and [1.0772e+004] Reads

Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads

Sparse Calculations: [1.3137e+007] Cells

Dense Calculations: [0.0000e+000] Cells

[Tue Oct 15 12:11:38 2013]Local/SampApp1/Consol/hypadmin@Native Directory/360/Info(1012579)

Total Calc Elapsed Time for [BRAggTst.csc] : [0.434] seconds

Some analysis

Agg	Focused	Variance	Variance Percent
0.434 seconds	0.059 seconds	-0.375	-86.406%

Nothing to sneeze at here – that’s a decrease of 86% in the amount of time the calculation took to run.  Worthwhile when applied to your real world application?  I think so.

Let’s look at the number of transactions.

Approach	Sprase writes and reads	Sparse cells addressed
AGG of Entity and Segments	2,869 writes, 10,722 reads	1,313,700
Planning User Variable Focused Aggregation	225 writes, 855 reads	103,030

There’s an enormous difference in the number of writes, reads, and overall sparse cells.  No wonder a Focused Aggregation is so much faster.

Where do we go from here?

Well, as far as this blog post is concerned, hopefully nowhere.  This is quite the post (19 pages in MS Word which is where I draft all of my blogs – that’s quite enough of the Cameron Effect for one day) but I think it was important to showcase how absolutely brilliant Christian’s hack is, and how important it can be for the Focused Aggregation approach which can now finally apply that technique to rows.

I am going to reach out to the Calculation Manager Product Manager (Sadly, I really only have the emails to a couple of people within Oracle – I lay that at the feet of being an independent with just about zero impact on sales.  Or maybe I am just lazy and don’t work the contacts the way others do.) and beg, beg, beg that this functionality not be removed.  Ever.  In fact, it would be super if this functionality was formally incorporated into the tool so we didn’t have to play games with default values and ampersands.  I am going to guess most of the functionality is already there within Calc Mgr so I am hoping we’ll see it soon, officially supported.

One last time – Christian, thanks man.  You have no idea how happy this has made me.

Be seeing you.