Asked by:
Currency Conversion (Many To Many)

Question
-
Hi...I need to implement currency converison in the cube and was wondering if you can provide your thoughts on this.
I have a fact table which contains the same measure in multiple local currencies...now rewuirement is to have a ability to view the data based on the currency selected (USD, EURO, GBP)...
Please correct me if I am wrong but according to me this is the case of "Many to Many" relationship?
I have a Currency Dim table (CurrencyCode and CurrencyName) and one Currency Fact Table which contains (DateKey, CurrencyKey, AvgRate)...
I would like to know how I can achieve the many to many type of conversion ...couple I thing came in my mind which are listed below
1. by defining the many to many relationship in dimension usage tab under the cube?
2. Use the Business Intelligence wizard ?
I am not sure if this can be done by both or I am missing anything here? Also please let me know what is the better way to achieve this and please share if any arcticle available which can walk through the steps...
Any help would be greatly appreciated...
Thanks, Gaurav
All replies
-
Hi,
As your facts are stored in multiple currencies,it is the case of Many-To-Many currency conversions.Go through the following BOL article link;
http://msdn.microsoft.com/en-us/library/ms175660.aspx
Even check following link which contains all implementation details;
http://www.bp-msbi.com/2010/10/a-guide-to-currency-conversions-in-ssas/
Aniruddha -
-
-
-
You may also refer to this walkthrough, http://blogs.msdn.com/b/sqlforum/archive/2011/01/04/walkthrough-implement-many-to-many-currency-conversion-in-an-analysis-services-solution.aspx
Please remember to mark the replies as answers if they help and unmark them if they provide no help -
-
Gaurav,
I cannot say that it is perfect now, however this is currently the best Microsoft BI tool for cube design. What makes you hesitate to use it? and what do you expect to do?
Please remember to mark the replies as answers if they help and unmark them if they provide no help -
Hi Charles...I was little bit confused because the article which you did mention last time was implementing the many to many converson without using the Business Intelligence wizard and though there might be some issues if I do use the wizard.
I do not see any issue in using the wizard for this, the only one disadvantgae I see is wizard creates a additional dimention (Reporting Currency) in the cube..
Thanks, Gaurav- Edited by Gaurav 17 Wednesday, February 9, 2011 9:05 AM test