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]

Author: Manohar Anchan

My name is Manohar Anchan ,founder & CEO of Nolina Consulting Inc. I have been in Hyperion consulting for past 17 years.I have been working in Essbase,Planning with addon knowledge of HFM/OBIEE/DRM/ODI/FDMEE. Here I look forward to share my knowledge in Hyperion in the hope that it will be useful for developers/clients,consultant etc.