Walkthrough: Implement many-to-many currency conversion in an Analysis Services solution

  • Article

For one-to-many currency conversion, you can download sample SSAS project to check how it works. This article describes the scenarios when you want to implement a Many-to-many currency conversion by yourself instead of using Business Intelligence Wizard.  Take the AdventureWorksDW2008 database for example. You want to produce reports with any currency format based on your requirement.

Populate mapping table for currency rate

Create a view VMappingRate  in source database:

USE [AdventureWorksDW2008]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE VIEW [dbo].[VMappingRate] as

WITH

SourceRate as

(

SELECT CurrencyKey,DateKey,AverageRate,EndOfDayRate

FROM dbo.FactCurrencyRate

WHERE CurrencyKey IN

(SELECT DISTINCT CurrencyKey FROM  dbo.FactCurrencyRate)

),

mapping as

(

SELECT t1.CurrencyKey AS sourceID, t1.DateKey, t2.CurrencyKey AS ReportID, t1.EndOfDayRate*t2.EndOfDayRate AS Rate

FROM SourceRate t1 CROSS JOIN SourceRate t2

WHERE t1.DateKey = t2.DateKey

)

SELECT * FROM mapping

 

GO

 

Create Analysis Services base on 'Adventure Works DW'

  • 1. New an Analysis Services project and then create a data source from Adventure Works DW.
  • 2. Create a DSV with tables: FactInternetSales, DimDate, DimCurrency and FactCurrencyRate.
  • 3. Create a named query ReportingCurrency with below query:

SELECT     CurrencyKey, CurrencyAlternateKey, CurrencyName,

                      CASE WHEN CurrencyAlternateKey = 'ARS' THEN '11274' WHEN CurrencyAlternateKey = 'AUD' THEN '3081' WHEN CurrencyAlternateKey = 'DEM' THEN '1031' WHEN CurrencyAlternateKey

                       = 'GBP' THEN '2057' WHEN CurrencyAlternateKey = 'MXN' THEN '2058' WHEN CurrencyAlternateKey = 'CAD' THEN '4105' WHEN CurrencyAlternateKey = 'SAR' THEN '1025'

                       WHEN CurrencyAlternateKey = 'EUR' THEN '2067' WHEN CurrencyAlternateKey = 'FRF' THEN '1036' WHEN CurrencyAlternateKey = 'BRL' THEN '1046' WHEN CurrencyAlternateKey

                       = 'JPY' THEN '1041' WHEN CurrencyAlternateKey = 'CNY' THEN '2052' WHEN CurrencyAlternateKey = 'VEB' THEN '16394' WHEN CurrencyAlternateKey = 'USD' THEN

                       '1033' END AS LCID

FROM         dbo.DimCurrency

WHERE     (CurrencyKey IN

                          (SELECT DISTINCT CurrencyKey

                            FROM          dbo.FactCurrencyRate))

 

Adjust the relationships in DSV and then you will be able to see bellowing:

 

  • 4. Create dimension InteralCurrency from DimCurrency with two attributes Currency Key and CurrencyAlternateKey. Set the Currency Key as key attribute, Currency Name as the namecolumn of Currency Key.

 

  • 5. Create dimension ReportingCurrency from ReportingCurrency with two attributes Currency Key (Named Currency) and LCID (Named Locale). Set Currency as key attribute, Currency Name as the namecolumn of Currency and property IsAggregatable to False. Set property AttributeHierarchyEnalbed to False.

 

Create Date dimension and remember to set the dimension type to be Time (this step is important, please ensure the type is Time).

 

  • 6. Create an empty cube. Create a measure [End of Day Rate] on VMappingRate .Rate with aggregation LastNonEmpty. Create a measure [Sales Amount] on FactInternetSales.SalesAmount with aggregation SUM, and then set MeasureExpression to be: [Sales Amount] / [End of Day Rate]

 

       7.   In usage tab, set the relationship like this:

 

Note the relationship between Fact Internet Sales and Currency is many-to-many, the Intermediate measure group is Mapping Rate. After that, set the DirectSlice property of this relationship to:

([Currency].[Currency Key].&[100])

  • 8. Adjust the format of the measure, set FormatString of Sales Amount to 'Currency', copy and paste below code into the Calculations tab:

Alter Cube

  CurrentCube

    Update Dimension [Reporting Currency].[Currency],

      Default_Member = [Reporting Currency].[Currency].[US Dollar];   

Scope

( [Reporting Currency].[Currency].Members ); 

   Language( This ) =

   [Reporting Currency].[Currency].Properties( "Locale" ); 

End Scope; 

 

  • 9. After that, you should be able get below result:

 

 

[ Download the sample ]

Comments

  • Anonymous
    April 29, 2014
    This is only the 2nd example of many to many currency conversion that one can find on the internet. I must say whoever wrote this did a very very poor job in explaining the steps. Please do a better job in explaining a solution.

  • Anonymous
    December 14, 2014
    why in the above e.g. CTE is used and scaling of the data is done , This can't be done using the normal cross join. Please correct me there is anything wrong. Thanks