## Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

### SSAS: Currency Conversion in MDX Script

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).

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.