Allocations pushed to leaf not rolling up into consolidations

[redwin]

I am having issues with my allocations.  I am creating a P&L cube that pulls volume/revenue from 1 cube (topline cube), SG&A from another, Cost from a 3rd and Freight from a fourth.  Most everything is straight forward in the rules to do this.  However, I have written a rule that allocates all my SG&A costs except one cost center across all products.  That works great.  Then for that one cost center I have pushed the cost to one particular part code, which of course is at the N: level.  The cost is showing up correct for the one part code (the 1st allocation plus the forced $'s), but the forced $'s are not rolling up into the consolidations.  I've tried consolidatechildren, but it only worked when I forced the $'s to a consolidation and not at the N: level.  How do I get all the costs to roll up? 

As a side note, I'd prefer to force the $'s at the consolidation and have TM1 push it down to the elements within that consolidation, but don't know how to do that.  The extra trick in this is that the product dimension has 5 different hierarchies, so if I did push the $'s down to the N: level, then it would have to roll up properly in each of the other hierarchies. 

I did a little more reading on this and think it might be because I have no feeders.  Every time I put feeders in the source cube I crash TM1 (it runs out of memory). 

Thank you for any guidance!! 

Drew

[dusherwo]

You _do_ need feeders for any system that's not tiny. If you have skipcheck on and you are not feeding the calculation then the numbers don't consolidate.
Without seeing the details of your rules (might be worth posting them) I can only sketch out what the feeder would look like, but let's try.

If your rule says
['Part Code 1','CC1'] = n: ['Total Costs'];

Then the feeder should be
['CC1','Total Costs'] => ['Part Code 1'];

Feeders are more or less the reverse of rules. Use the Trace Feeder and Check Feeder right click options (need to go to leaf level to enable the former) to see whether everything is working. When the feeder is working, all the consolidations will kick in.

Also - a well written feeder should not blow the server unless it's very near the 2g/3g limit and I doubt yours is.

HTH

[redwin]

Thank you so much for helping me out.  Below is the coding for my P&L Cube

Skipcheck; (in the P&L Cube)

['Planning P&L Measure':'MG&A'] = ['Planning P&L Measure':'Volume']/['All Products','Volume']*
((DB('SG&A - SAP','All Company Codes',!Year,!MM,'All Profit Centers','All Internal Orders','All G/L Accounts','Cost of Goods Sold & Mfg - 7200_1000','Actual (USD)')-DB('SG&A - SAP','All Company Codes',!Year,!MM,'All Profit Centers','All Internal Orders','All G/L Accounts','1509510 - Mfg Colorplus','Actual (USD)'))*-1)
+if(!Product Family @='471131',['CP_Select','Volume']/['Colored','Volume']
* (DB('SG&A - SAP','All Company Codes',!Year,!MM,'All Profit Centers','All Internal Orders','All G/L Accounts','1509510 - Mfg Colorplus','Actual (USD)')*-1),0);

Basically what I am doing is taking what we call MG&A expenses minus 1 cost center and multiplying it by each products % of the total (volume based).  Then I take the 1 cost center I subtracted above and pushed it to 1 particular part code "471131" based on its share of all Colored products.  If I got that to work I was going to add 2 more similar formula's to push the other amount to other colored product lines. 

Feeder that works is

Feeders; (in Topline cube that feeds the volume)
[Measures: 'Volume SQFT'] => DB('P&L JHBP',!Year,!MM,!Mfg Plant,!Sales District,!Product Family,!Customer Info,'Volume');

The feeder that blows the system is

Feeders; (in SG&A cube)
['Actual (USD)']=>DB('P&L JHBP',!Year,!MM,'All Plants','All District Divisions','All Products','All Customers','MG&A');

I think it has something to do with TM1 not liking the missing dimensions. 

There is probably a better way to do this.  Preferably I would like to push that 1 cost center to the "Colored" consolidation within the product hierarchy and have TM1 push it down to all the products below it based on volume, but I am not that skilled.  One other thing to consider is that the product dimension has 5 different hierarchies.  The part codes are the N level and they roll up many different ways.  So whatever I did it would have to roll up through all the different hierarchies.

Sorry for the long and confusing post.  Thank you in advance for any help!!

[dusherwo]

Here goes.

a    The allocation rule should have an n: after the =. This will make the calculations consolidate properly, through all the hierarchies you have. (Almost all rules should use n:. The only exceptions are 'KPI' type ratios where you want a back calc at all levels. C: only rules are _hardly ever_ needed.)
b    You _don't_ need to feed both sides of an allocation calc. It's a matter of judgement and experimentation which is better. Here you seem to have volume data, so try (in the P&L cube)
['Volume'] => ['MG&A'];
and remove your mega feeder from the SG&A cube. Since it's feeding from All Plants/All this/all that/all the other, it is effectively disabling the sparsity optimisation which makes TM1 work well.

Secondary issues/comments:
b    Any + or - calculations in a TM1 rule are better done with hierarchies (which can be + or - as you probably know). Hierarchies don't need feeding and are naturally optimised;
c    Rather than testing Product Family 471131, add an explicit rule
['471131','MG&A'] = n:
before the main rule. The rule engine works down the list of rules, and picks up the first one it finds which applies. Using this can manage size and complexity of rules down.

HTH

[redwin]

Thank you so much for the reply.  This has helped a ton.  Especially the tip on getting ride of the "if" statement and use explicit coding. 

Over the weekend I did work on this and came at it a different way.  I meant to post what I did before today, but ran out of time do to work and school (working on MBA). 

Anyway, what I did was to create another cube called G&A allocation.  In this cube I just had 4 dimensions.  Month, Year, Product Family, and Measures.  In the measures I had a "general Allocation", "C+ allocation", and "Final Allocation".  In the general allocation measure I simply took volume by part code / Total volume * what the general MG&A figure I wanted allocated.  Next, I took the 1 cost center and pushed it to several different part codes by taking the part code and dividing it by a level in the hierarchy and then multiplying it by the 1 cost center.  Next I added the 2 measures together in the "final allocation". 

Once I had the allocation to the part codes right, I brought it into the P&L cube.  I had to further allocate it so I wrote the following rule:

['Planning P&L Measure':'MG&A'] = N:['Volume']\['All Plants','All District Divisions','All Customers','Volume']*DB('G&A Allocation',!Year,!MM,!Product Family,'Final MG&A');

Feeders:
['Planning P&L Measure': 'Volume'] =>['Planning P&L Measure':'MG&A'];

This worked like a charm. 

I do have 1 more question on allocation, but will post it in a new post.  Thank you so much for your help!!

[dusherwo]

Glad to hear it worked. Once you get your head round how rules (and especially feeders) work, it's amazing what you can do.
Good luck!