Category: ASO

Use case for building profitability cube in Essbase for clients having OBIEE/OBIAW license

We are recently implementing Essbase studio at a Consumer goods company that had OBIEE with OBIAW license. They do not have Hyperion Planning License.

Note that OBIEE comes with Essbase studio license. You can then use this license to create an Essbase cube based of a data warehouse schema. We used OBIEE license to spin off a profitability ASO cube based on data warehouse.

All the allocations down to product/SKU number were done in relational database. Client deferred the HPCM license for now for budget reasons.

Essbase studio implementation is quite simple. First create connection for warehouse and Essbase. Bring in all tables from warehouse and create a mini schema (connection of facts and dimension tables). Manipulate you dimension elements and hierarchy. Then create essbase cube schema and deploy Essbase cube. We used the customized SQL to build data load since SQL generated from Essbase studio was inefficient and slow. Try to make a clean join between your fact and dimension tables. Follow all rules of SQL query optimization.

One of the issue we found was that sometimes Essbase studio did not load/deploy all properties for Essbase ASO cube reflected in relational database. E.g. members formula and consolidation operators. We did refresh at dimension element and cube schema level. That did not work, We had to delete cube schema and rebuild it again from scratch. That resolved our issues. Apart from rest everything was smooth.

Many ratios were built into ASO cube to give better insights e.g. COGS/NetSales, Gross Sales/Net Sales, Net Sales/Unit, Advertising Expense as a Percentage of Net Sales, SG&A expense /net Sales etc.

Please feel to reach out to me in case of any questions on implementation.

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]