SSAS : Multicurrency Problem (Part II)

Introduction

In this blogpost I'll improve the solution of the multicurrency problem I've written in the first blogpost (part I). In the first part about the MultiCurrency problem there were a couple of problems and improvements possible:

• The implementation is not a typical Dimension/Fact starschema. The CurrencyRate table is not a traditional Fact. The key of the Currency dimension is not a integer. I've changed that in this new version solution.
• As in the blog of Chris schmidt described, the currency (cube)dimension can be improved in order to gain some performance speed.

Improved MultiCurrency Solution

For this purpose I've created a MultiCurrencyV2 Database and used the following script:

USE [MultiCurrencyV2]
GO

DROP TABLE [dbo].[FactOrderLine]
GO

DROP TABLE [dbo].[FactCurrencyRate]
GO

DROP TABLE [dbo].[DimProduct]
GO

DROP TABLE [dbo].[DimDate]
GO

DROP TABLE [dbo].[DimCurrency]
GO

CREATE TABLE [dbo].[DimCurrency](
 [Currency_ID] [int] 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].[FactCurrencyRate](
 [CurrencyRate_ID] [int] IDENTITY(1,1) NOT NULL,
 [Date_ID] [int] NULL,
 [Currency_ID] [int] NULL,
 [Rate] [float] NULL,
 CONSTRAINT [PK_FactCurrencyRate] 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] [int]  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
           (1, 'EUR'),
     (2, 'GBP'),
     (3, 'USD'),
     (4, '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].[FactCurrencyRate]
           ([Date_ID]
           ,[Currency_ID]
           ,[Rate])
     VALUES
           (20140921, 1, 1.0), --EUR
     (20140922, 1, 1.0), --EUR
     (20140923, 1, 1.0), --EUR
     (20140924, 1, 1.0), --EUR
     (20140925, 1, 1.0), --EUR
     (20140926, 1, 1.0), --EUR
     (20140927, 1, 1.0), --EUR
     (20140921, 2, 1.25), --GBP
     (20140922, 2, 1.27), --GBP
     (20140923, 2, 1.30), --GBP
     (20140924, 2, 1.32), --GBP
     (20140925, 2, 1.40), --GBP
     (20140926, 2, 1.21), --GBP
     (20140927, 2, 1.22), --GBP
     (20140921, 3, 0.77), --USD
     (20140922, 3, 0.80), --USD
     (20140923, 3, 0.62), --USD  --xx
     (20140924, 3, 0.90), --USD
     (20140925, 3, 0.66), --USD
     (20140926, 3, 0.33), --USD
     (20140927, 3, 0.90), --USD
     (20140921, 4, 0.06), --MEX
     (20140922, 4, 0.10), --MEX
     (20140923, 4, 0.05), --MEX
     (20140924, 4, 0.11), --MEX
     (20140925, 4, 0.12), --MEX
     (20140926, 4, 0.14), --MEX
     (20140927, 4, 0.15) --MEX
GO

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

In this blogpost I'll recreate the Cube and I'll show the enhancement I've made to this MultiCurrency Problem. The starting situation is that the Datasource and the DataSourceView are already existing.

Take the following steps:

1. Run the New Cube wizard and create the Cube and the three dimensions Date, Currency and Product.  The cube should like this:

2. Start the Business Intelligence wizard on the cube and press Next in the Business Intelligence Wizard opening window.

3. Select "Define Currency Conversion" and press Next.

An error because the wizard can't determine the Currency dimension.That's because we didn't Type the Currency dimension as a Currency.

4. Press Cancel.

5. Edit the properties of the Currency dimension by right clicking on the Currency dimension  choose properties.

6. Select the Currencyname and select the Type property and change this to Currency>Currency>CurrencyName.

7. Change the property IsAggregatable of the attribute Currency Name to False.

8. Change the property Usage to key of the attribute Currency Name.

9. Change the property KeyColumns to Currency ID.

10. Change the property NameColumn to Currency Name.

11. Red squirels now occur under CurrencyName and the dimension name Currency. Solve this by removing the attribute CurrencyID.

The next step is defining the granularity of the Measuregroups for the Currency Dimension again.

13. Let's try to run the Business Intellligence wizard again on the cube but now an error happens because of the lack of a Time Type dimension.

14. Change the property Type of the Date dimension to Time, change the Type of Day to DayOfWeek and the Type of the attribute Month to MonthOfYear.

15. Start the Business Intelligence wizard of the Cube again and change the setting according the following window:

16. Press Next.

17. Select Amount and change the Exchange Rate Measures to Rate.

17. Select Many to Many and click Next.

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

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

20. 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.

Now, we are done. The Dimension usage tab, the Datasource View and the Calculations looks the same as the ones that I've described in Part I of the MultiCurrency problem.

Let's check whether this is correct:

And this is the same as in the Part I of the Multicurrency series.

Conclusion

This solution is a better solution because the solution is a typical starschema now, and the cube is more optimized and therefore has a better performance.

In my next blogpost about the multicurrency problem I'll show you an even faster solution. This solution is proposed by Chris Webb. He said that the generated MDX script by the business Intelligence wizard is terrible (his words). That's because of the LinkMember function.

Greetz,

Hennie