Today is T-SQL Tuesday #33 hosted by Mike Fal.  The topic is SQL Server – Trick Shots.  This assignment involves blogging about a cool process or trick you implemented that is related to SQL Server and the lessons or insight gained from the experience.   As a BI developer, I thought I’d write about a topic related to SSAS.  The first topic that came to mind is currency conversion in SSAS using measure expressions.

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

I recently implemented a currency conversion pattern in SSAS using measure expressions.  I had not worked with measure expressions previously and found them to be quite useful.  The following provides an overview of the currency conversion implementation.

First, the following dimensions and measures must exist in the cube:

Source Currency Dimension

• Attributes: Currency Code Key, Currency Code (e.g. U.S. Dollars)

Target Currency Dimension

• Attributes: Currency Code Key, Currency Code (e.g. U.S. Dollars)

Date/Time Dimension

• Attributes: Date Key, Date, Fiscal Week, Fiscal Month, etc.

Exchange Rate Type Dimension  (optional)

• Attributes: Exchange Rate Type Key, Exchange Rate Type

Exchange Rate Measure Group

• Measures: Exchange Rate

Transactional Measure Group(s) (e.g. Sales data, Purchase Order data, etc.)

• Stores the measures to convert to the target currency as well as keys that relate to the conversion rate dimensions (and other dimensions that have a relationship to the measure group).
• Dimension relationships: Date/Time dimension (Date Key), Source Currency dimension (Currency Code Key), Exchange Rate Type dimension (Exchange Rate Type Key).

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 Date/Time dimension, Source Currency dimension, and Exchange Rate Type dimension.  The Exchange Rate Type dimension is only necessary if  several different types of exchange rates are used.

The relationships between the measure groups and dimensions in the cube are 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 Exchange Rate measure group.

Once the relationships between the measure groups and dimensions are established, the measure expressions may be implemented.

First, a measure must exist or be created in the appropriate transactional measure group (e.g. sales) in the cube.  The measure should be defaulted to the source column to convert. 

In the measure properties ‘Measure Expression’ field, the currency conversion calculation is defined.

[Measure to Convert] * [Exchange Rate]

This experience taught me several important details regarding measure expressions in SSAS:

• Measure expressions must be in the form M1 op M2.  Where M1 is a measure from the same measure group as the measure you’re currently editing, M2 is a measure from a different measure group which shares at least one dimension with the current measure group, and op is either the * or / operator. (See Chris Webb’s blog postfor more details).
• Measure expressions are calculated at the lowest common level of granularity between the measure groups before aggregation takes place.
• Measure expressions are calculated before the MDX script is evaluated.  If the MDX script references a measure that has a measure expression defined, then the measure expression will be calculated first before any logic in the MDX script.

T-SQL Tuesday #33: Trick Shots