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.
