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.