ODI Series - Loading HFM data

In the last ODI – HFM blog I went through loading metadata into a HFM application, today I am going to take a quick back to basics look at loading data into the same application.

In the last part I loaded 5 new members into the application


Today I am going to load data into the three bottom level members, the members aggregate to the next level so I don’t need to bother loading any data into the higher levels.



I started out with a basic HFM form that I am going to populate with data, I have just designed this form for demonstration purposes as I know you would usually use a form to manually enter data.



In the designer if you expand your HFM application model you will see a DataStore called HFMData, this will be used as the target and contains all the dimensions of the application and a column DataValue to load the values into. 



The overall process is simple enough :- create your source, in this example is going to be a flat file, create an interface with source and target plus mapping logic, set options and execute.



To make life simpler and have a source file DataStore that matches the HFMData DataStore I duplicated the DataStore



As my source is going to be flat file, I dragged the copy into my File Model as shown below



So now I have a file datastore that is an exact match of the target, I just have to set up some of the properties for the DataStore



I updated the name, alias and resource name to point to a CSV file



I set the File Format to be delimeted as it is going to a csv file, comma as the field separator and quote as the Text Delimeter.

Obviously you don’t have to follow my method, this method is just quick and easy if the source and target match.

Your source may be totally different and so would just have to go down the route of reverse engineering it first, I have covered this area in blogs in the past.



Next I created the source file with the columns to match the columns of the DataStore, I populated with some dummy data against the POV of the web form I designed. The file was then moved to the location where the logical schema used in file model points to (defined in the topology manager)



Next I created a new interface to perform the data load, I am using the memory engine as the staging area this is just because I have a small amount of data. I only really use the memory engine when the source data is small or the transformations are lightweight.



In the diagram the source file DataStore was dragged on to the source diagram and the HFMData DataStore on to the target, as the source and target columns are identical all the mapping were automatically done. I am not uses any additional logic so I can kept the default mappings.



The IKM selected for loading is “IKM SQL to Hyperion Financial Management Data”.

The IKM has a number of options
 :-

CONSOLIDATE_ONLY 


Valid values: Yes and No. If set to Yes, data is consolidated but not loaded.

IMPORT_MODE
Determines how data in the application cells is handled during data load. 


Valid values:

Merge (default)—For each unique point of view that exists in the load data and in the
application, the load data overwrites the data in the application. For each unique point
of view that is in the load data but not in the application, the load data is loaded into
the application.


Replace—For each unique point of view in the load data, the system clears
corresponding values from the application, and then the data is loaded.


Replace by Security—For each unique point of view in the load data to which the user
has full access rights, the system clears corresponding values from the application, and
then the data is loaded. Cells to which the user lacks full access are ignored.


Accumulate—For each unique point of view that exists in the load data and in the
application, the value from the load data is added to the value in the application.


ACCUMULATE_WITHIN_FILE
Valid values: Yes or No (default)
If set to Yes, multiple values for the same cells in the load data are added before they are
loaded into the application.


FILE_CONTAINS_SHARE_DATA
Valid values: Yes or No (default)
Set to Yes if the load file contains ownership data, such as shares owned.

CONSOLIDATE_AFTER_LOAD
Valid values: Yes or No (default)
If set to Yes, data is consolidated after being loaded.



CONSOLIDATE_PARAMETERS

Specifies the parameters for consolidation as comma separated values in this order: Scenario (required), Year, Period, Parent.Entity, and Type..

Valid Type parameter settings:
“I” = Consolidate

“D” = Consolidate All with Data
“A” = Consolidate All
“C” = Calculate Contribution
“F” = Force Calculate Contribution

Example : Actual,2010,2,Canada,A

I just kept all the default options that meant I would merge the data, I did enter the path and filename for the log.



Executing the interface was successful.



As you can see the data has been loaded into the HFM application and the data can now be seen on the Form.



So what if my data was not unique and I need to sum up the values. I added extra line in my data file, if I use the default merge method the non-unique records would get overwritten.




Actual Equipment has been loaded with a value of 50, the first record with a value of 1000 is loaded and then overwritten with the next record for that POV with 50.





To accumulate the data you can either set the IMPORT_MODE to Accumulate, this would added to existing values in the application for the POV data point being loaded.



This time any data against a POV that already has data against it is accumulated



Or you can set ACCUMULATE_WTHIN_FILE to Yes, this would sum up the data from the source before it is actually loaded into the planning application.



The IMPORT_MODE is set to merge this means that the existing data in the application is replaced but because I have set accumulate within file to yes it aggregates all the data first before loading into the application



If I wanted to consolidate data after the load I would set CONSOLIDATE_AFTER_LOAD to Yes and set the POV and parameters in the option CONSOLIDATE_PARAMETERS.


So there we go a nice easy and quick example to loading data in a HFM application, this should give you a good starting point to building your own data loads.