Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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

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.

image

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

image

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

SNAGHTML2fd1718

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

Comments

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...