By Hoa Pham on March 8th, 2017
Oracle Hyperion Financial Management (HFM) is purpose-built to perform consolidations and reporting. HFM has a great built-in functionality to translate data from any currency into the reporting currency. In this blog post, we’ll discuss extending the use of the built-in currency translation capability.
First, let’s review the fundamental HFM currency translation setup. Typically, this means translating foreign currency data into US Dollar following the GAAP methodology. The setup for activating the built-in currency translation functionality is fairly straightforward:
- In the Currency dimension, define all the currencies that you will load data for.
- In the Account dimension, create the currency exchange rate accounts.
- In Application Settings, define the default reporting currency, assign the exchange rate accounts’ names, and define the translation methodology to apply for Balance accounts (typically this means the balance sheet) and for Flow accounts (typically this means the Income Statement/P&L).
- In the Entity dimension, create each entity member with its appropriate functional currency.
There is another more complicated step to create the USD Overrides process for accounts that need to translate on a historical basis, typically this applies to the Equity accounts on the balance sheet. We won’t go into the details of it in this blog. After all the above-mentioned setup steps are completed, we can load foreign currency data for a period and then also load the currency exchange rates for the period and the HFM application will perform its magic and translate foreign currency data during the consolidation process.
Next, let’s imagine how we could use the fundamental HFM currency translation functionality to satisfy a special reporting requirement. Recently, I helped an existing client make an enhancement to their HFM application. This client has a special reporting requirement to take data from the HFM dimension based upon a different default currency than the HFM entity dimension setup. The data is required for the downstream Hyperion Planning application. Here’s an illustration of the data requirement:
(A) In the HFM application, the data balances are stored like these example records shown below as they come in from the ERP system:
(B) In the Planning application, the setup for the inbound data requires that it gets records as shown below:
|Planning Entity Unit||Default currency||Account||Amount|
So, the client has a difference in the what represents the “entity” member between the HFM and Planning applications and related to that is a difference in the currency of the data. This means we cannot simply take data “as-is” from the HFM application and push it downstream to the Planning application.
The client discussed internally with their ERP team about the possibility of creating a different slice of data from their ERP system to be based upon the Planning Entity Unit. It was very likely that it would be a significant effort to do this from the ERP side. So, then we looked at it from within the HFM application.
We could have a viable solution by making an enhancement within the HFM application and relying on the built-in currency translation functionality. HFM can triangulate currency translation. So, if I have exchange rates to convert for EUR-to-USD and GBP-to-USD, then it means HFM can also translate from EUR-to-GBP and from GBP-to-EUR. Then the next step in our thinking is to have HFM re-compute the data by using the Geography dimension as the driver. We came up with a solution to build an alternate Entity dimension hierarchy that is dynamic based upon the combination of the Geography and Entity member names.
(A) Below is an example of the alternate Entity dimension hierarchy shown below:
Then we create a custom dynamic HFM business rule to copy data into the alternate Entity hierarchy. This would be a straightforward rule to copy data using the Entity and Geography dimension names and the important thing is that it is dynamic so the rule should not need any regular maintenance to it since it is driven by the Entity and Geography dimension. This rule populates data into the new children members and then we let HFM’s default currency translation via triangulation do its magic. This would give us the data that we need for total G105 that could then be sent downstream to the Hyperion Planning application. In the end, we put HFM’s purpose-built capability to further great use and addressed a business requirement need with the tool that we had.