Converting historical data for an Oracle EPM redesign

Hoa Pham explains the last step in an Oracle EPM Hyperion consolidation redesign: converting the historical data. Go through the process step by step and learn about loading and reloading, and setting data value.

This is the third part of a three part series. To start at the beginning, click here. To go to the second part of the series, click here.   

After completing the Hyperion Financial Management (HFM) metadata redesign and building it into your Oracle Enterprise Performance Management (EPM) environment, the next step is to convert historical data.  Data conversion is an iterative process  and, when you include the related step of data validation, it can become the longest process in a project cycle.  The best way to make the data conversion process as efficient as possible is to use Financial Data Quality Management (FDM).

Fig. 1: Loading and reloading

You don't need to build a new FDM application for the historical data conversion process.  If you need to move quickly,  you can create a new target adapter within an existing FDM application. You can then use the target adapter to connect to your redesigned HFM application. Then create one or more FDM data load locations using the new adapter.  You can use the Excel metadata dimension build spreadsheets as the source for the historical data mapping tables.  If you kept track of the old member names while redesigning, you can now copy and paste from the source spreadsheets to populate your FDM mapping tables.  From your existing HFM application, click Extract Data to create data files of base members.  In a short amount of time, you can start using FDM to convert and load historical data.  Add FDM Smart Merge functionality to the mix, or enable FDM data protection, to make the data conversion process even smoother. FDM Smart Merge ensures you do not inadvertently overwrite data from other load sources.

In the event that you need to convert HFM historical journal data and keep it separate in the <Entity Curr Adjs> value member, or any of the other HFM adjustment values, you can still use FDM, since it allows you to load to the HFM journal value members.  You can control this from the location settings for the Data Value field.  Normally  Data Value defaults to <Entity Currency>, but you can change it by clicking on the Browse button (Figure 2) and then selecting a different HFM journal value member.  Keep in mind that a single FDM data load location can only have one unique Data Value field setting. 

Fig. 2: Set the Data Value

Go to Extract Data in your existing HFM application, and beginning with v11.1.2.2, create data files from any of the HFM value members.  For older versions of HFM, use Extended Analytics.  The trouble with loading HFM journals data using a FDM data load location is that this causes FDM to automatically generate journal IDs and descriptions when it creates and posts the journals data into your HFM application.  The jounals data is grouped by entity and you cannot control if the posted journals are in balance.  By loading HFM journal data using FDM data load location, you lose the journal metadata. However, FDM will automatically convert extrememly quickly and create and post journals data as you load each period.

In some scenarios, you need keep your journal metadata. Essentially, in these cases, you want to migrate and convert all existing HFM journals from the old HFM application into the redesigned HFM application.  I've had a number of clients who have made this a business requirement in the redesign process.  There is no pre-built tool in the Oracle EPM 11.1.2.x world to technically handle this requirement.  Instead, I have built a repeatable process using an Excel workbook that the client's financial personnel will be capable of maintaining. The workbook is only used during the data conversion process and then it is no longer maintained. 

Export the the mappings from old to new members into a spreadsheet.  Then extract journals files from the existing HFM application using the Extract Journals functionality. In Figure 2, this is represented by the orange section columns -- A thru K.  In the Excel workbook, use the mappings you created earlier to build look-up formulas to convert old members to new members. In Figure 2, this is the gray section of columns L thru Q.  Use Excel logic and concatenation formulas to aggregate post-converted journals data In Figure 2, represented by the green column R.  In the example workbook in Figure 2, the user only needs to update the source journals data, the orange section, and everything else is automatically calculated to create the converted journals data.  Last, put the converted journals data into a text file and load it into the HFM application using Load Journals.

Fig. 3: Workbook showing extracted journals, conversions from old to new members and the post-conversion journal data.

Next Steps

Check out the buyer's guide to enterprise performance management software

Learn about Hyperion EPM implementations from the experts

EPM is growing with technology based on Oracle Hyperion

Dig Deeper on Oracle Hyperion