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

The resulting cube will have two currency dimensions: the original dimension is
used for the Local Currency and the dimension added by the wizard is used for the
Reporting Currency. When nothing is selected on these dimensions the end user will
see USD used as the Reporting Currency; the following screenshot shows both the
original values of the Local Currencies and the converted USD values:

One important thing to point out is that to get the results we just showed, we need
to define an exchange rate for converting USD values to USD. This is a requirement
of the MDX code generated by the wizard; if we don't do this, we'll see an empty cell
for the USD/USD cell in the previous report and this will affect the grand total too.
This is not smart behavior on the part of the wizard-generated code because after
all there should be no need to convert a currency into itself. As a result, we need an
exchange rate table like this:

Date Currency Rate

2009-02-06 USD 1.0000

2009-02-05 USD 1.0000

2009-02-06 EUR 1.2871

2009-02-05 EUR 1.2835

2009-02-06 CHF 0.8550

2009-02-05 CHF 0.8578

Data stored in multiple currencies with reporting in
multiple currencies
The third and last scenario is the most complex one. Here, we have transactions
stored in different currencies, but this time we want to report in any currency
chosen by the end user. Even in this case, some currency conversion could take
place during the ETL phase to normalize data to the Pivot Currency, but we might
need to do the conversion in Analysis Services for the same reasons we discussed in
the previous scenario.