Currency exchange rates in MicroStrategy 1

[jethrothul]

Hello,

I wonder if anybody could help me with the following problem ? I have data from 25 countries each in their own currency. In MicroStrategy I would like to have reports showing the data in their own currency but also in the standard Currency 'EURO'. Is it necessary to create 2 set of metric ie. one in local currency and one in 'EURO' or is there a way of having a standard metric which will be diplayed according to a chosen 'attribute' currency ie. 'LOCAL' or 'EURO'.

Any help greatly appreciated !

 

[nlim]

big can of worms. You really have to settle a few things first...

1) which currency exchange rate do you want to use. (today or the rate on the day/month/quarter of the transaction).
2) how would you maintain this exchange rate moving forward.

Let's simplify it to a rate today...

You can use the 2 sets of metrics approach...actually it would be 26 sets of metrics. One for each currency.

You can also try the attribute approach. In this case you would have an attribute with no relationships and would generate a cross join. Now in theory, let's say you create the lookup table as follows

ID DESC Rate to convert from fixed currency
-- ---- -----------------------------------
1 french 2.1

you can also create a "EXCHANGE" form for the currency attribute.

You need to have a base Sales metric in some fixed currency for this to work. Now you can define a "local currency sales metric"

SalesMetric x max(currency@exchange)

So say your base sales metric is in USD, then 20000USD becomes 42000French francs.

Here are some interesting benefits to this approach
1) you can apply a security filter for all country users so they can only see specific currencies
2) you can add other conversion factors and use a different form name.
3) You can use the attribute in filters and prompts so that only certain currencies show up.

Here are the downsides
1) you must build a way to maintain the exchange rates
2) the currency attribute cannot be used for any other report; which is ok I guess.
3) a cross join will occur to dynamically create the converted figures. This I guess is also inevitable...

There are many other issues to consider, mostly related to how reporting and analysis is done....but it's a start.

good luck.

 

[jethrothul]

Hello nlim,

many thanks for your detailed reply. I think my original post was not very clear in what I'm trying to achieve. The 25 countries provide monthly data for stores and product groups. The countries provide the data in their local currencies ie some are Euro countries most have other currencies. Every month we also get an ASCII file with the exchange rates for the non Euro countries currencies to the Euro. What I was concidering was to create a table in the RDBMS database with the following layout :
COUNTRY_ID
STORE_ID
PRODUCT_ID
MONTH_ID
SALES
PROFIT
...
...
EXCHANGE_RATE_TO_EURO

What I was hoping was that it is possible to have an attribute EXCHANGE_RATE with members 'LOCAL' and 'EURO' and a Metric which would perform SALES*1 for 'LOCAL' and SALES*EXCHANGE_RATE_TO_EURO for 'EURO'. Sorry if this is a silly idea but my 'background' is Oracle Express where this is somewhat easier.

Again thanks for your help !

 

[nlim]

you can put it in the db the way you outlined, then you'll need to calculate "exchange x sales" as your metric. The benefit is that you can roll up the numbers easily, filter them, and do time over time comparisons.

The main issue you might have is that this approach takes space and is actually slower because you have a scalar calculation for each row.

Here's another approach. Create this lookup table
CountryID MonthID exchangeRate
--------- ------- ------------

then add it to your schema with exchangerate as fact.

Now you can create a metric M1 defined as max(exchangerate) {country, month}. That way this metric is always calculated at the month and country level.

Create a local sales metric M2 as sum(sales). Then create a Euro sales metric M3 defined as M2xM1.

The upside to this approach is less storage, and you can see how the exchange rates fluctuate over time.

The downside is that you have to be careful when you define Euro metrics at different levels because drill up requires that you calculate sales at the month level before adding it up. (nested aggregation)

If your data volumes are small, I'd transform the sales into Euro numbers in the ETL process.

 

[Z3]

Back to your original question, either way is fine. Two metric will work for you for sure. You can also use a prompt to prompt on which one metric or both go in the display. I would not mess with Attribute because the idea of an "attribute" in MicroStrategy is being used in "group-by".