Rule/Feeders for currency conversion

[CRP0021]

Hi all,
Just looking for some advice on how to structure rules/feeders for fx conversion. I have provided sample screenshots below of the cube structures.

Essentially I am moving from an input cube to a reporting cube which runs 50+ currency conversions (each regional user would like to represent their data in several currencies).

The basics is it converts from local currency to a rate stored in a lookup cube. That part is pretty straight forward. The feeder is not performing great as currently it reads ['local']=>['Total Currency'];

Instead of writing individual feeder statements for each currency type i.e. ['local']=>['USD'], ['local']=>['CAD'] (which I believe achieves the same thing as the above noted feeder) I am looking for some suggestions on how to better achieve this.

Any advice would be appreciated in how this could be achieved. Using a TI to run through this is not ideal as the users will be inputting values on the fly and need to see the conversions happen in real-time.

Thanks in advance!

Cube FX.png (18.67 KiB) Viewed 2849 times

Cube Input.png (17.85 KiB) Viewed 2849 times

Cube Rpt.png (24.12 KiB) Viewed 2849 times

[Wim Gielis]

That's a nice explanation of the problem, well done. For someone with TM1 experience, it's almost immediately clear what the heart of the problem is.

I imagine that the source data on 'Local' is a mixture of several currencies, you mention 50 different currencies. That's totally okay, because I would image a Company or Department dimension, with a Text attribute to store the name of the Currency. Then you know what to expect in terms of interpretation of the numbers on 'Local'. Looking at 'Total Company' on 'Local" is nonsense and should be overwritten as 0, usually, with a non-fed C-level rule.

Then, you need to express the Local values in several currencies. That's fine too. But do you need to express it in again 50 currencies ? That would be a lot and not sure if that makes sense. Yes, if feeding TOWARDS a consolidation, you implicitly feed its children and also yes, it is the same effect as writing 'individual' feeders. No gain there.

The first thing to do is verifying whether all numbers need to be expressed in all such currencies. A local controller in a department or company working in EUR or GBP, what is the added value of expressing his numbers in almost 50 currencies ?

One solution could be to have many currencies as C level elements, with a child as (each time) Local. That would avoid feeders but you also give up the fact that currencies are level 0 elements, capable of capturing input (for certain other areas in the cube, or different cubes, for example). Also, it will be C-level rules instead of N.

Most importantly, assess the requirements of having all those recalculations in other currencies. Seems much overkill to me. If for example companies have a set of 5 currencies to look at, but not all companies share the same 5 currencies, then feeding in an alternative way might be an option. For feeding by using TI with a so called 'Feeder' element might be an option too but not my preferred option honestly.

[CRP0021]

Hey Wim,
Thanks for your reply.
Everything you posted makes total sense to me.
Also the thought of adding "local" as a child had occurred to me as well and the rule works well even eliminating the feeder is great but it got a little muddy when consolidating Regions since the consol dept cannot store a local currency type (i.e. Central Europe is made up of various currency types).

Currency.png (47.83 KiB) Viewed 2838 times

[Wim Gielis]

I didn't say it was a perfect solution and applicable in all circumstances.

Also, can you elaborate on the necessity to translate to 50 currencies (which is the root cause of this problem) ?

[CRP0021]

Hi Wim,
I believe we are both aligned with our thinking on the requirements.
The client has agreed that it makes sense to calc via rule only 5-6 currencies and make available on demand to calc to any other currency via TI.
I appreciate your responses and attention to this topic.
Cheers!

[Wim Gielis]

Well done !