T-SQL Tuesday: Trick Shots (Currency Conversion in SSAS)

Jennifer-Salvo, 2012-08-14

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





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


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


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


360 reads