Wednesday, February 19, 2014

FDMEE Custom Solution to Load Multiple Periods Through Single POV - Part 2

In post FDMEE Custom Solution to Load Multiple Periods Through Single POV - Part 1 I showed a custom solution to load data for multiple periods in HFM.

What about Hyperion Planning or Essbase?
Before shedding some light on it let's explain how data is loaded into Essbase (when we load either to HP or Essbase we are actually loading into Essbase cubes) from table TDATASEG. When  we configure our target application in FDMEE we can see two methods in application options:
  • File: data is exported to a .DAT file (ApplicationName_ProcessID) in OUTBOX folder and then loaded into Essbase. This method already existed in classic FDM.
  • SQL: data is loaded from a SQL View in the FDMEE database. This method has been added in FDMEE.

In Classic FDM, the .DAT was loaded using the Essbase Adapter API (LOAD ACTION script). You also had the option of configuring Essbase adapter to use an Essbase Rules File in order to use additional functionality when loading like adding/subtracting instead of standard replace/merge modes.

This has been slightly redesigned in FDMEE:
- There are 4 export modes: store data, add data, subtract data, override all data (only for HP)
Store Data: inserts the data by replacing any value that currently exists.
Add Data: adds the value from the source to the value that exists in the target application
Subtract Data: subtracts the value in the source from the value that exists in the target application.
Override All Data: clears all data in the target, and then loads from the source. For example if you have a year of data in your planning application, but are only loading a single month, this option clears the entire year before performing the load.
- FDMEE creates an Essbase Rules File either loading with File or SQL method. The rules file has one column for each target dimension. Name follows AIFXXXX convention where XXXX is the data load rule id (with zero left padding) 
You can check the rule id in different places like the FDMEE process log or table AIF_BALANCE_RULES:
 
The only drawback I see is that you cannot modify this rules file in EAS because it is overwritten every time data is loaded from FDMEE.
When running data loads you can check the FDMEE process to see how rules file is processed:
 Going back to load methods... we already know the file method from HFM data loads. What about SQL method? from which SQL view is data extracted?
It depends on which your target is:
- HP single currency or Essbase: AIF_HS_BALANCES
- HP multi currency: AIF_HS_BALANCE_HPL_MC_V
You can easily get the SQL query used to extract data from TDATASEG table if you open the rules file and then navigate to File > Open SQL. SQL queries are filtered by LOADID in order to get data for current data load:
If you are interested in technical details for the SQL views you can have a look to them with SQL Developer or any other SQL tool:

Loading multiple periods through single POV
After some technical introduction about data load methods for HP and Essbase it's time to continue with the initial purpose.
Taking into consideration that we usually load large volumes of data into HP/Essbase and that we cannot modify the Essbase rules file... I will propose using the SQL Method.
As occurs with HFM, the main objective is to load LOOKUP dimension Period_Custom instead of the POV period:
What about modifying the SQL query AIF_HS_BALANCES?
We export UD10 (lookup dimension) for our target application instead of target period for active POV. We can see how data is loaded:
If you take a look to the web form above you will notice that there are only two drillable cells. These two cells have December data which is actually the active POV in FDMEE from which data was loaded. We can check the FDMEE process log:
What I mean is that the drawback of loading multiple periods through single POV is that you cannot take drill-through from all periods just for the POV you loaded data from.
With some imagination you can use Description-1 field to show your custom period in order to have a better drill-through :-)
Note that currently lookup dimensions are not available as columns in the drill-through landing page.

Unpublished Bug: LOOKUP dimensions not being mapped in HP applications
You will also notice that LOOKUP dimensions are not mapped when using HP. There is a workaround for that. You will need to update column VALID_FOR_PLAN1 with value 1 for your LOOKUP dimension in table AIF_TARGET_APPL_DIMENSIONS before maps happen. Then before data is loaded into HP you will need to set this value back to 0 or load will fail as it will think your LOOKUP dimension is a valid dimension in HP.

Enjoy!

10 comments:

  1. Hey Francisco,

    short Question. Is there a trick to show the account description in Planning ?! I just defined Descriptiion 1 in the import format as i would do it in HFM but no description is shown. In HFM it works perfectly ?!

    Regards

    Tim

    ReplyDelete
    Replies
    1. Hi,
      It should be working in Planning as well.
      did you check you have correctly assigned your file field in the Import Format?

      Delete
  2. Hello,

    Great hack! However, could-you tell me where I have to insert the code in the original AIF_HS_BALANCES query ?

    Here the original one:
    SELECT bal.LOADID ,COALESCE(pa.YEARTARGET, p.YEARTARGET) YEAR ,CASE c.CATFREQ WHEN 'M' THEN COALESCE(pa.PERIODTARGETM, p.PERIODTARGETM) WHEN 'Q' THEN COALESCE(pa.PERIODTARGETQ, p.PERIODTARGETQ) WHEN 'Y' THEN COALESCE(pa.PERIODTARGETY, p.PERIODTARGETY) WHEN 'D' THEN COALESCE(pa.PERIODTARGETD, p.PERIODTARGETD) ELSE COALESCE(pa.PERIODTARGETM, p.PERIODTARGETM) END PERIOD ,COALESCE(ca.CATTARGET, c.CATTARGET) SCENARIO ,CASE WHEN (bal.STAT_BALANCE_FLAG = 'Y') THEN part.PARTCURRENCYKEY ELSE bal.CURRENCY END CURRENCY ,bal.DATAVIEW ,part.PARTDATAVALUE DATAVALUE ,bal.ACCOUNT ,bal.ENTITY ,bal.ICP ,bal.UD1 ,bal.UD2 ,bal.UD3 ,bal.UD4 ,bal.UD5 ,bal.UD6 ,bal.UD7 ,bal.UD8 ,bal.UD9 ,bal.UD10 ,bal.UD11 ,bal.UD12 ,bal.UD13 ,bal.UD14 ,bal.UD15 ,bal.UD16 ,bal.UD17 ,bal.UD18 ,bal.UD19 ,bal.UD20 ,bal.AMOUNT ,bal.JOURNALID FROM ( ( SELECT LOADID ,PARTITIONKEY ,CATKEY ,PERIODKEY ,DATAVIEW ,CURKEY CURRENCY ,ACCOUNTX ACCOUNT ,ENTITYX ENTITY ,ICPX ICP ,UD1X UD1 ,UD2X UD2 ,UD3X UD3 ,UD4X UD4 ,UD5X UD5 ,UD6X UD6 ,UD7X UD7 ,UD8X UD8 ,UD9X UD9 ,UD10X UD10 ,UD11X UD11 ,UD12X UD12 ,UD13X UD13 ,UD14X UD14 ,UD15X UD15 ,UD16X UD16 ,UD17X UD17 ,UD18X UD18 ,UD19X UD19 ,UD20X UD20 ,SUM(AMOUNTX) AMOUNT ,COALESCE(STAT_BALANCE_FLAG, CASE WHEN (CURKEY = 'STAT' OR CURKEY = ' ') THEN 'Y' ELSE 'N' END) STAT_BALANCE_FLAG ,JOURNALID FROM TDATASEG WHERE VALID_FLAG = 'Y' AND CALCACCTTYPE > 0 GROUP BY LOADID ,PARTITIONKEY ,CATKEY ,PERIODKEY ,DATAVIEW ,CURKEY ,ACCOUNTX ,ENTITYX ,ICPX ,UD1X ,UD2X ,UD3X ,UD4X ,UD5X ,UD6X ,UD7X ,UD8X ,UD9X ,UD10X ,UD11X ,UD12X ,UD13X ,UD14X ,UD15X ,UD16X ,UD17X ,UD18X ,UD19X ,UD20X ,STAT_BALANCE_FLAG ,JOURNALID ) bal INNER JOIN TPOVPARTITION part ON part.PARTITIONKEY = bal.PARTITIONKEY INNER JOIN AIF_TARGET_APPLICATIONS app ON app.APPLICATION_ID = part.PARTTARGETAPPLICATIONID INNER JOIN TPOVCATEGORY c ON c.CATKEY = bal.CATKEY LEFT OUTER JOIN TPOVCATEGORYADAPTOR ca ON ca.CATKEY = c.CATKEY AND ca.INTSYSTEMKEY = app.TARGET_APPLICATION_NAME ) INNER JOIN TPOVPERIOD p ON p.PERIODKEY = bal.PERIODKEY LEFT OUTER JOIN TPOVPERIODADAPTOR pa ON pa.PERIODKEY = p.PERIODKEY AND pa.INTSYSTEMKEY = app.TARGET_APPLICATION_NAME



    thanks!

    ReplyDelete
    Replies
    1. Hi, it depends on your logic. You can use CASE statement for PERIOD column which is the one being exported.
      Thanks

      Delete
  3. Custom software development specialists explore exactly of the company are on they need from their IT system in order to operate to their optimum.

    ReplyDelete
  4. Hi Francisco,

    For single period loads from a file with multiple period columns, I use and import script that chooses the column that corresponds to the period chosen in the POV that I will share with you here:
    def ChoosePeriodColBasedOnPOV (strField, strRecord):
      retval = 0
      indexOffset = 9

      try:
        monthsIndexedByColNum = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
        retval = float(strRecord.split("|")[monthsIndexedByColNum.index(fdmContext["PERIODNAME"][:3])+indexOffset])
      except Exception, e:
        fdmAPI.logError(str(e) + " - " + strRecord)
      finally:
        return retval

    ReplyDelete
  5. Hi Robb,
    thanks for sharing.

    You could also have 12 versions of the import format and change it dynamically in the befimport based of pov period.

    this is also a good solution if your import format is static and easy to maintain.

    Regards

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Thanks Francisco, Great Idea. Implemented it successfully.

      Delete

Thanks for feedback!