Limitations in Hybrid BSO Essbase

Before I begin

Lest anyone think that this blog post is Cameron’s Exercise in Whining About Essbase or Cameron’s Gift to Oracle’s Competitors or Cameron’s Act of Cutting His Oracle ACE Director Throat, please note that just about everything I am about to describe below is in the Essbase 11.1.2.3.500 ReadMe and I encourage you go to check out that oh so useful document to prove that I am not making this up from whole cloth.

This post will consist of a review of what that document means as Dan Pressman and I have tried (and failed) to get round the limitations and in the process discovered a few new surprises both positive and negative.  If you continue on you then have the opportunity to marvel at our stubbornness aka our inability to read, understand, and follow documentation which is both amusing and instructive.  Sometimes that stubbornness results in positive discoveries, so I suppose it isn’t a totally negative behavioral characteristic.  

Or you could just read and absorb the ReadMe and be done with it but then you’d miss the bits we figured out.  

One last preparatory note – we are doing extensive testing with Hybrid to try to figure out the architecture behind Hybrid because we believe that understanding at a high level how Hybrid works is key to the most effective utilization of the tool.  Also, it’s cool to figure things out.  Again, if this sort of thing piques your interest, I encourage you to come to the Evolution or Revolution:  The New Hybrid Essbase Dan and I are giving at Kscope14 where we will be setting ourselves up for ridicule and contempt when Oracle finally publishes how Hybrid works expounding at greater length on what we believe to be Hybrid’s architecture and resulting design good practices.  

The right perspective

The .500 patch is the first release of Hybrid.  In fact it is really a .0 release.  As such, it is not perfect, and Oracle freely admits and documents that in the ReadMe file.  Think of this release of Hybrid as a statement of technology direction and a proof of technological concept.

In that light, what they have done with Hybrid is extraordinary and to be applauded as this functionality will simply revolutionize how BSO databases are designed and utilized.  Yes, it is that important.

At the same time, we are in very early days with Hybrid so please do not read this blog post and walk away thinking, “Hybrid is half-baked, it isn’t good for anything, life isn’t fair, I never got a pony as a child, etc.”  This is the beginning of what I think of as the Hybrid revolution, you are getting in at the ground floor, and it is pretty easy to see where the improvements to Hybrid will be made.  Maybe today Hybrid is right for your applications, maybe it is not; I am willing to bet that as time goes on more and more of your BSO applications will become good candidates for Hybridization until each and every one of them is a Hybrid database.  I told you this is the beginning of the revolution.  

On to the limitations

What happens to a failed Hybrid operation?

Oracle has stressed, and I am reiterating, that Essbase will never return an incorrect value.  By that I mean that if an operation fails the supported-by-Hybrid test, Essbase will revert to Classic BSO operation and will return the correct value.  It may take a looooooooonnnnnngggggggg time to return the value, but return it Essbase will.  Do not be scared of inaccurate data coming out of Hybrid.  

Let’s review the ReadMe

Again, dear Oracle competitors/Oracle Legal, the following is directly from the ReadMe document, so again before anyone gets excited, go read that officially released document.

Unsupported calculations

I am afraid this is going to be a bit disappointing as the list of unsupported functionality is quite comprehensive.  Again, I stress that you consider this list in light of Hybrid being at its very first release.

One more thing to stress (or restress) – when I write “It does not work” that does not mean that Essbase doesn’t return the right value, it means that Hybrid didn’t fire and Classic BSO took over.  However as you have now set your database to calculate dynamically that could mean quite a delay.

I should also note that we only tested the top three but to my mind these are the most important bits of missing functionality.  Through this painful experience, I have learnt to trust the ReadMe so I no longer feel the need to try to prove the documentation wrong.

Cross-dimensional operators

To me this is the big does-not-work feature.  You cannot reference a cross-dimensional operator and use the Hybrid engine.  Dan Pressman and I went round and round and round on this trying some fairly crazy approaches, one of which involved CDFs, MDX, perl, and substitution variables before we finally came to our senses and realized it just didn’t work.  

Time Balance

Yes, that’s right, Time Balance is not available in Hybrid.  I really wanted this one to work and wasted a staggeringly large amount of time trying to get this to work via UDAs, member formulas in an Analytic dimension as you might in ASO, and I think by baying at the moon.  None of it worked.

Attributes

They just don’t work.  Nope, not at all.  G’wan, try it yourself.  They don’t work, even though they are dynamic sparse calculations which one would sort of think would be right up Hybrid’s alley if you know how ASO handles them.  With luck, they will be coming very soon.

Dynamic calcs with formulas that are targets of transparent partitions

I did not try this one, but I believe Oracle when they say it does not work. Dan did and confirms that this does not (he admits he did not read the ReadMe first and thus continues our near-perfect record of being unable, or at least unwilling, to read and comprehend documentation) work.

Queries with both two-pass and one-pass dynamic calc members from the same dimension

Ibid.

XOLAP

Ibid.

Other things that don’t yet work

These are not in the ReadMe file, but we personally tested these and know that at least in this release, they are not yet there in Hybrid.

DATAEXPORT

The calc script command DATAEXPORT doesn’t work in Hybrid, at least when writing out upper level members.  I didn’t try (or care) about level zero because there wouldn’t be any Hybrid functionality there.  Gary Crisci called this one in an email exchange with me.  And yes, it is super sad, but I have email exchanges about Essbase functionality with people.  Time to get a grip, Cameron.  Or a life.

Report Writer

The Essbase Report Writer, around since the beginning of Essbase is also not supported by Hybrid.

Transparent partitions that have Hybrid sources and Classic targets

One of the many harebrained and unsuccessful attempts Dan and I attempted was to try to push all of those lovely fast dynamic ASO aggregations from a Hybrid source to a Classic target where we thought Classic could then apply its Time Balance, cross-dim, etc., etc., etc. functionality.  There is no joy in Mudville.

XREF and XWRITE

To be fair, I didn’t test XWRITE, but for sure XREF doesn’t work and I have to believe where one goes, the other follows.

Top-down formulas

Any formula that fires in top down mode does not work in Hybrid.  Unfortunately, many calculation functions fit this profile.

So what does work?

Rack and stack BSO databases

If this functionality wasn’t there, Hybrid wouldn’t exist.  But it does work, quite well actually, and that is very exciting.

Formulas

Supported functions

@CHILDREN, @EXP, @INT, @ISMBR, @MIN, @MINSRANGE, @MOD, @MODE, @NOTEQUAL, @POWER, @RANGE, @REMAINDER, @ROUND, @VAR, @VARIANCEP, and @VARPER

These functions are supported.  

Sparse formulas

So long as the formula references only other members of the same sparse dimension members, Hybrid can fire.  

Dense formulas

So long as the formula references only other members of the same dense dimension members, Hybrid can fire.

One interesting thing to note here – intrablock calculations will, if possible, fire in Hybrid.  Think about what that means – Hybrid is working everywhere, not just for sparse calculations.  And not just at upper levels.  Again, ponder that bit of information because it is fairly earth-shattering.

Sparse to dense/sparse formulas

You cannot use cross dimensional operators, but if your sparse formula references sparse members and dense stored members, the calculation can succeed in Hybrid.  The example the ReadMe gives is as follows:  @MINSRANGE("Stereo","Qtr1":"Qtr2"); which assumes that this is a sparse member formula, Stereo is in a sparse dimension, and that Qtr1 and Qtr2 are stored dense members.

Transparent partitions with Classic BSO source to Hybrid BSO targets

This works quite nicely – hooking this up between a Classic and Hybrid database couldn’t be easier.  And it is fast, just like a Classic BSO to ASO partition.  With this, you no longer need to build two different engine versions of the same database.  This is a significant reduction in build and maintenance effort and a big win.  

NB – Before you try it in a futile attempt to get round the documented functionality of Hybrid by wrapping Classic BSO functionality around Hybrid as we did and as noted above, you cannot xref or transparent partition from a Hybrid source to a Classic target and fire the Hybrid engine; everything will run in Classic BSO mode.  Did I mention that this can be slow?  It is.

Other things that work

MDX as a data extractor

Remember how DATAEXPORT and the Report Writer do not work in Hybrid?  The good news is that MDX queries do use Hybrid so we can extract upper level data in a really fast mode so long as we can parse the output.

If there were any more proof needed to show that Oracle has bet big on MDX (and really, there isn’t), here it is – MDX trumps the Report Writer which has been around since the year dot in Essbase.

Where do we go from here?

The list of limitations is seemingly long and I suspect that it is not complete.  If you are disappointed by this, you shouldn’t be.   Cast your mind back to how limited ASO was when it first came out.  Now think about how powerful and capable it is.  I expect the same functionality improvements to occur in Hybrid.

Oracle have thrown a lifeline to BSO (and yes, you ASO bigots out there, BSO does have quite a few good points) that will, in my opinion, both transform BSO applications and bring the two engines much closer together.  It’s early days with Hybrid and I for one am going to continue to explore what Hybrid can and cannot do.

If this sort of geeky exploration is to your liking, and you want to know more about what Dan and I found, I again encourage you to come to our joint Kscope14 session.

Join us, won’t you?