Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!