TM1 Forum

Hello everybody,

i am to design a currency conversion in all of the cubes of my companies planning system. Currentliy i´m trying to figure out the best way to do it. Yes, I have already read the "Currency conversion - Best practices" thread My situation is a little different i think, so any advice is appreciated.

This is what i need to do:
- It has to be possible to input data on each currency
- every input has to be converted to all other currencys
- the sum for one "calculated currency" can consist of more than one input currency (e.g. [EUR calculated]=[EUR] + ([USD]*[rate USD-EUR]) + (([CNY]*[rate CNY-EUR]) + ... note: we currently have ~10 different currencys active in the company!
- we cannot use a "local currency"-element for input, cause every company inputs values on different(1-x) currencys

What i´ve thought of so far is to add a [<CURR> calculated]-element for every currency to the currency-dimension and then use a rule similar to the one above to calculate the values, but i´m pretty sure that this will "kill" the server...

Is there a more sophisticated approach that i´m just to blind to see at the moment?

Thanks for your ideas!

Regards
Markus


EDIT:

One more idea i came across in the meantime:

Build the currency-dimension like this:
Total Currencys
|- EUR
|- USD
|- CNY
|- RON

Total Currencys calculated
| - EUR calculated
|- EUR
|- USD - EUR
|- CNY - EUR
|- RON - EUR
|- USD calculated
|- EUR - USD
|- USD
|- CNY - USD
|- RON - USD
...

Using this structure, you could mark the elements to convert by an attribute, use two more attribute to hold "from currency" and "to currency" and thereby limit the number of rules you need to one. Plus the sums would be calculated by consolidations, not by rule.

What do you think about that?

Edit: my original idea wouldn't work in your situation.

However the following will I think.

1: have an element called input in your currency dimension.
2: suppose you are entering values. Have two measures; 1 of type N (value) and a String Measure where the currency is set.

So the user fills in both of these.

3: create a rule to perform the translation to all other currencies.

I think this will work well and with 9.5 you would be able to change the string measure cell into a dropdown so that the users can select the currency instead of having to enter it manually.

Jeroen

Hi

You might want to do a search on Multi-Currency for an earlier discussion on this. Your currency conversion scheme might need to take in to account the need for different rate sets, dividing and multuplying FX Rates, pivot currencies, etc.

As with any scheme there are pros and cons.

1) In one dimension have Input Currency and Output Currency, which is pretty much the first suggestion. The rule would then look like this (not full TM1 syntax for brevity)

GBP = N: GBP + USD / DB(FX_Rate,USD,GBP) + EUR / DB(FX Rate,EUR,GBP)

USD = N: USD + GBP / DB(FX_Rate,GBP,USD) + EUR / DB(FX Rate,EUR,USD)

feeder

All Input Currencies => All Output Currencies

The downside is that you need to amend the rules whenever a new currency is added. However, that is a problem for the developer. By comparison, this can make it easier for the end user.

2) Have two currency dimensions, a Currency_In and a Currency_Out. The Currency_Out will have an extra element called Input Currency, which is where you will record the input values against the Currency_In dimension, prior to conversion.

You can then have a catch all rule at the bottom of your rules list (assuming some STETs above) to calculate Currency_Out (or use {} notation and list all output currencies).

The rule says

[] =N: IF( !Currency_In @= !Currency_Out
,
DB(.... ,!Currency_In,'Input Currency',....)
,
DB(.... ,!Currency_In,'Input Currency',....)
*
DB(FX_Rate,!Currency_In,Currency_Out)
) ;

feeder every input currency needs to feed every output currency

['All Currency_In'] => ['All Currency_Out'] ;

In order to see the total of any Currency_Out, it is necessary to select All Currencies_In to add up all the individual Input currencies that have been converted, eg

GBP-USD
EUR-USD
USD-USD

So consolidated sum for All Currencies_In, with USD selected in the Currency_Out

The upside of this approach is that you don't need to change rules when a new currency is added.

The downside is that you probably don't need to see the full break down for eg USD of what part came from GBP, what came from EUR, etc. However, not only is that visible in the model, but it will need to be fed, which can dramatically increase memory requirements. If you have 10 currencies, then the currency output will be potentially 100 times the size. However, with the previous scheme you only need to feed as many elements as you have output currencies, so you only need to feed to 10 output currencies.

3) Store curency against each element

Eg Measure dim

Numeric Value and String Currency_In elements

The Currency Dim would then need an Input Currency or Local Currency element against which you would record the value when it was entered.

The rule for each Output currency is then

[] = N: [Local Currency,Value]
*
DB(FX_Rate,
DB(MainCube, ..... , ..... , Local Currency,Currency_In)
,
Currency_Out
)

However, this is not going to work as if we have eg USD as the output currency, this only allows for conversion from one input currency. Therefore we would need to repeat the rule for as many times as in scheme (1), and we would also need to put an IF to check that the Currency_In matched the Currency. I think that the storage overhead of storing a 2 character currency string for each input cell would also be quite high. It also does not allow you to see the total of what was input in any currency, unlike the previous two methods.

---

This is not an exhaustive list of all possible options. Someone has probably got some variant of scheme (3) working.

Personally I would probably go for scheme (1), and use some Excel formulae to help you generate the rules. It can also allow a little more flexibility, eg we found that as we stored a dividing rate for EUROs to GBP, when going from GBP to EUROs it was more accurate to multiply by the dividing rate rather than use the multiplying rate that we had calculated in our FX Cube as 1 / dividing rate.

Your situation is unusual. It is not unusual to have 10 input currencies, but most companies only want output in one or two currencies. Eg Singapore Branch might want to see what it had entered in Singapore Dollars and the Group currency of GBP, but it wouldn't need to see figures from eg the New York Branch entered in USD expressed in Singapore Dollars. In Head Office in London, they would need to see totals across all Branches, but only in GBP.

Regards


Paul Simon

Paul,

I was about to reply why I would definitely go for #3 (which was my suggestion), i.e. end user experience and comfort when I realized that this might be true and might not be true depending on how they do their input of which we know nothing so far.

I.e. do the end users fill in data directly in the cubes, in web templates, in excel? Do they have to change currencies all the time, or does a single user input 'batches' of data of the same currency?

All solutions are technically feasible, so I think the prime effort should go to what would provide the best end user experience, given such a small difference in development time. (2 similar dimensions s*ck from an end user perspective, especially if they have to change currencies 'every line of input' (which was my assumption from the post before I started using my brain :p)

Jeroen

In my experience in most cases data is loaded via TI. If data is being entered by users then it is generally during forecasting through a tightly managed template.

I think that whichever of the three methods were used, the user input would be the same.

With Method 3 (enter currency in to a string element) I would generally use a subnm to allow the user to pick the currency from a dimension, even though that dimension did not exist in the cube, since we are just recording the currency code as a string measure. Alternatively Data Validation on a list could be used. It would need a DBR behind it, since the value does actually need to go in to the cube.

With Method 1 (Input and Output currencies in same dimension), they would select the Input Currency eg GBP Input from a subnm or Data Validation on a list, or they could just type it in. In this case what matters is the link to the currency in the DBRW, and TM1 doesn't care how that is derived.

With Method 2 (separate Currency_In and Currency Out dims), they would be inputting, with the Currency_Out dim set to Local Currency as a Title Area Dim, so they would only need to select the Currency_In, so this is pretty much the same as Method 2.

One other issue that I have with the idea of entering the currency, in Method 3, is that it only allows for a single currency per cell. Most of the time that is OK, and it is not a problem if the user is entering at individual transaction level. However, in the forecasting scenario, which I believe is more typical of user input, they will usually be entering data at a higher level. Eg, something like Travel Costs in the Month of June, for Cost Centre 512, in Entity 21, and they may very well be forecasting 500 GBP and 6000 EURO. With Methods 1 and 2, they have the flexibility to enter values in more than one input currency. However, in method 3 then can only enter one currency as the input currency.

If I have misunderstood something about what you were proposing, please let me know. I did initially think that it was a possibility but the more I think about it, the more it seems that it has some drawbacks.

The Local Currency idea works well when eg input for a certain Legal Entity is always in the currency of the Entity. In that case you don't need to enter the currency but can pull it in from an attribute on the Entity. However, this question was about the need to have multiple input and output currencies.

Regards

Paul Simon

Hi Paul,

When I was thinking of his problem I was also thinking of the 9.5 demo I saw two days before. In 9.5 you have a client that is much more like contributor of planning and that a.o. allows you to set up a 'validation list' on a string measure directly in the cube.

I.e. I was thinking of cube based manual input where end users have to switch currencies very often. In this case option 3 is by far the easiest for the end user, it will be easiest in all current versions and it will be far far superior to the other options as of 9.5

I can see the problem if there is multi currency input at consolidated levels though (in which case I would agree with your #1 choice), and I do agree that you can get similar results out of all three mechanisms using Excel templates. I also agree that if data is being loaded by flatfile it is a different story. (Although you might want to consider doing the conversion in TI rather than using rules then,removing the 2nd currency alltogether in the process)

I think all things considered we have far too little information to determine the 'best' solution at this point, but it is an interesting discussion

Jeroen