Feeder problem in allocation model

Post Reply
Bakkone
Posts: 119
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Feeder problem in allocation model

Post by Bakkone »

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
tomok
MVP
Posts: 2831
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

Post by tomok »

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.
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Bakkone
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

Post by Bakkone »

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
whitej_d
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

Post by whitej_d »

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.
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.

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!
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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.
Technical Director
www.infocat.co.uk
Bakkone
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

Post by Bakkone »

Thanks for the feedback and for calling me out on my idea that would have made it even worse :)
Post Reply