It has become a common requirement in multi-national organizations to collect budgets and forecasts in local currency and expect automated currency translation as part of their Hyperion Planning application. This is for good reason: the operating units don’t want the hassle of translating to reporting currencies, corporate FP&A needs to report group and consolidated forecasts in one or more reporting currencies, and management needs the ability to easily perform constant rate and flex analysis based upon exchange rate assumptions. Configured properly, Hyperion Planning can eliminate all of the routine currency calculations that are buried in disconnected Excel-based forecasts today.
Hyperion Planning comes delivered with an “in-the-box” currency translation approach, and this may be preferred when integration with Hyperion Financial Management or other Hyperion Planning modules is required. Often the delivered approach is passed over in favor of a custom solution. Some of the key differences between the delivered and custom translation approaches are:
• The delivered approach requires two dimensions: Currency and HSP_Rates, whereas the custom solution only requires a Currency dimension and a handful of exchange rate accounts. This eliminates an unnecessary dimension and can improve aggregation performance dramatically.
• Under the delivered approach, Planning administrators must redeploy the Planning application for any FX rate changes to take effect. Also, currency conversion scripts must be recreated and redeployed after any changes to the Planning outline. A custom solution does not requires any redeploys.
• Redeployment under the delivered approach requires administrator privileges, which can sometimes delay the velocity of updates and reforecasts.
A custom currency translation solution starts with creating a user defined Currency dimension at the time the application is created. The Currency dimension stores all the various local and reporting currencies along with an additional member named “Local Currency”. Planning data is stored for each entity at their base level in Local Currency. The FX rates are stored directly in the Essbase Account dimension, therefore redeployment of the application is not needed for FX rate changes to take effect.
The local currency of each entity is determined by a set of User Defined Attributes (UDA) (such as AED, ARS, AUD, etc.). These are applied on the base members of the Entity dimension. Another set of UDA are associated with the base members of the Account dimension to identify the currency translation method (namely AVG, EOM, etc.). Finally, a custom business rule performs currency conversion using the FX Rates from local currency to USD. A condensed version of the business rule is provided below.
The decision to go with delivered or custom currency translation is a key design decision and many factors must be considered: number and type of Planning modules (Core, Capex, Workforce, PFP), number of dimensions, administrative personnel and workflow, inbound/outbound interfaces, the list goes on.
At the very least, I hope this helps explain an alternative to the delivered currency approach. If you have any questions about currency translation in your Hyperion Planning environment, feel free to drop me a line at manooj.thomas@perficient.com.
