With several hybrid Essbase implementations under our belt at eCap, we thought we’d share some general info, tips, and tuning thoughts for using hybrid applications successfully.
Hybrid Essbase General Info
As of version 11.1.2.3.500, hybrid can be enabled on any Essbase or planning application by assigning ASODYNAMICAGGINBSO APPNAME [DBNAME] NONE|PARTIAL|FULL in the essbase config. Turning this setting on for an app or database enables BSO functionality on dense members and ASO engine aggregation on sparse Dynamic Calc members in the same app (with FULL enabling ASO member formulas as well as aggregations; this is the recommended setting).
(Initializing hybrid in essbase.cfg)
If used properly, hybrid can utilize the strengths of both storage options in an efficient fashion.
Conditions Causing Hybrid to Fail
With a FULL or PARTIAL setting activated, the app or database will automatically operate using the hybrid engine until it meets a failing condition. When hybrid fails, the calculation or retrieve defaults to BSO functionality for the remainder of the operation. This can be excruciatingly slow for large calcs or retrievals on a great number of dynamic sparse members. Several conditions we have found that trigger a hybrid failure:
- Attempting a calculation on an upper-level dynamic member
- Using a function not supported by hybrid: Oracle documents these, and more functionality is added with each update
- Retrieving a member or its child when the parent is Label Only with the first child set to Never Consolidate (^)
- Attempting to retrieve accounts with XREF member formulas. This operation will fail altogether
Hybrid Application Design
Hybrid applications shine brightest when dense dimensions are used for calculations and sparse dimension upper-level members are tagged as Dynamic Calc; the idea being to use Dynamic Calc on sparse dimensions wherever possible. This can result in 97% reduction in PAG size for planning apps, and drastic reduction in large aggregation times in our experience.
For hybrid applications, use the HSP_NOLINK UDA on accounts with member formulas, as XREFs are not supported.
(Assigning HSP_NOLINK to Accounts)
Avoid setting members to Never Consolidate with a Label Only parent. If one of these members is used in a calculation or retrieve, the app will trigger its BSO engine and performance will suffer. Instead set the parent member to Store/Nevershare, or the child to Ignore Consolidation. An interesting note here: the Essbase log will read as though hybrid aggregation mode is enabled, but the calc/retrieve is performed with BSO technology.
(Never Consolidate member with Label Only parent)
Hybrid Tuning Tips
This technology comes with some quirks. For one, having many dynamic sparse aggregations can cause slow retrievals at times. Dimensions can be freely set to dense to address the issue.
Additionally, rollups within dynamic sparse dimensions can be set to store data to allow for calculation if the dimension necessitates heavier calculation. For example, if ‘West Markets’ is frequently calculated on within a sparse dynamic Market dimension, it can be set to store data without causing hybrid to fail.
(Setting ‘West Markets’ rollup to Stored)
Another topic we have faced is data copies on upper level sparse members; a common practice when performing allocation calculations. A hybrid-activated app will no longer perform data copies on upper-level members (assuming they’ve been changed to Dynamic Calc). One workaround is to fix on the copy target, and using a member statement instead of data copy to achieve effect. For example, the following calc script has a DATACOPY statement where “MarketMember” and “Product Line” are sparse upper-level dynamic members with hybrid activated. Essbase will not allow this calc to take place:
(DATACOPY with multiple Dynamic Calc sparse members in crossdim)
This script could be rewritten to validate and run with hybrid enabled:
(Script without DATACOPY on dynamic members)
Keep in mind that block creation may become an issue when moving from data copies to sparse member calcs, so keep an eye on this. Data copies involving base-level members only still function normally, dense or sparse.
Go Forth and Hybrid!
Hybrid Essbase has not yet been widely adopted or used. With these ideas in mind, our team would love to hear all questions, thoughts, and unique experiences you may have on the technology!
Feel free to reach us at:
Jay Adler, jadler@ecapitaladvisors.com
Amy Stine, astine@ecapitaladvisors.com
