My first task was to create essentially the same database in ASO, BSO and Hybrid. I had a generic financial type model I could use in both ASO and BSO. In order to create the Hybrid model, I simply downloaded Celvin Kattokaran's utility that changes upper-level sparse members to dynamic calc. I made a copy of the BSO cube, ran it through the utility, added the following line to my essbase.cfg file (with a restart of Essbase just for good measure)
ASODYNAMICAGGINBSO HY_APP FULL
and I was off to the races. Thanks, Celvin!
For my first test, I created an MDX query that pulled from several different levels and ran it on all three databases. Very simple, just a smoke test to see if there's fire. The results in a moment.
At Kscope15 I felt like Hybrid was shoved down my throat -- to the point that I felt badly that I hadn't already converted all of my production cubes and I became afraid that my precious ASO would be going away in the next two weeks. I was reassured by Essbase product management that ASO is not going away, at least not soon, and that there will likely always be some *fringe* cases for ASO as well as for pure BSO. Phew...
Now the results (sorted from slowest to fastest).
|Test 1: BSO||[63.08] seconds|
|Test 2: ASO (no aggs)||[21.43] seconds|
|Test 3: ASO (recommended aggs)||[19.06] seconds|
|Test 4: Hybrid||[11.86] seconds|
Well you could knock me over with a feather. I think I need to do some more investigation. Has anyone else seen similar results?
Notes: BSO DB Size: pag 34,507,358,289 b; ind 557,080,576 b
ASO DB Size: input 768,832 kb; agg 607,616 kb
Hybrid DB Size: 918,061,137 b; ind 32,792,576 b
MDX Output Row Count: 5,899
Most difficult part of this test: Waiting for the silly old BSO calc to finish.
I'm joining the club! Just started a full 220.127.116.11 project - 3 apps, 9 databases, 3 of which are planned to be Hybrid (the rest a mix of ASO due to the need for attributes and BSO due to the need to use the target entry version).
Finally I'm starting to see what our boy CLackpour has been ranting about for 15 months. It's almost creepy to have a BSO database that doesn't need aggregation.
Wait til you convert something complicated (I converted a full employee benefits application from BSO to Hybrid). It was already heavily optimised, taking around 1 sec to calculate a single org unit, and about 30 secs for all orgs (about 130 org units, about 4500 employees, so pretty damn good performance). The only thing that took time was the aggregation of the Org and Activity hierarchies. That took about 5 minutes for the entire application.
Now - exactly the same performance for a single org, exactly the same performance for all orgs, but I don't have to have a 5 minute aggregation. It's ridiculous.
I actually think we're not too far off having 'real' time applications. Doesn't seem to far to stretch.
Thanks for the comment Pete -- I'm looking forward to hearing your lessons learned on this implementation.
Post a Comment