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

About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on August 19, 2012, in ETL, SSIS and tagged , . Bookmark the permalink. 2 Comments.

  1. Hi Karl!
    Interesting post. I’m currently implementing currency conversion like you described. Would be great, if you would find the time to write Part 2,3,…..

    Referring to your commnt on a different blog.

    “It is preferable to have the “one” currency in your base currency, then have a view on top of this table that triangulates it to provide a many-many when necessary without physically storing all the rates. ”
    => This is exactly how I trying to implement it currently. Would be great to see the walkthrough in a new post.

    “Then simply join to this view on your cube process. This approach allows you to store the minimum amount of data and remain flexible.
    ” => Would be great if you could provide some more detaisl how you have done it exactly.

    “One additional complication is a situation where you need all measures to be converted at different dates. ie BilledAmount to be converted either at BillDate, WorkDate or DeliveryDate.”
    => Yes, having the conversion on different dates would be a nice enhancement. Any hint are appreciated!
    Many thanks in advance!

    Liked by 1 person

    • Hi Thomas,

      Thanks for your comment. I had planned to write two posts, on both multidimensional and tabular.
      i didn’t write the multidimensional one as there are already two excellent posts on the subject. I would advise first reading

      Christian Wade’s Blog – Currency Conversion in Analysis Services
      http://consultingblogs.emc.com/christianwade/archive/2006/08/24/Currency-Conversion-in-Analysis-Services-2005.aspx

      and then the post you have already seen

      Boyan Penev’s – A Guide to Currency Conversions in SSAS
      http://www.bp-msbi.com/2010/10/a-guide-to-currency-conversions-in-ssas/

      The post on tabular will come in a few weeks when the dust settles on two current projects.

      To your first point:
      This is essentially how AdventureWorksDW2008R2 does it, looking at FactCurrencyRate which is a one-many where you’ll notice all rows with CurrencyKey = 100 (USD), AverageRate and EndOfDayRate both = 1.
      What I don’t like about this table is that it just has CurrencyKey which would be much more logical to be named DestinationCurrencyKey and another column named SourceCurrencyKey containing 100 (USD) for all rows.
      If you use the above SELECT statement as a view on top of FactCurrencyRate, (might need to change fcr.Date to fcr.DateKey) you will expose a fully triangulated FactCurrencyRate table.
      Happy to do a walkthrough but it really is as simple as a CREATE VIEW FactCurrencyRateManyToMany AS…select statement above

      Second point:
      What this is doing is pushing the currency conversion into the ETL or Cube process rather than the query execution. And does the following:
      ETL: Original Transaction Currency –> Single Base Currency (many-one conversion), using SSIS lookup of t-sql join onto FactCurrencyRateManyToMany view (triangulation of FactCurrencyRate described above).
      or
      Cube Process: in a view, t-sql join to convert to single base currency for cube processing.
      Cube Measure Expression: Convert to limited set of reporting currencies (one-few conversion)

      As most aggregate analysis will be done in a single (or limited set of) reporting currencies, it means the cube holds measures in that currency only and only needs to store rates for the set of reporting currencies (say GBP, EUR, USD, JPY) and calculation can be pushed into cube processing windows rather than query execution windows.

      It also means the fact table retains the original transaction currency for detail level reporting or cube drill-through.

      This assumes a fairly consistent spread of fact rows across original transaction currency and only a single or limited set of reporting currencies. Your use-case may differ.

      I will write a post on this with some diagrams and examples to make this more clear.

      Third point:
      This is fairly painful with the above approach to be honest. It involves one of the below options:
      -cube measure groups replicated for each date role
      -each measure replicated for each date role
      -a cartesian product in the cube process view to generate additional measure group rows and an appropriate dimension with the All member disabled and default member set (to avoid illogical aggregation)

      It depends on whether the number of dates is dynamic or not. If it is then the last option is your only real choice. It also depends on whether you have a native one-many, many-many or many-one requirement. The native many-many (ie many original transactions and many reporting/analysis currencies) with multiple dates will be a handful to both manage and understand for the analysts, not to mention the annoying way

      More than happy to set up a dialogue to talk these through, might be useful to get a more complete post together.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: