Want to know The Truth About CPM?

08 December 2015

Want to read the official word about Hybrid? You’re probably missing this.

A note

Before I torpedo sales of Developing Essbase Applications:  Hybrid Techniques and Practices, what I’m about to relate does not replace it, but instead supplements the Hybrid chapter Tim German and I wrote.  Keep on buying the book, hopefully in lot cases of 100 so I can sooner or later make back my fairly exciting legal fees.  For real and for true, the Hybrid chapter covers a bunch of very practical as well as theoretical (I have yet to hear from anyone at Oracle that we goofed) concepts.  What you’re about to read has some of the practical orientation but nothing currently in the way of architecture.  Thank goodness for that, say my six coauthors.  And me.

I whine, Oracle listens

The above title  is likely not a 100% accurate statement.  Probably something like, “Yr. obt. svt. whines, in desperation and despair Oracle does its very best to make me shut up and go away,” is quite a bit more likely.  Regardless of motivation, Oracle’s EPM documentation team read my last post on PBCS documentation and that documentation’s general awesomeness (and it really is awesome – miles better than the traditional documentation) and caught my whine (whinge for those of you formerly or currently in the British Empire) about the dearth of Hybrid documentation.  It turns out that there is documentation about Hybrid, and some of it is quite interesting.

The catch is not to read the 11.1.2.4 but instead to read the 12c documentation.  One might have thought that, having written all about EssCS which is after all a Cloud version of Essbase 12c, I might have given a thought to reading that release’s documentation but nope.  Read on, gentle reader so that you may not sin as I did.

The seventh son of the seventh DBAG son

In the 12c DBAG, there are no further than tweleve references to Hybrid.  This is a Good Thing.  Also, I can’t count but couldn’t resist the temptation to use that line.  Maybe 12c always means twelve points?  You decide.

The bits in bold are my emphasis.

Using Hybrid Aggregation

There are a few interesting suggestions here and a rather important one.

The following are some scenarios where hybrid aggregation is likely to improve calculation performance:
  • A block storage database has sparse members that are not level 0, and are calculated according to hierarchy (rather than by calculation scripts).
  • A sparse Dynamic Calc member has more than 100 children.
  • You are using a transparent partition between an empty aggregate storage target and a block storage source. If the formulas on the aggregate storage target are simple and translatable to block storage formula language, you can achieve fast results on block storage using hybrid aggregation.
  • You are using a transparent partition between two block storage databases, and calculation performance is a concern.
The really interesting
Minimize the size of blocks, if possible. Consider adjusting the Create Blocks on Equations setting, or in the case of calculation scripts, using the SET CREATEBLOCKONEQ ON|OFF command.

And there we have it – Hybrid design advice, suggesting that smaller is better.  True or not?  Only benchmarking can answer that question, but it’s one I plan on trying out.  You should too.

Working with Attributes

If hybrid aggregation mode is enabled, you can set the solve order for attribute dimensions and their base dimensions, eliminating the need to tag members as two-pass. In hybrid aggregation mode, the default calculation order (also known as solve order) matches that of block storage databases, with some enhancements. If you wish to use a non-default solve order, you can set a custom solve order for dimensions and members.

Solve order in Hybrid is here.  Note that this is not supported in 11.1.2.4, but it is a sign of things to come.

More about Hybrid Aggregation Mode and custom solve order

Can attribute dimensions in ASO have their own solve order?  I don’t think so.

Per the DBAG:
When hybrid aggregation mode is enabled, the default calculation order (also known as solve order) matches that of block storage databases, with some enhancements. If you wish to use a non-default solve order, you can set a custom solve order for dimensions and members in hybrid aggregation mode.

The default solve order in hybrid aggregation mode is easier to decipher than standard block-storage calculation order, for these scenarios:
  • Forward references, in which a dynamic member formula references a member that comes later in the outline order. There is no outline order dependency in hybrid mode.
  • Aggregation of child values based on outline order more closely matches aggregation using equivalent formulas.
  • Dynamic dense members as dependencies inside sparse formulas. If a sparse formula references a dense dynamic member, the reference is ignored in hybrid mode, because by default, sparse dimensions are calculated first. You can change this behavior by assigning a custom solve order to the sparse dimension that is higher than (calculated later than) the dense dimension’s solve order.

Optimizing Calculations

Not all that much going on here, but it’s there regardless.
Hybrid aggregation mode is available for block storage databases. Hybrid aggregation for block storage databases means that wherever possible, block storage data calculation executes with efficiency similar to that of aggregate storage databases. See Using Hybrid Aggregation.

See also the ASODYNAMICAGGINBSO configuration setting topic in the Oracle Essbase Technical Reference.


Essbase Features No Longer Supported or Deprecated in Previous Releases

Want to know what’s deader than disco before 12c aka 12.2.1.0.0?  See the ReadMe for what’s in 12c.

This one is a red herring as it refers to XOLAP, not the Hybrid query processor.  I thought that XOLAP was a deprecated feature but no, it’s Hybrid Analysis that’s dead as a doornail.  

The Tech Ref

This is where I spend most of my time with Essbase documentation.  If I knew MDX, BSO calc scripts, or MaxL better, I wouldn’t need to refer to this quite so much but I’m never going to be called Mr. Memory so I’m glad it exists.  Here’s the gen on the interesting Hybrid bits.

ASODYNAMICAGGINBSO

This is the same as 11.1.2.4 but it contains an intriguing note:
Alter Application set cache_size and Query Application get cache_size, for managing the size of block- storage application cache.
Does this mean that the aggregate cache can now be changed?  It sure suggests it although the setting was there in 11.1.2.3.500 and 11.1.2.4 and it had no actual effect.  Fingers crossed that this actually works.

Functions Supported in Hybrid Aggregation Mode

There are lots of functions listed here, too many to count.  What’s interesting is that only 19 out of all of those functions are noted as not supported.  Yet.

ASODYNAMICAGGINBSOFOLDERPATH

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.

This one seems to go back and forth.  I think that in 11.1.2.3.500 this was the behavior and in 11.1.2.4 the folders persisted.  Gentle Reader, this one is for you to find out.

Alter Application

Want to set the aggregate cache?  Use alter application’s set cache_size to do so.

Again, let’s hope that it can actually be set to greater than 32 MB.

Query Application

Want to know how big that aggregate cache actually is?  Use query application sample get cache_size;

MAXFORMULACACHESIZE

Formula cache sizing applies to both BSO and Hybrid as the latter is a superset of the former so no surprise there.

So there you have it, so long as you know where to look

It’s a bit confusing and confounding to have to look at something other than the current release’s documentation but I’m really just glad it exists at all.  Hopefully we’ll see the relevant bits make it to the 11.1.2.4 documentation code stream but beggars can’t be choosers.

Again, my thanks to the Oracle EPM documentation team for helping us all out with this.  I’d have been looking at the 12c documentation about when 12c hits on-premises so this is huge, huge, huge.

Be seeing you.

No comments: