Feeders - for conversion rule

Post Reply
Analytics123
Posts: 44
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Feeders - for conversion rule

Post by Analytics123 » Thu Aug 03, 2017 7:50 pm

Hi ,

Cube has data loaded in local currency for Mexico and USA .

For budget alone, we wanted Mexico 's value to be converted to USD .so that we can see budget in usd as a whole .

SO I am writing the below rule :
['MX',Currency:'USD', 'Sales Measures': 'Budget','FX Rate':'Actual'] = N: ['MX',Currency:'Local Currency', 'Sales Measures': 'Budget'] *
(((DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', !Months))+(DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', ATTRS('Months', !Months, 'Prior'))))/2);


This for Mexico country and usd currency and budget measure does the following calculation

takes the value of budget in local currency and multiplies it by average of the month selected and prior month dollar converted rate .

Everything looks good and works fine .


I am not sure of the feeders here :

is it just the current one we have

[Currency:'Input Currency'] => [Currency:'USD'];

or should i be more specific here , the above one works but I know there is definetely a better way than this.

Drg
Posts: 40
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Drg » Fri Aug 04, 2017 2:28 pm

maybe ?
[Currency:'Local Currency'] => [Currency:'USD'];

This implies that all other dimensions of the feeds are one to one

Analytics123
Posts: 44
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 » Fri Aug 04, 2017 3:10 pm

Can I have this :

[Currency:'Local Currency','MX','Sales Measure':'Budget','FX Rate':'Actual']] => ['MX',Currency:'USD','Sales Measures':'Budget'];

User avatar
Steve Rowe
Site Admin
Posts: 1631
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Feeders - for conversion rule

Post by Steve Rowe » Fri Aug 04, 2017 3:24 pm

For speed efficiency consider putting an additional measure in your exchange rate cube to calculate the average

(((DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', !Months))+(DB('Exchange Rates', 'Actual', !Currencies, !Currency, 'Ending Rate', ATTRS('Months', !Months, 'Prior'))))/2);

Currently you are calculating and not storing the results of the above for many data points, just doing broadly the same work over and over again.

If you calculated this average against a ruled measure in the exchange rate cube then
1. The first time this value is requested the result will be cached.
2. You will be doing this (albeit simple) piece of maths many many fewer times.
3. Results should calculate faster.

Note RAM consumption may go up a little as you are now caching more numbers but I would not expect it to be too noticeable.

For feeders, you can just swap the two square bracket references (probably!)

['MX',Currency:'Local Currency', 'Sales Measures': 'Budget'] => ['MX',Currency:'USD', 'Sales Measures': 'Budget','FX Rate':'Actual'] ;

DRG is right though, it would be normal to just have all of Local Currency feed USD since all the Local Currency will eventually be converted into USD If different parts of it are done by different rules there is no reason to split the feeder up as will.

HTH

Analytics123
Posts: 44
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 » Fri Aug 04, 2017 4:33 pm

Yeah thanks , really appreciate your suggestions .

But the average rate is not always on the same months , its done on the month the user selects and its prior month . How would I calculate this as a calculated measure in the exchange rates cube .

I cannot say do an average of jun and July . It has to be the dynamic months the user requests for right ?

Thanks,

Wim Gielis
MVP
Posts: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Feeders - for conversion rule

Post by Wim Gielis » Fri Aug 04, 2017 5:59 pm

Isn't that the purpose of !Months ? To pass the selection of the user to the exchange rates cube ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

Analytics123
Posts: 44
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 » Fri Aug 04, 2017 7:00 pm

yes oh i got it , so i will write a rule in exchange rates cube to pick the month and prior months average ending rate and store it as a calculated measure .

Wim Gielis
MVP
Posts: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Feeders - for conversion rule

Post by Wim Gielis » Fri Aug 04, 2017 7:03 pm

True. I assume you have a months dimension in that cube so yes, that's the way to do it.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

Analytics123
Posts: 44
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeders - for conversion rule

Post by Analytics123 » Fri Aug 04, 2017 7:36 pm

Thanks Wim !!

Post Reply