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.

Hybrid Essbase has arrived

A patch, but much more than just a patch

After much anticipation, Oracle have released the latest and greatest update to Essbase, the Oracle Essbase Release 11.1.2.3.000 Patch Set Update (PSU): 11.1.2.3.500.  As a patch, you must first have 11.1.2.3 installed.  You then get to experience the pleasures of opatch.exe as you apply the following patches to Essbase and its components (there are many platforms available, the below are Windows 64-bit only):

Component	Patch
Hyperion Essbase Server 11.1.2.3.500	Patch 17767302: Essbase Server
Hyperion Essbase Client 11.1.2.3.500	Patch 17767299: Essbase RTC
	Patch 17767307: Essbase Client MSI
Hyperion Essbase Studio Server 11.1.2.3.500	Patch 17767295: Essbase Studio Server
	Patch 17767296: Essbase Studio Console MSI
Hyperion Essbase Administration Services (EAS) 11.1.2.3.500	Patch 17767309: EAS Server
	Patch 17767316: EAS Console MSI
Hyperion Analytic Provider Services (APS) 11.1.2.3.500	Patch 17767293: APS Services

If you are not on Windows, you can search for the patches.  Yes, the below search is for Windows but go ahead and change it to your platform of choice.  Oracle is OS agnostic.

That Windows-specific search will give you this (I cut off the non .500 files):

Just be sure that you also search for Studio, EAS, and APS.

If you want the whole 11.1.2.3.500 EPM patch (and it is a monster 1.7 gigabyte patch -- I heard about it from John Goodwin’s blog) go to Patch 17529887: Patch Set Update: 11.1.2.3.500 for Oracle Enterprise Performance Management System.  Here’s the readme (you must be logged into Oracle Support to read this).

Why should you care?

I’ll give you my take on this release, but as the saying goes, Read The Whole Thing.

I’m going to briefly cover Hybrid, but I think the other big hits from this release are:

• MDX Aggregate and Sum are sped up
• FIXPARALLEL
• A change to the way Essbase stores BSO data

MDX Aggregate and Sum

A pretty common issue in ASO databases is the need to do a level zero-only calculation (say for a rate calc) and then aggregate up.  Except of course ASO doesn’t work that way – it’s dynamic, remember?  And that means that ASO wants to calculate data at all levels, including that rate calculation.  If the rates are only valid at level zero, this fully dynamic nature results in inaccurate results when those rates are summed and then calculated at upper levels.

Many (including me) have tried to get round that by creating member formulas that test for the level of the dimensions and use MDX to aggregate dimensions on the fly.  This often doesn’t work because MDX calculations are dynamic and hence do not take advantage of the bitmap.  As Dan Pressman would say, doing this is not letting ASO be ASO.  What I say is that this is S-L-O-W.  Too slow, usually, to actually work.

Do it in batch

Oracle have realized this and, in 11.1.2, came up with ASO calc scripts.  They are run via MaxL and the execute calculation command – it’s a little ugly looking but supports those level zero only calcs.  Run that and let ASO be ASO and do the aggregations it loves to do – perfect, right?

What about interactive applications?

While that’s fine for batch processes, it doesn’t work very well in interactive applications.  I’ve seen some stunningly kludgy ways of launching MaxL in the background to do the level zero calcs after a user inputs data.  It works, kind of, but is a pain.

A better way, hopefully

Oracle feels your pain, and have optimized Essbase so that the member formula MDX technique may allow you to do those dimension level tests and then do a Aggregate or Sum.

I note that this may be a solution for you because:

1. I haven’t tested it
2. I have to believe that it only scales so far

OTOH, per the “I haven’t tested it” statement I could be 100% wrong and maybe this approach will work for large dimensions.  Watch this space for a test in the near future.

What does the read me say?

Faster Queries for MDX Aggregate and Sum Functions

In aggregate storage databases, performance is improved for MDX queries containing the Aggregate or Sum functions. Essbase performs dependency analysis and uses a formula cache to execute these requests dynamically.

For the optimized performance of these functions on aggregate storage databases, include in your query the following elements:

• Any of the following functions, used within the named set and/or as an argument to this function: Intersect, CurrentMember, Distinct, CrossJoin, PeriodsToDate. The use of any other functions (such as Members) disables the optimization.
• The second parameter, accounts_member, must be included for optimal performance.

Note: Optimal query performance may require a larger formula cache size. If you get an error message like the following, adjust the Essbase.cfg setting MAXFORMULACACHESIZE accordingly:

Not enough memory for formula execution. Set MAXFORMULACACHESIZE configuration parameter to [1072]KB and try again.

Pretty cool, eh?

FIXPARALLEL

We all know, and mostly love, CALCPARALLEL.  It can be a little tricky to set up correctly, causes greater PAG file (we are back in BSO land, btw) fragmentation, and lots of functions revert Essbase to serial mode, but the performance boost is so great most developers just use as much of it as possible.

FIXPARALLEL is just what it says – parallel calculation for blocks of commands as defined by FIXPARALLEL…ENDFIXPARALLEL.  

A question arises:  If BSO parallel calculation already exists through CALCPARALLEL, why bother with a different method of parallelization?  It’s partly about choice, and partly because FIXPARALLEL doesn’t rely on task selection the way CALCPARALLEL does.  In other words, FIXPARALLEL doesn’t depend on “sparsity, outline order, dependencies, and member formulas” when generating a task list.

Also, FIXPARALLEL works with temporary variables (VAR functions), DATACOPY, and DATAEXPORT (flat files only).  

A change to the way Essbase stores data

This one caught me by surprise, mostly because it tells me that the way I thought Essbase stored data in blocks has been wrong.  

Per the ReadMe:

Prior to this release, each time a data block was updated, it was written to a new disk location. With this release, for Exalytics, Essbase enables in-place data writing.

In-place data writing means that when updates occur, the data block can be written to the same location, as long as the compressed size of the data block fits in its original location on the disk.

In-place data writing can help reduce data fragmentation and lower the need for frequent restructuring of database. It also reduces the need for frequent index updates, resulting in improved performance.

I could have sworn that Essbase always did that.  In fact, many people (although not Glenn Schwartzberg) have.  Even Oracle (at least previously) stated:

The Average Fragmentation Quotient ratio measures free space in a given database.  As you update and calculate data, empty spaces occur when a block can no longer fit in its original space and will either append at the end of the file or fit in another empty space that is large enough.  These empty spaces take up space in the .PAG files

I, and others, took this to mean that when Essbase can write a data value back to its original block, it did.

Reading this, and Glenn’s comments that this has always been the way Essbase works led me to try an experiment with Sample.Basic on 11.1.2.3.000:

1. I cleared the database and loaded calcdat.txt. That got me to 1 for the Average Clustering Ratio and 0 for the Average Fragmentation Quotient as displayed in Esscmd’s GETDBSTATS command (I am not exactly sure this is yet available in MaxL).
2. I went into Smart View, navigated to New York->Budget->Sales->Jan->100-10, changed 640 to 645, and did a submit.
3. Looking at GETDBSTATS again showed me that Sample.Basic now had an:
1. Average Clustering Ratio of 0.9999647
2. Average Fragmentation Quotient of 0.04882698

Glenn was right (yet again, I might add -- I wouldn’t bet my life on arguing technical functionality with Glenn as I am still to young to die), changing even a single value causes Essbase to fragment.  I can only conclude, just as the documentation states, Essbase, outside of Exalytics, this release, and the INPLACEDATAWRITE Essbase.cfg setting, that Essbase for most of us does not write back to the same location.

Hybrid is here, or ASO meets BSO

I’ve been waiting for this one ever since it was announced at Oracle OpenWorld 2013 – Hybrid, formally known as BSO/ASO Hybrid Aggregation Mode, is a fascinating piece of code.  What it essentially does is brings ASO dynamic aggregations to BSO databases.  This is beyond huge because it solves the data explosion issue that has bedeviled BSO Essbase since its inception.

Hyrid aggregation means that you get all of the BSO goodness (think all of those great BSO calc script functions) that you know and love but get away from the all (or most) of the bad things in BSO that stem from the aggregation process of stored members:

• Upper level blocks don’t have to exist
• Database sizes are much smaller
• Calc times are much shorter (or even nonexistent)

It’s all very exciting stuff that I and Dan Pressman will cover in coming blog posts and of course our ODTUG Kscope14 presentation Evolution or Revolution:  The New Hybrid Essbase.

The future

I’m going to stop for now (mostly because I have to have a life and because I need to get rid of the beta patches I have and install the commercially released version) but will note that this this release has a lot of limitations as one might expect for an initial go round.  Think of 11.1.2.3.500 as a statement of direction, with limitations being taken away and new functionality being added over time.  

The next few blog posts will cover what I and others discovered during the beta process, some of the workarounds, and the implications of this exciting new extension to BSO Essbase.

You may know that I was part of a team that wrote a book all about advanced Essbase:  Developing Essbase Applications.  In it, author Dave Farnsworth wrote a chapter called, “BSO Will Never Die and Here is Why”.  Despite many people’s attempts to declare BSO dead, I think it’s safe to say that Oracle doesn’t agree.

Be seeing you.

An appeal for Mike Riley, cancer victim

Who is Mike Riley?

This will be a blog post like no other I have ever written.  I make it a very deliberate point to not make this blog about marketing, sales, or any kind of commercial appeal.  That isn’t why you, oh Gentle Reader, are here.

This blog post is an appeal, but it is not related to commerce of any sort.  It is an appeal for a man who is a friend of all EPM geeks – Mike Riley.

Have you ever wondered how EPM came to ODTUG in the first place?  Or why ODTUG has been such a comfortable home for EPM?  It is largely due to one man – the very same Mike Riley, former ODTUG board member, past president, and current conference co-chairman who is the subject of this post.  Success has many fathers, and Mike was not alone in his support of all things EPM at Kscope, but he was the primary guiding light.

When I reflect on what ODTUG has done for my professional education, network, and career it is a little hard to credit given its impact.  I am firmly convinced that my Oracle ACE Director award (not to mention my continued employment) would have occurred in, oh, about 6,000,000 years (I may be underestimating) if ODTUG had not opened its collective arms and welcomed me and all of you into the community of professionals that make up ODTUG.  Professionally, we owe Mike a debt of gratitude.

ODTUG is too big for Mike to have met and touched every one of you (although I have seen Mike work a room – it certainly isn’t for lack of trying), but for those of us who have interacted with Mike in depth, we personally owe Mike a debt of gratitude.  This obligation stems from the fact that Mike believes in the best in all of us.  I have watched Mike take flyers on people that he barely knew, given them the room to succeed (or fail and that in particular takes a special kind of courage), and unwaveringly support them as they became fully fledged volunteers in ODTUG.  Not many people have that insight, maturity, or altruistic concern for others.

If Mike is reading this, I know like all good modest Midwesterners (my family hails from Michigan so I get it) he is a bit uncomfortable with praise, especially publically delivered.  Mike, I would not write the above if every word wasn’t exactly how I feel.  Try not to blush.  :)

This post is not intended to be a hagiography (the above is the unvarnished truth) but instead that appeal I wrote about.  What do I mean by appeal?  Mike Riley has stage III rectal cancer.  As horrific as that sounds, I should note that this is not an obituary.  He is under treatment; Mike has undergone radiation, surgery, and now chemotherapy.  Getting and beating cancer is not for the timid.  Despite the almost unimaginable difficulties he has undergone his spirit remains and he is tough.

His family – wife Lisa, daughter Morgan, and son Cameron (What an excellent choice in male names.  Ahem.) are all tough too.  They need to be.  Mike and his family are always in my thoughts and prayers.  Cancer is a cruel bitch of a disease.  And that’s where you can materially help.  

Enter OracleNerd

Chet Justice, fellow Oracle ACE Director, Kscope14 database content chairman, OracleNerd, and super nice guy has initiated several fund drives for Mike.  The first was an informal one to get Mike to see his home town St. Louis Cardinals play the Boston Red Sox for the 2013 World Series.  Chet then really got serious and set up a GoFundMe charity drive for Mike and his family.  Its goal is $10,000 and is almost there – a testament to Mike’s friends and the ODTUG community (I am not too sure that both sets of people aren’t one and the same).  

Please ignore that fact that the fund has almost reached its goal because the medical monetary needs of Mike and his family continue – insurance does not cover all expenses and they add up rapidly.  Remember how I wrote cancer is a cruel bitch?  First it destroys your body, then it destroys your savings.  Cruel is an understatement.

Please give generously

The ODTUG community, and Essbase hackers in particular, are generous, kind, and thoughtful people.  We are obligated to Mike for his pioneering work in giving so much to the EPM world.  He is also one hell of a nice guy and he needs our help.  Edward Roske has asked you to do this; I am asking again.

I wrote in the first paragraph of this post that this would be a blog post like no other I have ever written and in over 150 posts I have never written about a person or appealed for money.  I likely, God willing, never will again.  The exigencies of Mike’s medical situation demands that I do.  And they demand that you reach into your heart and help him.

Give generously, won’t you?