TM1 Forum

Posted: **Mon Mar 25, 2013 5:21 am**

Hi all,
I am currently constructing a rule for cost centre/account/product allocations. In a nutshell it is simply : amt input by user x product % allocation for the cost centre

Simple enough, but the complication comes because under the reporting methodology, the account mapping changes depending on the allocation. Hence we have a multi-layered GL account structure that looks something like this e.g.

+0916510000 (Base GL)
-0916510000-6A29670 (PreAllocation GL from user input)
-0916510000-6A29670-1639 (Allocation GL for calculation)
-0916510000-6A99999-1639 (Recovery Offset GL for calculation)

(the pre/allocation GLs will then map to other reporting lines depending on their extension)

Code: Select all

['BIZ1 Calc Test'] = N:

# if account is an allocation string (rolls up to Expenses & >18 digits & without recovery extension & function extension matches RC) - then calculate product allocation
If( ELISANC('Account', DB('z_ctrl_measures','Account Type 05','PMF_Rollup'), !Account)=1 & Long(!Account)>18 & Subst(!Account,12,7)@<>'6A99999' 
    & Subst(!Account,20,10)@=Attrs('ResponsibilityCentre',!ResponsibilityCentre,'Function') ,

           DB('Fin_GL',!Version,'BIZ1 Input',!SectionA,!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,'9991',!ProductProcessor,!ResponsibilityCentre,Subst(!Account,1,10),!CurrencyCode,!Period,!BalanceType,!MeasureType)
            * DB('Fin_GLAllocation',!Version,'BIZ1 Input',!Product,!ProductProcessor,!ResponsibilityCentre,'No Account','GENX',!Period,'Proportion')

           , STET);

The corresponding feeder I have tried is:

Code: Select all

# feed input to calc test - limit products using Allocation Products structure + extract first 10 digits of Account
['BIZ1 Input',Product:'9991']=>
     DB('Fin_GL',!Version,'BIZ1 Calc Test',!SectionA,!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,'Allocation Products',!ProductProcessor,
     !ResponsibilityCentre,SUBST(!Account,1,10),!CurrencyCode,!Period,!BalanceType,!MeasureType);

...however it seems to work a bit slow... am wondering does anyone have any ideas how to feed this rule properly without using a conditional feeder? possibly an intermediary lookup cube...?

cheers!

Matt

Posted: **Mon Mar 25, 2013 9:04 am**

Have you checked your model for overfeeding?
It's simple technique with new clone cube and 1 rule IF(<..>=0,1,0)

Posted: **Wed Mar 27, 2013 6:07 am**

thanks for the feedback

...i ended up going with the following as a feeder :

Code: Select all

['BIZ1 Input',Product:'9991'] =>
DB('Fin_GL',!Version,'BIZ1 Calc Alloc',!SectionA,!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,'Allocation Products',!ProductProcessor,!ResponsibilityCentre,
!Account | '-' | Attrs('ResponsibilityCentre',!ResponsibilityCentre,'Function') ,!CurrencyCode,!Period,!BalanceType,!MeasureType) ;

...it works, but seems to have a few niggling performance issues when calculating at top level

TM1 Forum

Improve my feeder! :)

Improve my feeder! :)

Re: Improve my feeder! :)

Re: Improve my feeder! :)