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

Data collected in a multiple currencies
Our source data might have transactions stored in their original currency. When this
happens, the transaction amount cannot be aggregated across different currencies
because we cannot add Euros to Dollars without converting them to a reference
currency first. This normalization requires currency conversion using an exchange
rate that could be different for each transaction or, at the very least, for each day if
we can apply the same exchange rate to all the transactions that took place on the
same day.

We'll assume that, in this case, for each transaction, we have the currency used and
the amount expressed in that currency stored in the fact table, but that we don't have
a measure value in a single reference currency, otherwise we'd be in the situation
described in the previous section ( Data collected in a single currency ).

Again, we have two possible subcases:

• Data collected in multiple currencies has to be displayed in a reference

currency chosen by the end user at the query time:
° Conversion to the reference currency must take place at query time.
If we don't do this, we will have to convert the source data into all the
possible reference currencies in advance during ETL.

• Data collected in multiple currencies can only ever be displayed in a single

reference currency:
° In this case, we could perform the conversion at query time or during
the ETL phase.

Where to perform currency conversion
In the next section, we will see how to use the Add Business Intelligence wizard
in SQL Server Data Tools to implement currency conversion. This wizard can be
useful when we decide to implement currency conversion inside the Analysis
Services cube itself. However, this is not always the best choice, as we are going
to explain in this section.

Generally speaking, it's always better to perform any currency conversion during
the ETL phase. However, this is usually only feasible when data will only ever be
displayed in one reference currency—in other words, when the end user is not able
to modify the currency to be used to display aggregated data. This does not mean
we do not need to have a currency dimension in our cube because it could be useful
to know the original currency of the transactions. It only means that aggregated data
will always be displayed in the same currency, regardless of any selection made on
any dimension.