Feeding from a Consolidation to a Leaf Element in TM1
We are working with a client to create a rolling forecast model. As part of this we are using a concept called Planning Accounts. These are aggregates of general ledger accounts so the business can plan at a level that matters, rather than planning for every GL account. We use these aggregates to bring through Actuals as the foundation for the forecast. The consolidation to Planning Account occur in the GL cube and we then need to transfer those rolled up values to the leaf level in the planning cube (note that the dimensionality of both the GL and planning cube are different, otherwise we would have used the GL cube for planning!). So we need a feeder that feeds from the GL cube to the planning cube, i.e. from a consolidated level to a leaf level.
Feeding from a Consolidation to a Leaf
IBM quote the following about feeders:
Unlike rules, FEEDERS only ever apply to leaf level cells and never to consolidated cells. However, consolidated elements can be used in the specification of a FEEDER statement as a shorthand way of specifying all leaf elements within the consolidation. When specifying a consolidated element in a FEEDER statement the following occurs:
- Feeding from a consolidation means all leaf descendants of the consolidation will feed if there is a value present. For example, if the consolidated element contained four leaf elements but only two of these contained a value, then only those two would feed.
- Feeding to a consolidation means that all leaf cells under the consolidation will be fed. For example, if the consolidated element contained four leaf elements then all four-leaf elements would be fed.
Options for Our Feeder
We could write a rule that takes each Planning Account consolidation from the GL cube and target a specific leaf level Planning Account in the planning cube. This feeder might look like the following:
['Actual','Local', 'No Category', 'Value', '41200p'] => DB('Operating Plan', !Year, !Month, 'Actual', 'Local', !Entity, !Branch, !Program, 'Source', '41200p');
Using the feeder above would mean that we need a feeder for every planning account and therefore that we need to add a new feeder every time we add a new planning account – a pain in the proverbial!
There is a better way, that does it dynamically for all Planning Accounts using a consolidation feeder.
What we really want is to design our feeder so that it dynamically works for all Planning Accounts and does not need to be modified when we add a new one. Here we choose on the left a consolidation element that includes every Planning Account. On the right, because in the target cube we are using identical element ID’s for Planning Accounts, we can refer to the source Account dimension and TM1 will look for those specific elements from the source Account dimension in the target. The rule looks like this:
['Actual', 'Local', 'No Category', 'Value', 'Planning Accounts'] => DB('Operating Plan', !Year, !Month, 'Actual', 'Local', !Entity, !Branch, !Program, 'Source', !Account);
Going back to the IBM definitions above, this consolidation feeder will feed every leaf element of the ‘Planning Accounts’ element in the source to the specific matching elements in the target cube, where the element is identical to the element used in the ‘Account’ dimension.