SSAS : Multicurrency Problem (Part III)

Introduction

I wrote two blogposts about the multicurrency problem in SSAS. The first blogpost (Part I) was about the multicurrency solution and is mainly inspired by the book of "Expert Cube Development with SQL Server 2008 Analysis Services" by Chris Webb, Alberto Ferrari and Marco Russo. In the second blogpost (Part II) further improvements were implemented. In this blogpost I'll bring the solution a step further.

Chris Webb wrote in his blogpost "Tuning the Currency Conversion Calculations created by the Add Business Intelligence Wizard" that the "Add Business Intelligence" Wizard is a missed opportunity from Microsoft. He is saying that because the idea is great, simplifying the difficult stuff of SSAS, but the generated MDX script in SSAS is bad implemented.

Improving the Multicurrency problem a step further

In this blogpost won't  describe step by step the complete solution. I'll start with the solution that ended in my second blogpost about MultiCurrency.  In this third blogpost I'll work out the solution that is proposed by Chris Webb in his blogpost.

The business Intelligence Wizard adds a named query in the datasource view, adds an extra dimension to the cube and adds some MDX script to the cube. The following situation could is a typical many-2-many conversion example.

The datasource view:

The dimension usage tab in the cube:

The MDX script calculation in the calculation tab:

 Scope ( { Measures.[Amount]} );
   Scope( Leaves([DimDate]) ,
    [Reporting Currency].[EUR], 
    Leaves([DimCurrency]));
  
    
        
        // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Rate] 
        Scope( { Measures.[Amount]} );
            
         This = [Reporting Currency].[Local] * Measures.[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([DimDate]) ,  
    Except([Reporting Currency].[Currency Name].[Currency Name].Members, 
    {[Reporting Currency].[Currency Name].[Currency Name].[EUR],
    [Reporting Currency].[Currency Name].[Currency Name].[Local]}));
  
    
      
      // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [Rate]… 
      // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
         Scope( { Measures.[Amount]} );
             This = [Reporting Currency].[Currency Name].[EUR] / (Measures.[Rate], LinkMember([Reporting Currency].[Currency Name].CurrentMember, [DimCurrency].[Currency Name])) ;
      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  

Okay let's summarize the settings that has been set so far (from my former posts):

• Property Type of Currency dimension to Currency
• Property Attribute Type of  CurrencyName to Currency > Currency > Currency ISO Code.
• Property Attribute Type of month to Date > Calendar >Month Of Year
• Property IsAggregatable of the attribute Currency Name to False. 
• Property Usage to key of the attribute Currency Name.
• Property KeyColumns to Currency ID.
• Property NameColumn to Currency Name.

• Property Type of Date dimension to Time
• Property Attribute Type of  Day to Date > Calendar > Day of Week.
• Property Attribute Type of Month to Date > Calendar >Month Of Year

• Property of the MeasureGroup FactEchangeRate to ExchangeRate.

Now, new settings introduced for this blogpost (or described) in this blogpost:

• Property Formatstring of the Measure Amount to Currency.
• Measure Rate is semi additive and therefore set the property Aggregatefucntion to "Last Non Empty". For instance If we want to see the Exchange rate for a week we don't want to see the sum of the Exchange rates of that week but the most recent exchange rate of the week.
• Add to the calculation tab:
• Language([DimCurrency].[Currency Name].[EUR]) = 1043;
• Language([DimCurrency].[Currency Name].[GBP]) = 2057;
• Language([DimCurrency].[Currency Name].[MEX]) = 2058;
• Language([DimCurrency].[Currency Name].[USD]) = 1033;

There are also better solutions by making this a variable solution by storing this information in the currency dimension table and use them by the Value column or the Member property. I'll investigate this in the future.

Now we are going to adjust the solution with the improvement of Chris :

1. Go to the DataSource View in the Solution explorer of the project in Visual Studio

2. Create a named query "Reporting Currency Rates" in the data source view. With the following query :

     SELECT        CurrencyRate_ID, Date_ID, Currency_ID, Rate
     FROM            dbo.FactCurrencyRate

3. Create a relationship between Date and the new created named query  "Reporting Currency Rates"

4. Create a relationship between Reporting Currency and the new created named query  "Reporting Currency Rates". The Data source view would look like this now:

5.  Create a new measure group "Reporting Currency Rates" by clicking on the Cube Structure Tab.

6. Right click on the Cube (MultiCurrencyV3Redo in my case) and create a new measure group.

7.  Adjust the dimension Usage Tab according to the following screenshot:

8. Now go the Calculations tab and replace the following code:

  Scope ( { Measures.[Amount]} );
   Scope( Leaves([DimDate]) ,
    [Reporting Currency].[EUR], 
    Leaves([DimCurrency]));
  
    
        
        // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Rate] 
        Scope( { Measures.[Amount]} );
            
         This = [Reporting Currency].[Local] * Measures.[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([DimDate]) ,  
    Except([Reporting Currency].[Currency Name].[Currency Name].Members, 
    {[Reporting Currency].[Currency Name].[Currency Name].[EUR],
    [Reporting Currency].[Currency Name].[Currency Name].[Local]}));
  
    
      
      // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [Rate]… 
      // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
         Scope( { Measures.[Amount]} );
             This = [Reporting Currency].[Currency Name].[EUR] / (Measures.[Rate], LinkMember([Reporting Currency].[Currency Name].CurrentMember, [DimCurrency].[Currency Name])) ;
      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> 

By this code :

       Scope ( { Measures.[Amount]} );
    Scope([DimDate].[Date ID].[Date ID].MEMBERS);
      Scope([DimCurrency].[Currency Name].[Currency Name].MEMBERS);
   Scope(Except([Reporting Currency].[Currency Name].[Currency Name].Members, 
    {[Reporting Currency].[Currency Name].[Currency Name].[Local]}));
 
       THIS = IIF([Measures].[Rate - Reporting Currency Rates] = 0, NULL, 
                    IIF([Measures].[Rate] = 0, NULL, 
                        ([Measures].[Amount] *[Measures].[Rate])) / [Measures].[Rate - Reporting Currency Rates]);
        
        End Scope;
       End Scope; 
      End Scope; 
    End Scope;

Now let's take a look at the result:

Conclusion

In this blogpost I've discussed an improvement of the multicurrency problem. This is the third blogpost about multicurrency.

More information available....

There is more information available at the following pages:

• Chris Webb
• Chris Schmidt
• SQL Magazine
• Christian Wade
• Bob Penev
• Technet