Multiple currency exchange rates in fact table

Hi,
Currently, we have multiple fact tables with measures stored in local currency (AUD, GBP... etc) and the business need the following requirements:
1. Report them in both local and USD currencies as at the transaction date.
2. Report them in both local and USD currencies as at todays exchange rate.

Please find below the options:


Option 1:
Create Fact_ExchangeRate and join with other Fact tables using Currency dimension (This would satisfy both requirements1 and 2)

Option 2:
Repeat 'ExchangeRate' and 'ExchangeRateDate' in all fact tables. (This would satisfy only req1 without joining ExchangeRate table)
Create Fact_ExchangeRate and join with other Fact tables using Currency dimension (This would satisfy req2)

Option 3:
Create DimExchangeRate and include ExchangeRateKey in all fact tables. (This would satisfy req1)
Join Fact table and DimExchangeRate using Currency dimension. (This would satisfy req2)

Option 4:
Create FactExchangeRate and include F_ExchangeRateKey in all fact tables.(This would satisfy req1)
join FactExchangeRate and other fact tables using Currency dimension (This would satisfy req2)

Kindly let me know which method is appropriate for the requirement.

Option 2 is the common way to do it, with some minor changes.

I don't understand why this example has 'exchageratedatekey' but others do not. Assuming they all have transaction date, the only scenarios I can think of: to adjust for time zone differences, allow for dates where a rate is not available (i.e. use a prior or future date), or if your company uses a nominal rate over some period of time; would require such a key on all facts.

Another common practice is to store the amount in the standard currency (i.e. $) rather than the exchange rate. There would be two versions of every value, one in local currency and the other in standard currency. It makes queries much simpler.

Another common practice is to store the exchange rate as mutually exclusive multiplier or divisor. This allows for more precise calculations when dealing with some of the more extreme exchange rates.

Thanks for your reply.

We do not receive exchange rate on some days (holidays?) but transaction may happen. So included 'ExchangeRateDateKey' which would be the previous day of 'TransactionDateKey' in such scenarios.

I am happy to implement Option2, However (just for my knowledge), Is there any limitations or design flaw in the following options?
1. option4 where ExchangeRateKey of FACT table is stored in all other fact tables instead of repeating exchange rate related attributes in all fact tables?
2. option3 where ExchangeRateKey of DIM table is stored in all other fact tables instead of repeating exchange rate related attributes in all fact tables?

Option 4 doesn't get you anything. Given the fact itself reflects both currencies at the time of the transaction, you don't need to use it. The other requirement, to reflect the standard currency at some arbitrary point in time, requires you to join on currency and the desired date, which option 4 does not help.

As far as the exchange rate itself goes, you need to store either the local currency and the exchange rate in the fact or the local currency and the equivalent standard currency (at the time of the transaction). So you are not saving anything by complicating the model to try to avoid it. Besides, the exchange rate for a currency at a point in time is technically a fact (business state), not a dimension.

As I mentioned before, you are better off storing all amounts in local and standard currencies rather than storing the rate in the facts. The rate itself is non-additive while the amounts are fully additive. You can always display the rate, if you need to, by deriving it from the amounts or joining to the rate table.

Hi If we are converting local currency (CAD) TO Standard (USD) during ETL process and maintaining both local and standard currencies in fact table. Is it necessary to create currency exchange dimension.

is the 'exchageratedatekey' is the surrogate key of fact_usdExchangeRate table or it is Transaction date of fact table?