Skipcheck Feeders in TM1
If you want to understand skipcheck and feeders, you need to learn the whole concept in detail.
NOTE: Please remember that in example that is being used in this tutorial, I’ve kept the simple cube for clarity and have ignored irrelevant details (for this concept) such as “Price at consolidated elements should be weighted average instead of simple roll up”.
What are Skipcheck Feeders in TM1
Let’s learn the Whole Story:
TM1 can handle very large cube sizes. In TM1, leaf level data (the data that users input) is stored in cube files whereas the rule based (e.g. Sale = Units * Price) and consolidated data (e.g. Q1, Q2, Year) is just kept in memory.
If a cube is just an input cube and it doesn’t contain any rules TM1 uses the fast consolidation algorithm to do the consolidation in the cube. Fast consolidation algorithm ignores the consolidation of cells which contain zero values. So if Q1 is consolidation of (Jan, Feb, Mar), it will check values of Jan, Feb and Mar; and if all three are zero, it will not put any values in Q1. It will simply ignore it. For a sparse cube this results in tremendous performance improvements.
For example: In below diagram, the fast consolidation engine just had to consolidate 6 cells
[‘1 Quarter’,’Units’], [‘1 Quarter’,’Price’], [‘1 Quarter’,’Sales’], [‘Year’,’Units’], [‘Year’,’Price’], [‘Year’,’Sales’]
Fast consolidation engine ignores ‘2 Quarter’ ‘3 Quarter’ and ‘4 Quarter’ because values in detail level cells (Apr, May, Jun etc.) of these rollups are zeros.
Because of this, it didn’t have to do any consolidation for 9 cells: (Units, Price and Sales X Q2, Q2,Q3)
In large sparse cubes, this approach results in hundreds of thousands of cells and results in better performance.
Now see what happens when you write a rule in a cube:
When you write a rule (any simple rule), TM1 turns this fast consolidation engine off. That means TM1 no longer ignores zero cells while doing consolidation. While processing the cube, it will check ALL cells and will perform consolidation on all of them.
So if we take above example, it will now have to perform 15 consolidations:
‘1 Quarter’ ‘2 Quarter’ ‘3 Quarter’ ‘4 Quarter’ ‘Year’ values of ‘Units’, ‘Price’, ‘Sales’ (5X3=15)
This deteriorates the performance in sparse cubes. The reason simply being that sparse cubes contain a huge number of zero cells and TM1 will check all cells to do consolidation even though it is not necessarily required for all of them.
What should we do to correct the issue?
Now the first logical thought that comes to mind is, why the hell turn the fast consolidation algorithm OFF? Let’s keep it ON.
Okay, point taken. So We’ll use a keywork SKIPCHECK for that. You have to put it in the beginning of the rule file. If you do so, TM1 will turn the fast consolidation algorithm (FCA) back ON. But doing so will not solve our problems. Because now, here is what will happen:
1. To find the value of [‘1 Quarter’, ‘Sales’], FCA will check the hierarchy of Months dimension. It will check values of [‘Jan’, ‘Sales’], [‘Feb’, ‘Sales’] and [‘Mar’, ‘Sales’].
2. However you must know that TM1 doesn’t save the values of calculated cells in cube file, it just saves them in memory. So although [‘Jan’, ‘Quantity’] and [‘Jan’, ‘Price’] do exist in cube, [‘Jan’, ‘Sales’] remains 0 in cube file and, is calculated and shown every time someone opens the cube.
3. Because of this when FCA checks values of [‘Jan’, ‘Sales’], [‘Feb’, ‘Sales’],[‘Mar’, ‘Sales’] and finds them to be zero; it ignores the value for [‘1 Quarter’, ‘Sales’] and shows 0. Similar thing happens with [‘Year’, ‘Sales’] values.
Now although we’re using the powers of fast consolidation engine, we have incorrect values in so many consolidations.
Here come the Skipcheck Feeders
Only if we could tell it which rule-based-cells are producing values!
Our problem lies in the fact that FCA takes the rule calculated cells as zero while doing the consolidation. If we could somehow check which rule based cells will produce non-zero values and tell FCA ‘that’, FCA will correctly do the consolidation for those cells.
So here comes what we’ve been trying to learn: FEEDERS
You can use Feeders statement to do that.
Let’s understand it using above example:
Our problem will get resolved if we could find those rule based cells which will produce values (will be non-zero) and tell FCA that. E.g. if we could just find those ‘Sales’ values which are non-zero and tell FCA to take only those values into consideration for consolidation. Here is how we’ll do that:
[‘Units’] => [‘Sales’];
We use Feeders (followed by semicolon) statement to say that we’re specifying rules for FCA.
By using the above other statement we’re saying that Sales value depends on Units. If Units is zero, Sales will be zero and if Units is non-zero, Sales will be non-zero. So please consolidate those Sales values for which Units are non-zero and ignore the others (ones for which units are zero).
In this scenario, FCA will consider Sales-Jan, Sales-Feb and Sales-Mar as non-zero values (because their Units values are non-zero) and will consolidate 1Quarter correctly this time. Furthermore, it will ignore ‘2 Quarter’, ‘3 Quarter’ and ‘4 Quarter’ consolidations because units in all respective 9 months (Apr, May, Jun etc.) are zero so Sales will be zero and so no consolidation for 3 quarters. Doing so will improve the performance because now we’re consolidating only those cells which DO require consolidation (total 6 cells)
Search Terms which could be helpful to find this article:
what is the point of a feeder in tm1
What are skipcheck and feeders
Skipcheck and feeders in tm1
what are feeders
why should i use skipcheck and feeders