SSAS – Summing on different currencies. Don’t hide your data.

Typically, measures cannot be aggregated along a currency dimension and so we set the IsAggregateable property to False. However, doing so has some side effects because you need to set a default member for the attribute (if you don’t, the first member of the attribute will be used). The default member will then automatically act as a default filter if the user does not explicitly use the currency dimension.

Using all dimensions, there is no issue:

But remove the Currency dimension and this is what you get:

The data behind this very simple pivot table actually contains 3 transactions in 3 different currencies (CAD, EUR and USD) but because of the currency dimension’s behaviour of not aggregating, displaying transaction amounts by any dimension other than currency will, in fact, only show transactions for the currency that was used as the default and thus, transactions in all other currencies will be hidden from the user.

To me, this is a big risk because we cannot ask of users to remember to ALWAYS use the currency dimension. It’s just not a good design from a usability point of view. What is needed is some way to show to the user something that can tell him that there are amounts for what he is currently looking at but that we cannot show it because that amount would be summing up multiple currencies. After thinking for quite a while and trying various approaches, I have finally come up with what seems to me like the best solution.

If you have a fact table containing amounts in various currencies, then without a doubt you have a foreign key pointing to the currency for that amount.

The trick here is to use that foreign key (let’s call it CurrencyID) as a measure in your measure group. Actually, you’re going to have to use it twice. Set one of those two measure’s AggregateFunction to Min and the other to Max naming both as MaxCurrencyID and MinCurrencyID respectively.

What will happen when you browse your cube is that for any given position, these two measures will represent the minimum and maximum values of your foreign key, CurrencyID, used for all transactions used in the sum for any amount. If these two measures have the same value, you can confidently display the amount knowing that only one currency was used. Conversely, if they have a different value, then it means that more than one currency is used in summing the displayed amount. In this case, all you need to do is display some text like “Multiple Currencies” instead of the actual amount.


Of course, you cannot show that 24$ total since all detail amounts are in different currencies so you will need to scope your measure with some very simple MDX scripting as follows:

[Measures].[Amount] = iif([Measures].[MinCurrencyID] = [Measures].[MaxCurrencyID], [Measures].[Amount], “Multiple Currencies”);

This scope statement will then produce something the following output:

Of course, MinCurrencyID and MaxCurrencyID aren’t the kind of measures you want your users to be using, so you’ll need to set the Visible property for those measures to False.

Using this technique, browsing the cube suddenly becomes much more informative to end users and nothing ever gets hidden to your users.

Be warned that this technique will work well for regular measures but implementing this on calculated measures is a bit trickier and will be the subject of my next post.

Advertisements

Posted on May 4, 2012, in MDX, SSAS. Bookmark the permalink. 3 Comments.

  1. This is a great post. I had this problem exactly, although with a billing currency dimension and a source currency dimension together. Recommended practice is to set both to IsAggregatable = False, but after doing that, unless both currencies are used in the grid, they act as a filter to each other, with the default currency of the unused dimension limiting the results of the other one, and results in general.

    I thought I must be doing something wrong – or maybe that perhaps there was an issue with IsAggreagatable = False that everyone was either missing or ignoring – so its useful to know that this is actually the way it works, undesirable as that may be. I like your proposed solution.

  2. Hi, this is indeed a good post.. I have a question though? If I understand this correctly this issue only occurs when in need of a multi currency cube. In my case I load my cube in a single currency (converting that what is needed beforehand). This will be my default currency. I was wondering though what will happen if I negate the default currency within a certain role, as those users have no use for this default currency. How will the cube handle this. Let’s say I hide EUR (my default) and USD and GBP are available for the role, yet everything is loaded in EUR. Is there a way to auto change to one of the currencies within the role, or will it always show EUR first?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: