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
Target Currency Dimension
Exchange Rate Type Dimension (optional)
Exchange Rate Measure Group
Transactional Measure Group(s) (e.g. Sales data, Purchase Order data, etc.)
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:
T-SQL Tuesday #33: Trick Shots