IBM Cognos TM1 Feeders

feeder misconceptions (aka how to overfeed)

One of the most common misconceptions – especially when you’re writing your first rules – is to feed all parts of a calculation. Take the following rule – calculating an employee’s monthly bonus in a generic headcount cube – as an example:

[‘Monthly Bonus’] = N:([‘Annual Salary’] * DB(‘Calendar’, !Year, !Period, ‘Monthly’, ‘Percent of Year’) ) * [‘Bonus %’];

I’ve found that many developers will assume they need to feed all parts of the calculation. So, for the above example, they will feed “Monthly Bonus” with “Annual Salary” and “Bonus %” from the same cube, and “Percent of Year” from the “Calendar” cube. However, all they’re doing is “marking” the cell for calculation three separate times when all they had to do was mark it once. This is adding three times the amount of processing time per cell. Extrapolate that out to the number of cells in your cube and you’re talking about a serious performance issue in your model.

So the cell only needs to be fed once, and to do this in the most efficient way possible you feed it with the part of the calculation that will most likely signify that the cell will result in a value of zero.

To elaborate on this point a bit: what universal logic can we grasp that will help us in determining the best possible feeder? Well, first you have to separate these logical truths into two categories: multiplication/division calculations, and addition/subtraction calculations.

feeders for multiplication and division calculations

Multiplication and division calculations are our best friends. In these calculations, we truly need only one feeder. Take a simple example of calculating a monthly FICA amount at the most basic level – Monthly Salary * FICA %. If either of these amounts equals zero, the FICA amount is going to be zero.

So we take this logic and conclude that, in fact, we only need one feeder – the one most likely to be zero. The FICA percentage is a single number (a lot of companies take them as two – Social Security and Medicare, but for simplicity’s sake we’ll assume that it’s one percentage), provided by the Social Security Administration every year. This percentage will never be zero, so we can rule it out as a possible feeder. The Monthly Salary, however, is a good feeder because unless our company has an employee who loves his job so much that he’s willing to work for free, it should be a pretty good indicator that we need to calculate that employee’s FICA %.

This same logic can be applied to division calculations. If the numerator is zero, the calculation must result in zero. If the denominator is zero, the calculation is illogical because you cannot divide anything by zero, and this is handled by TM1 as a zero.

feeders for addition and subtraction calculations

Feeders for addition and subtraction calculations, while not trickier, are slightly more involved. Take FICA for instance, and now assume that we do prefer to split FICA into its’ two parts – Social Security and Medicare. If we didn’t want to include these as children of a consolidation, we would calculate FICA as: [‘FICA’] = N: [‘Social Security’] + [‘Medicare’];

However, if either Social Security or Medicare were to be zero, this would not guarantee that FICA will be zero for the obvious reason that x + 0 = x. The solution to this, unfortunately for us, is to feed both Social Security and Medicare. The same can be said for subtraction calculations, as x – 0 = x. With this in mind, let’s examine each of the parts of the above rule to see which we should use to feed Monthly Bonus:

• Annual Salary: every employee should have a salary, so if we run across a Monthly Bonus cell belonging to an employee slot that does not have a salary, we can safely assume they will have no monthly bonus since any value multiplied by a zero will result in a zero. But this does not necessarily mean they will have a Monthly Bonus since not all employees receive a bonus, so let’s continue to the other parts of the calculation.
• Percent of Year: we can instantly eliminate this as an option because there will never be a non-zero Percent of Year amount. If we were to use this as a feeder every cell would be fed, negating the entire advantage of feeders.
• Bonus %: assuming every employee who receives a bonus has their own Bonus % populated, this is a great indication that Monthly Bonus for a given employee will need to be calculated. We should use “Bonus %” to feed Monthly Bonus: [‘Bonus %’] => [‘Monthly Bonus’];.

However, another misconception is that a feeder even needs to be a part of the fed calculation. This is not at all true. Remember, you’re just “marking” a fed cell, in the easiest and most efficient way possible.

Say that we need to derive an hourly pay rate from an annual salary:

[‘Hourly Rate’] = N: [‘Annual Salary’] \ DB(‘Calendar’, !Year, !Period, ‘Annual’, ‘Hours’);

Based on our prior analysis of Monthly Bonus, we can eliminate Hours in the Calendar cube as a contender to feed Hourly Rate. We could definitely use Annual Salary to feed these cells.

Say we have another element called FTE. This is basically used to signify a valid employee with a value of 1, and could also be used to calculate part-time employees with a value less than 1 – say, 0.5. But no matter what specific value it is, if it’s greater than zero it signifies a valid employee. And since any valid employee will have some value in Annual Salary, FTE would also be a great feeder for “Hourly Rate”: [‘FTE’] => [‘Hourly Rate’];

At this point you might be asking yourself, “Why wouldn’t I just use ‘Annual Salary’?” Well, in the instance of a single rule such as Hourly Rate, yes it might not make much sense to confuse the situation by using a value outside of the components of the calculation you’re feeding. Consider this very likely situation: you have a number of these elements that you can assume every valid employee will have in all situations. Instead of feeding them with parts of their calculations, which will all be different and obviously dependent upon how they’re calculated, you can potentially feed them all with FTE:

[‘FTE’] => [‘Hourly Rate’],
[‘Social Security’],
[‘Medicare’],
[‘FUTA’],
[‘SUI’];

This example is a purely aesthetic one, but it could potentially help performance in the event you are pulling amounts from another cube, one-for-one with no calculation on the amount. We’ll discuss this further in the following section on inter-cube feeders.

inter-cube feeders and how to avoid them

In my experience inter-cube feeders are the biggest drag on performance and they should be avoided if possible. Continuing our example with FTE, we can use this value to aid us in pulling amounts from another cube. Let’s say our originating cube, PositionInfo, is just a snapshot in time of employees (i.e. it does not have time dimensions) and we need to pull those values into our Headcount cube and apply them to Period and Year. We can feed FTE in the Headcount cube like this:

[‘FTE’] => DB(‘Headcount’, ‘Fcst Years’, ‘Year’, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

It, hopefully, goes without saying that when dealing with inter-cube feeders you’re going to run into situations where your dimensions do not match and you’re forced to feed to consolidations, as we do in this example when we feed to all forecast years in the Year dimension and all months in the Period dimension. Feeding to consolidations is probably the single biggest reason as to why you should avoid inter-cube feeders as much as possible. When you’re feeding to a consolidation such as Fcst Years, you’re actually creating multiple feeders, equal to the amount of elements in the consolidation. To illustrate this, imagine we actually have time dimensions in our originating cube, PositionInfo, we would feed the Headcount cube with one feeder:

[‘FTE’] => DB(‘Headcount’, !Year, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

However, when we feed to Fcst Years, we’re essentially creating the following feeders:

[‘FTE’] =>
DB(‘Headcount’, ‘2011’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),

DB(‘Headcount’, ‘2012’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),

DB(‘Headcount’, ‘2013’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),

DB(‘Headcount’, ‘2014’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

As with feeding each component of a calculation, this creates a huge strain on performance and should be avoided. But it cannot always be avoided, and so you should take actions that minimize the adverse affects. So let us operate under the assumption that if we can feed our great “universal” feeder 'FTE' from the 'PositionInfo' cube to the 'Headcount' cube, we can then create all our other feeders inside the 'Headcount' cube. Here would be our feeder from the 'FTE' element in the 'PositionInfo' cube to the 'FTE' element in the 'Headcount' cube:

[‘FTE’] => DB(‘Headcount’, ‘Fcst Years’, ‘Year’, !Scenario, !Company, !CostCenter, !Location, !Employee, ‘FTE’);

We would then be able to use that newly-fed FTE element in the Headcount cube to feed all of the elements for which we otherwise would have written inter-cube feeders:

[‘FTE’] => [‘Hourly Rate’],[‘Social Security’],[‘Medicare’],[‘FUTA’],[‘SUI’];

conclusion

Hopefully this can help some of you to improve your model’s performance through making your feeders more efficient. We’re never going to completely eliminate the headaches that come with feeders, but we can at least minimize the heartburn that comes with a poorly-performing system.