Wednesday, August 19, 2015

The Plunge Into Hybrid Part 1

Hybrid Essbase has been around for over a year now but from what I can tell it hasn't yet been widely adopted. It is my hope to test Hybrid extensively over the next several months to see how promising (or not) this new architecture is. As a brief refresher, Hybrid technology combines the calculation functionality of BSO with the fast on-the-fly aggregations of ASO.

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)


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.


Pete said...

Hey Tim,

I'm joining the club! Just started a full 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.


TimF said...

Thanks for the comment Pete -- I'm looking forward to hearing your lessons learned on this implementation.