How to create a highly performant multi-currency solution in SSAS

Anyone working on a reporting solution in SSAS for an international company will eventually run into the problem of working with multiple currencies, and how to implement this.

There are multiple ways to solve this, depending on what the business needs, and the size of your data. In most cases, the business needs a way to convert from multiple currencies to multiple currencies. (many-to-many) In our case, it also has be very fast, because we have tens of millions of rows which may be aggregated over dozens of dimensions.

In this post, I will go over a couple possible solutions to solve this many-to-many conversion, the one we’ve decided to go with for our data warehouse, and why. I will also explain how to implement this.

Some possible solutions:

1. Create multiple currency columns in the fact table and convert source data to these currencies. The end-user can then select the column which they want to see. The main downside to this method is that there are a lot of columns the end-user can select, causing a lot of bloat. (This can be solved by hiding certain currencies for certain roles, but this removes freedom for the end-user)
2. Import the source currency and the exchange rates into the cube, and depending on what reporting currency the end-user has selected, dynamically convert to this currency at query time. This is a very neat solution, which doesn’t clutter the columns and allows flexibility for the end-user. The downside is the “convert to this currency at query time” part. (It’s slow!)
3. The same as step 1, but hide all these columns. Then depending on the selected reporting currency, dynamically select the correct column for the end-user. This has all the advantages from option 2, but the performance of option 1.

Initially, we went for option 2. This solution worked great for us for a while, until some of our business analysts started coming to us with certain spreadsheets taking extremely long to load, and sometimes even crashing excel.

At first, we thought this had to do with the fact that those spreadsheets had a lot of dimensions selected. An example: Show the revenue, aggregated down to company -> customer -> department -> project -> week. However, if instead of looking at revenue, they would look at amount of hours spent, for the exact same dimensions, the spreadsheet would load practically instantly.

Turns out, dynamically calculating millions of rows at query time from one currency to another currency, eventually starts becoming slow. (Shocking, I know!)

There are some possible optimizations that can be done however. I tried some of those. The biggest one being: don’t convert the currency per row, but instead sum the metrics per day, per currency (and group by all other dimensions the end-user selected) and then convert the currency. This however is quite complicated to do dynamically at run-time, and it ultimately didn’t even improve performance that much. It mainly made already workable spreadsheets slightly faster, but the ones taking 30 seconds to load or crash, didn’t become notably faster.

Eventually we came up with solution number 3. Nothing would change for the end-user (besides being way faster to use), it would just be how we implemented it.

How to implement a performant multi-currency solution in SSAS (option 3):

A very broad overview of what we will be doing:

1. Create columns in fact tables for every currency we want to report
2. Fill these columns during the ETL process by using exchange rates
3. Create mdx scripts to dynamically select the correct column depending on what reporting currency the user has selected.

So let’s begin.

1. Create columns in fact tables for every currency we want to report

Relevant tables for tutorial

The main table we will be talking about is FactEmployeeHours. This is a relatively simple table, which has two metrics, and a bunch of foreign keys to different dimensions. The two metrics are Hours and Amount. As you can see, we split up the Amount metric into four different columns: AmountEUR, AmountUSD, AmountGBP, AmountLocal. (local = the original currency)

The other table we need is the FactCurrencyRates table. This is a table containing the exchange rates per day, per currency. We use this table to convert from a source currency to the four different Amount columns, during the ETL process.

The last table we will need is a very simple currency table. We will use this table to create a dimension in the cube, so users can select the reporting currency they prefer.

Currency table to be used to create currency dimension

2. Fill these columns during the ETL process by using exchange rates

Now that we have the database we need, we need to change our ETL process to fill the different currency columns. This is a simple join and calculation during the already existing import (only relevant parts included):

If you noticed us using the ‘MonthlyAverage’ column from CurrencyRates, this is because the business users specifically asked us to use the monthly average, instead of the daily open or closing rate. You need to judge for yourself (in discussion with end-users) whether you want to use a daily/monthly opening/closing/average rate.

3. Update the Cube for the new columns.

Now we just need to work on the cube. The first thing we need to do is update our DSV so the new columns are added. You do this by right-clicking anywhere in the DSV and clicking ‘refresh’. We also need to add the ‘DimCurrency’ table from before. Simply right-click anywhere in the DSV and click “Add/Remove tables” and select the DimCurrency table.

Now create a Currency Dimension, by right-clicking ‘Dimensions’ and following the wizard.

As you can see from the screenshot, I have renamed ISO_CODE to “Reporting Currency”. Let’s also call the dimension “Reporting Currency”. This dimension will be how the users select what currency they want displayed.

Now open this dimension, and on the bottom right there will be properties. Change the Type to Currency.

Now go click on the column “Reporting currency” and there will be properties in the bottom right as well. Change two things here:

Select a DefaultMember. This makes it so the user doesn’t have to select a reporting currency for the cube to work, because it will use the default member. Most of our end-users of the Data warehouse are european, so I set the default member to EUR.

Set IsAggregatable to False. This makes it so if a user select multiple reporting currencies, it won’t try to add them together or take the average or something, it will simply use the first selected currency.

Now go to the actual cube, and on the first tab, add the Reporting Currency dimension (so people can select the reporting currency they prefer) and add the Amount metrics

After adding the Amount metrics, select them, and again in the bottom right there will be a property we need to edit.

Change Visible to False. We don’t want the end-users to see all the different currency columns, we just want them to see one, which will update dynamically depending on the selected reporting currency.

4. Create mdx scripts to dynamically select the correct column depending on what reporting currency the user has selected.

Now we have everything setup except the custom MDX scripts.

Go to the calculations tab, and swap to script view.

In here, we want to do two things:

1. Create a “dummy” metric that end users can select (Simply “Amount”, instead of AmountEUR, or AmountUSD)
2. Set the value of this Amount metric to the value of the correct currency column, depending on the selected reporting currency.

First, the dummy metric end-users can select:

CREATE MEMBER CURRENTCUBE.[Measures].[Amount]
AS null,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Utilization';

This creates a metric “Amount”, with the value null. So if we don’t do anything else, there will be a metric users can select, but the value will always be empty.

Now, depending on what reporting currency is selected, we select the correct amount metric:

//Only do the below logic when the Amount metric is selected
Scope ([Measures].[Amount]);
  //When EUR is selected, select the Amount EUR column.
  Scope ( [Reporting Currency].[Reporting Currency].[EUR] );
    THIS = [Measures].[Amount EUR];
  End Scope;
  Scope ( [Reporting Currency].[Reporting Currency].[USD] );
    THIS = [Measures].[Amount USD];
  End Scope;
  Scope ( [Reporting Currency].[Reporting Currency].[GBP] );
    THIS = [Measures].[Amount GBP];
  End Scope;
  Scope ( [Reporting Currency].[Reporting Currency].[Local] );
    THIS = [Measures].[Amount Local];
  End Scope;
End Scope;

Now, when anyone selects the Reporting Currency EUR it will automatically show the value of “Amount EUR” when the “Amount” metric is selected. The users can change the reporting currency dynamically, and the spreadsheets will update instantly, because all it has to do is select a different column.

Hopefully this post was helpful for anyone running into problems implementing multi-currency support in SSAS. If you still have any questions feel free to ask below!