COLLABORATE — OAUG Forum Essbase Hybrid Cube: Learn the basics
Manohar Anchan Presenter
Nolina Consulting, Inc.
Abstract:
Essbase Hybrid cube is a new OLAP engine for faster aggregation and complex calculation. Hybrid cube is a big leap from traditional and popular BSO (Block storage outline) cube and most recent instant aggregation ASO (Aggregate storage outline) cube. New Hybrid will reduce the budget and forecasting cycle time for organization enormously and reduce maintenance time by having fewer cubes.
History of Essbase
Essbase is very powerful OLAP multidimensional database engine used mainly for budgeting and forecast in corporate finance. Essbase was typically seen as the extension of excel with added functionality for budgeting. Essbase was initially developed by Arbor in early 90’s. Hyperion & Arbor merged in 1998. Oracle acquired Hyperion in 2007 for 3.3 billion dollars. As per Oracle press release in 2007, 12,000 companies were using Hyperion software including 91 of the Fortune 100 Essbase Named as “Top Ten Technology Innovation of The Decade” By Information Age Magazine in 2005.
Early version of Essbase included only BSO but later ASO came along the way after version 7. Now the latest Hybrid cube has been added to its arsenal. Today’s Essbase is far more scalable and better than several years back. Currently lot of Oracle products are built around Essbase e.g. Planning (On premise & cloud version), FCSS (Financial Consolidation and Close Cloud Service). There are also plans to build HFM (Hyperion Financial management) around Essbase in future.
ASO & BSO cube
Currently ASO cube and BSO cube are widely used in industries for budgeting and forecasting. Users leverage the faster aggregation in ASO cube and use the BSO for complex calculations. The combination of BSO and ASO gives a robust solution. BSO used a proprietary language for calculations whereas ASO uses MDX syntax for calculation.
BSO offers lot of flexibility as far as calculation is concerned compared to ASO cube. ASO is faster aggregation engine. Companies use BSO cube for their calculation and the transfer data from BSO to ASO cube for aggregation.
What is Hybrid cube?
Essbase hybrid aggregation cube is combination of block storage functionality with aggregate storage performance. Hybrid cube make use of aggregate storage query processor and the block storage query processor. Internally Hybrid makes decision whether to make use of aggregate storage query processor or the block storage query processor. Logs can reveal the extent of aggregate storage query processor used in the cube thus, helping users to fully understand the benefits of Hybrid cube.
How to build a Hybrid cube?
Essbase hybrid cube building process is straightforward. Anyone with knowledge of ASO and BSO cube will find it easy to build the cube. The starting point for building Hybrid cube is to have a BSO cube. Users can create a new cube or leverage an existing BSO cube to enable Hybrid aggregation mode. Once the BSO cube has been set up, enable a hybrid aggregation mode using a setting in Essbase.config file i.e.
ASODYNAMICAGGINBSO [appname [dbname]] NONE | PARTIAL | FULL
Appname: Optional. Specifies the application for which hybrid aggregation mode is used. If you specify a value for appname and do not specify a value for dbname, the setting applies to all databases in the specified application. To enable the setting for a specific database, you must specify an application and database.
Dbname: Optional. Specifies the database, in the application specified by appname, for which hybrid aggregation mode is used.
NONE-Disable hybrid aggregation in block storage databases. This is 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. A sample of Essbase.config file is give below in Fig 1.
Fig 1
Once the ASODYNAMICAGGINBSO has been enabled then upper level Sparse members need to set to dynamic. Fig 2 below shows upper level member tagged as dynamic calc.
Fig 2
The Application & database needs to restarted to activate Hybrid mode for cube.
Functions in Hybrid cube
It is crucial to understand the limitation of Hybrid cube before beginning any development work. They many functions that are traditionally supported to BSO cube not valid for Hybrid mode. Below is the list of all the functions that are supported and not supported for Hybrid cube.
Following is the complete list of supported and unsupported functions in Hybrid cube version 11.1.2.4
| Supported Functions |
|
|
| @ABS |
@ISDESC |
@MODE |
| @ALLANCESTORS |
@ISGEN |
@NAME |
| @ALIAS |
@ISIANCEST |
@NEXT |
| @ANCESTORS |
@ISIBLINGS |
@NEXTS |
| @ATTRIBUTE |
@ISICHILD |
@NEXTSIBLING |
| @ATTRIBUTEBVAL |
@ISIDESC |
@NOTEQUAL |
| @ATTRIBUTESVAL |
@ISIPARENT |
@POWER |
| @ATTRIBUTEVAL |
@ISISIBLING |
@PREVSIBLING |
| @AVG |
@ISLEV |
@PRIOR |
| @BETWEEN |
@ISMBR |
@PRIORS |
| @CALCMODE |
@ISMBRUDA |
@RANGE |
| @CHILDREN |
@ISMBRWITHATTR |
@RANGEFIRSTVAL |
| @CONCATENATE |
@ISPARENT |
@RANGELASTVAL |
| @COUNT |
@ISRANGENONEMPTY |
@RANK |
| @CURGEN |
@ISSAMEGEN |
@RDESCENDANTS |
| @CURLEV |
@ISSAMELEV |
@RELATIVE |
| @DATEDIFF |
@ISSIBLING |
@RELXRANGE |
| @DATEPART |
@ISUDA |
@REMAINDER |
| @DATEROLL |
@LANCESTORS |
@REMOVE |
| @DESCENDANTS |
@LDESCENDANTS |
@RETURN |
| @ENUMVALUE |
@LEV |
@ROUND |
| @EQUAL |
@LEVMBRS |
@RSIBLINGS |
| @EXP |
@LIKE |
@SHARE |
| @EXPAND |
@LIST |
@SHIFTMINUS |
| @FACTORIAL |
@LN |
@SHIFTPLUS |
| @FORMATDATE |
@LOG |
@SHIFTSIBLING |
| @GEN |
@LOG10 |
@SIBLINGS |
| @GENMBRS |
@LSIBLINGS |
@SPARENTVAL |
| @IALLANCESTORS |
@MATCH |
@SUBSTRING |
| @IANCESTORS |
@MAX |
@SUM |
| @ICHILDREN |
@MAXRANGE |
@SUMRANGE |
| @IDESCENDANTS |
@MAXS |
@TODATE |
| @ILANCESTORS |
@MAXSRANGE |
@TODATEEX |
| @ILDESCENDANTS |
@MBRCOMPARE |
@TODAY |
| @ILSIBLINGS |
@MBRPARENT |
@TRUNCATE |
| @INT |
@MEDIAN |
@UDA |
| @INTERSECT |
@MEMBERAT |
@VAR |
| @IRDESCENDANTS |
@MERGE |
@VARPER |
| @IRSIBLINGS |
@MIN |
@VARIANCE |
| @ISACCTYPE |
@MINRANGE |
@VARIANCEP |
| @ISANCEST |
@MINS |
@WITHATTR |
| @ISATTRIBUTE |
@MINSRANGE |
|
| @ISCHILD |
@MOD |
|
| Below functions not supported in version 11.1.2.4 |
| @ACCUM |
@MOVAVG |
| @ALLOCATE |
@MOVMAX |
| @ANCEST |
@MOVMED |
| @ANCESTVAL |
@MOVMIN |
| @AVGRANGE |
@MOVSUM |
| @COMPOUND |
@MOVSUMX |
| @COMPOUNDGROWTH |
@NPV |
| @CORRELATION |
@PARENT |
| @CREATEBLOCK |
@PARENTVAL |
| @CURRMBR |
@PTD |
| @CURRMBRRANGE |
@SANCESTVAL |
| @DECLINE |
@SHIFT |
| @DISCOUNT |
@SLN |
| @GROWTH |
@SPLINE |
| @INTEREST |
@STDEV |
| @IRR |
@STDEVP |
| @IRREX |
@STDEVRANGE |
| @MDALLOCATE |
@SYD |
| @MDANCESTVAL |
@TREND |
| @MDPARENTVAL |
@XRANGE |
| @MDSHIFT |
@XREF |
| @MEMBER |
@XWRITE |
Hybrid mode condition for formulas
Hybrid mode can execute query in Classic mode or Hybrid mode depending on few conditions. There are certain conditions that needs to be met for a formula to be executed in Hybrid mode. If the sparse member from one dimension is assigned sparse member from other dimension, then formula will be executed in Hybrid mode. If the dense member from one dimension is assigned dense member from other dimension, then formula will be executed in Hybrid mode. If the sparse member from one dimension is assigned sparse and dense member combination from other dimensions, then formula will be executed in Hybrid mode.
If the any of above condition is not satisfied, then query will be executed in classic BSO mode. This means that the retrieval performance can be slower depending on the size of the database.
Below are few examples from logs file when the Hybrid mode is enabled or disabled.
Fig 3
Fig 4
Tips for optimization
There are few tips to keep in mind while implementing Hybrid cube. User need to be aware of all condition that needs to be met for enabling query in Hybrid mode. Most of BSO optimization rules apply that apply for BSO cube also apply for Hybrid cube for calc script and data loading.
Upper level reference in Hybrid cube can make calculation slow so avoid upper level dynamic reference in calc script. If a query mixes supported and unsupported hybrid mode calculation types, Essbase defaults to block storage execution. The work around for the above would be to stored data at level zero and execute calculation around it.
Business users will always use data as much as possible. So, if database size if huge, then exalytics box is recommended. It is a better idea to QRYGOVEXECTIME & QRYGOVEXECBLK to ensure that your system does not hang below two parameters can be set in Essbase.config file.
i.e. SET QRYGOVEXECTIME [appname [dbname]] n
This sets max amount of time a query can execute before being terminated in seconds
SET QRYGOVEXECBLK [appname [dbname]]
This sets max number of blocks a query can retrieve before being terminated.
Examples
Some of typical scenarios and example are discussed below that are being used widely at many other clients. Corporate finance does lot of driver’s base calculations i.e. clients used different types of drivers to drive their budgeting and forecasting process.eg merit percentage, gross margin % across various Entities etc. Changing these drivers can help them to do what if analysis in Essbase on the fly. Customers can also make use of data copy command. This can be used if the customer is working on draft version of budget. Once the draft version of budget is finalized they can copy it to Final version. The final version will be ready only copy that can be view by CFO/CEO of the organization. Currency conversion can be done in hybrid cube based the exchange rates drivers on automated basis. Each Entity of Business unit is associated with a tag named UDA e.g. property that defines business unit e.g. USD/EUR/MXN etc. that drives the currency calculation. Dynamic Member formula is used in the Essbase cube outline e.g. EBDITA, Growth Margin%, Financial ratios etc.All the scenarios described above can be implemented in Hybrid cube with much better results and faster efficiency.
Are we there yet?
There have being vast improvements with Oracle 11.1.2.4 release. Some of scenarios covered above account for almost 75% of calculation used at various clients but there are few limitations too. As discussed above not all functions are supported in this version but there are workarounds. As per Oracle Technical reference guide, following limitation currently exits as for version 11.1.2.4 i.e. Attribute calculations, Formulas with cross-dimensional operators, Dynamic Calc members with formulas that are a target of transparent partitions, Queries which include both two-pass and one-pass dynamic calc members from the same dimension, XOLAP, Text measures / text lists. If the client is made aware of these limitations and if they can find creative ways to resolve problems around these limitations, then Hybrid cube would be a perfect solution. It also depends of risk appetite of the client for hybrid cube implementation. But one thing is sure, Hybrid cube will only get better and users will see less and less use of BSO plus ASO combinations in coming few years.
This copies data from Actual-Jan-E1 to Forecast-Jan-E1 based on UDA on Account.
