Currency Conversion with SSAS
Microsoft tried to include best practices and other common tasks you had to implement on your own in Analysis Services 2000 in the new 2005 release by wizards. What do I have to say – THANKS for that! However there are some problems arising…
One is the currency conversion… Microsoft added a nice wizard for that. It handles the currency table and all the exchange rates. The result is a calculation added automatically in the cube. Here’s an example:
// <Currency conversion>
// Currency conversion wizard generated script.
// Currency conversion generated on: Freitag, 24. Juni 2005 11:44:51
// by user: cthiell
// Currency conversion type: OneToMany
// Selected members to be converted: Kosten des Einkaufs
// Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again.
// All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension
Scope ( { Measures.[Price]} );
Scope( Leaves([Date]) ,
Except([Reporting Currency].[Currency].Members, [Reporting Currency].[Currency].[Currency].[EUR]));
// This section overrides the Pivot Currency values with the Converted value for each selected measures/account members/account type members needing to be converted with Measure rate FAKT KURS - FAKT KURS KURS
// LinkMember is used to reference the currency from the source currency dimension in the rate cube.
Scope( { Measures.[Price]} );
This = (Measures.[Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) * [Reporting Currency].[Currency].[EUR];
End Scope;
End Scope;
So what does this script do? It simply exchanges the calculation of the measure “Price” for all currencies when the currency selected is not the currency the measure is stored in. So if you use “USD” instead of “EUR” each day’s value is multiplied by each day’s conversion rate (which is 100% correct) and then aggregated.
OK, so what’s the problem? Well, I see two problems:
-
If you have any calculated measures based on measures working with currency conversions, the calculated measures don’t work anymore. This might be a problem of my limited MDX knowledge but at the moment this is a fact for me…
-
If you have a complex cube you will notice significant performance degradation. Why? Well, that’s easy… Instead of retrieving the year’s sales (that’s already pre-aggregated) each day sales is queried, multiplied by the conversion rate and then aggregated... This is really a significant overhead…
I didn’t try to use a different approach… This would be that I change the Cube’s underlying Data Source view to do a cross join of the fact table and the currency conversion table. So I would have a fact record per currency with the correct conversion rate. This would end up in a much bigger cube but performance should be better (since everything can be aggregated) and calculated measures should be no problem anymore.
Any comments are very much appreciated…
Thomas