Friday, October 05, 2012 2:33 AM
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!
Friday, October 05, 2012 8:47 AM
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.
Tuesday, October 09, 2012 1:27 PM
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 ).
Tuesday, October 09, 2012 3:27 PM
Thanks Jayanth. I was leaning in the direction of option 2. Good to get a confirmation from someone else though. Appreciate your insightful comments.
Tuesday, October 09, 2012 3:28 PM
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.
Saturday, February 23, 2013 12:48 PM
hope you might have implemented this scenario. if you have time could you please share what approach have you taken here?
- Edited by Amit KM Saturday, February 23, 2013 12:49 PM
Monday, February 25, 2013 4:42 PM
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.