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!
