Conversion to US Dollar issue

[Chuks]

Hi All,

I have a P&L report which has all different accounting group costs displayed in USD. This report however takes more than a min to refresh while the same report using the local currency takes only few seconds ( less than 5 secs) to refresh.

USD is calculated using a rule as given below, considering the possibility of a market having more than 1 currency input( multi currency Market)

['USD','Amount]= N: ['LOC1','Amount']*(DB('Lookup FX','LOC1','RATE')+ ['LOC2','Amount']*(DB('Lookup FX','LOC2','RATE')+ ['LOC3','Amount']*(DB('Lookup FX','LOC3','RATE');

FEEDERS;

[{'LOC1','LOC2','LOC3'},'Amount']=>['USD','Amount'];

The LOC1, LOC2, LOC3 amount is also calculated using different rules.

Could you please let me know the reason for such refresh delays? Also please let me know if there is any other efficient way of calculating Conversion to USD from Local Currency.

[qml]

I am not surprised that the way you are doing it it takes about one order of magnitude longer to calculate a USD report than it does a LCY report. This is because for every leaf cell that is consolidated up for LCY TM1 just takes a ready value whereas for USD it needs to calculate at least two rules (one for LOC1/2/3 and one for USD). Calculating a formula that relies on multiple values surely must take more time than just retrieving a value from memory, right? And this needs to be done multiple times (thousands, millions?) for each final value that is shown in the report.

There might be a solution to it or likely even more than one. You are not showing the full rule file, so I'll make some assumptions in proposing another approach.

Since you seem to be using static currency scenarios where FX rates don't change in time, you should be able to use weighted consolidation to achieve your USD figures. This would be much faster than using rules. USD would be a parent of LOC1/2/3 in the appropriate dimension with weightings that correspond to the FX rates that sit in your FX cube. Of course you would need to update these weightings every time the FX rates change, but that isn't too much work.

Another possibility that should be faster than what you're doing would be to use a slightly amended rule for USD by removing the 'N:' designation. That way LOC1/2/3 would be aggregated first and the rule would only apply once at the consolidated level instead of being applied to every leaf cell before consolidation.

Of course to really get a visible performance gain you'd have to apply the same logic to LOC1/2/3 so that this underlying calculation is faster too.

[Wim Gielis]

Try the following to compare with your current situation.

Create a consolidation called "USD Total". It contains the 3 elements: LOC1_USD, LOC2_USD, LOC3_USD.
Each of them has an N-level rule to convert the local currency to USD.
If you feed these 3 elements from the local currency element then the consolidated value USD_Total will be correct.
Lastly, if needed, have a rule where the USD element takes over the same value as USD_Total.

The key is that TM1's consolidation algorithms are much more powerful comparing summing values with +

You will use more memory because of more feeders but it's a first step in understanding the delay in calculation times.

Alternatively, have 1 LOC currency element and 1 USD element. An attribute 'Currency' on a dimension like country or company could then be used for the currency conversion.

Another option: use TI to calculate the converted rates. Obviously this option has drawbacks too.

Wim

[mattgoff]

Since you seem to be using static currency scenarios where FX rates don't change in time

This jumped out for me too, and I'm concerned that it's a flaw/oversight in the database design. Chuks, are you sure that you want to use one rate for all periods? I also don't see a version dim, so if that exists in your cube you could pare down the rule/feeders accordingly. (And I'm also concerned there's not a version dim, for that matter.)

Matt