You might find you get quite slow performance as well because you are repeating your “which price band test†many many more times than you need, so I'd also look to move the price band logic out of the final calculation as well. Obviously if you are happy with the performance then no problem with the current approach.
To move the logic out of the calculation set up a string cube that is correctly dimensioned to hold the price band, it will be at least product, scenario, channel and period/year, plus a new measure dimension from what I can see.
Rough Rules in Product Price Band Cube.
Code: Select all
#Change average reference to correct DB (...) reference.
#Note no feeders required since only an internal rule reference not for end user consumption, watch out if you need to TI this data into a static area though
[‘Ruled Price Band’] =S:
IF (DB('Price Band Definitions', 'PB3', 'High', !Product) >= ['Average'], 'PB3',
IF (DB('Price Band Definitions', 'PB2', 'High', !Product) >= ['Average'], 'PB2',
IF (DB('Price Band Definitions', 'PB1', 'High', !Product) >= ['Average'], 'PB1', '')));
Revised rule in main cube.
Code: Select all
['FY10', 'WhatIf', 'Store', 'POS Sales' ] = N: if (attrs('Product', !Product, 'PSP Class') @= '', STET,
# PB1 CHECK
DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'Units',!Period) *
DB('Attach Drivers', !Year, !Scenario, !Channel, !Product,
#reference to new cube
DB ('Product Price Band', !Year, !Scenario, !Channel, !Product, !Period, 'Ruled Price Band'),
'Attach Rate', !Period) *
DB('Attach Drivers', !Year, !Scenario, !Channel, !Product,
#reference to new cube
DB ('Product Price Band', !Year, !Scenario, !Channel, !Product, !Period, 'Ruled Price Band'),
'Rev / Attach Unit', !Period),
STET));
This makes the main rule much more concise since the PB logic is now in a reference cube, but you wont see much of a performance advantage (yet, bare with me!) since TM1 does not cache the results of string rules (aside:don't know why, probably an idea to save Ram that should have been dumped when 64 bit came along) and we are still performing the test the same number of times as in your original rule.
Anyway so once we have the cube for the PB logic working we either need to make it a numeric rule or make a copy of the rule based data, this will mean that either the results of the PB test get cached or they PB test does not get performed at all.
Making a copy of the rule based string data should be straight forward with a TI to copy the string values from ‘Ruled Price Band’ to ‘Static Price Band’, a new element in the measure dimension of the PB reference cube. Change the reference in the main rule cube too! This approach will give you a significant performance gain since the test for the PB band is not repeated many thousands of times. In fact once the TI is run the test is not performed at all. The downside is that you move away from a truly dynamic system since you need to run the TI to update the static data when the underlying values changes. It depends on your business process if this will work for you.
The second approach which I think in principal should still give a performance gain but I have not tested. This is to change the rule in the Product Price Band to return a numeric value, since numeric values are cached, we should get a performance gain since the PB logic test is only performed once for a given combination.
Something like this
Rough Rules in Product Price Band Cube.
Code: Select all
#Change average reference to correct DB (...) reference.
#Note no feeders required since only an internal rule reference not for end user consumption, watch out if you need to TI this data into a static area though
[‘Ruled Price Band’] =N:IF (DB('Price Band Definitions', 'PB3', 'High', !Product) >= ['Average'], 3,
IF (DB('Price Band Definitions', 'PB2', 'High', !Product) >= ['Average'], 2,
IF (DB('Price Band Definitions', 'PB1', 'High', !Product) >= ['Average'], 1, '')
Revised rule in main cube.
Code: Select all
['FY10', 'WhatIf', 'Store', 'POS Sales' ] = N: if (attrs('Product', !Product, 'PSP Class') @= '', STET,
# PB1 CHECK
DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'Units',!Period) *
DB('Attach Drivers', !Year, !Scenario, !Channel, !Product,
#reference to new cube
‘PB’ | Str(DB ('Product Price Band', !Year, !Scenario, !Channel, !Product, !Period, 'Ruled Price Band'),1,0),
'Attach Rate', !Period) *
DB('Attach Drivers', !Year, !Scenario, !Channel, !Product,
#reference to new cube
‘PB’ | Str( DB ('Product Price Band', !Year, !Scenario, !Channel, !Product, !Period, 'Ruled Price Band'),1,0),
'Rev / Attach Unit', !Period),
STET));
Now the rule logic is does not involve any string calculations or tests so the whole system should be able to build up the cached results. That is until someone changes a number and it all gets thrown away!
You could even give the elements in the Price band dimension an alias of 1, 2 and 3. Then you would not have to do the concatenation in the rules, giving another potential performance gain. Note that its not really good practice to write rules on alias and having alias that are just numbers will confuse the hell out of the formula editor in Excel.
Anyway I’m rambling now, hope this has given you some ideas of how you can fine tune the rules. Fell free to ask some questions if this does not make sense!
