Category Archives: ETL

Currency Conversion – Part 1 – Principles (ETL & DW)

Currency conversion seems to needlessly cause issues for accountants, developers, managers and analysts. So I thought I would put together a short series of posts to breakdown and simplify the concepts.

First up are some basic principles I have learnt to adhere to, so here goes:

1. Capture and use daily rates:

Period (weekly, monthly, quarterly, annual etc) closing rates or average rates will lead to incorrect results. Some more astute accountants will argue period closing rates for  Profit and Loss items and period average rates for Balance Sheet items are perfectly acceptable. I’m sorry but they aren’t! With major currencies fluctuating by as much as 14% during a month (Sep 2011 CHFUSD which caught out this guy to the tune of $2.3 billion) it isn’t going to do much for faith in the accuracy of your DW numbers if you build in this level of inaccuracy, particularly for P&L items with cost or revenue not evenly distributed by value throughout the period as is often the case for products like insurance premiums, power/gas/phone bills, salary payments, pension transactions, credit card payments… you get the idea, as this will magnify any fluctuations in the rate.

2. Always multiply:

Source currency –> Destination currency should be a multiplication operation. The reason for this one is quite simple (or maybe that’s because I’m quite simple!) -it’s easier to do multiplication in your head than division. So the rate for Source = ‘GBP’ to Destination = ‘USD’ should be apprx 1.6 (not 0.625).

3. Don’t use a money data type:

Yes it can save storage space, but the rounding errors resulting from implicit conversion will cause you headaches. Decimal is your friend

4. Store one-many rates in your relational fact table:

I’ve tested this on fairly large volumes and you’re usually better off to get many-many rates by doing the triangulation on the fly in a view (similar to that below), providing your currency fact table is indexed properly. Last check there were 182 current official currencies, so you could either store a rate for each currency into your base currency (say ‘USD’ or maybe ‘CNY’ in the years to come…) which would mean 66,430 rows per year in your currency fact table (182 currencies x 365 days). If you were to fully triangulate that you’d end up with 12,090,260 rows (182 currencies x 182 currencies x 365 days). Pretty obvious what will perform better. This of course assumes that you have a need for many-many currency conversion which isn’t often the case, most organisations may measure across many original source currencies and report in only three or four reporting currencies. If this is the case you can restrict your views to a limited set of “To currency” keys.

5. Use sufficient scale data type to retain accuracy:

Some currencies aren’t particularly strong, so if you’re base currency is relatively strong (USD, GBP, EUR, AUD, NZD, CAD, KWD etc) and you are adhering to rule 2 above, you must have sufficient scale in the decimal data type to store all the decimal places required as indicated in the table below.

Currency codes/names

US Dollar

Sao Tome/Principe Dobra

Somali Shilling

Turkmenistan Old Manat

Vietnamese Dong

USD

1

0.00005051

0.0006341

0.00007

0.00004798

STD

20000

1

12.6823

1.403508

0.9597

SOS

1667

0.08419

1

0.1169824

0.07999

TMM

14250

0.7195

9.035

1

0.684

VND

20850

1.053

13.2213

1.463158

1

 

In the next post I’ll cover principles within Analysis Services Multidimensional


SELECT fcr.CurrencyKey                       AS FromCurrencyKey
       ,fcr2.CurrencyKey                     AS ToCurrencyKey
       ,fcr.DateKey
       ,fcr.AverageRate / fcr2.AverageRate   AS AverageRate
       ,fcr.EndOfDayRate / fcr2.EndOfDayRate AS EndOfDayRate
       ,fcr.Date
FROM   factcurrencyrate fcr
       INNER JOIN dimcurrency dcFrom
               ON fcr.CurrencyKey = dcFrom.CurrencyKey
       INNER JOIN factcurrencyrate fcr2
               ON fcr.DateKey = fcr2.DateKey
       INNER JOIN dimcurrency dcTo
               ON fcr2.CurrencyKey = dcTo.CurrencyKey 

Advertisements
%d bloggers like this: