Multicurrency best practice

[rcoppa]

Hi there,

I am currently looking at re-implementing my master cube to handle multi-currency. At this stage it will only be 2 currencies but will likely expand in the near future.

Is there a best practice method for doing this?

Thanks in advance.

[nhavis]

Are there any issues with having the other currencies in your measure dimension?

[lotsaram]

Although it is possible to mix currency into measure (except in a GL cube context where the "measure" dimension is the account) this is definitely not best practice. Breaking currency out to a separate dimension is recommended. Typically you would also have a xRate cube with 2 currency dimensions and currency version or currency measure (average rate, spot rate, closing rate, budget rate, ...etc.) and calculate the different currency values (either via rule or TI input) by reference to the xRate cube x the local currency.

[rcoppa]

I think that would get awfully messy, as I would need to duplicate every account I have in the measures dimension (It is a P&L and BS cube).

I was thinking of having two additional dimensions: 'currency', and another, say 'curradj' for the purpose of this example.

currency would comprise: aud, usd, etc
curradj: Local
-adj
-base

In this way the base amount could be entered in any currency and the adjustment could be calculated for whatever currency was being viewed. I was interested in the best way to design the currency lookup cube, and haven't really developed my idea much further.

edit: posted before the post above

[mattgoff]

I went through this design process a while back. I ended up with my GL cube (combined P&L and B/S) structured as follows:

• Version (Budget, Forecast, Snapshots)
• Cost_Center (Oracle department accounting code)
• Account (Oracle account code)
• Period (month)
• Year (year)
• GL_m (measures)

GL_m is the following:

• multiple ISO 3-letter currency codes (e.g. USD, GBP, JPY)
• $ (translated and consolidated from all 3-letter currencies elements using forecast f/x rates via rule)
• $bud (translated and consolidated from all 3-letter currencies elements using budget f/x rates via rule)

All f/x rates used in translation are stored in a Currency cube with the following structure:

• Version
• Currency
• Period
• Year
• Currency_m (AVG and EOP)

The idea is that the 3-letter elements contain the natural (as booked) currency balances. $ contains the translated sum at forecast rate and $bud at budget rate. USD is different than $ and is the one concept that users sometimes are slow to understand ($ != USD).

This schema lacks the full flexibility to look at any period at any rate, but this is very rarely required. If necessary, it can be built in Excel relatively easily by pulling local currency balance and rate separately. Ultimately, I think it is a good balance of capability and simplicity (both for users as well as performance).

Matt

[Martin Ryan]

Matt gives a good response, but I'd highlight his last point about constant currencies and suggest you find out from your users if this is every going to be an issue. My users require things to be restated using rates from a given period. E.g they want USD at the rates applicable to each month, but they also want (for example) USD using the Apr 2009 exchange rate for all periods. This is especially useful for restating forecasts to eliminate FX differences.

To do this you'll need two dims for the FX calc:
- Currency (elements such as local currency, USD, EUR, AUD, etc etc)
- Rate (periodic, Jan 2009, Feb 2009 etc)

Also, in your rates cube (as Matt points out) you'll need to have at least two: monthly average for P&L lines and EOM Spot for BS items.

Cheers,
Martin

[paulsimon]

Hi

Another approach we have been using is to have an FX Version. Our users often have the requirement to be able to show eg Actuals at Budget rates etc.

In the VFX dimension we have VFX Input which is used to record the initial values. We then have the other versions. In some cases these are the same as those in our Version dimension, eg Actual and VFX_Actual. In other cases they may be completely different, eg a special rate used for the current Budget.

If a user selects VFX_Input against a version of Actual then it will use the rates from VFX_Actual. If the selected version is Budget, then it will use the rates from VFX_Budget.

Of course they can select Actual with VFX_Budget to get the Actual's converted using the Budget rates, eg to compare Actuals with Budget removing any effect of currency variation.

The FX rates cube has the VFX dimension rather than the Version dimension. It has Currency From and Currency To dims. It has periods and years, and Rate Type (Month Avg or Month End), and the measures hold the dividing rate, and calculate the multiplying rate. The use of Currency From and Currency To allows for different rates depending on whether you are converting from eg GBP to USD or USD to GBP etc.

This works reasonably well. I limit the feeders to just those versions that are required in each year to avoid too much data explosion.

Whether it uses Monthly Avg rates or Month End rates is determined by whether the Nominal is a P&L or Balance Sheet.

Most finance systems that I have come across require monthly rates at least for accuracy. Most also want to calculate the FX rate difference between the Bal Sheet As At End of Year rate and the P&L monthly rates, though in some cases this is handled in the GL.

In the main cube we only have to bother about two functional currencies so we just have a GBP Input and EURO Input and a result of GBP and EURO. However, if we wanted to handle more then I would go for a local currency with a string measure to hold the currency code, and then you convert out to the other currencies from this. This probably offers the most flexibility.

However, the right approach depends on what you want. If you have multiple input currencies but only want to show data in one reporting currency, then have a measure for each input currency can work.

Although less common you may have one input currency but multiple reporting currencies. In which case the you have the reverse. If you only need to work with one year at a time, or you have a single time dimension, then you can do currency conversion as a consolidation. This can be very efficient, as no feeders are needed, and there is no explosion in the data size.

I have worked with currency conversion in other systems such as Essbase and SQL Server Analysis Services and what TM1 can do is often a lot more comprehensive than other systems. In tried using the "built in" currency conversion facility in SSAS but soon gave in and had to bring in a consultant to develop the final conversion method which was still a lot less comprehensive than the above.

Regards


Paul Simon

[rcoppa]

Thanks for the input.

I created a test cube and a sample rule, and the performance is so bad it is unusable. Am I missing something?

The cube has 9 dimensions:
- Local_base
- Currency
- Version
- Year
- Month
- Entity
- Location
- Product
- Account (PL+BS)

The Local_base dimension is:
- Local
-- Local_Adjustment
-- Local_Base

In the exchange rate cube i have:
- Year
- Version
- Month
- FromCurr
- ToCurr

For the purposes of this example I am just using AUD and USD.

The rule is:

['Local_Adjustment'] = ['Local_Base','AUD'] * DB('ExRate',!Year,!Version,!Month,'AUD',!Currency) + ['Local_Base','USD'] * DB('ExRate',!Year,!Version,!Month,'USD',!Currency) ;

FEEDERS;
['Local_Base'] => ['Local_Adjustment'];

The exchange rate for AUD --> AUD is set 0 and the same for USD --> USD. Therefore for ['Local', 'Currency'] the [Local_Base for that currency will be consolidated with the Local_adjustment for all other currencies.

It works at a base level, but as soon I select consolidated elements for a few dimensions it grinds to a halt.

Any advice much appreciated.

Cheers.

[David Usherwood]

May be a very dumb question but....
You _do_ have skipcheck on?
I would also replace your + by a chart (always more efficient) but that's secondary (though the feeder may be slightly wrong if you don't).

[paulsimon]

Hi

As well as the 'skipcheck;' the other thing that I think you are missing is the N:

['Local_Adjustment'] = ['Local_Base','AUD'] * DB('ExRate',!Year,!Version,!Month,'AUD',!Currency) + ['Local_Base','USD'] * DB('ExRate',!Year,!Version,!Month,'USD',!Currency) ;

should be

['Local_Adjustment'] = N: ['Local_Base','AUD'] * DB('ExRate',!Year,!Version,!Month,'AUD',!Currency) + ['Local_Base','USD'] * DB('ExRate',!Year,!Version,!Month,'USD',!Currency) ;

Otherwise it will go looking for rates at consolidated levels of Month, when you probably only hold your rates at month level, not to mention doing the currency conversion for other dimensions.

Regards

Paul Simon

[paulsimon]

Hi

I am not too sure that I understand what you are trying to achieve here, however, your feeders may not work

['Local_Adjustment'] = ['Local_Base','AUD'] * DB('ExRate',!Year,!Version,!Month,'AUD',!Currency) + ['Local_Base','USD'] * DB('ExRate',!Year,!Version,!Month,'USD',!Currency) ;

FEEDERS;
['Local_Base'] => ['Local_Adjustment'];

With this feeder you will only feed from eg AUD to AUD or USD to USD. To identify the result, you need to feed all currencies

Code: Select all

['Local_Base'] => ['Local_Adjustment','All Currencies'];

This will automatically feed eg AUD and USD if there is a value in USD or AUD.

From the look of it, you want to get the values expressed in the local currency, presumably of each entity, converting values in any other currency to that currency? Is that correct? More usually companies want to convert values to a common currency and then possibly out to one or two reporting currencies. If you can explain a little more about what you want the rules to do, I might be able to offer some more suggestions.

Regards


Paul Simon

[rcoppa]

Thanks again for all the feedback.

I am new TM1, but have used other OLAP tools in the past. I was indeed missing the skipcheck, but now understand and am using it.

I was also missing the N: and my feeders were incorrect, although without the N: the feeders were working?

I think my understanding of feeders was slightly off. I thought that the feeder applied to the view requested by the user, so that in my example when the user requested to view local_adjustment for USD, the rule would be run. However it looks like the feeder applies to the location of the source data, not the data being requested by the user, which makes more sense I guess. I assume TM1 has already parsed the rules and worked out that that rule would affect the view being requested?

So, this now works for my purpose

I don't understand the benefits of converting to a standard currency first Paul? But that was why I created the thread initially and I'm definitely interested to hear. I developed my solution to allow data to be imported at the local currency, and then viewed in either the regional currency or international currency as required. It also easily allows for comparisons between a local location and another location at the local currency (more of a nice to have than a requirement).

Cheers.

[Martin Ryan]

You might find this article on feeders informative: http://wiki.olapforums.com/index.php?title=TM1_Feeders

The benefits of a standard currency are pretty much exactly what you said - the ability to compare two different countries and also the ability to sum all the countries up together and get a meaningful figure. The actual currency used is arbitrary, but generally USD is used because most people can relate to it.

Cheers,
Martin