Moving Data Between EPM Applications

So you’ve purchased Oracle Hyperion Planning, Hyperion Financial Management (HFM) & Financial Data Quality Management (FDM).  You determine that you need actual data from HFM in Planning to support your budgeting process.  Additionally, you want to load budget data back to HFM to provide cost center managers month end actual to budget variance reporting throughout the close.   You think to yourself; no problem, these are all Oracle products so they should exchange data seamlessly.

I hate to be the bearer of bad news but data movement between the EPM apps isn’t always as seamless as most think it should be.  Without delving too deep into the technical reasons behind this, take a moment to consider the underlying technologies of Planning & HFM.  Hyperion Planning stores the majority of its data in Essbase.  Essbase is an OLAP engine.  HFM on the other hand stores its data in a relational repository.  Those are 2 fundamentally different technologies that speak very different languages.  Sounds like someone needs an interpreter.

Within the EPM suite, there are a number of options available to facilitate the movement of data between applications.  Below is a list (albeit not exhaustive) of some of the common techniques used:

  • Extended Analytics (EA) is a module of HFM. EA allows a user to extract from any level of the Value dimension as well as parent level values of every dimension. There is no additional licensing cost to use this functionality.
  • The Data Synchronization Module of EPMA can only be used when both applications are EPMA enabled. This will not function with Classic applications. There is no additional licensing cost to use this functionality.
  • I won’t often flat-out say that something should not be used but in all reality, SmartView is not a production level solution for moving data between applications. Yes it can work bit is mired with risk. I strongly recommend against this approach; however, there is no additional licensing cost to use SmartView.
  • EAL is the product formerly known as HyperRoll. EAL will only extract data from HFM. There is additional licensing cost to use this functionality.
  • Oracle Data Integrator (ODI)
  • FDM

Being that this is an FDM blog, I’ll spend time exploring the latter option but this should not preclude you from investigating each of the available options and determining which is right for your implementation.  There is no magic bullet to address this common project need.  The right solution will depend on a number of variables including:

  • Direction of data movement
  • Frequency of data movement
  • Data volumes & Performance requirements
  • Mapping requirements
  • Automation requirements
  • Organization responsible for the data movement (and quality)
  • Software owned within your organization

Let’s take a look at a couple of common scenarios where data needs to move between the EPM applications.

Scenario 1:

Hyperion Planning & HFM co-exist in your organization.  HFM is the system of record for actual statutory as well as management reporting.  As with most any Budget & Forecast process, actual data is needed to support the budget & forecast process.  In this scenario, the data flow would be bi-directional; actual data would flow from HFM to Hyperion Planning while Budget & Forecast data would be fed to HFM to support month, quarter & year end budget to actual variance analysis.

Scenario 2:

Again, Hyperion Planning & HFM have both been implemented in your organization.  HFM is the system of record for statutory reporting but Planning is the system of record for management reporting.  Each month, actual financial results are fed to HFM & Planning through an FDM process.  This ensures that actual data are in alignment (at a base level) between the 2 systems.  However, each month, a number of top side/corporate adjustments are booked in HFM due to timing, statutory restrictions or the need to keep the data (and its related reasons – think acquisition/reorganization) from being viewed by the local controllers.  These adjustments made in HFM must be reflected in Hyperion Planning to ensure that the total organizational view reconciles between the 2 systems.

Each quarter, a rolling 12 month forecast is completed in Hyperion Planning in local currency.  This process includes intercompany revenue and cost of sales.  While you can replicate the intercompany elimination process in Hyperion Planning; it is often be far more efficient to simply move the data into HFM and use the consolidation engine of HFM to perform the intercompany eliminations.  Once the eliminations are processed by HFM, the resulting data can be transferred back to Hyperion Planning.

Leveraging FDM

As I noted above, there are a number of factors that determine the solution that will address these needs.  Let’s assume for this posting that your (and your consulting partner) analysis determines that FDM is a good fit to address each of these scenarios.

In scenario 1, At a 50k foot level, actual data needs to flow from HFM to Planning (Essbase).  Once you start to explore the requirements a bit further, you realize that journal adjustments are occurring monthly in HFM as are intercompany eliminations.  Let’s take it one step further and introduce a series of entities for which your company only has partial control/ownership – i.e., minority interest.  Each of these data points is stored at unique members of the value dimension – , [Elimination], [Proportion] – but HFM’s data export functionality will only provide data for .  Not to worry, various other approaches can be used to extract the data from HFM at these required intersections and the great news is, FDM can be leveraged to initialize each of them.

Now we have data extracted from HFM.  At this point, native FDM can be used to process the data and load to Planning utilizing all of the out of the box functionality that comes with the Essbase adaptor.  For a brief highlight on the native functionality of the Essbase adaptor, take a quick look at my Top 10 Out of the Box Features List.

We’ve addressed half of the requirements in scenario 1; the remaining requirement is to load data from Planning back to HFM.  As with HFM, there are a number of ways to extract data from Essbase.  With a touch of creativity, you can leverage FDM to initialize any of these various methods thereby producing a data extract file.

Now we have data from Planning – and more specifically, multiple periods of data from Planning.  With a quick custom script in FDM, a multiload file is can be created and the out of the box MultiLoad functionality can be used to load data to HFM.

What I have not explicitly called out is that each directional data flow can be fully automated to be a 1 click end-user initiated or a completely lights out scheduled process.  Much of this automation is driven by the batch loader of FDM and a collection of custom scripting to initialize and process the data extracts from each system.

Now that we’ve addressed Scenario 1, let’s take a quick look at Scenario 2.  In scenario 2, we need to take specific data intersections driven largely by the value dimension out of HFM.  Check!  This is not any different from Scenario 1.  We can leverage the same approach.

We also need to extract data from Planning, feed it to HFM, run an HFM calculation (likely a consolidation), extract the elimination data and reload to Planning.  Again, same basic concept as Scenario 1 except this is a full data loop.

I hope you can see that using FDM to move data between the EPM applications is very achievable and something that you will explore in your organization.  Leveraging FDM for additional creative solutions will only further enhance your return on investment.

This entry was posted in Extending FDM, Out of the Box Functionality and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply