Exchange rate calculation using a lookup cube

The preceding example is a good first step towards developing a rules-based solution to currency conversion, but the DB function for currency conversion does not reflect the fact that exchange rates vary by currency, not by market.

For example, both the Helsinki and Lisbon markets use the Euro currency, and the exchange rate for the Euro fluctuates independent of the market in which it is used.

The preceding solution requires the identical exchange rate for the Euro to be stored twice; once for Helsinki and once for Lisbon. Ideally, a currency conversion solution stores the exchange rate for a currency in a single location.

You can solve this problem of redundant data by using a cube that stores the relationship of market to currency. The sample data contains just such a cube, named Currency.

This cube is comprised of two dimensions: the Market dimension, and a dimension named MarketCurrency, which contains a single string element (also named MarketCurrency).

When used in conjunction with a lookup cube that stores exchanges rates by currency (CurrencyExchangeRate), you can develop a rule statement that calculates Price/Kg -USD for each market based on the daily exchange rate for a given currency. The next section illustrates the creation of such a statement.