Integrating HFM and Planning Multi-Currency Applications

I am often asked by clients and colleagues why Hyperion Financial Management and Hyperion Planning are so different.  It’s typically when they are struggling to build an interface between the two, and they can’t reconcile converted currencies.  So today I want to share some of the industry’s best practices that should be taken into consideration when designing an integration between HFM and Hyperion Planning multi-currency applications.

It is important to understand how currency translation works in these two products before designing your integration solution. The following topics will be discussed:

• Key differences between HFM and Planning dimensions
• Currency translation in HFM
• Currency translation in Hyperion Planning
• A simple integration strategy using native Hyperion tool

Sprint	Days	Story Points	Takt Time
Sprint 1	10	6	1.67
Sprint 2	10	6	1.67
Sprint 3	10	5	2.00
Sprint 4	8	8	1.00
Sprint 5	10	8	1.25
Sprint 6	10	7	1.43
Sprint 7	8	9	0.89
Sprint 8	10	9	1.11
Sprint 9	10	8	1.25
Sprint 10	9	9	1.00

Currency Translation in HFM

---

Before the deployment of an HFM application for the first time, some application and dimensional configurations are performed. It is helpful to know some of these application properties that are set at the time of the application creation.

FROM and TO Dimension Settings:

The FROM and TO dimensions define where to store the currency exchange rates. Any custom dimensions can selected but they cannot be changed after the creation of the application.

To identify this property in EPMA, select a custom dimension and check the property “Use for Currency” in the property grid to determine this setting.  In this case, we will use Custom1 and Custom2.

Other Application and Dimension properties    

• Default Currency: Generally set to USD
• Default rate for Flow Accounts: Generally set to AverageRate
• Default rate for Balance Accounts: Generally set to ClosingRate
• Present Value Add (PVA Translation Method): Accumulates the physical value of an account. Usually enabled for Flow Accounts
• Accounts assigned to translate Balance or Flow in Application Settings
• Define the local currency that is associated with each entity in the metadata

Once these properties are set, HFM has a delivered approach to currency translations and consolidations.  The typical process in HFM has the following steps:

1. Open future period
2. Load data
3. Run consolidations on an Entity
4. The system runs calculation rules for all descendants of the Entity.
5. If the data for the child Entity and the data for the parent entity are in different currencies, the system translates data based on the local or global exchange rate. For the child entity, the translated value is stored in the Parent Currency member of the Value dimension. The translated value in Parent Currency is rolled up to the parent.
6. Optionally, you can add adjustments to base data through journals. These are stored in <Entity Currency Adjs> or <Parent Currency Adjs> or <Parent Adjs>.
7. The consolidation process continues. If the parent’s ownership of the child is less than 100%, the ownership percentage is applied. The system generates proportion and elimination detail, and creates contribution data.
8. You can make further adjustments to contribution data through journals.
9. And reapprove
10. Lock data (Lock/close the month)
11. Publish reports

Currency translation can be performed using the delivered approach or can be further customized using translation rules to suit your accounting and reporting needs. For the delivered approach, the currencies and rates are defined as part of the metadata definition. The exchange rates are entered in the memo accounts above (AverageRate, ClosingRate) and can be loaded to a global entity or a specific entity.

Value dimension

The value dimension is one of the eight standard dimensions in HFM. It is a system defined dimension that stores the different types of values in the HFM application for a given entity, including currencies.

The diagram below shows how the data flows through the system as part of the consolidation process. This is where the actual translations are stored. The value dimension is important when viewing and extracting the right intersection to export translated and untranslated data.

Currency Translation in Planning

---

Delivered Approach

A multi-currency setting is enabled at the time of the creation of a Planning application. The default application and reporting currency is generally set to USD.  Additional reporting currencies can be enabled as needed.  A multi-currency application has two additional dimensions.  Both of these dimensions are, by default, sparse in nature.

• Currency: defines local and reporting currencies.
  • Local Currency
  • USD
  • EUR
  • GBP

• HSP_Rates: stores data values and exchange rate information.
  • HSP_InputValue: stores data values
  • HSP_InputCurrency: stores the currency type for HSP_InputValue above
  • HSP_Rate_USD
  • HSP_Rate_EUR
  • HSP_Rate_GBP etc

Example, the HSP_InputValue might be 500 and the HSP_InputCurrency might be GBP.

Note: The exchange rates are initially stored in the relational planning schema and pushed to Essbase.

Configuring Exchange Rate Tables

Before defining the Exchange Rate table for the first time, the following pre-requisites should be met:

• Define the currency members the Currency dimension
• Associate the base currency with the members in the Entity dimension
• Navigate to Administration > Manage > Exchange Rates
• Define an Exchange Rate table and enter the rate information into the table

There are three exchange rate types associated with a currency:

• Average
• Ending
• Historical

The exchange rate type is associated with the account members in their properties. You also must associate the exchange rate to a scenario for which you want to perform the currency conversion.

Next, create the Currency Conversion Script:

• Navigate to Administration > Manage > Currency Conversion
• Provide the currency conversion script name – e.g. Budget
• Choose the reporting currency – USD
• Choose the scenario that you have the Exchange Rate table attached
• Choose the version associated with the scenario
• Select the version type : Bottom-up or Target
• On Save, two calculation scripts are generated: HspCRtB.csc , Budget.csc

The exchange rate information is initially stored in planning application’s relational database. Upon deployment of the application, the rates are stored in Essbase.

These are planning relational tables where the exchange rate information is stored:

• HSP_FX_RATE_VALUES
• HSP_FX_RATES
• HSP_FX_TABLE
• HSP_FX_VALUES

Execute the conversion script HspCRtB.csc:

• This script copies the appropriate rates to each account based on the account’s properties.
• This script must be executed any time the exchange rates have been updated.

NOTE:  The script is available in all three plan types and therefore must be executed for each one individually.

Integration between HFM and Planning

---

The keys to building a proper interface for currency integrations are:

• Understanding the integration points (for the Master data and Data), especially how and where the data that needs to be interfaced are stored in both the source and target destinations
• Establishing naming standards as part of the design definitions
• Selecting the appropriate tools for integration solutions

The diagram below depicts a sample interface from HFM to Planning.  This example relies on Extended Analytics and EPMA Batch Client to move data between the applications:

 

 

 

 

 

 

 

 

 

 

 

 

These are the steps in the data flow:

Expenditure Type	Unit Name	Unit Description	(a) Expenditure Category	(b) Revenue Category	(c) Expenditure Type Class
Administrative	Hours	Administrative	labor	Non-Bill labor	Straight Time
Clerical	Hours	Clerical	labor	Non-Bill labor	Straight Time
Customer Labor	Hours	Billable Labor Hours	labor	Billable labor	Straight Time
Customer Hotel	Dollars	Billable Hotel	Travel	Billable Travel	Expense Reports
Customer Supplies	Dollars	Billable Supplies	Supplies	Billable Supplies	Supplier Invoice

 

 

 

 

 

 

Using extended analytics, the data is extracted out of HFM to a dimensional data model where the data layout is in star schema format. There are various extract formats such as Standard, Essbase, Data Warehouse, etc. The best practice is to use the Data Warehouse format. The database topology is like a star and consists of two sets of tables: Fact tables and Dimension tables.

The two levels of data designed to be exported out of HFM are:

1. Base data – This level contains the data from the base entities.

 

 

 

 

 

 

 

 

 

 

 

—————————————————————————————————————————-

2. Adjustments data – This level contains all adjustments data. The different adjustment types exported are Eliminations, Parent and Contribution Adjustments.

Planning only stores data at the base level for a bottom up version. Hence base members were created in Planning with a suffix ‘_Elim’ in the Entity dimension that will store the adjustment data, and the currency at this level is identified by the default currency of the parent.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Once the data is in the staging warehouse, there are a variety of tools and techniques to load the data to Planning.  In our simple example, we used EPMA Data Synchronization.  Some of the other techniques which are commonly used are:

1. Data Load using Essbase load rule
2. Oracle Data Integrator (ODI)
3. Financial Data Quality Management (FDM)

In my next blog, I will discuss alternatives to using the default currency dimensions in Planning.  This often makes interfaces between HFM and Planning even more straightforward.  Thanks!