Measure expressions and currency conversion

OK - here is my scenario... I have a fact table containing one measure: Sales. The values of the measure are stored in the same currency (DKK) for all records. In another fact table I have exchange rates for four different currencies per day for a 15 year period (approx. 22000 records). I have built a cube around these fact tables containing two measure groups.

Measure Group 1
Measure: Sales
Dimensions: Time, Product, Company

Measure Group 2
Measure: Exchange rate
Dimensions: Time, Currency

The only shared dimension between the two measure group is thus Time... The currency dimension contains one attribute hierarchy (CurrencyCode) with IsAggregatable set to False and DefaultMember set to DKK.

For the measure Sales, I have created a measure expression like this:

[Measures].[Sales] / [Measures].[Currency]

I thought this should work - i.e. give me the opportunity to select any of the four available currencies and have the value of Sales displayed accordingly. However - it does not. Instead my Sales measures is multiplied by 4, which corresponds to the number of currencies for which I have exchange rates in my exchange rate fact table. Why is this? Am I not modelling the scenario correctly?

I hope someone can help, since this really puzzles me!

Hi Michael,

I think I might know what's going on here: you need to give your Currency dimension a many-to-many relationship with Measure Group 1 using Measure Group 2 as the intermediate measure group. Does this work?

Regards,

Chris|||Hi Chris,

Sound very reasonable... I will give it a try. Do you think this approach will perform better than an MDX script like this (inspired by one of your own blog entries: http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry)

([Measures].[Sales],LEAVES([Time]),LEAVES([Currency])) = [Measures].[Sales] / [Measures].[Exchange Rate]

?

Chris, thanks for your input. It would be a shame to say that Microsoft has spent too much time documenting how to use measure expressions. |||Well, I think it might perform a little bit better, but to be honest I'm not sure. You'll have to try both!|||Michael,

Since you've marked my first reply as solving your problem, I don't suppose you can tell us what the performance on your cube is like now? Do your currency conversion queries run fast enough with measure expressions? I'd really be interested to know.

Chris|||Hi Chris

Unfortunately, I have not yet had the time to implement the solution with measure expressions yet (currently, we are using the MDX Script approach for solving the problem). I will, however, test it with measure expressions as well and report back my findings. I am sure that the solution you pointed out is correct wrt. using measure expressions (also after having reviewed the implementation in the AdventureWorks cube), which is why I marked your answer as correct.

I really appreciate your input!|||

Mosha's latest blog entry is also highly relevant to this problem:
http://sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx

|||OMG... Really a very nice article. It guess it also means that if we have to divide two measures with each other in a measure expression like this: A / B, and B has the higher granularity, then it would be a good idea to invert the B measure (1/B) and instead make a measure expression like this: B * A...

Lesson learned... I guess.

Still has not tested the measure expression vs. the MDX Script approach, though...|||Hi,

I finally got around to test this... My results show that measure expressions performs marginally better compared to using MDX scripts. But the two approaches perform almost just as well (and they do perform remarkably good) - a statement Mosha has previously made as well. So which approach do I prefer? Well - since using measure expressions has the best peformance (not by much, though), I will be using these. Perhaps there are scenarios where you cannot model your UDM to fit this approach, and MDX scripts might be more appropriate then...

I hope that these findings are useful to others...|||Well... What do you know?! I just found a scenario myself in which using measure expressions for currency conversion is not possible. One of my measures used "LastNonEmpty" as the aggregation function and measure expressions do not support this... Will of course be using the MDX script approach for solving this...