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
Rule question
- Steve Rowe
- Site Admin
- Posts: 2415
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Rule question
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.
[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
www.infocat.co.uk
-
- Posts: 124
- 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
Thanks , that sounds the right approach. I will set it up the same way .