Fact Table having local currency amount columns vs. calculated columns using exchange rate

    Allgemeine Diskussion

  • I am in the design phase for a fact table in a data warehouse. I have 9 amount columns (ie. ExtendedProductAmount, TaxAmount, DeliveryAmount) and the values in these columns will all be in USD (United States Dollars). Most of the time, reporting within the company will be using these values so that they can see everything in one common denomination (USD). However, sometimes my users will want to see these amounts in the local currency based on where the order was placed. So, if the order was placed in London, we want to see that amount in GBP (British Pound Sterling).

    Now, here's my question for a discussion starter. I could accomplish the "local" amount columns in one of 3 ways. Let's just talk about the data warehouse for now, not the cube. I would love to hear opinions for others on their pros and cons of these options or other options that I haven't thought of yet.

      • Have 9 additional corresponding columns (ie. LocalExtendedProductAmount, LocalTaxAmount, LocalDeliveryAmount) and store the local currency amounts. Handle this as part of the ETL process.
      • Add an ExchangeRate column and then 9 calculated columns. For example, LocalTaxAmount = ([TaxAmount]*[ExchangeRate])
      • Add an ExchangeRate column and view the 9 "local" columns in a view.

    For options 2 and 3, if the transaction originally happened in USD, then 1.0 would be stored in the ExchangeRate column.

    I realize that the ExchangeRate column could instead be a key to an ExchangeRate dimension table. Probably don't need discuss that here. I've already decided to store it in the fact table if I decide to go with options 2 or 3.

    Thanks in advance!


    Freitag, 5. Oktober 2012 02:33

Alle Antworten

  • I can descrive how we store our multi-currency calculations. The database structure is the following:

    The presented structure has the following advantages:

    1. It's flexible. In case when you've got new currence, you do not need to change database structure.

    2. You do not have NULL columns for positions where not all currency calculations are needed.

    You also can move all price fields from main table to OrderCurrency table - but in our case we store main document currency in the document itself in order to speed reports' processing.

    Hope this helps.

    Freitag, 5. Oktober 2012 08:47
  • I would go with option 2 mainly becuase you also mentioned  a cube.  The first one isnt an ideal situation becuase you already have a way to derice the values in local currency anyway. persisting the information simply wastes space. Especially since most of your data is in USD anyways.

    Creating a claculated columns with a exchange rate option allows you to dynamically account for incorrect exchange rates and also becomes a good based table for the cube as well.

    The view is similar to the computed column except I find it easier to work with base tables instead of view ( esp when it comes to cubes and indexing ).

    Dienstag, 9. Oktober 2012 13:27
  • Thanks Jayanth. I was leaning in the direction of option 2. Good to get a confirmation from someone else though. Appreciate your insightful comments.


    Dienstag, 9. Oktober 2012 15:27
  • Thanks Boris. I'm not sure this would be the best solution in my situation, but I do like the approach for other similar needs. Thanks for replying.


    Dienstag, 9. Oktober 2012 15:28
  • Hi Calvin,

    hope you might have implemented this scenario. if you have time could you please share what approach have you taken here?

    Regards Amit

    Samstag, 23. Februar 2013 12:48
  • Amit,

    I went with option 2, added a column to the fact table that has the exchange rate and then added calculated columns. It's working well.


    Montag, 25. Februar 2013 16:42
  • Hello,

    I am aware that this post is over 5 years old, but I stumbled over it when I was searching for something related with this topic. Maybe this is helpful for someone:

    According to Kimball, it is best practise to use a pair of columns when dealing with currencies: One column for the original transactionamount and one column for the transactionamount in the currency your company is usually working with.

    I am using the following approach:

    I use one column for the amount in EUR, one column for the Amount in USD and one Column for the original amount in the currency of the transaction. The Last column also needs an additional column with the foreign key to the currency dimension, which holds the information about the currency of the transaction.

    Montag, 16. April 2018 13:22