TM1 Forum

Hi, I have a Problem where I am trying to get around for some time and now hoping for some assistance from this Group.

Here the Business Situation:
I have a cube where I am loading the Revenue and direct cost by customer Level where the cost is directly assigned to the customer. I have then also further cost, which needs to be allocated to the customers. To do this, I am loading the total cost by GL account and cost Center, on a source code which is outside of the "All_Source" consolidation. I have then written a rule, which calculates the difference of the total cost by cost center and GL account and total cost by customer, cost center and GL account and then allocates this difference by the customer revenue splits in the cost center ( for example the difference of 100 will be allocated to customer 1 with 60, as he makes 60% of the revenue in the cost center and the customer 2 with 40%)

Here is the rule and the feeders, the rule has 2 parts - the 2nd part is for situations where there is no revenue. Also, the customer revenue share is calculated on a specific Source type (NR) because, here I uploaded only positive revenues to do the splitting on positive revenue only)


# Allocation rule part

[ 'SOURCE':'ALO'] = N: IF(ELISANC('ACCT', !ACCT, '5999 - TOTAL DIRECT COST')=0,
( DB('GPC_1', 'OTH', 'PS', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, !ACCT, !PROD, '9999999999') -
DB('GPC_1', 'ALL_CUST_TYP', 'HEX', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, !ACCT, !PROD, 'ALL_CUSTOMER'))
*
(
DB('GPC_1', !CUST_TYP, 'NR', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, 'STAFFING / INTERIM REVENUE', 'ALL_PROD', !CUST ) \
DB('GPC_1', 'ALL_CUST_TYP', 'NR' , !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, 'STAFFING / INTERIM REVENUE', 'ALL_PROD', 'ALL_CUSTOMER' )
), continue );

[ 'SOURCE':'ALC','9999999999'] = N: IF(ELISANC('ACCT', !ACCT, '5999 - TOTAL DIRECT COST')=0,
IF( DB('GPC_1', 'ALL_CUST_TYP' , 'NR', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, 'STAFFING / INTERIM REVENUE', 'ALL_PROD', 'All_CUSTOMER' )=0,

(DB('GPC_1', !CUST_TYP, 'PS', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, !ACCT, !PROD, '9999999999' ) -
DB('GPC_1', !CUST_TYP, 'HEX', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, !ACCT, !PROD, 'ALL_CUSTOMER' )) ,0) ,0);

FEEDERS;

['STAFFING / INTERIM REVENUE'] => DB('GPC_1', !CUST_TYP, 'ALO', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, '5999 - TOTAL DIRECT COSTS', 'ALL_PROD', !CUST );

[ 'HR_FED'] => DB(IF( DB('GPC_1', 'ALL_CUST_TYP' , 'ALL_SOURCE', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, 'STAFFING / INTERIM REVENUE', !PROD, 'All_CUSTOMER' )<>0, '',
IF( (DB('GPC_1', !CUST_TYP, 'PS', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, !ACCT, !PROD, '9999999999') -
DB('GPC_1', !CUST_TYP, 'HEX', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, !ACCT, !PROD, 'ALL_CUSTOMER' )) =0, '',
'GPC_1')), !CUST_TYP, 'ALC', !BUSINESS_UNIT, !YEAR, !PERIODE, 'MP', !DEPT, '5999 - TOTAL DIRECT COSTS', !PROD, '9999999999' );

The rule works fine and the results are as expected, however as you can see I had to use a consolidation element in the feeder and this makes the cube quite big and slow. I have worked some time on improving the rule, even tried conditional feeder as you can see in the 2nd part, but with no major improvement.

Because of this, I then tried to go from rule to TI.

Again, I managed to get a result by using this calculation in the TI. See here the part for the 1st rule part:

vSource='ALO';
vDiff=cellgetn('GPC_1','OTH', 'PS', BUSINESS_UNIT, V4, PERIODE, Business_Typ, DEPT, ACCT, PROD, '9999999999') - cellgetn('GPC_1','ALL_CUST_TYP', 'HEX', BUSINESS_UNIT, V4, PERIODE, Business_Typ, DEPT, ACCT, PROD, 'ALL_CUSTOMER');

vSpread=cellgetn('GPC_1',CUST_TYP, 'NR', BUSINESS_UNIT, V4, PERIODE, Business_Typ, DEPT, 'STAFFING / INTERIM REVENUE', 'ALL_PROD', CUST) / cellgetn('GPC_1','ALL_CUST_TYP', 'NR', BUSINESS_UNIT, V4, PERIODE, Business_Typ, DEPT, 'STAFFING / INTERIM REVENUE', 'ALL_PROD', 'ALL_CUSTOMER') ;
vValue=vDiff * vSpread;

CellPutN(vValue,'GPC_1',CUST_TYP,vSource,BUSINESS_UNIT,V4,PERIODE,BUSINESS_TYP,DEPT,ACCT,PROD,CUST);

When I am running this as a test on a close range of test data all works fine, when I am running this on a bigger range, the TI runs forever. I am guessing I need to build in some looping, so that the code goes thru the data and not pulls the data in a view and then working up the calculations, but I am not sure and not really familiar with building a code which loops thru the code.

Any helps you guys can give me on this, either rule or TI, is greatly appreciated.

If my details are confusing and you need further explanations, please advise

Regards Manfred

I don't have the bandwidth to look at your code in depth but I can tell you that a TI process with a bunch of CellGets and CellPuts to the same cube is going to be really slow, especially when the CellGet is asking for a consolidated node that could have been updated by a previous CellPut. Bad idea.

Are you aware that there is a TI function for spreading called CellPutProportionalSpread that can be used for simple allocations? For example, if you are allocating something along revenue then you have two TI processes. In the first process you load revenue to the elements which will later hold the allocated cost(s). Then in the second process you call the CellPutProportionalSpread to write the amount you want allocated at the top level nodes. It will then take that cost and in each child, replace the revenue you loaded in the first process with a cost equal to the total you want allocated times that childs percent of total revenue. In your case all you have to do is figure out the amount you want to allocated and pass that to CellPutProportionalSpread function. I have used this strategy on a number of occasions and it is a lot faster than what you are trying to do.

Hi Tom,
Thanks for your reply.
I must say, I am not fully understand your info about the approach.
I do have the value which needs to be spread and I also have the split as it is based on the revenue as described above.

What I am not understand, is how the function proportionalspread can spread my cost value to cost accounts, when the split is loaded on revenue accounts.

For example, I have to split a cost of 1000.00 among all customers and the split is the revenue of the customers. But my understanding of the function is that it replaces the values of the leaf elements with the value to spread????

As I said, I am struggling to understand this.

Thanks for any assistance......Manfred

Gruss Manne,

If the revenue data dictates how the cost data should be allocated to customers then you can use it as the "pattern" on which to spread the cost data. The easiest way to do this would be a 2-step process:
step 1 - TI process to copy revenue account data to cost account for the leaf cells (maybe *-1 depending on signage of revenue vs. cost data)
step 2 - At total customer level do a CellPutProportionalSpread of the real cost onto the area of the cost account currently populated with revenue (this could even be on the epilog of 1st process.)

If going the TI route this is what I would do. It's also exactly the same advice that Tom already gave you.

Don't know how much relevant to you issue,Please have a look at it:
http://www.tm1forum.com/viewtopic.php?f=3&t=3582

I have worked a bit with your advises and many thanks for this.

I created a TI which copies the revenue info I want to use as a split to the cost accounts - however, I have difficulties to only copy them to these combination where I need to do a spreading. Any advise, how to approach this best ?

I also created then a process to do the CellPutProportionalSpread and this worked fine, in the combinations where the above issue was not a problem.

So I think, I need to work out the 1st TI and then should be nearly there with the suggested solutions.

However, I still believe I will have a issue then. The cost I need to be spread is the difference between the cost which is already assigned to a customer , cost account, product code and cost center combination and the total of the cost account, product code and cost center combination (unallocated cost) and sometimes I have unallocated cost where there is no revenue to base the spreading on and so, the cost needs to be assigned to a dummy customer (see my 2nd part of the rule) - any advise how to solve this ?

Many thanks....Manfred

Looking back through this thread I see:

The rule works fine and the results are as expected, however as you can see I had to use a consolidation element in the feeder and this makes the cube quite big and slow. I have worked some time on improving the rule, even tried conditional feeder as you can see in the 2nd part, but with no major improvement.

Because of this, I then tried to go from rule to TI.

Donning my asbestos hoodie, I'm going to assert that rules are fundamentally preferable to TIs for calculating allocations. Yes, the feeders can get large, but you can (and should) conditionalise them. For reporting, take the results of the rules and TI them to another cube. The rules give you traceability and the reporting cube gives you performance.

Let's see what comes back....

There is more detail explaining of the allocation by David here:
http://www.cognoise.com/index.php?topic=5778.0

Gosh...
I wrote that six years ago....