The Currency Conundrum – 2

markosun.wordpress.com

In BI context, currency is a critical component for any report or dashboard. Currency based reporting is simple and straightforward in majority of cases. Users typically look at reports in one or two currencies, which is easy to implement. A data warehouse that consolidates data from wide range of countries (i.e. OLTP systems) and has user base spanning across multiple regions must be capable of providing reports in any currency the user wishes to see the data.

An ideal solution should be “ANY to ANY” model wherein data could be stored in any currency and reporting solution is able to provide output in any currency on demand as requested by the user. Another key feature of an ideal solution is the provision of multiple exchange rate conversion options. In real world scenarios, different industry domains follow different exchange rate policies. In vast majority of cases, it is a monthly fixed value provided by Central Bank or Customs or Internal Finance teams. Using a daily exchange rate or weekly averaged rate or custom forecast rate is not uncommon practice.

Irrespective of the rate types are used for reporting, from an accounting perspective, only the rate types allowed by law and supported by GAAP are recognized.

Two approaches for addressing currency conversion are available and both require Currency Master Data to be maintained, which has to be time dependent data for accurate historical reporting.

First approach uses the ETL processing layer to perform the conversion and materialize the data. This approach is suitable for small requirements where we deal with smaller set of currencies and rate types. A measure for example Revenue will be available as Revenue-USD, Revenue-EUR, Revenue-INR and Revenue-Transaction in the data mart.

CURR_1

Second method performs currency conversion at the reporting layer. This is preferred approach over ETL due to its simpler solution architecture and option to scale up or down based on requirements.

Since exchange rates fluctuate over time, the conversion from transaction currency to the target reporting currency must happen for each fact record and aggregated calculations will giver erroneous results. Due to this property, currency conversion scenarios are always database delegated operations.

CURR_2

SAP BW provides flexible currency conversion methods as out of the box functionality. Any to Any scenario(s) are supported with options for converting during either ETL process or Report run-time. SAP HANA architecture also mimics SAP BW for currency translation. 

In both cases, the actual currency data is maintained in source ERP system and synchronized on daily bases.