Using Multiple Exchange Rates in SSAS

Recently I had a client ask me to support different exchange rates for different measure groups in their Sales cube.  They wanted to be able to specify a separate exchange rate for budget data, forecast data and sales history data.  With a few simple changes to the exchange rate table in the data warehouse and to the currency conversion MDX generated by the business intelligence wizard, I was able to use a different exchange rate for measures of my choosing.

Before making this enhancement, the currency rate fact table had one column for the exchange rate of the day.  I added two more columns and renamed the original rate column so that we could have a rate for sales history, a rate for budget and a rate for forecast, all for the same date.  I then updated the ETL to populate these rates from the source system.

Currency Rate fact query, old method:

Currency Rate fact query, new method:

I already had currency conversion set up in my cube, by leveraging the business intelligence wizard to generate my initial MDX for currency conversion, and then updating it for any additional measures that were added to the cube needing currency conversion.  In order to support a different rate for different measures, I simply added a new scope for each rate type, and specified the rate fact for measure group in the calculation, and then added the measures I wanted to use with that rate to the scope.

In both script blocks, the “Reporting Currency” dimension is my dimension of currencies available for conversion, and which have rates in my exchange rate fact table.  All my facts go into the cube in USD, hence the use of “Except” in the script, to avoid looking for an exchange rate to convert the fact when the user selects “USD” from the Reporting Currency dimension.

Business Intelligence Wizard Generated Script

Calculations table, script view, old method:

Modified Business Intelligence Wizard script that can accommodate any measure:

Calculations table, script view, new method:

By simply adding a couple additional scopes, changing the name of exchange rate fact used in the conversion in each scope, and placing the relevant measures into the appropriate scope, I was able to use a different rate for different measures.

When my customer first came to me with this request, I had no idea it would be end up being this easy.  The most work was to change the ETL to populate 3 columns in my exchange rate table instead of 1.  Performance was not affected and this logic is infinitely flexible.  Another implementation for this technique might be unit of measure conversion.

At the close of the project, the customer was very happy to be able to use different rates for different measures and it hardly took any effort at all! Thanks for reading.