Hi all,
We have this allocation model that was built a while ago. Since I have some spare time I decided to do a small review of this model. There are massive amounts of overfeeding in this cube which bothers me. But I cant really think of a good way to get around it.
Basically we have 2 cubes. The first one gets a value loaded into it from accounting. We then want to re-allocates this for our operational follow up. We do this using keys in a separate cube.
AllocaitonCalcCube Rules:
['AllocatedMeasure'] = N:
DB('AllocationCalcCube', !Account, !Period, 'AllCountries', 'AllProfitCenters', 'GLValueMeasure')
* DB('AllocationKeyCube', !Period, !Country, !ProfitCenter, 'KeyMeasure' ) ;
FEEDERS;
['GLValueMeasure'] => ['AllocatedMeasure', 'AllCountries', 'AllProfitCenters'] ;
The problem with the above is that only half of the countries and profitcenters has a key in it. So I feed all these countries and profitcenters that don't have values.
Here is what Im currently thinking:
1, ['GLValue'] => could be changed to ['GLValue', 'AllCountries', 'AllProfitCenters'] This would mean feeding from a consolidation. But since I specify the targets it should work. This wouldn't really fix overfeeding since Im still feeding as many cells. But maybe there is a performance gain to not having the feeder refire if any of the countries or underlying profitcenters are changed. As long as the consolidation is <> 0.
2, Pulling the key to the allocation cube and use it as a feeder. So in the allocation cube I would have:
['FeedKey'] = N:
IF(
DB('AllocationCalcCube', !Account, !Period, 'AllCountries', 'AllProfitCenters', 'GLValueMeasure') <> 0,
DB('AllocationKeyCube', !Period, !Country, !ProfitCenter, 'KeyMeasure'),
STET ) ;
But then I would just add a feedkey measure and need to figure out how to feed that one. Also I would be multiplying a lot of data from the key cube to the allocation cube. So probably not a good idea.
3, Doing this with TI-processes
If the performance was really bad this is always the go-to. But not really required at this moment. Also some of the keys are manual inputs that are tweeked. The users do want instant feedback on the allocated results.
4, Reducing the targets
Im creating a routine to clear out old an unused profitcenters and countries from the dimensions. This will decrease the overfeeding a bit.
This seems like something that would come up quite often in TM1 development. So Im hoping someone in the community out there has a good idea on how to tackle this. And I know you're thnking "Why doesn't the guy just test it all". Well I dont have a test environment... its not my decision. So testing different feeders will require a lot of restarts of the production server. Figured I would ask you guys first to avoid downtime. And a good answer to this might be worth sharing in the community. Hopefully Im missing something totally obvious.
Thanks
Dave
Feeder problem in allocation model
-
- MVP
- Posts: 2834
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Feeder problem in allocation model
Why do you feel you need to pull the key value into your allocation cube? Can't you just feed the allocation cube from the key cube directly? I don't know your dimensionality situation but that's the way I would approach it, if possible.Bakkone wrote:2, Pulling the key to the allocation cube and use it as a feeder. So in the allocation cube I would have:
['FeedKey'] = N:
IF(
DB('AllocationCalcCube', !Account, !Period, 'AllCountries', 'AllProfitCenters', 'GLValueMeasure') <> 0,
DB('AllocationKeyCube', !Period, !Country, !ProfitCenter, 'KeyMeasure'),
STET ) ;
But then I would just add a feedkey measure and need to figure out how to feed that one. Also I would be multiplying a lot of data from the key cube to the allocation cube. So probably not a good idea.
-
- Posts: 119
- Joined: Mon Oct 27, 2014 10:50 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Feeder problem in allocation model
The allocation cube has the account dimension in it. Id rather not feed all the accounts.
Cube 1: AllocationCalcCube
Dimensions: Account, Period, Country, ProfitCenter, Measure
Cube 2: AllocationKeyCube
Dimensions: Period, Country, ProfitCenter, Measure
Cube 1: AllocationCalcCube
Dimensions: Account, Period, Country, ProfitCenter, Measure
Cube 2: AllocationKeyCube
Dimensions: Period, Country, ProfitCenter, Measure
-
- Community Contributor
- Posts: 103
- Joined: Mon Sep 05, 2011 11:04 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2010
Re: Feeder problem in allocation model
This wouldn't actually make a difference. Feeding always takes place from the N level. Specifying a consolidation on the left hand side of a feeder is just a way of saying 'feed from all children of this consolidation'. Assuming that all country elements and all profit centers are children of the c-level elements mentioned, then the resulting feeders would be exactly the same whether you include the c-levels or not.1, ['GLValue'] => could be changed to ['GLValue', 'AllCountries', 'AllProfitCenters'] This would mean feeding from a consolidation. But since I specify the targets it should work. This wouldn't really fix overfeeding since Im still feeding as many cells. But maybe there is a performance gain to not having the feeder refire if any of the countries or underlying profitcenters are changed. As long as the consolidation is <> 0.
Unfortunately, your example is all too common in TM1, and in my opinion one of the weaknesses of TM1. One to many relationships through driver based allocations are basically impossible to do without overfeeding if the driver is has fewer dimensions than the target. It's annoying as it's quite easy for a human to logically determine which cells need feeding, but there's no way to tell the system. The only solution sometimes is changing to a TI process, which I don't like as it removes the real time aspect, makes what-if analysis harder, and usually ends up turning what could be a simple requirement into something with more moving parts than it needs. If IBM were to look outwards a little more, they would see that there is other very similar software which has solved this issue already!
- Steve Rowe
- Site Admin
- Posts: 2439
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Feeder problem in allocation model
Yup this is a common problem.
Would be great if we could write half a feeder
['A']==>['C'];
['B']==>['C'];
C is fed if A and B are populated, otherwise not fed.
Would be great if we could write half a feeder
['A']==>['C'];
['B']==>['C'];
C is fed if A and B are populated, otherwise not fed.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 119
- Joined: Mon Oct 27, 2014 10:50 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Feeder problem in allocation model
Thanks for the feedback and for calling me out on my idea that would have made it even worse