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.