TAGS

Recent Posts

Archives

Dissecting Data Load Options in Hyperion Financial Management (HFM)
Posted on September 24, 2014
Author: Joseph Francis, Performance Architects

In Hyperion Financial Management (HFM), you can load data using a text file; Oracle Financial Data Quality Management Enterprise Edition (FDMEE) in the newest version of Hyperion; or ERP Integrator (ERPi) in earlier versions of Hyperion.  This blog covers the first method, loading a text file.

The general structure of a data load text file is comprised of a Group Dimension Section and a Data Section.  I call these out as they are going to come into play as we focus on data load options.  In HFM Version 11.1.2.3.500, you load data from the application by navigating to Consolidation -> Load -> Data:

Joef1

When it comes to loading data from a text file into an application, you have multiple options: Merge, Replace by Security, Replace, Accumulate, and Accumulate within File.

joef2

To demonstrate how these options work, we are going to load the same trusty data load file and see how and if results differ.

Merge Option

The Merge option essentially overwrites data in the application with data from the load file.  The saving grace is that application data that is not changed by the load file remains unchanged in the application.  Here are the results after loading the data load file with this option:

Application Data:  joef3   

Data Load File:joef4

Result of data load using Merge option:joef5

With the Merge option you can see that the Cash and Trade Receivables data from the data load file rules the roost and overwrites the application data.  However, since there was no data in the file for Marketable Securities, the application data for this account remains in the system.

Replace Option

The Replace option replaces data in the application with data from the load file.  Maybe this sounds the same as the Merge option to you?  It’s not.  For each combination of the Scenario, Year, Period, Entity and Value dimensions, the Replace option clears all data values from the application.  After the values are wiped out, the data from the data load file is pushed into the system.  The result looks like this:

Application Data: joef6      

Data Load File: joef7

Result of data load using Replace option: joef8

Notice that with the Replace option, the Marketable Securities value has been wiped clean.  This is because it shares the same unique combination of the Scenario, Year, Period, Entity and Value dimensions as stipulated in the Group Dimension Section of our load file below:

joef9

Replace by Security Option

I am not going to devote much space to this option, but essentially you can use the Replace mode only on the dimension members to which you have access.

Accumulate Option

The Accumulate option gives you the best of both worlds, where the data from the data load file is added to the data in the application.  Results for this option look like this:

Application Data: joef10

Data Load File: joef11

Result of data load using Accumulate option: joef12

Accumulate within File Option

The last load option we will investigate is the Accumulate within File option.  Do not confuse this with the Accumulate option covered previously, as they are not the same.  The Accumulate within File option does not appear in the Load Mode drop down, but rather it is displayed next to a check box in the Default Load Options pane highlighted below (as an aside, other Default Load Options include File Contains Ownership Data and File contains Process Management Data, which are topics for another day):

joef13

Consider Accumulate within File an “add-on” to the Merge and Replace load modes.  The Merge and Replace methods will function as outlined above, but with a twist.  With the Accumulate within File option checked, when a data load file contains multiple lines of data for the same point of view, the lines are added together and their total is loaded.  What I didn’t tell you before (truly for your own good – this is to keep your head from spinning!) is that the basic Merge and Replace options will only load the last record for multiple lines of data for the same point of view.

Confused?  Let’s examine one more example, but this time we’ll look to the data load file for an explanation.  Imagine the Data Section (in the red box) of our data load file looked like this:

joef14

In this case, we have two lines for Cash with data for the same intersection (100 and 200).  If we just used the basic Merge option when loading this file we would see a value of 200 populate this cell in our application, as 200 represents the last record for this data point.  However, if we used Merge and checked the Accumulate within File option, our result would be a value of 300 for Cash as it would total the two records (of 100 and 200).  Let’s compare the results of Merge versus Merge with Accumulate within File: 

Result of data load using Merge option: joef15

Result of data load using Merge with Accumulate within File optionjoef16

Using Replace with the Accumulate within File option, HFM acts similarly where the multiple lines of data for the same point of view would be totaled together and then loaded into the system.  The data load then behaves similarly as it would in the basic Replace mode where data with the same unique combination of the Scenario, Year, Period, Entity and Value dimensions would be cleared before loading the text file.

Result of data load using Replace option: joef17                      

Result of data load using Replace with Accumulate within File optionjoef18

Author: Joseph Francis, Performance Architects

 

Share
© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Leave a Reply

Your email address will not be published. Required fields are marked *