An Essbase ASO procedural calculation too screwy to be true

But it is

Tim German and I presented at Kscope14 on ASO Planning.  As part of the use case for that presentation, I wrote code that mimicked BSO Planning’s fx (I seem to have currency conversion on the brain, cf. my last post).  You can read all about the power of ASO procedural allocations here:  Calculation Manager, BSO Planning, and ASO Planning combine for an awesome ASO Essbase procedural calculation hack -- Part 3.

What I didn’t cover in that presentation is something I don’t really understand (although I have high hopes that this blog post will spur explanations):  increasing the scope of an ASO procedural execute allocation slows down the calc (so this is pretty self-evident), but decreasing the scope (so far, this makes sense) and and then combining the multiple procedural allocations speeds up the calc (so not quite so self-evident).

There are Doubting Thomases out there, and I completely understand their skepticism given how odd this finding is.  I too was amazed, and would be equally doubtful given the claim.  I’m not from Missouri, but I completely believe in proving what I state.

The numbers

Given the same database, same data, and same general code with the only difference being the range of the Accounts dimension within the execute allocation POV, I get the following times.

Split code line

Single code line

The analysis

Just in case you aren’t following this, that’s the same set of data at 111,222 cells but 32.944 seconds for three execute allocations in a row versus 134.581 seconds.  That’s a difference of 101.637 seconds.  The repeated code is four times as fast as the single code line.  Weird, eh?

The code

For those of you who don’t believe me (and hey, why would you?), here are the logs straight from MaxL:

Split code line

MAXL> execute allocation process on database T3_ASO.T3_ASO with

  2> pov

  3> "CROSSJOIN( {[FY07]},

  4> CROSSJOIN( {[Final]},

  5> CROSSJOIN( {([Actual])},

  6> CROSSJOIN( {([No fx])},

  7> CROSSJOIN( Descendants( PERIOD, PERIOD.Levels(0)),

  8> CROSSJOIN( { (Descendants( [Net Income], ACCOUNT.Levels(0)))},

  9> CROSSJOIN( Descendants( Product, Product.Levels(0)),

 10> ( Descendants( PostCode, PostCode.Levels(0)) ) ) ))))))"

 11> amount "([MTD USA])"

 12> amountcontext "([Local])"

 13> target "([MTD])"

 14> range "{([USD])}"

 15> spread;

OK/INFO - 1300006 - Essbase generated [61523] cells.

OK/INFO - 1013374 - The elapsed time of the allocation is [2.197] seconds.

OK/INFO - 1241188 - ASO Allocation Completed on Database ['T3_ASO'.'T3_ASO'].

     essmsh timestamp: Wed Oct 22 07:39:25 2014

Assets

     essmsh timestamp: Wed Oct 22 07:39:25 2014

MAXL> execute allocation process on database T3_ASO.T3_ASO with

  2> pov

  3> "CROSSJOIN( {[FY07]},

  4> CROSSJOIN( {[Final]},

  5> CROSSJOIN( {([Actual])},

  6> CROSSJOIN( {([No fx])},

  7> CROSSJOIN( Descendants( PERIOD, PERIOD.Levels(0)),

  8> CROSSJOIN( { (Descendants( [Assets], ACCOUNT.Levels(0)))},

  9> CROSSJOIN( Descendants( Product, Product.Levels(0)),

 10> ( Descendants( PostCode, PostCode.Levels(0)) ) ) ))))))"

 11> amount "([MTD USA])"

 12> amountcontext "([Local])"

 13> target "([MTD])"

 14> range "{([USD])}"

 15> spread;

OK/INFO - 1300006 - Essbase generated [23466] cells.

OK/INFO - 1013374 - The elapsed time of the allocation is [14.45] seconds.

OK/INFO - 1241188 - ASO Allocation Completed on Database ['T3_ASO'.'T3_ASO'].

     essmsh timestamp: Wed Oct 22 07:39:40 2014

Liabilities

     essmsh timestamp: Wed Oct 22 07:39:40 2014

MAXL> execute allocation process on database T3_ASO.T3_ASO with

  2> pov

  3> "CROSSJOIN( {[FY07]},

  4> CROSSJOIN( {[Final]},

  5> CROSSJOIN( {([Actual])},

  6> CROSSJOIN( {([No fx])},

  7> CROSSJOIN( Descendants( PERIOD, PERIOD.Levels(0)),

  8> CROSSJOIN( { (Descendants( [Liabilities], ACCOUNT.Levels(0)))},

  9> CROSSJOIN( Descendants( Product, Product.Levels(0)),

 10> ( Descendants( PostCode, PostCode.Levels(0)) ) ) ))))))"

 11> amount "([MTD USA])"

 12> amountcontext "([Local])"

 13> target "([MTD])"

 14> range "{([USD])}"

 15> spread;

OK/INFO - 1300006 - Essbase generated [26133] cells.

OK/INFO - 1013374 - The elapsed time of the allocation is [16.297] seconds.

OK/INFO - 1241188 - ASO Allocation Completed on Database ['T3_ASO'.'T3_ASO'].

Single code line

MAXL> execute allocation process on database T3_ASO.T3_ASO with

  2> pov

  3> "CROSSJOIN( {[FY07]},

  4> CROSSJOIN( {[Final]},

  5> CROSSJOIN( {([Actual])},

  6> CROSSJOIN( {([No fx])},

  7> CROSSJOIN( Descendants( PERIOD, PERIOD.Levels(0)),

  8> CROSSJOIN( { (Descendants( [Net Income], ACCOUNT.Levels(0))), (Descendants

( [Assets], ACCOUNT.Levels(0))), (Descendants( [Liabilities], ACCOUNT.Levels(0))

)},

  9> CROSSJOIN( Descendants( Product, Product.Levels(0)),

 10> ( Descendants( PostCode, PostCode.Levels(0)) ) ) ))))))"

 11> amount "([MTD USA])"

 12> amountcontext "([Local])"

 13> target "([MTD])"

 14> range "{([USD])}"

 15> spread;

OK/INFO - 1300006 - Essbase generated [111122] cells.

OK/INFO - 1013374 - The elapsed time of the allocation is [133.581] seconds.

OK/INFO - 1241188 - ASO Allocation Completed on Database ['T3_ASO'.'T3_ASO'].

Conclusion from the data

I have a few, although they are not satisfying:

1. Those results are freaking weird.
2. Something is going on within ASO Essbase that makes the multiple code lines faster.
3. I wish I was smart enough to know the answer to point number two.
4. Someone will be smart and knowledgeable enough to figure this out.
5. General rejoicing will occur on the completion of point number four.  I will cheer the loudest.

A plea for help

I’m not enough of a scientist to delve into the why (I do try, somewhat, to have a life) nor am I smart enough to figure it out.  Dan Pressman is working on this and he is way smarter, and even more obsessive than me, so we all stand a very good chance of finding out why this is so.

Dan did address this in the Network54 thread, and wrote:

The allocation POV is required to be a symmetrical area of the cube. I know this because I tried some tricks with filters and nonemptytuple using <dimension>.currentmember. To understand this suppose I had a cube with the dimensions FloraOrFauna and Species (among others). Well we know that there will never be tuples such as (Flora, Canine) or (Fauna, ChristmasTree).

Looking at the whole we can not eliminate Canines just on the Flora side because that would be non-symmetrical. That is what using leaves and nonemptytuple is faced with. However if we split the allocation into a Flora allocation and a Flora allocation then we are ok.

 
I believe that Dan’s explanation correctly states that fewer and smaller allocation POV ranges are faster than large ones.  

What I do not understand (but am waiting with bated breath for) is an explanation as to why multiple small POVs that when combined equal the size of the full POV set are faster than a single full POV definition.  As you can see from my statistics, the number of addressed cells is the same.

It just don’t make sense.

I look forward to the explanation.  :)

Be seeing you.