SSAS : Multicurrency Problem (Part I)

Introduction

Suppose, you're working for an international company in Europe and this company has many branches across the world. These branches are located in countries with different currencies. Therefore, this multinational company book her transactions in local currencies. This blogpost is about how to handle multicurrency in a SQL Server Analysis Services environment.

Now, the first thought solution is to introduce two columns in the fact table, in case you've a Kimball data warehouse, off course.  But, suppose there are region managers in countries who are responsible for the sales in other countries. And the region manager reports in it's own currency (let's say American Dollars). The region manager is also responsible for Mexico. The currency of Mexico is Pesos. Now, the region manager wants to see the sales in American Dollars of the countries he is responsible for. The pesos should be converted into American Dollars. The head office of this multinational company is in the Netherlands and they report in Euros.

This blog post is about collecting the sold products and services in a local currency and convert it to any other currency that is desired. So, this is in my opinion the most flexible solution. In this way, we can handle very easily organization changes, for instance, like that region management is moved from USA to Mexico. Now, the American Dollars and Pesos can now easily consolidated into Pesos.

Overall, there are a couple of scenarios possible:

1. Data is collected in one currency and it must be converted into multiple currencies (website scenario).
2. Data is collected in multiple currencies and it must be converted into one currency (headquarter scenario).
3. Data is collected in multiple currencies and it must be converted into multiple currencies (subsidiary and multiregion scenario).

In this blogpost I'll discuss option number 3.

And, there are a couple of solutions:

1. Create multiple currency columns (EURO, USD, etc) in the fact and convert the local currency into the columns. The advantage is that it's simple. Disadvantage is that it's fixed.
2. Convert the different currencies into an base currency during the ETL, and build multicurrency conversion in the cube. This is more complex than number 1 option.
3. Import the different currencies into the cube and convert the currencies at query time. This is the most complex conversion solution but also a neat one!

I'll take number 3!

For this blogpost I've used the book "Expert Cube Development with SQL Server 2008 Analysis Services" by Chris Webb, Alberto Ferrari and Marco Russo. In Chapter 7 an explanation is given about MultiCurrency problems. Now as you are googling about MultiCurrency the proposed MDX Script isn't the best generated solution (in any way for the Many2Many way) and better and more performant solutions are available. I'll blog about them in future blogpost. So be aware!

For this blogpost I've used SQL Server 2008 R2 and Excel 2013.

MultiCurrency Problem

In this blogpost I'll write down a solution that worked for me in a lab experiment. In future blogposts I'll investigate improvements of the base solution I'll present here. In the base solution, there a couple of tables needed:

• FactOrderLine.
• DimProduct.
• DimDate.
• CurrencyRate.
• Currency.

I've created a database "MultiCurrency" and ran the script below.

USE [MultiCurrency]
GO

DROP TABLE [dbo].[FactOrderLine]
GO

DROP TABLE [dbo].[CurrencyRate]
GO

DROP TABLE [dbo].[DimProduct]
GO

DROP TABLE [dbo].[DimDate]
GO

DROP TABLE [dbo].[DimCurrency]
GO

CREATE TABLE [dbo].[DimCurrency](
 [Currency_ID] [varchar](50) NOT NULL,
 [Currency_Name] [varchar](50) NULL,
 CONSTRAINT [PK_Dimcurrency] PRIMARY KEY CLUSTERED 
(
 [Currency_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DimDate](
 [Date_ID] [int] NOT NULL,
 [Day] [varchar](50) NULL,
 [Month] [varchar](50) NULL,
 CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED 
(
 [Date_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DimProduct](
 [Product_ID] [int] NOT NULL,
 [Product_Name] [varchar](50) NULL,
 CONSTRAINT [PK_DimProduct] PRIMARY KEY CLUSTERED 
(
 [Product_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CurrencyRate](
 [CurrencyRate_ID] [int] IDENTITY(1,1) NOT NULL,
 [Date_ID] [int] NULL,
 [Currency_ID] varchar(50) NULL,
 [Rate] [float] NULL,
 CONSTRAINT [PK_CurrencyRate] PRIMARY KEY CLUSTERED 
(
 [CurrencyRate_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FactOrderLine](
 [OrderLine_ID] [int] IDENTITY(1,1) NOT NULL,
 [Date_ID] [int] NULL,
 [Product_ID] [int] NULL,
 [Currency_ID] varchar(50)  NULL,
 [Amount] [money] NULL,
 CONSTRAINT [PK_FactOrderLine] PRIMARY KEY CLUSTERED 
(
 [OrderLine_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO [dbo].[DimCurrency]
           ([Currency_ID],
      [Currency_Name])
     VALUES
           ('EUR', 'EUR'),
     ('GBP', 'GBP'),
     ('USD', 'USD'),
     ('MEX', 'MEX')

-- [DimDate]
INSERT INTO [dbo].[DimDate]
           ([Date_ID],
      [Day],
      [Month])
VALUES
           (20140921, 'Sunday', 'September'),
     (20140922, 'Monday', 'September'),
     (20140923, 'Tuesday', 'September'),
     (20140924, 'Wednesday', 'September'),
     (20140925, 'Thursday', 'September'),
     (20140926, 'Friday', 'September'),
     (20140927, 'Saturday', 'September')

--[DimProduct]
INSERT INTO [dbo].[DimProduct]
           ([Product_ID],
      [Product_Name])
     VALUES
           (1, 'Bicycle'),
     (2, 'Car'),
     (3, 'Motor'),
     (4, 'Bus')

INSERT INTO [dbo].[CurrencyRate]
           ([Date_ID]
           ,[Currency_ID]
           ,[Rate])
     VALUES
           (20140921, 'EUR', 1.0), --EUR
     (20140922, 'EUR', 1.0), --EUR
     (20140923, 'EUR', 1.0), --EUR
     (20140924, 'EUR', 1.0), --EUR
     (20140925, 'EUR', 1.0), --EUR
     (20140926, 'EUR', 1.0), --EUR
     (20140927, 'EUR', 1.0), --EUR
     (20140921, 'GBP', 1.25), --GBP
     (20140922, 'GBP', 1.27), --GBP
     (20140923, 'GBP', 1.30), --GBP
     (20140924, 'GBP', 1.32), --GBP
     (20140925, 'GBP', 1.40), --GBP
     (20140926, 'GBP', 1.21), --GBP
     (20140927, 'GBP', 1.22), --GBP
     (20140921, 'USD', 0.77), --USD
     (20140922, 'USD', 0.80), --USD
     (20140923, 'USD', 0.62), --USD
     (20140924, 'USD', 0.90), --USD
     (20140925, 'USD', 0.66), --USD
     (20140926, 'USD', 0.33), --USD
     (20140927, 'USD', 0.90), --USD
     (20140921, 'MEX', 0.06), --MEX
     (20140922, 'MEX', 0.10), --MEX
     (20140923, 'MEX', 0.05), --MEX
     (20140924, 'MEX', 0.11), --MEX
     (20140925, 'MEX', 0.12), --MEX
     (20140926, 'MEX', 0.14), --MEX
     (20140927, 'MEX', 0.15) --MEX
GO

INSERT INTO [dbo].[FactOrderLine]
           ([Date_ID]
           ,[Product_ID]
           ,[Currency_ID]
           ,[Amount])
     VALUES
           (20140922, 1, 'EUR', 5), 
     (20140922, 1, 'EUR', 7.7),
     (20140922, 2, 'GBP', 8.95),
     (20140925, 1, 'EUR', 5),
     (20140923, 2, 'USD', 100),  -- 0,62 * 100 = 62
     (20140921, 1, 'EUR', 500),
     (20140925, 1, 'EUR', 1000),
     (20140926, 4, 'MEX', 300)
GO

SELECT D.Currency_Name, Sum(F.[Amount])
FROM FactOrderLine F
LEFT OUTER JOIN [DimCurrency] D ON F.Currency_ID = D.Currency_ID
GROUP BY D.Currency_Name

Now start SQL Server Analysis Services and create a Datasource:

1. Create a New project in SQL Server Analysis Services.

2. Name it MultiCurrency and press OK.

3. Create a Datasource by richtclicking in the in the Solution Explorer (on the right side of Microsoft Visual Studio).

4.  The DataSource Wizard is started and press Next.

5. Create a new connection by pressing New.

6. Enter . at the server name and select the right database "MultiCurrency".

7. Press Next.

8. Select Use the Service Account at the Impersonation Information Window.

9. Enter "dsMultiCurrency" at the Data Source Name.


The next step is creating a Data source view:


10. Right click in the Solution explorer on Data Source Views. (on the right side of Microsoft Visual Studio)

11. Press Next when the wizard opens.

12. Select the Datasource and press Next

13.  Press Next in the Name Matching window.

14. Select all tables in the Available objects, press > button and press Next.

15. Name the DataSourceView "dsvMultiCurrency".


This is the Data Source View :

Okay let's create the dimensions:

16. In Solution Explorer (on the right side of Microsoft Visual Studio), right-click Dimensions, and then click New Dimension. The Dimension Wizard appears.

17. On the Welcome to the Dimension Wizard page, click Next.

18. On the Select Creation Method page, verify that the Use an existing table option is selected, and then click Next.

19. On the Specify Source Information page, verify that the dsvMultiCurrency data source view is selected.

20. In the Main table list, select DimDate.

21. Click Next.

22. On the Select Dimension Attributes page, select the check boxes next to the following attributes:

• Day
• Month

23. Change the setting of the Day attribute's Attribute Type column from Regular to Day of week.To do this, click Regular in the Attribute Type column. Then click the arrow to expand the options. Next, click Date > Calendar > Day of Week. Click OK.

24. Do this also for Month. Change Attribute Type to Month.

25. On the Completing the Wizard page, in the Preview pane, you can see the Date dimension and its attributes.

26. Click Finish to complete the wizard.

27. In the Solution Explorer, in the MultiCurrency project, the Date dimension appears in the Dimensions folder.

28. On the File menu, click Save All.

Now do the other dimensions. Don't forget to make special adjustments for the Currency dimension.

For the Currency dimension:

29.   Change the setting of the CurrencyID attribute's Attribute Type column from Regular to Currency ISO Code.To do this, click Regular in the Attribute Type column. Then click the arrow to expand the options. Next, click Currency>Currency>Currency ISO Code.

30. The same approach for Currency Name.

31.. On the File menu, click Save All.

Now let's define the cube.


32. In Solution Explorer, right-click Cubes, and then click New Cube.

33. On the Welcome to the Cube Wizard page, click Next.

34. On the Select Creation Method page, verify that the Use existing tables option is selected, and then click Next.

35. On the Select Measure Group Tables page, verify that the dsvCurrency data source view is selected.

36. Click Suggest to have the cube wizard suggest tables to use to create measure groups.

37. Click Next.

38. On the Select Measures page, review the selected measures in the Currency Rate and the Fact Order Line measure group.

39. Click Next.

40. On the Select Existing Dimensions page click Next.

41. Click Next.

42. On the Completing the Wizard page, change the name of the cube to MultiCurrency.

43. Click Finish to complete the wizard.

44. On the File menu, click Save All.

Before using the Business Intelligence wizard we have to make some adjustments in the model:

45. Change the IsAggregatable property to False for the key attribute.

46. Change the Type property of the measure group "Exchange Rate"  to ExchangeRate (Best practice).

Now it's time to start the Business Intelligence Wizard :

47. Right click on the cube in the Solution Explorer and Click on " Add Business Intelligence".

48. Click Next.

49, Click on "Define Currency conversion" in the Choose Enhancement window and press Next.

If you did not process the cube earlier the following window appears

Now let's process the cube and see if there are some changes in this window:

Now this looks better...

50. Press Next.

51. Select Amount in the "Select Members" window and press Next.

52.  In the "Select Conversion Type" window it's possible to select the currency conversion. Make sure that Many-to-Many is selected. Press Next.

53. In the "Define Local Currency Reference" Window a link between the local currency and the transaction is selected.

54. In the "Specify Reporting Currencies" Window select All of the Reporting Currencies and press Next.

55. On the Completing the Wizard page, in the Changes pane, you can see the changes that are being made by the Business Intelligence wizard to the Datasource view, dimensions and the Cube. Press Finish.

The dimension Usage Tab looks like:

A table in the Datasource View is added:

And on the Calculations Tab a MDX Script is added (snippet):

56. In the File Menu,  Click Save All.

57. Deploy and process the Cube.

Now we are done making changes to the cube. Now it's time to check whether the adjustments are doing the right calculations.

58. Start Excel and create a new excel sheet. For this blogpost I used Excel 2013.

59. Go to the Data Tab and select "From Other sources", Select "From Analysis Services"

60. Enter . in the Servername in the Connect to Database Server window.

61. Make Sure that the database "MultiCurrency" and the Cube "MultiCurrency" is selected and press Next.

62. Press Next.

63. Press OK.

If everything went ok a pivot table is created in excel.

64. Now let's select the Amount in the PivotTable fields and put them in the Measures pane, Select currency ID of the Dimcurrency dimension and put that in the Rows Pane and Select the Currency ID of the Reporting Currency and put that in the Columns Pane.

The window now looks like this:

On the left you can see the initially entered currencies and in the columns are the converted amounts to the reporting Currencies shown. Now we have to check whether these are correct values.

For example, lets take the 100 USD. There is one order with a value of 100 dollar on September 23, 2014 and the currency conversion rate on September 23, 2014 is 0.62. So the 100 * 0.62 = 62 dollar. And that is correct.

If we drag the DateID (I know) into the Rows Pane the following values are shown:

Let's double check whether one these values are correct.

Suppose we take the orderlines of September 22, 2014 and compare these with the cube..There are three Orderlines:

• Two bikes for 5 and 7.7 euro (hmmm bit cheap)
• One Car for 8.95 Britisch Pounds

The Currency rates on that day are:

I checked the calculations manually in Excel and the calculations are correct.

Conclusion

In this blogpost I've shown the conversion of currencies at query time in the Cube. Now, although this is great, there are some drawbacks about this solution. The CurrencyRate and the DimCurrency are not built according to the Kimball starschema theory. For instance, the key of the dimCurrency dimension is not integer but varchar. So, although it looks great, I'll investigate whether if things could be improved.

According to the blog of Chris Web this solution is not a very performant solution, unfortunately. The solution above takes about 9 seconds on the AdventureWorks Database.

Other references:

• Another Chris
• SQL Magazine
• Christian Wade
• Bob Penev
• Technet

Greetz,

Hennie