I will be available on 15 April, 2014. Essbase forever! Or something like that. Contact me on LinkedIn if you're interested.

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.