C level rule not working properly

[dan.kelleher]

Morning all,

I'm having trouble with a rule that calculates Gross Margin % and Average Selling Price for Budget data at consolidated levels incorrectly (i.e. aggregating instead of overriding). I have no skipcheck in the rule for now. Budget data comes from the Sales Assumptions and Product Assumptions cubes, and Actual data resides in the Sales cube (where the rule resides). Is this something to do with the order I have my rules in my rule file?

Code: Select all

['Budget', 'Volume'] = N: 
  DB('Sales Assumptions', 'Budget', !Year, !Customer, !Product, 'New Full Year') \		
  DB('Sales','Actual', ATTRS('Year', !Year, 'Prev'), 'TOTAL YEAR', !Customer, !Product, 'Volume') *
  DB('Sales','Actual', ATTRS('Year', !Year, 'Prev'), !Week, !Customer, !Product, 'Volume');

['Revenue'] = N: ['Volume'] * ['Average Selling Price'] ;

['Budget',{'Average Selling Price', 'Gross Margin %'}] = N: DB('Product Assumptions',!Product, !Revenue_Measures);

['Gross Margin'] = N: ['Revenue'] * ['Gross Margin %'] ;

['Average Selling Price'] = C: ['Revenue'] \ ['Volume'] ;

['Gross Margin %'] = C: ['Gross Margin'] \ ['Revenue'] ;	

['Cumulative Margin'] =  
  DB('Sales', !Scenario, !Year, !Week | ' YTD', !Customer, !Product, 'Gross Margin') ;

Any help greatly appreciated.

Thanks in advance,

Dan

[mce]

Make sure you have AllowSeparateNandCRules=T in your config file.

[John Hammond]

I am assuming that your consolidations are showing zero, but your N levels for ['Budget'] are ok.

The only thing I can see possibly wrong is that these might be reversed in order

Code: Select all

['Revenue'] = N: ['Volume'] * ['Average Selling Price'] ;

['Budget',{'Average Selling Price', 'Gross Margin %'}] = N: DB('Product Assumptions',!Product, !Revenue_Measures);

You seem to be using your average selling price before obtaining it from the product assumptions.

[Jeroen Eynikel]

MCE,

"Make sure you have AllowSeparateNandCRules=T in your config file."

I find this bad advice. This parameter only exists for legacy reasons and it is definitely not intended that you write your N: and C: rules seperately.
Instead of using that parameter(*), I recommend you write your rules properly.


(*) IIRC there is absolutely no guarantee that this parameter will be kept in the future.

[Jeroen Eynikel]

Change your rules to the syntax below and you should be fine.
['Budget','Average Selling Price'] = N: DB('Product Assumptions',!Product, !Revenue_Measures);C: ['Revenue'] \ ['Volume'] ;
['Budget','Gross margin %'] = N: DB('Product Assumptions',!Product, !Revenue_Measures);C: ['Gross Margin'] \ ['Revenue'] ;

[mce]

MCE,
"Make sure you have AllowSeparateNandCRules=T in your config file."
I find this bad advice. This parameter only exists for legacy reasons and it is definitely not intended that you write your N: and C: rules seperately.
Instead of using that parameter(*), I recommend you write your rules properly.
(*) IIRC there is absolutely no guarantee that this parameter will be kept in the future.

Hi Jeroen,
Use of this parameter was mentioned in another post as well and no body mentioned that it is a bad practice. See this http://forums.olapforums.com/viewtopic.php?f=3&t=3079.
Could you please tell us from where you found that it is a bad practice or from where you found that it may be removed in later releases?
Regards,

[Gregor Koch]

Hello

I don't think that advising the usage of an exisiting setting is bad at all.
There is no indication that this will not be supported in the future. In fact it would create havoc if it was discontinued as lot of models use this absolutely fine way of writing the rules. Neither of the two styles are more proper than the other, imho.
But, you can use the setting and still use the other (I think newer) syntax.

Both your suggestions will solve the problem.

@John
The 'order' in which you 'pick up' values from another cube and use it in a rule doesn't matter at all. For this matter there is no 'order'.

Cheers

[Martin Ryan]

Dan, can you be a bit more specific about what's not working? Perhaps a screenshot too. I can't see anything wrong with your rule.

Re the cfg parameter debate, I personally always write my N and C rules separately as I find it easier to run my eye down rules that way. I still don't understand why they've changed the default behaviour to disabling this, and what benefit it is supposed to create.

Martin

[John Hammond]

Good fun this one.

I think Jeroen has given us the answer to this one, but it has been a good discussion because the example is quite understandable.
mce's solution will work too but is more unconventional.

@John
The 'order' in which you 'pick up' values from another cube and use it in a rule doesn't matter at all. For this matter there is no 'order'.

Gregor

Are you saying that TM1 will always evaluate calculations that are sourced from external cubes first.

Code: Select all

['Revenue'] = N: ['Volume'] * ['Average Selling Price'] ;

['Budget',{'Average Selling Price', 'Gross Margin %'}] = N: DB('Product Assumptions',!Product, !Revenue_Measures);

Or are you saying that since TM1 evaluates on the fly when it needs a result that depends on subresults it will search for the rules that contain the subresults regardless of order and then evaluate that dynamically.

Thus, removing the complications of the external calculation

Code: Select all

['Revenue'] = N: ['Volume'] * ['Average Selling Price'] ;

['Budget',{'Average Selling Price', 'Gross Margin %'}] = N: 66 ;

will work just as well as the more 'readable'

Code: Select all

['Budget',{'Average Selling Price', 'Gross Margin %'}] = N: 66 ;

['Revenue'] = N: ['Volume'] * ['Average Selling Price'] ;

[lotsaram]

Gregor

Are you saying that TM1 will always evaluate calculations that are sourced from external cubes first.

No. This isn't what Gregor said or meant, .. not sure where you could get this interpretation from.

Your second interpretation is more on the money.

Or are you saying that since TM1 evaluates on the fly when it needs a result that depends on subresults it will search for the rules that contain the subresults regardless of order and then evaluate that dynamically.

Thus, removing the complications of the external calculation

Code: Select all

['Revenue'] = N: ['Volume'] * ['Average Selling Price'] ;

['Budget',{'Average Selling Price', 'Gross Margin %'}] = N: 66 ;

will work just as well as the more 'readable'

Code: Select all

['Budget',{'Average Selling Price', 'Gross Margin %'}] = N: 66 ;

['Revenue'] = N: ['Volume'] * ['Average Selling Price'] ;

To my mind neither is more readable, both are exactly equivalent.

In terms of the order of writing rules only the assignment of a calculation definition to a cube area is important and priority (as in the order from top to bottom that rules are written) is critical. Once a cube area has a rule defined if the same are is re-defined later in the rule file it will have no effect (this can be modified a with use of CONTINUE but the general principle still applies). For this reason rules should always be written with specific cases or exceptions at the top and general cases or "catch alls" at the bottom.

In terms of calculation dependency the order which rules are written has no effect as this is resolved by TM1 on the fly when the rule is evaluated.

[John Hammond]

Cheers Lotsaram.

The second interpretation is clearly the correct one and obviously so since it covers the first in its generality in that TM1 will go to an external cube when ever it sees appropriate in the compiler tree that is generated each time a cell needs to be evaluated.

Knew the bit about once a cell is referenced in an area if referenced again the calc will be ignored and in this one way order is critical.

TM1 rules never fail to amaze me in that they are very different to 3GL rules and I always think I have it nailed but there are still surprises lurking.

[Gregor Koch]

Thanks lotsa for the correct interpretation.

[dubs]

focusing purley on the original question are you saying that it is aggregating instead of calculating? just need to be clear on what the problem is before I make a suggestion.

[Jeroen Eynikel]

MCE,
"Make sure you have AllowSeparateNandCRules=T in your config file."
I find this bad advice. This parameter only exists for legacy reasons and it is definitely not intended that you write your N: and C: rules seperately.
Instead of using that parameter(*), I recommend you write your rules properly.
(*) IIRC there is absolutely no guarantee that this parameter will be kept in the future.

Hi Jeroen,
Use of this parameter was mentioned in another post as well and no body mentioned that it is a bad practice. See this http://forums.olapforums.com/viewtopic.php?f=3&t=3079.
Could you please tell us from where you found that it is a bad practice or from where you found that it may be removed in later releases?
Regards,

OK this is from memory as it has been a few years

But I recall this parameter being mentioned at a conference in the US a few years ago with the following explanation.

1: they disabled the 'old' way of creating rules as they did not want to support models / encourage writing of the calculation rules whereby you first had 100 N: Rules and then 100 C: Rules. (I.e. I recall that being stated as the main reason.)
2: as a lot of models already in use had rules seperate for N: and C: they included an extra parameter to allow the old behaviour again (i.e. legacy support), but at the time it was stated clearly (* by whoever from Applix I was speaking to at the time - don't rememeber who it was) that it was not meant to keep on writing rules in that way.

Now,

consider the facts for a second.
- One way of writing rules is the default, the other way is still supported. Will it be supported forever? Probably so. Is it guaranteed? Absolutely not.
- All of the trainings have been using the default syntax for years. I don't believe the alternate syntax is even mentioned in trainings as a possibility. -

Personally, I like having all of the N and C rules concerning a same slice very close to one another in the rules. Although it is possible to do so with the 'old' syntax there is no obligation, it will just depend on the discipline of the developper.

Considering all of the above, I see no reason at all to advocate the use of the Allow... config setting and certainly not for non legacy applications.