Rule question

Post Reply
manu0521
Posts: 102
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Rule question

Post by manu0521 » Wed Apr 28, 2021 9:30 pm

Hi All,

I used to have the belwo rule

[Currency:'USD', 'FX Rate': 'Budget'] = N:[Currency:'Input Currency', 'Actual'] * (DB('Exchange Rates', 'Budget', !Currencies, 'USD', 'Ending Rate',!Months));

it takes the sales value for that month and goes to exchnage rate cube to get the rate for that month and calculates .

Now my source cube has data stored at day level , but the exchange rates are stored at month level ,so when I pass !Months is it sufficient that I Pass
first parent of the Day member .

Thank you

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

Re: Rule question

Post by Steve Rowe » Thu Apr 29, 2021 7:22 am

It is probably simpler, more resiliant and the rule would run faster if you put an attribute on the day dimension that holds the month.

[Currency:'USD', 'FX Rate': 'Budget'] = N:[Currency:'Input Currency', 'Actual'] * (DB('Exchange Rates', 'Budget', !Currencies, 'USD', 'Ending Rate',Attrs('Day',!Day,'Month'));

If you use Elpar ('Day',!Day,1) to get to month then that (relatively more complex function) has to be evaluated for every data point.

You also have the risk associated with the assumption in the rule that the month is always the first parent in the day dimension. You are linking your business logic directly to the structure of your dimension, this has a risk of going wrong. For example if you were to introduce a week consolidation in a seperate consolidation structure you may find that you need to use Elpar ('Day',!Day,2) to get to month.
Technical Director
www.infocat.co.uk

manu0521
Posts: 102
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: Rule question

Post by manu0521 » Thu Apr 29, 2021 4:52 pm

Thanks , that sounds the right approach. I will set it up the same way .

Post Reply