SSAS: Currency Conversion in MDX Script

Jennifer-Salvo, 2012-02-09

In my prior blog post, I provided some details regarding currency conversion via measure expressions in SSAS.    I recently attempted to implement currency conversion for some inventory measures and discovered a limitation with measure expressions – they do not work for measures with a ‘Last non-empty value’ aggregation.  As an alternative to measure expressions, the currency conversion may be implemented in the cube’s MDX script. 

The basic expression for currency conversion is:

[Measure in Target Currency] = [Measure in Source Currency] * [Currency Conversion Rate]

In this example, we have a measure that represents a value converted from the source currency to a target currency.  The initial value of this measure is set to the source currency (specified by the Source column). 

SNAGHTML10b77c

Now, we need to overwrite the measure’s value in the MDX script so it represents the measure in the target currency (in this example, the measure we’re overwriting is called ‘Quantity Available Cost Converted’. The measure ‘Quantity Available Cost Local’ is in the original/source currency).  The target currency is selected by the end-user.

SCOPE([Measures].[Quantity Available Cost Converted]
,LEAVES([Source Currency])
,LEAVES([Destination Currency])
,LEAVES([Currency Rate Type])
,LEAVES([Dates]));
THIS = [Measures].[Quantity Available Cost Local] * [Measures].[Currency Rate Ov];
END SCOPE;

The SCOPE function ensures that we are only affecting the portion of the cube specified in the function.  In this case, the function overwrites the measure Quantity Available Cost Converted where it intersects the leaves of the Source Currency, Destination (target) Currency, Currency Rate Type and Dates dimensions.  The LEAVES function is important since the measure uses the ‘Last Non-Empty Value’ aggregation and is therefore semi-additive.  We only want to perform the calculation at the leaf level and not use the aggregations.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads