28 April 2014

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?

07 April 2014

EPM 11.1.2.3.500 Patching Woes, Trials, and Tribulations

A warning
Don't take infrastructure advice from me.  Ever.  That begs the question, “Why are you reading a blog post on infrastructure by yr. obdnt. srvnt.”?  Simply because tales of suffering, woe, and misadventure are always instructive and sometimes entertaining.  Read the below to know that when I write this I state the truth. 

The following workaround fixed my issue, but I make no warranty that it will apply to anything other than my environment.  With that huge caveat, off we go...
Why I am here
As most of you know, I am a serially challenged (I do it again and again and I fail again and again) infrastructure idiot.  However, needs must when the devil drives, and no one else is going to install the 11.1.2.3.500 patch for me, so....

I spent most of the last weekend in March struggling with that EPM 11.1.2.3.500 17529887 patch.  Much of the issue was that Planning simply didn't work.  My compact deployment started up all right, but when I tried to enter a Planning app, nothing.  And by nothing I mean I could not edit dimensions, forms, etc., etc., etc.  Bummer.

Help is on the way
In desperation, I reached out to John Booth (if a customer needs infrastructure work, I am not sure why he would use anyone else – for sure you shouldn't hire me) and described my system symptoms.  John asked if the ADF patches 16964825 and 18362693 had been applied. 

I think they are supposed to be applied automatically as part of that big (2 gigabyte) patchset but it didn't (apparently) happen. 

I tried applying those patches from the normal C:\Oracle\Middleware\EPMSystem11R1\OPatch location.  They failed.  I reached out to John again (not many would take calls on a Sunday -- thanks, John) and he explained to me that there are two opatch locations.  The ADF patches, because they are at a base level of Fusion (I think I have this right), are applied at C:\Oracle\Middleware\oracle_common\OPatch.  I applied the patches and now Planning mostly worked.

Here are the screenshots (the second one is important because there is a twist) of the patches being applied.

16964825


18362693


Did you see the special bit of the patch here?  Don't just patch 18362693, patch 18362693\oui.  From John I know that "oui" in this context is not "Yes" in French, but instead Oracle Universal Installer.

Did it work?  Yes and no.

The issue

I could now get into Planning via IE.  Terrific.  But Planning via Smart View...it was interesting.  What's wrong with the below screenshot?

Take a look at it in IE:

Yet Another Infrastructure Moment of Pain or YAIMP.

The fix

What I did was roll everything back (thank you VMWare and thank you Cameron for being the cautious geek you usually are) via VMWare’s snapshot functionality to a prepatch environment.

I then applied those two ADF patches before I did anything else in that C:\Oracle\Middleware\oracle_common\OPatch location.

I then applied the rest of the .500 patches in the normal C:\Oracle\Middleware\EPMSystem11R1\OPatch location.  Opatch tried to apply the two ADF patches (it is built into the 17529887 patch as far as I can tell) and aborted their reapplication, but continued on with the rest of the patching process.

I then crossed my fingers.  Did it work? 

Why yes it did. Whew.

The clew I should have knew

Were I more eagle eyed, I would have seen this in my initial failed install:

That would be the two required ADF patches going KABOOM.  Did I see it?  Nope.  Dumb, dumb, dumb on my part.  As my teachers used to say back in school, “A smart boy, but does not pay attention to detail.  Must do better.”   It is nice (?) that my fundamental personality traits are constant through time.

Even dumber is that this is explicitly noted in Oracle Support KB article Issues Using Planning after Applying EPM Patch 11.1.2.3.500 (Doc ID 1640411.1).  Although I swear that when I looked for some clew (or clue) to my issue I couldn’t find it.  Sigh.

The cause

So why didn’t it work?  Most likely because I didn’t have my 11.1.2.3.000 environment set up quite right.  Careful reading (oh sure, now I do this) of the Great and Good John Goodwin’s post on 11.1.2.3.500, rewards the faithful reader with this important note:
One thing is nice is that applying the patch looks to automatically install the required ADF patches in to oracle_common home. (if you have opatch added to the path variable that is).

Looking at my PATH environment variable I see:

No reference to C:\Oracle\Middleware\oracle_common there and I think that is why the whole thing didn’t work.

The end

I think I have ten new grey hairs from all of this.  And if you wonder why I suffered through this
process, I have two Kscope14 sessions riding on a .500 environment.  It was either do or die.  Luckily this time I lived.  Again, I would never have gotten this far without John Booth's help.  Thanks, John!

Be seeing you.

Cameron

01 April 2014

Getting started with Hybrid Essbase

How do you use Hybrid Essbase?

It’s actually very simple.
  1. Install the 11.1.2.3.500 patches as I outlined here.
  2. Configure the Essbase.cfg file to use the ASODYNAMICAGGINBSO setting (see below).
  3. Tag all of the upper level members in your sparse dimensions as dynamic.
  4. Have fun, fun, fun, discovering what Hybrid can and cannot yet do.

This post is a one of a series that will explore how to use Hybrid for fun and profit.

And with that, off we go!

Right out of the documentation


Once you have successfully installed 11.1.2.3.500, have a good read of the readme file.  In it, you’ll find the following parameters for ASODYNAMICAGGINBSO.

Parameter
Description
Appname
Optional. If you specify only an application, all the databases in that application are affected. If you leave out the application and database name parameters, the setting applies to the entire server.
Dbname
Optional. If you specify an application and database, the database you specify is affected by the setting.
NONE
Disable hybrid aggregation in block storage databases (the default).
PARTIAL
Turn on hybrid aggregation only for simple outline aggregations based on the consolidation operators +, -, and ~, but excluding the operators *, /, and %. Leave formulas to be calculated in block storage mode.
FULL
Turn on hybrid aggregation for simple aggregations and formula calculations. See Notes for formula limitations.

Before you blithely turn on Hybrid everywhere, have a good read (and reread) of the below section.

How can you use these settings in Essbase.cfg?

Setting
Result
ASODYNAMICAGGINBSO NONE
Turn off Hybrid for all BSO databases, except where overridden
ASODYNAMICAGGINBSO FULL
Turn on Hybrid for all BSO databases, even when not specified
ASODYNAMICAGGINBSO Sample NONE
Turn off Hybrid for the Sample application, even if ASODYNAMICAGGINBSO FULL is specified
ASODYNAMICAGGINBSO PARTIAL
Behavior as FULL

A warning, and quite an important one

Did you catch that bit about ASODYNAMICAGGINBSO FULL?  Databases that are not (you think) candidates for Hybrid mode now are Hybrid databases.  Even if upper level sparse members are not tagged as dynamic, Essbase will still calculate what it can in Hybrid mode, and that includes dense calculations.  Hybrid touches everything.  Do you want this?  Only you can decide but it seems a bit extreme for a “Wot’ll she do, mister?” first look.

One further note – if you turn ASODYNAMICAGGINBSO on, Essbase will not convert classic BSO databases to Hybrid databases until the database is started.  After that, well, read on.

My Essbase.cfg

The above turns off Hybrid for all databases and then enables Hybrid for all databases in the SampleH application.  

Unlike ASO, you can still have multiple databases per application, and they can all be Hybrid.  Ain’t Hybrid grand?  But speaking of ASO…

What does a Hybrid database do to the directory structure?


Do those folders look familiar?  Take a look at ASOSamp.

The ASO (this ought to give you a hint as to the internal architecture) folders of default, log, metadata, and temp are now part of Hybrid databases.  

You can even change where those temporary folders go.  Per the ReadMe:
Directories for Hybrid Aggregation Mode
Related to the addition of hybrid aggregation in block storage databases, in this release, the following subdirectories are created under $ARBORPATH/hybrid/AppName:
default
log
metadata
temp

These subdirectories are similar to those found in aggregate storage application directories. When the application stops, the directories are removed, and when the application restarts, they are replaced.

To change the location of the directories, edit the following setting in essbase.cfg:

ASODYNAMICAGGINBSOFOLDERPATH [appName] path-to-directory

AppName is optional. path-to-directory specifies the new directory after you have moved it.

Just like ASO databases can split their tablespace files across drives (with a little more granularity because ASO can put default and temp in different locations), Hybrid allows you to move all of the tablespace folders to a separate drive using the ASODYNAMICAGGINBSOFOLDERPATH setting.

No going back

I set ASODYNAMICAGGINBSO to NONE, stopped Essbase, tarted Essbase, and started the SampleH database back up.  The tablespace directories are recreated (they are by definition dynamic in nature anyway), even if you disable Hybrid for the database.

Personally, I would not use the ASODYNAMICAGGINBSO FULL setting just yet and instead go with a combination of ASODYNAMICAGGINBSO NONE and ASODYNAMICAGGINBSO Appname Dbname.  But I am the cautious type.

Was that query Classic or Hybrid?

The application log has the answer.  

I am going to use good old Sample.Basic, aka, My Very Favorite Essbase Database In The Whole Wide World, versus a Hybridized version called SampleH.Basic.  The queries are all simple Smart View retrieves.

One note about Sample.Basic – yes, it is kind of silly to test Hybrid against Sample.Basic as its performance is fast no matter what one does to it.  However, it is a handy way to try out Hybrid’s functionality with a small data set.  As you will see a bit later on, that smallness can be important.

Classic all the way

The below is from Sample.Basic.  There’s nothing you haven’t seen many times before.  I only include it for purposes of comparison.  This is a simple query from Smart View.

[Sat Mar 29 15:18:03 2014]Local/SampleH///1748/Info(1042059)
Connected from [::ffff:192.168.46.149]

[Sat Mar 29 15:18:03 2014]Local/SampleH/Basic/hypadmin@Native Directory/1748/Info(1013091)
Received Command [SetAlias] from user [hypadmin@Native Directory]

[Sat Mar 29 15:18:03 2014]Local/SampleH///7080/Info(1042059)
Connected from [::ffff:192.168.46.149]

[Sat Mar 29 15:18:03 2014]Local/SampleH/Basic/hypadmin@Native Directory/7080/Info(1020089)
Ignoring span Hybrid Analysis option. Spanning into Hybrid Analysis Relational Source has been disabled. See the essbase.cfg file

[Sat Mar 29 15:18:03 2014]Local/SampleH/Basic/hypadmin@Native Directory/7080/Info(1020055)
Spreadsheet Extractor Elapsed Time : [0.001] seconds

[Sat Mar 29 15:18:03 2014]Local/SampleH/Basic/hypadmin@Native Directory/7080/Info(1020082)
Spreadsheet Extractor Big Block Allocs -- Dyn.Calc.Cache : [1] non-Dyn.Calc.Cache : [0]

Hybrid

And here’s SampleH.Basic’s log file using the same query.  Do you see it?  A single line tells you that the query is indeed in Hybrid mode.

[Sat Mar 29 15:22:13 2014]Local/SampleH/Basic/hypadmin@Native Directory/6828/Info(1013091)
Received Command [SetAlias] from user [hypadmin@Native Directory]

[Sat Mar 29 15:22:13 2014]Local/SampleH///3576/Info(1042059)
Connected from [::ffff:192.168.46.149]

[Sat Mar 29 15:22:13 2014]Local/SampleH/Basic/hypadmin@Native Directory/3576/Info(1020089)
Ignoring span Hybrid Analysis option. Spanning into Hybrid Analysis Relational Source has been disabled. See the essbase.cfg file

[Sat Mar 29 15:22:13 2014]Local/SampleH/Basic/hypadmin@Native Directory/3576/Info(1204002)
Hybrid Aggregation Mode enabled.

[Sat Mar 29 15:22:13 2014]Local/SampleH/Basic/hypadmin@Native Directory/3576/Info(1020055)
Spreadsheet Extractor Elapsed Time : [0.001] seconds

[Sat Mar 29 15:22:13 2014]Local/SampleH/Basic/hypadmin@Native Directory/3576/Info(1020082)
Spreadsheet Extractor Big Block Allocs -- Dyn.Calc.Cache : [1] non-Dyn.Calc.Cache : [0]

Taking advantage of Hybrid

Remember, Hybrid touches every calculation, if it can.  The big win is the ability to get rid of AGG and CALC DIM commands.  After all, those upper level blocks are the source of all bad things in BSO Essbase related to data explosion – the size of upper level blocks.

As I noted above, once Hybrid is enabled, it will try to execute against the BSO database on every query.  Thus you do not need (and cannot) to tag a dimension as “Hybrid”.  If the Hybrid engine can be invoked, it will be.  

Here’s what a fully Hybridized Sample.Basic looks like:

Tagging upper level members as Dynamic Calc gets rid of those blocks (this is true in Classic BSO as well) and, if Hybrid is enabled and its rules are followed, allows the super fast Hybrid engine to aggregate data on the fly.  That’s all there is to it.

A Hybrid failure

I’m going to hold off on all of the things that Hybrid fails at for another post as the list is so long.  Yes, long.  And yes, I still think it is awesome.  It isn’t all that hard to break Hybrid.  

Again, think of this release as a technology statement of direction and, if you are sufficiently clever, a useful tool.  Hopefully I am of that sufficiently clever ilk to actually use Hybrid in a meaningful way, but let’s face it, yr. obdnt. srvnt. has a somewhat mixed record on that whole “Isn’t he a clever chap” thing.  Wish me luck.  :)

Again, I will share with you my workarounds both within this blog and of course at the Kscope14 session I am giving with Dan Pressman entitled Evolution or Revolution:  The New Hybrid Essbase.

Be seeing you.