Understanding Tm1 Rules

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Understanding Tm1 Rules

Post by Analytics123 »

Hi All ,

I am a new bie to tm1 rules and went over the tm1 user guide to understand the basics about the rules and gone through few existing rules that were used by existing cubes .I appreciate your help in clearing my doubts .The cube is basically used as AR scoring model which will have many measures and a total points will be calculated based on these measures .

AR Measure looks like in the attachment .

My rules are
['AR% 0'] = if(['Current AR'] = 0 ,0,(['Current AR'] \ ['Total AR View']));
['AR% 1 - 30' ] =if(['Past Due 1 - 30' ] =0,0,(['Past Due 1 - 30']\['Total AR View']));
['AR% 31 - 60'] =if(['Past Due 31 - 60'] =0,0,(['Past Due 31 - 60'] \['Total AR View'] ));
['AR% 61 - 90'] =if(['Past Due 61 - 90']=0,0,(['Past Due 61 - 90'] \['Total AR View']));
['AR% 91 - 180'] =if(['Past Due 91 - 180']=0,0,(['Past Due 91 - 180'] \['Total AR View']));
['AR% 180+'] =if(['Past Due More than 180']=0,0,(['Past Due More than 180'] \['Total AR View']));

SO these rules has calculations if the measure value is not equal to zero, perform the calculations otherwise do nothing and it makes sense to have feeders.
My assumption is we need feeders now so that tm1 will skip rows with zero values and feed only non zero cells . This is all good so far .

Next set of rules .
['Current Points'] =if(['AR% 0'] >=0, ['AR% 0'] *DB('AR Parameter Scoring', '0 Weight', 'Values'),['AR% 0']* -1);
['1 to 30'] =if(['AR% 1 - 30'] >=0, ['AR% 1 - 30'] *DB('AR Parameter Scoring', '1 -30 Weight', 'Values'),['AR% 1 - 30']* -1);
['31 - 60'] =if(['AR% 31 - 60'] >=0, ['AR% 31 - 60'] *DB('AR Parameter Scoring', '31 - 60 Weight', 'Values'),['AR% 31 - 60']* -1);
['61 - 90'] =if(['AR% 61 - 90'] >=0, ['AR% 61 - 90'] *DB('AR Parameter Scoring', '61 - 90 Weight', 'Values'),['AR% 61 - 90']* -1);
['91 - 180'] =if(['AR% 91 - 180'] >=0, ['AR% 91 - 180'] *DB('AR Parameter Scoring', '91 - 180 Weight', 'Values'),['AR% 91 - 180']* -1);
['180+'] =if(['AR% 180+'] >=0, ['AR% 180+'] *DB('AR Parameter Scoring', '180 + Weight', 'Values'),['AR% 180+']* -1);

These rules tells us if the value of the measure is positive then do some calculations else just multiply by -1 , here its nothing to do with 0 values its all based on greater or lesser
DO I really need a feeder here , it doesnt work if I a feeder is not added .


And now the issue is only in this rule
['0 AR Adjustment'] = IF(['Total AR View'] >0, 0, DB( 'AR Parameter Scoring', '0 AR Adjustment', 'Values' ));
All the rule says is measure value is negative then use the lookup value otherwise put 0 .
On zero suppression of above measure my measure value disappears and says no applicable values , which means its not fed even if I have my feeders in place.


Feeders:
['Current AR'] => ['AR% 0'] ;
['Past Due 1 - 30' ] => ['AR% 1 - 30' ] ;
['Past Due 31 - 60']=> ['AR% 31 - 60'] ;
['Past Due 61 - 90']=> ['AR% 61 - 90'] ;
['Past Due 91 - 180']=> ['AR% 91 - 180'] ;
['Past Due More than 180'] => ['AR% 180+'] ;
['Current Points']=> ['AR% 0'] ;
['1 to 30']=> ['AR% 1 - 30'] ;
['31 - 60']=> ['AR% 31 - 60'] ;
['61 - 90'] => ['AR% 61 - 90'] ;
['91 - 180']=> ['AR% 91 - 180'] ;
['180+']=> ['AR% 180+']
['Total AR View'] =>['0 AR Adjustment'];

Thanks,
Attachments
ARMeasureView.png
ARMeasureView.png (41.02 KiB) Viewed 3951 times
User avatar
mattgoff
MVP
Posts: 516
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: O365
Location: Florida, USA

Re: Understanding Tm1 Rules

Post by mattgoff »

A couple of general comments:
  • Why are the IF statements necessary in any/all of these rules? e.g. if Current AR is zero, the division is going to result in zero anyway.
  • The element names here are pretty distinct, but it can be risky to use implied dimension names in rules as they can become ambiguous if the same element name is used in more than one dimension in the cube. TM1 will alert you if there's ambiguity when you save the rule, but if you later add an element to a dimension (creating the ambiguity after the rule has already been saved) the rule will become invalid silently. I would update them to the [dimensionName:elementName] syntax.
  • It's hard to diagnose issues when the whole rule file isn't supplied. Also, it looks like you've retyped your rules instead of copy/paste since the element names in the text of your rules doesn't match those the screenshot (plus you've used inconsistent element names in the rule which I assume was a typo).
With the last bullet above as a caveat, it's not obvious why you're not getting fed. You are relying on other rule-calculated values to feed that element, so I would start there. Have you checked feeders (via context menu) on one of the populated children of Total AR View to ensure that it's getting fed? It's happened before that zero suppression works correctly even though a cell is not fed. You may want to add N: to your rules too to avoid something going with rules to consolidations.

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Understanding Tm1 Rules

Post by Analytics123 »

Hi thanks for the reply!! I made the changes and now everything looks fine.

Here is a simple situation .

I have the same Account Receivables cube which has customers and their total Accounts receivable grouped in Buckets.The same measure dimension as in attached screenshot in the below post .

Now I am having a new measure called Current Month Sales in AR cube , which will go pick the sales values from the sales cube for the selected parameters of the AR cube .

SkipCheck:

['Current Month Sales']=DB('Sales Detail', !Months, 'Input Currency', !Currencies,!Customers SoldTo Only,'Full Revenue');

Feeders:
['Current Month Sales'] =>DB('Sales Detail', !Months, 'Input Currency', !Currencies,!Customers SoldTo Only,'Full Revenue');

Now the issue is
Total Ar CurrentMonth Sales
Customer1 1000 5000
Customer2 0 500
Customer3 230 0

Now what happens when zero suppression is used in AR Cube

I get the results as

Total Ar
Customer1 1000
Customer3 230

My currentMonth Sales column disappears and only Total Ar which has non zero values are shown .

I have fed the CurrenMonth Sales value from SalesCube and why there is an issue with zero suppression .
If I right click and say check feeders on the 500 sales cell for customer it says calculated as 500 .

Thanks,
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Understanding Tm1 Rules

Post by tomok »

Analytics123 wrote: ['Current Month Sales']=DB('Sales Detail', !Months, 'Input Currency', !Currencies,!Customers SoldTo Only,'Full Revenue');

Feeders:
['Current Month Sales'] =>DB('Sales Detail', !Months, 'Input Currency', !Currencies,!Customers SoldTo Only,'Full Revenue');
This is not how you write feeders for intercube rules. When you are pulling in values from the Sales Detail cube then the feeder would go in that cube. It would look something like

['Full Revenue'] => DB('Accounts Receivable', !Dim1, !Dim2, !Dim3......);

Just be aware that if there are any dimensions between the two models that are different, you'll need to pick a specific element (meaning you can't use the "!" syntax).
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Understanding Tm1 Rules

Post by Analytics123 »

Thank You !! Now I am having a weird issue , saving my tm1 rules file for AR cube takes more time to save .

I am pasting below the rule file and it has no relation with other cubes .But when the rule file is saved ,the other feeders from other cubes are also recreated . I have no clue why this happening and this is taking more time for the rule file to be saved . Ideally here there is no relation to other cubes except a look up cube .But I see feeders from other 5 cubes are recreated .

SKIPCHECK;
# AR scoring Starts
# AR Percentage Calculations based on the Buckets with respect to Total AR , Higher the percentage the risk of payment is more)
['AR Measures':'AR% 0'] = ['AR Measures':'Current AR'] \ ['AR Measures':'Total AR View'];
['AR Measures':'AR% 1 - 30' ] =['AR Measures':'Past Due 1 - 30']\['AR Measures':'Total AR View'];
['AR Measures':'AR% 31 - 60'] =['AR Measures':'Past Due 31 - 60'] \['AR Measures':'Total AR View'];
['AR Measures':'AR% 61 - 90'] =['AR Measures':'Past Due 61 - 90'] \['AR Measures':'Total AR View'];
['AR Measures':'AR% 91 - 180'] =['AR Measures':'Past Due 91 - 180'] \['AR Measures':'Total AR View'];
['AR Measures':'AR% 180+'] =['AR Measures':'Past Due More than 180'] \['AR Measures':'Total AR View'];
# AR Percentage Calculations based on the Buckets with respect to Total AR)

# Calculate the points based on the weight for each bucket
['AR Measures':'Current Points'] =if(['AR Measures':'AR% 0'] >=0, ['AR Measures':'AR% 0'] *DB('AR Parameter Scoring', '0 Weight', 'Values'),['AR Measures':'AR% 0']* -1);
['AR Measures':'1 to 30'] =if(['AR Measures':'AR% 1 - 30'] >=0, ['AR Measures':'AR% 1 - 30'] *DB('AR Parameter Scoring', '1 -30 Weight', 'Values'),['AR Measures':'AR% 1 - 30']* -1);
['AR Measures':'31 - 60'] =if(['AR Measures':'AR% 31 - 60'] >=0, ['AR Measures':'AR% 31 - 60'] *DB('AR Parameter Scoring', '31 - 60 Weight', 'Values'),['AR Measures':'AR% 31 - 60']* -1);
['AR Measures':'61 - 90'] =if(['AR Measures':'AR% 61 - 90'] >=0, ['AR Measures':'AR% 61 - 90'] *DB('AR Parameter Scoring', '61 - 90 Weight', 'Values'),['AR Measures':'AR% 61 - 90']* -1);
['AR Measures':'91 - 180'] =if(['AR Measures':'AR% 91 - 180'] >=0, ['AR Measures':'AR% 91 - 180'] *DB('AR Parameter Scoring', '91 - 180 Weight', 'Values'),['AR Measures':'AR% 91 - 180']* -1);
['AR Measures':'180+'] =if(['AR Measures':'AR% 180+'] >=0, ['AR Measures':'AR% 180+'] *DB('AR Parameter Scoring', '180 + Weight', 'Values'),['AR Measures':'AR% 180+']* -1);
# Calculate the points based on the weight for each bucket

# AR Adjustment is applied when AR Balance is 0 or negetive meaning they dont owe anything and will recieve the full points
['AR Measures':'0 AR Adjustment'] = IF(['AR Measures':'Total AR View'] >0, 0, DB( 'AR Parameter Scoring', '0 AR Adjustment', 'Values' ));

# AR scoring ENDS

Feeders;
['Current AR'] => ['AR% 0'] ;
['Past Due 1 - 30' ] => ['AR% 1 - 30' ] ;
['Past Due 31 - 60']=> ['AR% 31 - 60'] ;
['Past Due 61 - 90']=> ['AR% 61 - 90'] ;
['Past Due 91 - 180']=> ['AR% 91 - 180'] ;
['Past Due More than 180'] => ['AR% 180+'] ;
['Current Points']=> ['AR% 0'] ;
['1 to 30']=> ['AR% 1 - 30'] ;
['31 - 60']=> ['AR% 31 - 60'] ;
['61 - 90'] => ['AR% 61 - 90'] ;
['91 - 180']=> ['AR% 91 - 180'] ;
['180+']=> ['AR% 180+'] ;
['Total AR View'] =>['Point Factor'];
['AR Measures':'Total AR View'] =>['AR Measures':'0 AR Adjustment'];
Post Reply