Roughly a year ago, I implemented currency conversion logic in multidimensional cubes using measure expressions (as well as using the Leaves function in MDX). I documented the implementation in a few blog posts:
I have now also implemented currency conversion in a tabular cube using DAX. This blog post covers the implementation in DAX as well as some of the performance issues I encountered and how I resolved them.
Data Model Overview
The tables required for currency conversion in the tabular cube are the same as those in multidimensional.
- Source Currency Dimension Table
- Stores data regarding the currency we are converting from.
- Attributes: Currency Code Key, Currency Code (e.g. Euros)
- Target Currency Dimension Table (optional)
- I did not include this dimension in the tabular model since we only convert to one currency – US Dollars. If you need to convert to multiple target currencies, then this dimension is required.
- Exchange Rate Type Dimension Table (optional)
- This dimension stores data regarding different types of exchange rates and may be unnecessary depending on business requirements.
- Attributes: Exchange Rate Type Key, Exchange Rate Type
- Date Dimension Table
- Attributes: Date Key, Date, Fiscal Week, Fiscal Month, etc.
- Exchange Rate Fact Table
- Measure: Exchange Rate
- Other Transactional Fact Tables (e.g. Sales, Purchase Orders, 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 these tables (ignoring target currency in this tabular cube implementation).
The following is a screen shot of the tabular cube table relationships:
The tables relevant to the currency conversion are Sales, GL Date, Currency Conversion Rate, Currency Code (Source Currency), Currency Conversion Rate Type. The tables grayed out are hidden in end-user client tools.
Currency Conversion Implementation Using DAX
The following measure was added to the Sales table of the tabular model to perform the currency conversion:
Sales Amount USD:=SUMX(CROSSJOIN(‘Currency Code’,'Currency Conversion Rate Type’,'GL Date’),CALCULATE(VALUES(‘Currency Conversion Rate’[CurrencyRateOv]) * SUM(‘Sales’[SalesAmountLocal])))
I learned from Marco Russo’s PASS Summit presentation (Inside DAX Query Plans) that this formula involves a single scan of a temporary table (single SUMX). The SUMX over a CROSSJOIN is resolved as a single VertiPaq scan using CallbackDataID to compute the multiplication.
When I connected to the cube in Excel and sliced the measure by an attribute with a small number of distinct values, the performance was very good; but, when I selected an attribute with a larger number of distinct values (greater than 5000), the measure never returned a value.
I improved the performance by creating a Calculated Column in the Sales table to store the exchange rate for each transaction (row). It’s important to note that Calculated Column values are calculated during processing and not ‘on-the-fly’ like measures.
The DAX formula for the ‘ConversionRate’ calculated column is shown below:
=LOOKUPVALUE(‘Currency Conversion Rate’[CurrencyRateOv], ‘Currency Conversion Rate’[DateKey], Sales[GlDateKey], ‘Currency Conversion Rate’[FromCurrencyKey], Sales[CurrencyCodeKey], ‘Currency Conversion Rate’[CurrencyRateTypeKey], 2)
LOOKUPVALUE performs a lookup of the conversion rate for the transaction based on the GL Date (GlDateKey), Source Currency (CurrencyCodeKey), and the Currency Rate Type (CurrencyRateTypeKey).
The measure to perform the actual currency conversion using the ConversionRate calculated column is:
Sales Amount USD:=SUMX(‘Sales’,'Sales’[SalesAmountLocal] * ‘Sales’[ConversionRate])
When I connected to the cube in Excel and sliced the measure by an attribute with 7000+ distinct values, the performance was very good.
Although I was disappointed that I was unable to implement this calculation as a measure without the need for a calculated column; I am happy that this alternate implementation delivers excellent performance for the end-users.
