Tuning the Currency Conversion Calculations created by the Add Business Intelligence Wizard
Reposted from Chris Webb's blog with the author's permission.
I see the ‘Add Business Intelligence’ wizard in BIDS as a bit of a missed opportunity on Microsoft’s part: it was a great idea to have functionality that would automate difficult stuff like adding MDX calculations to a cube, and it’s a shame that most of the MDX code it generates is so rubbish. Take, for example, the MDX currency conversion calculations that it creates. If you’re using Enterprise Edition the best way to do currency conversion is not to use MDX at all but to use measure expressions, but if you have Standard Edition you have no choice but to use MDX to do your currency conversion and if you use the calculations the wizard produces you’d probably think that this type of calculation has to be painfully slow. That’s not true, though – and in this post I’ll show you how to replace the currency conversion calculations created by the wizard with some that are not only much simpler but also much, much faster.
Let’s use a simple cube to illustrate this. In the Adventure Works database I created a cube with two measure groups: one based on the Internet Sales fact table, the other based on the Currency Rates fact table. I then added the Currency dimension and the Date dimension (joining on OrderDateKey in the Internet Sales table), and ran the ‘Add Business Intelligence’ wizard to add many-to-many currency conversion to the cube. Here’s what my Dimension Usage tab looked like after I’d run the wizard (note that it added a new Reporting Currency dimension to the cube):
And here’s the MDX that it adds to the MDX Script:
// <Currency conversion>
// Currency conversion wizard generated script.
// Currency conversion generated on: 11 January 2011 21:05:19
// by user: Chris
// Currency conversion type: ManyToMany
// Selected members to be converted: Sales Amount
// 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.
// This is the Many to One section
// All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension
Scope ( { Measures.[Sales Amount]} );
Scope( Leaves([Date]) ,
[Reporting Currency].[USD],
Leaves([Source Currency]));
// Convert Local value into Pivot currency for selected Measures that must be
//converted with Measure rate [Average Rate]
Scope( { Measures.[Sales Amount]} );
This = [Reporting Currency].[Local] / Measures.[Average Rate];
End Scope;
End Scope;
// This is the One to Many section
// All currency conversion formulas are calculated for
//the non pivot currency and at leaf of the time dimension
Scope( Leaves([Date]) ,
Except([Reporting Currency].[Source Currency Code].[Source Currency Code].Members,
{[Reporting Currency].[Source Currency Code].[Source Currency Code].[USD],
[Reporting Currency].[Source Currency Code].[Source Currency Code].[Local]}));
// This section overrides the local values with the Converted
// value for each selected measures needing to be converted with Measure rate [Average Rate]…
// LinkMember is used to reference the currency from the
// source currency dimension in the rate cube.
Scope( { Measures.[Sales Amount]} );
This = [Reporting Currency].[Source Currency Code].[USD] * (Measures.[Average Rate], LinkMember([Reporting Currency].[Source Currency Code].CurrentMember, [Source Currency].[Source Currency Code])) ;
End Scope;
End Scope; // Leaves of time, all reporting currencies but local and pivot currency
End Scope; // Measures
// End of the currency conversion wizard generated script
// </Currency conversion>
Scary, eh? I won’t explain what it does in detail, but basically it does the following:
- Converts the various local currency values held in the Internet Sales fact table into US Dollars
- Converts these US Dollar amounts into whatever Reporting Currency the user has selected
It’s a lot of code for something so straightforward, and the reason why so complex is because it needs to use the Currency Rates measure group for both sets of currency rates: from local currency to US Dollars, and from US Dollars to reporting currency. As we all know overly-complex code is usually poorly-performing code, and in this case the use of LinkMember in particular is a killer; also there are no checks for situations where exchange rate values don’t exist, which is a fairly basic mistake.
Take the following query:
SELECT [Date].[Calendar Year].MEMBERS ON 0,
NON EMPTY
[Reporting Currency].[Source Currency Code].MEMBERS ON 1
FROM [CurrencyTestWizard]
WHERE([Measures].[Sales Amount])
It displays all Years on columns and all Reporting Currencies on rows for the Sales Amount measure, so it’s doing quite a lot of work. On my quite beefy laptop, using the cube I’d just created, it ran in just under 9 seconds on a cold cache to give me this:
We can do a lot better than 9 seconds though. The first step to optimising this is, as always, to push complexity out of the MDX and back into the cube design. Given that a fact table containing exchange rate values is usually pretty small relative to other fact tables, what I decided to do was to add a second measure group to my cube based on exactly the same Exchange Rate fact table, use the original measure group for the conversion from local currency to US Dollars and then the new one for the conversion from US Dollars to reporting currency. This did increase both the size of the cube and the amount of time taken to process it, but only by a tiny amount.
BIDS didn’t let me create a second measure group from a fact table that was already in use, so I had to create a new named query in BIDS that did a SELECT * from the Exchange Rate fact table and then use that. Having done this, I was able to set up my Dimension Usage as follows, with Reporting Currency joining to this new measure group:
Now, I could replace the code above with the following MDX:
SCOPE({[Measures].[Sales Amount]});
SCOPE([Date].[Date].[Date].MEMBERS);
SCOPE([Source Currency].[Source Currency Code].[Source Currency Code].MEMBERS);
SCOPE(EXCEPT([Reporting Currency].[Source Currency Code].[Source Currency Code].MEMBERS
,{{[Reporting Currency].[Source Currency Code].&[2147483647]}}));
THIS = IIF([Measures].[Average Rate - Reporting Currency Rates]=0
, NULL
,IIF([Measures].[Average Rate]=0
, NULL
,([Measures].[Sales Amount] / [Measures].[Average Rate]))
* [Measures].[Average Rate - Reporting Currency Rates]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
What I’m doing here is using a single set of nested scoped assignments to perform the calculation at the leaf level of the Date, Source Currency and Reporting Currency dimensions. With two different measure groups containing exchange rates there’s no need for the use of LinkMember – I just use two different measures for each step in the conversion – and I make sure I don’t bother doing any conversions where exchange rate values don’t exist.
When I ran my test query, I got exactly the same results but the query ran in around 0.5 seconds on a cold cache – 18 times faster than on the original version of the cube! It just shows you what a few tweaks to your cube design and MDX can achieve, doesn’t it? It’s a shame that MS didn’t invest a bit more time on the ‘Add Business Intelligence’ wizard to improve it – it would probably have improved the quality of many implementations no end. In the future, with PowerPivot and BISM being focused so much on ‘self-service’ BI, I think something similar to the wizard is a ‘must have’ to help users create complex DAX calculations, and I hope MS realise that functionality like this can be key to the overall success of a product.
 |
Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com . |