Currency Conversion Wizard in SSAS

Picking up where we left off, just delete the measure expression and run the wizard.  As I said, pretty much all the other stuff we had to do to enable measure expressions has to be done prior to running the wizard anyway.

From the Cube Structure tab, select Cube > Add Business Intelligence.

Select “Define Currency Conversion” and click Next.

Fill in the next page in the wizard as shown here.  Specify which measure group contains the exchange rates.  The pivot currency, as discussed above, is US Dollars.  “1.5 US Dollars per 1 United Kingdom Pound” sounds right, so let’s go with that.

Select Sales Amount as the measure(s) we want to convert.

Specify One-to-many currency conversion.

Now it wants us to specify the “reporting currencies”.  It will actually create another currency dimension called “Reporting Currency”, which will be based on this selection.  I would imagine that most cubes that only need one-to-many currency conversion will have the base Currency dimension purely for the purpose of viewing the values in foreign currencies!  Adding another currency dimension can be a little overkill in my opinion.  I selected United Kingdom Pound and Brazilian Real for my reporting currencies.

The last page shows us what changes will be made to the cube/DSV.  Click Finish.

Let’s take a look at what it actually did.  Firstly, we have a new entity in our DSV called Reporting Currency.  It is basically the same as the FactCurrencyRate, but filtered on the pivot currency and the other currencies we selected as reporting currencies.  Interestingly, it is not related to any other entity in the DSV.

The wizard also created a dimension called Reporting Currency that is based on the Reporting Currency entity in the DSV.  It is very similar to our Currency dimension.  The Currency attribute has a DefaultMember property of US Dolllars, its IsAggregatable property is set to False, etc., etc.  In fact the only difference of any significance is that it has not set the Cardinality property of the attribute relationship to One.

And here is the MDX script it generated.  Note: there is a bug in this script.  If you are using a Date dimension that is called something different to the base dimension name (e.g. Order Date vs. Date), you will have to replace “Scope( Leaves([Date])” with “Scope( Leaves([Order Date])”.

      // <Currency conversion>

           

            // Currency conversion wizard generated script.      

            // Currency conversion generated on: 24 August 2006 15:36:38

            // by user: Conchango2 

            // Currency conversion type: OneToMany   

            // 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. 

     

            // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension

            Scope ( { Measures.[Sales Amount]} );

                  Scope( Leaves([Date]) ,

                        Except([Reporting Currency].[Currency].[Currency].Members, [Reporting Currency].[Currency].[Currency].[US Dollar]));

           

                // 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 End Of Day Rate

                // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 

                Scope( { Measures.[Sales Amount]} );

                       This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;

                End Scope;   

           

                  End Scope; // Leaves of time and non pivot currency  

            End Scope; // Measures

           

        // End of the currency conversion wizard generated script

      // </Currency conversion>

Having done some (thorough) scoping, the line of code that is of real interest is

This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;

This line is doing the division in a similar way to the measure expression.  However, it is using the LinkMember function to do a runtime link between Currency and Reporting Currency based on the member name.  This is effectively just replacing the Currency dimension with the Reporting Currency dimension for reporting purposes.