Salvo(z)

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.

Currency conversions can be implemented several ways as part of a business intelligence solution.  In some scenarios it may be preferable to implement the conversion in the ETL, while in other scenarios it might make more sense to implement the conversion in the cube itself.  Implementing currency conversion in the ETL is only feasible when the source currency will be converted to one or two target/destination currencies.  If your business requirements dictate that users need the ability to select from a large set of target currencies, then you will likely need to implement currency conversion in the cube.

This blog post describes implementing currency conversion in an SSAS cube via Measure Expressions.  In this scenario, the data is collected and reported in multiple currencies.

Dimensional Modeling

The dimensions and facts are modeled as follows:

• Source Currency Dimension
  • Attributes:  CurrencyCodeKey, CurrencyCode (e.g. USD)
• Target / Destination Currency Dimension
  • Attributes:  CurrencyCodeKey, CurrencyCode (e.g. USD)
• Date/Time Dimension
  • Attributes: DateKey, Date, Fiscal Week, Fiscal Month, etc.
• Exchange Rate Measure Group (labeled FactConversionMRate in the diagram below) 
  • Attributes: SourceCurrencyKey, DestinationCurrencyKey, CurrencyRate
• Transactional Measure Group(s) (e.g. sales data, purchase order data, etc.)
  • Relationship with the Date/Time dimension (DateKey) and Source Currency Dimension (CurrencyCodeKey).

The diagram below shows the relationships between the fact and dimension tables.  I did not include the table with transactional data in the diagram.  The transactional data will generally have a relationship to the DimDate table, DimSourceCurrency, and DimCurrencyRateType tables.  The currency rate type is only necessary if  several different types of conversion rates are used. 

SSAS Currency Conversion

Once you have the data loaded into the physical fact and dimension tables, you can begin to modify the cube.  The actual conversion calculations will be performed via Measure Expressions in SSAS.

The first step is to add the fact and dimension tables to the data source view.

Next, you will need to create a measure group that represents the conversion rate.  In the example diagram above, the measure group is based off the FactConversionMRate table with two measures CurrencyRateOv and CurrencyRateDiv.  These measures are the rates used to convert from the ‘FromCurrency’ to the ‘ToCurrency’ and vice versa.

You will also need to include dimensions for each of the dimension tables shown in the diagram above.  This includes date, source currency, target / destination currency and currency rate type (optional).  The source currency and target currency have one attribute: currency code.  The currency rate type dimension also has one attribute: currency rate type key. 

The Source Currency Code and Destination Currency Code attributes have their KeyColumn set to CurrencyCodeKey and NameColumn set to CurrencyCode.  Similarly, the Currency Rate Type Key attribute has the KeyColumn set to CurrencyRateTypeKey and the NameColumn set to CurrencyRateType.

Both the source currency and currency rate type dimensions may be hidden (Visible set to False) since the values are already defined in the transactional data and should not be modified by the end-user.

The Destination Currency dimension should be visible so the end-user has the ability to select the desired target currency.

After creating the measure groups and dimensions in the cube, you will need to modify the relationships in the ‘Dimension Usage’ tab.  The relationships should be configured as follows (I am only displaying the measure groups and dimensions that relate to the currency conversion.):

The Many-to-Many relationship is based on the Conversion Rate measure group.

Once the relationships between the facts and dimensions are established, the measure expressions may be added.

First, create a new measure in the transactional measure group (e.g. sales).  Select the appropriate Source Table and Source Column.  The source column contains the value you want to convert to the target currency.

In the measure properties ‘Measure Expression’ field, enter in the appropriate calculation to perform the currency conversion.  Something similar to that shown below:

[Extended Price] * [Currency Rate]

You now have all the pieces in place to perform currency conversions in SSAS.

Summary

This blog post demonstrates one approach to implementing currency conversions.  Many alternative approaches exist in BI.  It is often possible to implement currency conversion directly in the ETL as opposed to SSAS.  This is especially true if you are converting between a small number of source and target currencies.  If your users need the ability to convert to a wide range of target currencies, it may be ideal to implement the currency conversion directly in SSAS using an approach similar to that described in this blog post.