Adding Currency Conversion - Expert Cube Development with SSAS Multidimensional Models - page 177

Data collected in a single currency
If our source data is already stored in a single currency, we probably don't have very
much work to do. If the original transactions were made using different currencies,
probably the conversion to a single reference currency is done at the transaction
level, otherwise it happens somewhere during the ETL phase. If the original
transactions were made using a single currency, conversion is not necessary at all.

In both cases, the only reason we'll need to convert values from one currency to
another is for reporting purposes because we don't have information about the
original currency of the transaction any more (if indeed it was different from the one
used to store the transaction amount in our fact table). This kind of conversion can
happen at the highest level of aggregation using the same exchange rate regardless
of the date of the transactions. However, one common requirement is to perform the
conversion using the exchange rate that was correct on the date of each transaction.
We suggest double-checking this kind of requirement if you have it, as if all the
transactions have been stored using the same currency, it is probably the currency
that was used when the transaction was originally made.

From a business point of view, forcing the use of a different exchange rate for each
day does not respect reality because no currency conversion took place when the
transactions were loaded into the fact table. The story is different if the original
transactions were made using different currencies and they were converted to a
single currency before loading the fact table. In that case, we should also store the
original currency of the transaction, and also the original amount itself and/or the
exchange rate that was used.

Thus, in the situation where we have data collected in a single currency, there are
two possible subcases:

• Transactions were originally made in the same currency, and there are no

signs of currency conversion having taken place for any transactions, so:
° It should be possible to convert an aggregated amount by a fixed
exchange rate
° There should be no relationship between the fact table containing the
transaction data and a historical exchange rate table

• Transactions were made in different currencies, and currency conversion

took place during the transaction or in the ETL phase:
° Each transaction could have the exchange rate used recorded with
the transaction itself
° There could be a relationship between the fact table containing the
transaction data and a historical exchange rate table