Skip to Content
author's profile photo Former Member
Former Member

Memberset to pass 400+ Accounts in an allocation

Hi Experts,

I have an allocation where I need to allocate 400+ Accounts down to it's own base members, is this possible? Currently I had setup the property ALLOC_TAG and have the following values: DUMMY for dummy accounts which will be allocated to the members under the same parent in which the dummy account is in.

Example:

ACCOUNT				ALLOC_TAG
Account A1
	Sub Account A1		Dummy Account A1
	Sub Account A2		Dummy Account A1
	Sub Account A3		Dummy Account A1
	Dummy Account A1	Dummy
Account B1
	Sub Account B1		Dummy Account B1
	Sub Account B2		Dummy Account B1
	Sub Account B3		Dummy Account B1
	Dummy Account B1	        Dummy
Account C1
	Sub Account C1		Dummy Account C1
	Sub Account C2		Dummy Account C1
	Sub Account C3		Dummy Account C1
	Dummy Account C1	        Dummy

Using this setup I created the following code:

*XDIM_MEMBERSET ACCOUNT=%ACCOUNT_SET%

*RUNALLOCATION

*FACTOR=USING/TOTAL

*DIM COPAACCOUNT	WHAT=%ACCOUNT_SET%;		WHERE=[ALLOC_TAG]='%ACCOUNT_SET%';	USING=<<<;		TOTAL=<<<;
*DIM CUSTOMER		WHAT=BAS(EXPORT_CUST);		WHERE=BAS(EXPORT_CUST);			USING=<<<;		TOTAL=<<<;
*DIM DATASRC		WHAT=11_INPT_ADJ;		WHERE=11_ALLOC;				USING=11_INPT_ADJ;	TOTAL=<<<;

*ENDALLOCATION

*COMMIT

Using this logic users will select an Account in the data manager which is tagged as "Dummy" which will be passed to %ACCOUNT_SET%. This code is working fine aside from the fact that the user's will have to run the package 400+ times using this setup. I was hoping if there is a way I can load all the 400+ Accounts and allocate them in their specific Sub Accounts.

I have already tried revising the code so user's can select all of the Accounts tagged as "Dummy" but I get zero amounts in my allocation results. And also it takes too long to run. Here's my revised code.

*XDIM_MEMBERSET ACCOUNT=%ACCOUNT_SET%

*PROCESS_EACH_MEMBER=ACCOUNT

*RUNALLOCATION

*FACTOR=USING/TOTAL

*DIM COPAACCOUNT	WHAT=%ACCOUNT_SET%;		WHERE=[ALLOC_TAG]='%ACCOUNT_SET%';	USING=<<<;		TOTAL=<<<;
*DIM CUSTOMER		WHAT=BAS(EXPORT_CUST);		WHERE=BAS(EXPORT_CUST);			USING=<<<;		TOTAL=<<<;
*DIM DATASRC		WHAT=11_INPT_ADJ;		WHERE=11_ALLOC;				USING=11_INPT_ADJ;	TOTAL=<<<;

*ENDALLOCATION

*COMMIT

Thanks,

Marvin

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 13, 2010 at 08:41 AM

    Hi Marvin,

    I would suggest a little different approach. If you maintain your dimension something like below:

    ACCOUNT				ALLOC_TAG
    Account A1
    	Sub Account A1		Account A1
    	Sub Account A2		Account A1
    	Sub Account A3		Account A1
    	Dummy Account A1	Dummy
    Account B1
    	Sub Account B1		Account B1
    	Sub Account B2		Account B1
    	Sub Account B3		Account B1
    	Dummy Account B1        Dummy
    Account C1
    	Sub Account C1		Account C1
    	Sub Account C2		Account C1
    	Sub Account C3		Account C1
    	Dummy Account C1	Dummy

    Now, change your code to:

    *SELECT(%TBALLOCATED%, "ID", "COPAACCOUNT", "[ALLOC_TAG] = 'DUMMY'"
    *FOR %MY_VAR% = %TBALLOCATED%
    
    *RUNALLOCATION
    *FACTOR=USING/TOTAL
    
       *DIM COPAACCOUNT	WHAT=%TBALLOCATED%;		WHERE=[ALLOC_TAG]='%TBALLOCATED%.PARENTH1';	USING=<<<;		TOTAL=<<<;
       *DIM CUSTOMER	WHAT=BAS(EXPORT_CUST);		WHERE=BAS(EXPORT_CUST);			USING=<<<;		TOTAL=<<<;
       *DIM DATASRC		WHAT=11_INPT_ADJ;		WHERE=11_ALLOC;				USING=11_INPT_ADJ;	TOTAL=<<<;
    
    *ENDALLOCATION
    *NEXT
    
    *WHEN COPAACCOUNT.ALLOC_TAG
    *IS "DUMMY"
    *REC(EXPRESSION = 0)
    *ENDWHEN
    
    *COMMIT

    Hope this helps.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi nilanjan,

      Thanks for the reply. I've tried your suggestion and it works but the Validate and Save of logic takes a lot of time since aside from the Accounts, I have other dimensions used in my allocation that are also in FOR/NEXT loop. The total runtime of package is also a bit long so we're looking for another way of loading all those accounts. It already exceeded 2 hours so we aborted the package.

      Thanks,

      Marvin

  • author's profile photo Former Member
    Former Member
    Posted on May 12, 2011 at 01:42 AM

    Just used a custom stored procedure for the allocation. Allocation with this much of members takes hours via script logic while the stored proc take about 15mins or less.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.