TM1 Forum

Posted: **Tue Jun 30, 2009 5:04 pm**

I'm hoping there's a cleaner way of implementing this general capability --
take a product's average selling price and based on the price band of that product (i.e. PB #1 = $0 to $99.99, PB#2 = $100 to $199.99 etc), calculate a result.

In Excel, I could take an ASP value and a price band, high-low table and with one sumproduct statement, calculate which price band the product fell into.

In TM1, I've so far written this mess (and this is only for the first Price Band -- there are 5 total):

Code: Select all

['FY10', 'WhatIf', 'Store', 'POS Sales' ] = N: if (attrs('Product', !Product, 'PSP Class') @= '', STET,
	# PB1 CHECK
	IF (DB('Price Band Definitions', 'PB1', 'Low', !Product) <=
		# POS Sales /
		 DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'POS Sales',!Period) /
		# Units
		 DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'Units',!Period) &
		# And
		DB('Price Band Definitions', 'PB1', 'High', !Product) >=
		 DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'POS Sales',!Period) /
		 DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'Units',!Period),
		# Calculation
		# Parent Units * Attach Rate * Rev / Unit
		DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'Units',!Period) *
		DB('Attach Drivers', !Year, !Scenario, !Channel, !Product, 'PB1', 'Attach Rate', !Period) *
		DB('Attach Drivers', !Year, !Scenario, !Channel, !Product, 'PB1', 'Rev / Attach Unit', !Period),
	STET));

Is there a better way than replicating this for the 4 remaining price bands (and then re-replicating for a different metric)?

There's no CASE or Variable support logic in the rules world is there?

Thanks.

-- John

Posted: **Tue Jun 30, 2009 5:40 pm**

I'd make a couple of suggestions that might tidy up the code but doesn't change your logic a tremendous amount.

First, do your average as a separate calculation.

Code: Select all

['Average'] = N:  
DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'POS Sales',!Period) /
DB('M',!Year, !Scenario, !Channel, attrs('Product', !Product, 'PSP Class'), 'Units',!Period);

then (this assumes that each price point carries on from the next, so that an elseif apporach will work. Therefore we only need to check if the current value is less than the next step up, because failing the previous if statement proved that it is greater than that step)

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, 
   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', ''))),
'Attach Rate', !Period) *

      DB('Attach Drivers', !Year, !Scenario, !Channel, !Product, 
   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', ''))), 
'Rev / Attach Unit', !Period),
   STET));

I.e. instead of your hard coded 'PB1', I have replaced that with a bunch of if statements. It's still not the cleanest and is not automatically extendable - if you add another price point you'll have to manually update the rules.

Hope it gives you some thoughts anyway,
Martin

Posted: **Tue Jun 30, 2009 6:55 pm**

Definitely good ideas and better logic... will incorporate. Thanks.

Posted: **Tue Jun 30, 2009 9:46 pm**

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!

Posted: **Tue Jun 30, 2009 10:24 pm**

Thanks Steve!!! Walking off on holiday right now but will test when I return.

TM1 Forum

Data Bands in TM1

Data Bands in TM1

Re: Data Bands in TM1

Re: Data Bands in TM1

Re: Data Bands in TM1

Re: Data Bands in TM1