SSAS: Currency Conversion (Many-to-Many)

Original Link: SSAS: Currency Conversion (Many-to-Many)

This article is a complement of the book "SSAS Step by Step 2005". In this book, Reed Jacobson and Stacia Misner gave us some guidelines and generic directions about how to apply currency conversion based on many-to-many relationships, but they did not provide a step-by-step case. In order to make it complete, I will try to describe how I work on it, and I hope you can join in the discussion.

First of all, let's suppose that you have finished the exercises described before the part of "Supporting Currency Conversion". So that we can do the work based on a qualified "SSAS" cube.

1. Go to DSV, right click DSV pane, click ADD/Remove tables, add FactCurrencyRate and DimCurrency into DSV and click "OK"

2. Right-click "Diagram Organizer" to create a new diagram, and add DimCurrency, FactCurrencyRate, FactInternetSales and DimTime into it.

3. Go to "Cube Structure", right-click "Measures" to add a new Measure Group "Fact Currency Rate"

4. After we add the measure group "Fact Currency Rate", change the property "Type" of it to "ExchangeRate", expend it then
4.1 Right-click the measure, "Average Rate", to show its properties, change AggregateFuction to "AverageOfChildren"
4.2 Right-click the measure, "End of Day-Date", to show its properties, and change AggregateFuction to "LastNonEmpty". Save the project.

5. In Solution Explorer, right-click the folder "Dimensions" to create a new dimension using Dimension Wizard.

6. Make sure "Build the dimension using a data source" is chosen and the check box of Auto Build is clear, click Next

7.Available data source view should be "SSAS Step by Step DW" and click Next.
8.Dimension Type should be "Standard dimension"

9.Choose dbo.DimCurrency in the step of "Select the Main Dimension Table", key column should be "CurrentKey", member name can be "CurrencyName", click Next.

10. At the step "Select Dimension Attributes", make the setting like following, and click Next.

11. At the step "Specify Dimension Type", set Currency ISO Code to Currency Alternate Key, set Currency Source to Dim Currency, which is in fact CurrencyKey. Click Next.

For Currency ISO Code, please refer to:
http://www.iso.org/iso/support/currency_codes_list-1.htm

12. At the step "Define Parent-Child Relationship", click Next.

13. At the step "Completing the Wizard", name the dimension as "Currency". Click Finish.

14. Double click "Currency.dim" in Solution Explorer, rename the attribute "Dim Currency" to "Currency" and "Currency Alternative Key" to "Currency ISO Code"

15. Go to Cube Structure, and add Currency in the dimensions, then deploy the project. Save the project.

16. Right-click SSAS.cube to run Business Intelligence Wizard. Choose "Define currency conversion" and click Next (before we start to use BI Wizard, we should deploy the project first).

17. At the step "Set Currency Conversion Options", choose "Fact Currency Rate" and set other options like the following, then click Next.

18. Since we just simply use currency conversion to apply an exchange rate to measures, at the step "Select Members", we will only check Reseller Sales Amount and Internet Sales Amount. Click Next.

19. Select "Many-to-Many" as Conversion Type, then Next.

20. The step "Define Local Currency Reference" will be like the following, usually we do not need to change anything, so we just click Next.




http://msdn.microsoft.com/en-us/library/ms175660.aspx

Local currency:

The currency used to store transactions on which measures to be converted are based.
The local currency can be identified by either:
A currency identifier in the fact table stored with the transaction, as is commonly the case with banking applications where the transaction itself identifies the currency used for that transaction.

A currency identifier associated with an attribute in a dimension table that is then associated with a transaction in the fact table, as is commonly the case in financial applications where a location or other identifier, such as a subsidiary, identifies the currency used for an associated transaction.

21. At the step "Specify Reporting Currencies", select all reporting currencies, click Next.

22. At the step "Completing the Wizard", notice that BI Wizard will generate script about currency conversion in the script of the cube. This part gives us an idea, that is if we find there is something wrong with the result generated by BI Wizard, in order to roll back the state before we run BI Wizard, we can go the script of the cube, find the script of currency conversion and delete it, then deploy the project to roll back.
Click Finish and save the project.

23. Go to DSV, we can find a named query "Reporting Currency", and set CurrencyKey as Logical Primary Key. Relate "Reporting Currency" to "FactInternetSales" and "FactResellerSales" by dragging CurrencyKey to them.

24. Go to Dimension Usage. Click the intersection of Reporting Currency and Fact Currency Rate, set a regular relationship between them as follows.

25. Click the intersection of Reporting Currency and Internet Sales, set a many-to-many relationship between them as follows, then do the same thing to Reseller Sales. Save the project.

26. Deploy the project. We may run into the following issue. It tells us that there is something wrong with the named query, Reporting Currency.
-----------------------------------------------------------------------------------------
Error 1 Dimension 'Reporting Currency' > Attribute 'Currency' : The 'Integer' data type is not allowed for the 'NameColumn' property; 'WChar' should be used. 0 0
-----------------------------------------------------------------------------------------

27. Double click "Reporting Currency.dim" in Solution Explorer, and check the properties of the attribute "Currency" of Reporting Currency. We can find BI Wizard incorrectly set the data type of NameColumn to Integer. We need to reset it to WChar. Then deploy it again.

27. The deployment run into another issue which is saying "Conversion failed when converting the varchar value 'Afghani' to data type int " as follows.

28. In order to solve it, go to DSV, right click Reporting Currency to edit the named query

29. We can notice that the query tries to union 2147483647 which is an integer and CurrencyName which is varchar together. This might be a reason to explain the error.
So how about we update the second part of the union as follows:
---------------------------------------------------------------------
SELECT DISTINCT NULL AS [Local 0], Null AS Local, NULL AS [Local 2]
FROM DimCurrency
---------------------------------------------------------------------

When we deploy it again, it runs into another issue. It seems there should be a member called [Local] in Reporting Currency.dim

So let's edit the name query again and this time we will update the second part of the union as follows and deploy the cube.:
---------------------------------------------------------------------
SELECT DISTINCT NULL AS [Local 0], 'Local' AS Local, NULL AS [Local 2]
FROM DimCurrency
---------------------------------------------------------------------
Deployment Completed Successfully!!!

30. Drag Currency from Reporting Currency, Average Rate from Fact Currency Rate and Reseller Sales Amount and Internet Sales Amount in Cube Browser, we can get the final result!

31. Properties of the attribute of Reporting Currency/Currency (To be continued)
Because the attributes, "Currency" and "Currency ISO Code" are not groupable attributes, we should set the property "AttributeHierarchyEnabled" to False, but we need to set "IsAggregatable" to True. I will also talk about this later