Remove From My Forums

Currency Conversion (Many To Many)

Question
0

Sign in to vote

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

Friday, February 4, 2011 9:49 AM

Reply

|

Quote

All replies

0

Sign in to vote

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://consultingblogs.emc.com/christianwade/archive/2006/08/24/Currency-Conversion-in-Analysis-Services-2005.aspx

http://www.bp-msbi.com/2010/10/a-guide-to-currency-conversions-in-ssas/

Aniruddha

Friday, February 4, 2011 12:00 PM

Reply

|

Quote
0

Sign in to vote

How about first converting the fact data in one base currency (using view etc...) and then using the one to many type of currency conversion?
Thanks, Gaurav

Friday, February 4, 2011 1:04 PM

Reply

|

Quote
0

Sign in to vote

If it's possible for you to convert the fact data in single base currency then that's good approach to implement one-to-many currency conversion.
Aniruddha

Friday, February 4, 2011 1:11 PM

Reply

|

Quote
0

Sign in to vote

Any other comments/suggestion on this?
Thanks, Gaurav

Friday, February 4, 2011 5:25 PM

Reply

|

Quote
0

Sign in to vote

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

Monday, February 7, 2011 8:32 AM

Reply

|

Quote

Moderator
0

Sign in to vote

Hi Charles...is there any disadvantage if I do use the Business Intelligence wizard for many to many type currency conversion?
Thanks, Gaurav

Monday, February 7, 2011 8:56 AM

Reply

|

Quote
0

Sign in to vote

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

Wednesday, February 9, 2011 8:52 AM

Reply

|

Quote

Moderator
0

Sign in to vote
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.

http://blogs.msdn.com/b/sqlforum/archive/2011/01/04/walkthrough-implement-many-to-many-currency-conversion-in-an-analysis-services-solution.aspx

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
Wednesday, February 9, 2011 9:03 AM

Reply

|

Quote