Dec 1, 2017

How to find over feeding in your TM1 model

Feeders are a crucial part of IBM TM1/Planning Analytics, giving us ad-hoc rule calculations without loss of performance or requiring pre-calculations of results. However, getting feeders right takes a good understanding of the model and how values influence a calculation.

How to check if a value is fed?

The first hurdle when working with feeders, is making sure that every calculation that results in a value is fed. This is so important, as in TM1 only fed cells roll up in a consolidation. If a system is underfed, it means that you most likely have missing values when looking at aggregations. Luckily, TM1 has a tool to check for missing feeders, which can be accessed by right clicking on a cell and select “Check feeders”. Any cell not being fed will show up and you can then work on fixing it.

How to find overfed cells?

However, the opposite result is overfeeding a system. In this case, rule based cells that result in a zero value are flagged with a feeder. While a small amount of overfeeding might not have an impact, large cubes with a lot of overfed cells will result in much slower end user performance, as the consolidation engine has to check the cells only to find out that the result is zero and does not have an impact on the overall consolidation.

In order to assess, how many cells are overfed and which feeder statement is the likely candidate, you can apply the following simple trick.

For the cube you want to analyse, create a shadow cube with the same dimensionality. In our example, we work with the standard demo model from IBM and analyse the SalesCube. The shadow cube we have created is called SalesCube – Overfeeding.

For the SalesCube – Overfeeding, create a new rule file and add three statements to it.

SKIPCHECK;[] = N: IF(        DB('SalesCube',!actvsbud,!region,!model,!account1,!month) = 0        ,1        ,0);FEEDERS;

The last step in our preparation is to add one additional feeder to the initial SalesCube pointing to our SalesCube – Overfeeding.

[] => DB('SalesCube - Overfeeding',!actvsbud,!region,!model,!account1,!month);

Once this is completed you can open the SalesCube – Overfeeding and browse the data:

Any cell showing up with a 1 is overfed and a candidate to be fixed.

The idea behind this trick is to check if a cell in SalesCube – Overfeeding is fed even though the value in the same intersection in SalesCube equals 0. 
If the value is fed in SalesCube – Overfeeding, the value at a consolidation level will be equal to 1. In this case a cell which has the value equals to 0 in SalesCube sends a feeder flag to a cell in SalesCube – Overfeeding cube, meaning that the cell in SalesCube is fed even though the value equals 0.

How to fix it?

To understand why this cell is overfed we need to drill down to the lowest level:

We can see that Gross Margin % has a 1 in the SalesCube – Overfeeding even though the Gross Margin% is equal to 0 in SalesCube. If we have a look at the rule:

['Gross Margin%'] = ['Gross Margin']  ['Sales'] * 100;

Instead of being fed by Sales or Gross Margin, Gross Margin% is fed by Units:

['Units'] => ['Gross Margin%'];

In this scenario Gross Margin % is overfed because it is fed by Units. Even though Gross Margin % equals 0, it is still fed because Units equals 10 in Feb.

Use feeder-less rules

Feeders can take lots of memory if you work with large cubes. In this scenario where Gross Margin % is always a calculation for N and C levels, you can get rid of the feeder by adding Gross Margin as a children of Gross Margin %:

Now Gross Margin % is a consolidation and will be “fed” when Gross Margin has a value without having to write a feeder. Using this method to remove feeders will speed up the TM1 server startup time and reduce the size of the cubes or feeders files.


Related content

Loading related content