My presentation at Collborate 2017 white paper

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Delete members in Essbase database using Java

The case discussed below will help clients to delete a selected hierarchy of Dimension and rebuild it in pure native Essbase application. This is not applicable to Hyperion Planning since all Essbase database information is stored in relational database for Planning. Below code makes use of Essbase API i.e. Java program talks to Essbase directly and not to any relational database.

Consider a Location Hierarchy below.

Location

AllLocations

FinLocations

StoreStatus

XXX

YYY

 

In Location Dimension, requirement was to delete FinLocations & StoreStatus and all members below it and rebuild it. Note that XXX & YYY should not be deleted.

We can make use of java program that can talk to Essbase via Essbase API. There are various functions in Essbase API that can help to login, connect & carry out several other Essbase related task on Essbase database. I made use of EditOutline.java for this purpose. It has a function called testDeleteDimension in it. testDeleteDimension has code to delete a specific member in a Essbase dimension. I had to modify the code to fit my requirement. It is straightforward.

First create an object for Location Dimension. Using this object handle you can add/delete dimension members as shown above. In my case , I am deleting FinLocation and StoreStatus from Location and then adding it under “AllLocations” later. Later FinLocations and Storestatus are built using a separate process.

I am also attaching the code for reference. You will need to run runsamplesEmbedded.cmd to compile the .java file to .class file.

List of all things that you need to know.

1)Check if E:\Oracle\MiddlewareEPM\EPMSystem11R1\common\EssbaseJavaAPI\11.1.2.0\samples exists. We will leverage java files present here.

2)Modify the appropriate java file present at F:\Oracle\Middleware\EPMSystem11R1\common\EssbaseJavaAPI\11.1.2.0\samples\japi

as per your need including username/password & server. In our case it is EditOutline.java.

3)You need to compile this java to generate .class file.

You can compile using runsamplesEmbedded.cmd . It is present in the path F:\Oracle\Middleware\EPMSystem11R1\common\EssbaseJavaAPI\11.1.2.0\samples\japi

It has the information of login,password and server

Challenges

You need to understand java for this. Once you make changes to .java file you must compile the java file using runsamplesEmbedded.cmd. Ensure that JAVA_HOME & Hyperion_Home are set correctly in runsamplesEmbedded.cmd file.Ensure that all jar files are present.eg ess_*.jar. Jar files are nothing but collection of (.java & .class) file

Data Copy in ASO cube based on UDA.

I had a requirement at a client where I had to copy data from one intersection to another based on UDA’s attached on Accounts dimension.

Following is a simple example that I worked on before real implementation on different cube.

Outline:

Below is the MDX script

This copies data from Actual-Jan-E1  to Forecast-Jan-E1 based on UDA on Account.

I have 2 Accounts Sales and A2. Only Sales has UDA ie MYUDA.

Note escape characters in MYUDA. You need this or else it will throw syntax error.

I have made use of INTERSECT here.

INTERSECT function gets me the common eg

Intersect(

{[New York], [California], [Oregon]},

{[California], [Washington], [Oregon]}

)

returns the set: {[California], [Oregon]}

In the above example, it will return only those Accounts that have UDA as “MYYDA” attached to it.

Output

Note here that only Sales Account has UDA attached. So it copied only Sales data from Actual to Forecast.

 

Tips for implementing of Dynamic Time series in ASO cube (Version 11.1.2.3)

Today I will discuss the common issue that I encounter at various clients. ie implementing Dynamic time series in 11.1.2.3.

Typically client would need TBlast( eg headcount)/TBFirst(eg revenue/expense/liability accounts) feature in Accounts Dimension. Accounts hierarchy is typically selected as Dynamic type hierarchy since we would need alternate hierarchies and dynamic formulas in Accounts Dimension. The other advantage is that Period dimension can have Time balance functionality i.e. we can have apply formulas in period dimension if Accounts dimension is Dynamic. If the Accounts dimension is Stored hierarchy type,then Formulas cannot be applied in Period Dimension.

It is also important to note that if we are retrieving or creating dashboards via OBIEE these type of functionality works best instead of dynamic Time series inbuilt in Essbase. OBIEE dashboards may not support dynamic time series in built in Essbase.

Implementation of YTD/QTD is straight forward.

1)Let Account Dimension be Dynamic(Hierarchy  Type)

2)Let Period Dimension be Multiple Enabled Hierarchy type

3)Tag UDA as TBFirst/TBLast in Account dimension for each member.

4)Place formulas in YearTotal/Q1/Q2/Q3/Q4 and QTD-Jan/Feb..so on and YTD-Jan-Feb and so on.

Note that NOT ISEMPTY in member formula is very important for better retrieval performance.

 

 

Formulas are shown below .Hope this helps.

YearTotal

CASE

WHEN (ISUDA([Account].CurrentMember, “TB_Last”))

THEN (

IIF(NOT ISEMPTY([Q4]), [Q4],

IIF(NOT ISEMPTY([Q3]), [Q3],

IIF(NOT ISEMPTY([Q2]), [Q2],

IIF(NOT ISEMPTY([Q1]), [Q1],

MISSING))))

)

ELSE [Q1] + [Q2] + [Q3] +[Q4]

END

 

=======================================

Q1

CASE

WHEN (ISUDA([Account].CurrentMember, “TB_Last”))

THEN (

IIF(NOT ISEMPTY([Mar]), [Mar],

IIF(NOT ISEMPTY([Feb]), [Feb],

IIF(NOT ISEMPTY([Jan]), [Jan],

MISSING))))

ELSE [Jan] + [Feb] + [Mar]

END

=======================================

Q2

CASE

WHEN (ISUDA([Account].CurrentMember, “TB_Last”))

THEN (

IIF(NOT ISEMPTY([Jun]), [Jun],

IIF(NOT ISEMPTY([May]), [May],

IIF(NOT ISEMPTY([Apr]), [Apr],

MISSING))))

ELSE [Apr] + [May] + [Jun]

END

=======================================

QTD -Feb

CASE

WHEN (ISUDA([Account].CurrentMember, “TB_Last”))

THEN (

IIF(NOT ISEMPTY([Feb]), [Feb],

IIF(NOT ISEMPTY([Jan]), [Jan],

MISSING)

)

)

ELSE [Jan] + [Feb]

END

=======================================

YTD -Apr

CASE

WHEN (ISUDA([Account].CurrentMember, “TB_Last”))

THEN (

IIF(NOT ISEMPTY([Apr]), [Apr],

IIF(NOT ISEMPTY([Mar]), [Mar],

IIF(NOT ISEMPTY([Feb]), [Feb],

IIF(NOT ISEMPTY([Jan]), [Jan],

MISSING))))

)

ELSE [Jan]+[Feb]+[Mar]+[Apr]

 

END

=======================================

FullYear

[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]

Data Transfer from HFM application to Planning

Today I will discuss the things to look out for using data transfer in EPMA from HFM to Planning application(Version 11.1.2.3).There are 2 ways in which data can be moved from HFM to Planning

1)Extended analytics & Essbase rul file

2)For EPMA applications ,use of data sync(Much easier option)

3)FDMEE(Will not be discussed in this post)

1)Extended analytics & Essbase rul file

The whole idea here is to extract data from HFM using EA(Extended Analytics).Data extracted is automatically stored in star schema type arrangement i.e fact table and attached dimension table. Once the data is inserted into these tables,you can create a SQL view that can be consumed my essbase rule file.

You will first need to create a DSN for this extract.

 

Below is the view when you click POV for extract

 

 

SQL VIEW Example

CREATE VIEW VIEWNAME AS
SELECT *
FROM HFMAPPNAME_FACT Z
LEFT JOIN HIHFM20_SCENARIO A ON Z.SCENARIOID = A.ID
LEFT JOIN HIHFM20_YEAR B ON Z.YEARID = B.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_ENTITY) C
ON Z.ENTITYID = C.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_CUSTOM4) D
ON Z.CUSTOM4ID = D.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_CUSTOM3) E
ON Z.CUSTOM3ID = E.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_CUSTOM2) F
ON Z.CUSTOM2ID = F.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_CUSTOM1) G
ON Z.CUSTOM1ID = G.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_ICP) H
ON Z.ICPID = H.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_PERIOD) I
ON Z.PERIODID = I.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_ACCOUNT) J
ON Z.ACCOUNTID = J.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_value) K
ON Z.VALUEID = K.ID
LEFT JOIN (SELECT DISTINCT ID, LABEL
FROM HFMAPPNAME_view) L
ON Z.VIEWID = L.ID
WHERE Z.DDATA <> 0
OR K.LABEL <> ‘<Entity Currency>’
OR K.LABEL <> ‘<Entity Curr Adjs>’

It is very important to understand how the data is stored in these tables.When you select ‘<Entity Currency>’ & ‘<Entity Curr Adjs>’ ,it will give you all local currency ie USD,EUR,GBP etc in addition to system defined members ” & ” .Play around with the EA extract & tables view to understand it fully.I would recommend just extract one small intersection with different combination of Value Dimension especially Entity Currency & Entity Curr Adjs ,USD USD Adjs etc for one year and one month.

Then using Essbase rul file ,data can be loaded into Essbase application.

2)For EPMA applications ,use of data sync(Much easier option)

This is a applicable only for EPMA Applications.

It is very easy to build a data sync but I have found out that it takes more time than EA for transferring data from HFM to Planning.Planning to Planning/Planning BSO to ASO data sync works fine but HFM to Planning/Essbase might be slow depending on the data size.

The whole idea is is that you have a HFM source and HFM target.You make a connection between two and include mapping eg 2016 in HFM can be mapped to FY16 in Planning/Essbase.

If you are coming from Essbase world it is important to understand the value dimension in HFM to be able to understand data transfer .Atleast you should know Entity Currency & Entity Curr Adjs .In essbase combination of these 2 will go into Local currency.

As you can see below Source is HFM and Target in Planning.

Value dimension mapping is shown below which is most crucial part of this mapping.Here Plan=PlanBase + Planadjs in Essbase.