Multicurrency

[paoloderegibus]

Being able to display data in different currencies has become a must have feature for business intelligence and analytics apps.

So far I was not able to find a solution that fits all purposes, but I have come up with 2 approaches that can be used alternatively depending on context and user needs. Attached you can find a very basic example for both approaches.

So far I was not able to find a solution that fits all purposes, but I have come up with 2 valid approaches that can be used alternatively, depending on context and user needs. Attached you can find a very basic example for both approaches.

In both cases you will find 2 concepts: Local Currency and International Currency. The first is what the source systems provide and it tells you what currency was used for the transaction that has been recorded. The International Currency, on the other hand, is a reference currency created by QV (or by an OLAP system in general) to reconcile different transactions.

You can choose to have more than one International Currency in your OLAP system and the resources to calculate and store each of them will scale linearly as the effort needed for implementation and maintenance.

Approach 1 - Runtime conversion: This approach consists in converting everything to the international currency in the ETL and convert again everything to the currency selected by the user at runtime. This approach is very flexible because it will allow to provide analysis in as many currencies as the user needs and it can be easily adapted to work with historical exchange rates. At the same time it could have some scalability and performance issues for very large applications. To go more in detail we will consider an example in which we have a fact table (Sales) which contains the price and the quantity of each product sold. All prices have already been converted to Euro (our International Currency for this example) during the ETL phases.

The fact table is then linked to a dimension table which contains all the exchange rates for the needed currencies. The 2 tables are linked using the International Currency Code and prices can be multiplied by the right exchange rate when needed. For this application to work it is needed to have one currency always selected.

2 variables are calculated in order to display money measures in the right format:

LET vCurrencySymbol = '=Only(CurrencySymbol)'; This variable can be used in charts to show the right currency symbol next to the axis.
SET vMoneyFormatting = "=vCurrencySymbol&' #.##0'"; This variable can be used in all the num() functions to convert the expression to the right format

When calculating an expression that should be converted to the selected currency the amount to be displayed must be multiplied by the right exchange rate.

Below an example of an expression to calculate the total revenue:

num(sum((Price*Sales)*ExchangeRate), vMoneyFormatting)

Below an example of an app which is using this approach:

Selected currency: EUR

Selected currency: GBP

The always one selected value must be checked in the currency ListBox for this approach to work.

Approach 2 - Batch conversion: This approach is less flexible than the one described previously, but it scales better with big applications. In this case currencies won’t be stored in rows but in columns and all currency conversions will be calculated during the batch/reload phase. This means that the application will be faster, but it will be much more difficult to allow users to view data in more than one currency. In the attached example in fact we have assumed that for the end user would be enough to see numbers in Local Currency or in International Currency (in this case Euros). Aggregates of countries which do not have the same currency would be possible only when using the International Currency (you can’t sum Euros and Dollars). At the same time we want our user to be able to switch between Local and International currencies when the Local currency can be used (the user is looking at an aggregation of countries with the same currency).

To use this approach the fact table is enough: there is no need of an additional currency dimension.

4 columns are needed to use this approach:

1. PriceLocal: contains the prices in Local currency
2. CurrencySymbolLocal: contains the symbol of the Local currency
3. PriceInternationalà contains the prices in International currency
4. CurrencySymbolInternational: contains the symbol of the International currency (it will be the same for each row)

5 variables are created in the script of the application:

LET vSelectedCurrency = 'International';
LET vUsedCurrency = replace('=if(vSelectedCurrency=|Local| and   count(distinct CurrencySymbolLocal)=1,|Local|, |International|)', '|',chr(39));
SET vPrice = "=if(vUsedCurrency='Local', 'PriceLocal', 'PriceInternational')";
LET vCurrencySymbol = replace('=if(vUsedCurrency=|Local|, Only(CurrencySymbolLocal), Only(CurrencySymbolInternational))', '|',chr(39));
SET vMoneyFormatting = "=vCurrencySymbol&' #.##0'";

• vSelectedCurrency à is the currency selected by the user and by default is set to International. This variable will be controlled by the 2 buttons (see the examples for details).
• vUsedCurrency à is going to check whether the Local currency can be used (we only have one local currency available in the current selections) and the currency Type the user has selected. Depending on these 2 conditions this variable tells what currency to be used in the fronted.
• vPrice à depending on the value of vUsedCurrency is used in expressions as a pointer to the right price column: PriceLocal if the user has selected Local currency and a single local currency is available in the selections, International currency otherwise.
• vCurrencySymbol and vMoneyFormatting have the same purpose they have in the first Runtime approach, but they are conditioned by vUsedCurrency.

Below an example of an expression that would calculate the total revenue: num(sum(($(vPrice)*Sales)), vMoneyFormatting)

Finally an example of data displayed in international currency:

and one with local currency selected:

I hope this could be useful.