O'Reilly logo

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX by Denny Guang-Yeu Lee, Robert Zare, Sethu Meenakshisundaram, Matt Carroll, Sivakumar Harinath

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

24.2. Currency Conversion Scenario (m:n)

A good many-to-many dimension financial scenario is the currency conversion scenario. Here the goal is to provide you with the ability to look at your sales figures in the desired currency. Let's start by reviewing the [Internet Sales Amount] for June 2004:

select {
 [Measures].[Internet Sales Amount]
} on columns, non empty {
 [Destination Currency].[Destination Currency].[Euro],
 [Destination Currency].[Destination Currency].[US Dollar]
} on rows
from [Adventure Works]
where ([Date].[Fiscal].[Month].[June 2004]);

The output of this MDX statement provides you with the June 2004 Internet Sales Amount value in the US dollar and Euro currencies, as shown in the following table:

CurrencyInternet Sales AmountAverage Rate
Euro1,969,575.15.99
US Dollar$1,949,361.111.00

This conversion looks relatively simple but recall that the preceding query is for the month of June 2004 and exchange rates are calculated on a daily basis (and for that matter, intra-day rate). You actually calculate this by taking each day, applying the US Dollar Internet Sales Amount, dividing it by the average rate (which is that day's average exchange rate to the US dollar), and then summarizing the value:

with set [My Currencies] as '{ [Destination Currency].[Destination Currency].[Euro], [Destination Currency].[Destination Currency].[US Dollar] }' select { Crossjoin( [My Currencies], {[Measures].[Internet Sales Amount], [Measures].[Average Rate]} ) } on columns, non empty ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required