Want to hire me? For availability and scheduling please email info@arcepm.com

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.

5 comments:

er77 said...


I have founded memory leaks with 500 PSU

Bug 18490773- MDX QUERY CRASHES 11.1.2.3.000 AND HANGS ON 11.1.2.3.500

er77 said...

I have founded memory leaks with 500 PSU

Bug 18490773- MDX QUERY CRASHES 11.1.2.3.000 AND HANGS ON 11.1.2.3.500

Jared Brame said...

Thanks for posting this, Cameron! Very interesting stuff.

It'll be interesting to hear about how the Hybrid feature affects things like calc order. Is the two-pass setting still allowed? And how does that work with Attribute Dimensions? So many questions...

Looking forward to your session at Kscope14!

Sunita said...

I have enabled ASODYNAMICAGGINBSO Full on essbase 11.1.2.3.500. But Hypbrid folder is not generate at Arbor path. any suggestion ?

Secondly does this Hybrod feature apply to planning BSo cube as well?

Prashanth said...

Hi Cameron,

I have done everything you mentioned in this post. My BSO cube is about 60 GB before converting to hybrid model. I hybridized 4 Sparse dimensions. When I run query from SmartView, it never returns results. The query is running forever. I even bumped up the values in regedit. When I checked the application log, I am getting the same message that you posted above - "Spanning into Hybrid Analysis Relational Source has been disabled". Could you please let me know what does this mean?

Thanks,
Prashanth