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:
- Data is collected in one currency and it must be converted into multiple currencies (website scenario).
- Data is collected in multiple currencies and it must be converted into one currency (headquarter scenario).
- 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:
- 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.
- 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.
- 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:
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 I
sAggregatable 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 C
urrency 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:
Greetz,
Hennie
