FEEDERS HELP

[rill22]

Hello All,

I was hoping that someone could give me some advice/tips on an issue i'm having with some feeders. Please bare with me as I've only been working with it for 3 weeks. Currently, I have 2 cubes that I'm using to pull commissions for our sales reps. When I enter a value into the CUBE 1, I should be able to see the change in CUBE 2. However, nothing changes in CUBE 2. Its like i'm not feeding something correctly and if i turn off the skipcheck it will never recalc due to the amount of data it tries to pull into the memory.

When I go into Cube1, I enter a commission for a part number that is assigned to a particular sales rep. We have two types of commissions, one is a flat rate('Y') and the second is a percentage(000). For example, a sales rep could get a value of Y050. This rep would receive a flat commission (Y) and also a QT commision of 50%.

Cube 1(KCC Assignments)
-Working Forecast
-Sales Reps
-Planning Hierarchy

Cube 2(Performance Reporting)
-Planning Hierarchy
-Sales Reps
-Months
-Scenario
-All Part Numbers
-All Factories
-Revenue
-Total Revenue Segments

RULES IN CUBE 2:
SKIPCHECK;

['Working vs Approved']=N:(['Working Forecast']-['Approved Forecast']);
['Working vs Budget']=N:(['Working Forecast']-['Budget']);
['Approved vs Budget']=N:(['Approved Forecast']-['Budget']);

[ ]=N:IF (subst(DB('KCC Assignments','Working Forecast', !Planning Hierarchy, !Sales Reps), 1,1) @='Y',
DB('Sales Forecast Input', !Standard Part Number, !Planning Hierarchy, 'All Approval groups', !Factory, !Months, !Revenue Segment, !Scenario, !Sales Measures)
,0);

['QT Billings' ]=N:IF (subst(DB('KCC Assignments','Working Forecast', !Planning Hierarchy, !Sales Reps), 1,1) @='Y',
['Billings'] * Numbr(subst(DB('KCC Assignments','Working Forecast', !Planning Hierarchy, !Sales Reps), 2,3))/100
,0);


FEEDERS;
[{'Working Forecast','Approved Forecast'}] => ['Working vs Approved'];
[{'Working Forecast','Budget'}] => ['Working vs Budget'];
[{'Approved Forecast','Budget'}] => ['Approved vs Budget'];
[{'Working Forecast'}]=>['QT Billings' ];
**************************************************************************************
Any help/tips would be greatly appreciated!

[declanr]

I presume that you have feeders in the rulesheet of cube1 and the rule in the rulesheet of cube2?

Not of the greatest help but if you right click on a cell and choose trace feeders It will tell you if a cell is not fed (if it is fed it will tell you nothing.)

When you say nothing changes in cube 2.... is this even if you have your view with everything at N-Level and supress zeroes turned off? ... if so and nothing is changing then it will infact be the rule that isn't working... the signs of a feeder not working are more commonly unusual Consolidations and the disappearance of values when zeroes are suppressed.

[qml]

rill22, please post your rules and feeders from cube1. The feeder statement for cube2 would normally have to be attached to cube1.

declanr, the functionality you are writing of is Check Feeders, not Trace Feeders. Trace Feeders does the opposite.

[declanr]

declanr, the functionality you are writing of is Check Feeders, not Trace Feeders. Trace Feeders does the opposite.

sorry rill22, I'm getting my words mixed up and feeding you duff info.

[rill22]

I'm not sure what my feeders need to say in CUBE1...

SKIPCHECK;

[ ]=S:IF(!Scenario@='Working Forecast',STET,'');

FEEDERS;
['Working Forecast' ] => ['QT Billings' ] ;

[tomok]

You need to read the TM1 documentation on rule writing, specifically around intercube rules and feeders. It will be blindingly obvious to you once you read it. (Hint: feeder statements go in the cube where the data in left hand side of feeder statement is stored).

[Gregor Koch]

Hi
I am a bit confused by the dimension and element names as you list 'Working Forecast' as dimension name and as element name in a (I guess the scenario) dimension. Once Planning Hierarchy is an Element and in the rule it is a dimension name. They also do not seem to be in order. So my first question would be whether you actually see a value coming through at all on the N Level in Cube 2? I am a bit doubtful that the rules actually do what you need them to do in the first place. Don't be discouraged but there are several things in your rules that are not very good at best.

For example if I look at this...

['QT Billings' ]=N:IF (subst(DB('KCC Assignments','Working Forecast', !Planning Hierarchy, !Sales Reps), 1,1) @='Y',
['Billings'] * Numbr(subst(DB('KCC Assignments','Working Forecast', !Planning Hierarchy, !Sales Reps), 2,3))/100
,0);

Here you are saying that you are storing the commission percentage in a String cell (why would you do that?) like this 'Y010'. Do you have other values where it says 'N015' to imply that although there is a percentage (15) it should not apply because you have a 'N' in front of it. Logically you could just store 10 as a number. Apart from that you are implying that your Sales Rep elements are of String type and it has to be the last dimension in any cube you use it. I'd recommend having only N elements in you Sales Rep dimension and adding a measure dimension to your 'KCC Assigments' cube that has at least one N measure called 'Commission Percentage' or whatever.

The other rule..

[ ]=N:IF (subst(DB('KCC Assignments','Working Forecast', !Planning Hierarchy, !Sales Reps), 1,1) @='Y',
DB('Sales Forecast Input', !Standard Part Number, !Planning Hierarchy, 'All Approval groups', !Factory, !Months, !Revenue Segment, !Scenario, !Sales Measures)
,0);

Why? This reads: for every possible cell (including all scenarios) in the cube check if you have a 'Y' for any hierarchy and any sales rep combination to get everything across form 'Sales Forecast Input'.
You could just do your commission calc in the 'Sales Forecast Input' cube, it seems to have the same or at least very similar dimensionality.

Anyway, if you do see values on N Level in your Performance Reporting cube here are two very general tips on Feeders.

1. Try to avoid writing them if you don't have to.

['Working vs Approved']=N:(['Working Forecast']-['Approved Forecast']);
['Working vs Budget']=N:(['Working Forecast']-['Budget']);
['Approved vs Budget']=N:(['Approved Forecast']-['Budget']);

All of the above rules can and should be simple - better performing - consolidations that you don't need to feed at all. Saves you writing the feeders for those, which admittedly are not your real problem as they are easy ones. So please, change it.

2. Make them as simple as possible.
In your case having an extra measure 'QT Billings' in the 'Sales Forecast Input' cube could (not 100% sure because of confusion on the dimensionality) could be used for the rule and a possible feeder within that cube would be

['Billings']=>['QT Billings'];

Yes, you could put in a condition on the percentage to make it best practice but it could get you started.

3. If you do ask for assistance on rules and feeders it really helps if you list the dimensions of the cubes (including your third cube) in order as they are in the cubes. You just might get a 100% correct answer.

Cheers

[rill22]

Hey Gregor,

You were right, I wasn't getting anything at the N level in cube 2(performance reporting cube). I changed the scenario in the statement below to read 'Working Forecast' instead of !Scenario.

[ ]=N:IF (subst(DB('KCC Assignments','Working Forecast', !Planning Hierarchy, !Sales Reps), 1,1) @='Y',
DB('Sales Forecast Input', !Standard Part Number, !Planning Hierarchy, 'All Approval groups', !Factory, !Months, !Revenue Segment, 'Working Forecast', !Sales Measures)
,0);


The reason that I have my commission in a string cell.
I get an xls file from my sales team each month that has all of the up to date commission values. I then push that sheet into my KCC assignments cube. In that sheet, there are two types of commissions in a single cell. One commission is the 'Y' and the other is a Quick Turn Commission. The Y simply means that they get paid out to %100 percent of the regular commission price.

COMMISSION EXAMPLE: Y010
Y - the 'Y' stands for a regular commission and it always pays at 100% - I'm trying to calculate that into the 'Working Forecast'
010 - the sales rep would receive 10% of the normal commission. I'm trying to calculate that into the 'QT BILLINGS'

Currently, If I make a change to cube 1(KCC ASSIGNMENTS), the change doesnt reflect in cube 2(Performance Reporting).

[rill22]

I added a picture of both cubes so you can see that the values are not updating. I went into the KCC assignments cube and picked out a part number (CSM_CELI1R). I changed the commission to show that Ryan Z. is no longer receiving credit for the commission and Joe Ray should get it now. However, the update doesnt take place in the Performance Reporting cube. Is this a feeder issue or a problem with my calculation in the performance reporting cube?

Thanks again for you help,

[rill22]

CUBE 1: KCC ASSIGNMENTS

Scenario
Planning Hierarchy
Sales Reps

CUBE 2: PERFORMANCE PLANNING

Standard Part Number
Planning Hierarchy
Sales Reps
Factory
Months
Revenue Segment
Scenario
Sales Measures

CUBE 3: SALES FORECAST INPUT
Standard Part Number
Planning Hierarchy
Approval Hierarchy
Factory
Months
Revenue Segment
Scenario
Sales Measures