Walkthrough: Implement many-to-many currency conversion in an Analysis Services solution
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:
