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.