Many - Many Currency Conversion

I ran the "Currency conversion" BI wizard to implement Many to Many currency conversion but get the following error when I click on Finish in the wizard. Looks like the wizard is trying to generate some thing wrong. Any clue ?

The 'DimensionAttribute' with 'Name' = 'Reporting Currency' doesn't exist in the collection

Cheers,

Arun

Got this to working by creating a "Role Playing dimension" for currency that is used by the FX rates measure groups. Now, I have a bigger problem.

1. er the wizard generates the dimensions, calculation etc., we need to define the relationship b/w the generated "Reporting Currency" dimension and the measure groups. (Else the new dimension will not be displayed in the client). But, logically there is no relationship. How do we overcome this ?

measure group1 : Balance

Dimension : Calendar, Currency, ......

measure group2 : Rates (The fact table contains rates for all currencies from EUR for all days. cols - Date, CurrencyCode, Rate)

Dimension : Calendar, ToCurrency, ....

Newly generated dimension : Reporting Currency

2. Also a member called "Local" is used in calcualtion. Can some one explain the need for this ?

Any help is appreciated.

|||

Could you clarify your goal? Are you saying you want to see the "To Currency" (cube) dimension against the Balance measure group? If so, could you confirm a (many to many) relationship exists between the "To Currency" dimension and the Balance measure group after the wizard is finished?

Thanks,

Bryan

|||

Well, my goal is to get the many to many currency conversion working.

One of the problems is that once the wizard is finished, I can see a new "Reporting Currency" dimension created and no relationship exists in Dimension Usage and hence I'm not able to see this dimension through Excel or from any other client.

Thanks,

Arun

|||

I am familiar with currency conversion in theory, but I have not used the wizards. I suspect all you need to do is set a many-to-many relationship between the "To Currency" (cube) dimension and the Balance measure group.

To do this, open the cube editor and select the dimension usage tab. Click on the grey-box at the intersection of the Balance measure group and the "To Currency" dimension. This should give you a button in the right-hand side of this box. Click that button to get the Define Relationship dialog.

In the Define Relationship dialog, set relationship type to many-to-many and set the Rates measure group as the intermediate measure group. If the Rates measure group is not available in the drop down, SSAS can't identify a set of shared dimensions between these two measure groups. This indicates a deeper problem and I suspect you might need to revisit how you've used the Currency Conversion wizard.

BTW, the SSAS 2005 Step-by-Step book covers currency conversion with reasonable detail. That could be a good resource for getting started.

Bryan

|||

I have a ToCurreny dimension (I created this myself. Has relationship only to Rates) + another "Reporting Currency" dimension generated by the wizard.

In all the examples given in the book, they have the above 2 dimensions as the same but in real-world scenario it cant be the same. ToCurrency - Will contain all currencies for which rates are available. They will have to include rates for all transaction currencies in the fact table. (in my case 100 currencies)

Reporting Currency - should include only the set of currencies for which we need the conversion (in my case only some 6 currencies)

But the problem is that, there is no logical relationship b/w Reporting Currency and Rates or Balance measure group as that dimension will only be used for on-the-fly calculation. So, I'm in a fix now to set the dimension usage for this dimension whithout which , this cannot be viewed from the client.

thanks,

Arun

|||

Problem solved.

Solution:

I had created 2 diff measures (so 2 diff measure groups)

1) Rates_ToEUR ==> Will contain rates for all currencies to EUR for all days

2) Rates_FromEUR ==> Will contain rates from EUR to only the reporting currencies

Dimension Usage :

Measure group - Rates_ToEUR -- Currency (same one as used by balance measure group) and Calendar

Measure group - Rates_FromEUR -- Calendar and ReportingCurrency. Also, Balance measure group has many to many relation ship with ReportingCurrency dim through this measure group.

Also, I have simplified the calculation to below :

Scope ( { Measures.[DLY Value Reporting CCY], Measures.[MTD Value Reporting CCY], Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

Scope( Leaves([Calendar]) ,Leaves([Currency]), [Reporting Currency].[Currency].Members);

This = Measures.CurrentMember * Measures.[Rate_ToEUR] * (Measures.[Rate_FromEUR], [Reporting Currency].[Currency].CurrentMember) ;

end scope;

End Scope; // Measures

The above solution works perfectly till now. Any one can see any issues?

So, I would suggest implementing currency conversion by yourself without using the wizard (BI) which does not help much.

Cheers,

Arun

sql