0

# Using script logic to multiply data at 2 different level and write back to detail level

Mar 06, 2017 at 05:28 AM

178

Former Member

Hi All,

I am hoping for some assistance with a script logic calculation that I am working on. Scenario is : We have a Work Force Planning model which is used to calculate the Employee Cost at a detail level during the budget process.

For the Forecast, the basic idea is to flex the Employee cost by multiplying it with a % increase which is input at a higher level than what the employee cost is at.

i.e. The % increase is input at a valid Cost Centre and Functional area only (as seen in the attached snapshot)

I need to Multiply A * B to get C

The destination record C needs to get its Cost Centre and Functional Area from B and rest of the dimension members from the A record.

Currently I am using 2 for loops to achieve this as seen in the code. This is throwing up a out of memory error (which i expected to)

BPC version is 10.1 NW

Quite sure Allocation wont be an option due to multiple source members.

Would appreciate some suggestions / pointers

//Scope the TIME horizon
*SELECT(%TIME_HOR001%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'") //- 2017.Jan to 2017.Dec
*SELECT(%TIME_HOR002%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'") //- 2018.Jan to 2018.Dec

*SELECT(%A_TYPE%,ID,ACT_TYPE,"IS_SELECTABLE = 'Y'")  //- 1011,2011,3011 etc
*SELECT(%CC_R%,ID,COSTCENTRE,"CCATEGY = 'NSP','CSP','AM','AO'") //-CC_1,CC_2,CC_3 .. CC_1300
*SELECT(%FUNC_A%,ID,FUNC_AREA,"CALC = 'N'") //- AC0054,AB0052 etc

//Scope dimension members
*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET AUDITID = CALC_LABOUR,GLB_FCT_PERC
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET ACCOUNT = BAS(OC_TOT_EMP_COST),SKF_FCT_PERC
*XDIM_MEMBERSET ACT_TYPE = %A_TYPE%,NO_ACTTYPE
*XDIM_MEMBERSET CC_SENDER = BAS(CS_ALLCC)
*XDIM_MEMBERSET EMPLOYEE = BAS(ALL_EMP),NO_EMPLOYEE
*XDIM_MEMBERSET POSITIONS = BAS(TOT_POS),NO_POS
*XDIM_MEMBERSET COSTCENTRE = CC_NONE,%CC_R%
*XDIM_MEMBERSET FUNC_AREA = NO_FA,%FUNC_A%

// =  =  =  =  =
*FOR %C% = %CC_R%
*FOR %F% = %FUNC_A%
*WHEN AUDITID
*IS CALC_LABOUR
*WHEN ACCOUNT
*IS <> SKF_FCT_PERC
*WHEN ACT_TYPE
*IS %A_TYPE%
*WHEN EMPLOYEE
*IS <> NO_EMPLOYEE
*WHEN POSITIONS
*IS NO_POS
*WHEN COSTCENTRE
*IS CC_NONE
*WHEN FUNC_AREA
*IS <> NO_FA
*REC(EXPRESSION = %VALUE%*([AUDITID].[GLB_FCT_PERC],[ACCOUNT].[SKF_FCT_PERC],[ACT_TYPE].[NO_ACTTYPE],[EMPLOYEE].[NO_EMPLOYEE],[COSTCENTRE].[%C%],[FUNC_AREA].[%F%],[POSITIONS].[NO_POS]), COSTCENTRE = %C%,FUNC_AREA = %F%,AUDITID = FCST_INTERIM_1)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*NEXT
*NEXT
working.png
working.png (137.6 kB)

Vadim Kalinin Mar 06, 2017 at 10:47 AM
1

"I believe Many to Many allocation isnt supported in BPC NW" - wrong! Search better :)

Share
Vadim Kalinin Mar 06, 2017 at 07:36 AM
0

Not easy to understand but I will try:

PERCENT is entered:

For each valid combination of COSTCENTRE and FUNC_AREA

with:

AUDITID=GLB_FCT_PERC
ACCOUNT=SKF_FCT_PERC
ACT_TYPE=NO_ACTTYPE
EMPLOYEE=NO_EMPLOYEE
POSITIONS=NO_POS

How many COSTCENTRE and FUNC_AREA ID's you have?

.

P.S.

Common dimensions are:

*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET CC_SENDER = BAS(CS_ALLCC)

Data to be multiplied:

AUDITID = CALC_LABOUR
ACCOUNT = BAS(OC_TOT_EMP_COST)
ACT_TYPE = %A_TYPE%
EMPLOYEE = BAS(ALL_EMP)
POSITIONS = BAS(TOT_POS)

Is it correct?

Share
Vadim Kalinin Mar 06, 2017 at 08:52 AM
0

2800*300=840000 - for sure you will get out of memory!

Try allocation!

Share
Former Member Mar 06, 2017 at 08:42 AM
0

Sorry if I wasnt clear but you are spot on till here.

Valid CC's = 2800

Func Areas = 300

Thank you very much for assisting on this.

(I have tried with the 2 for loops and using *XDIM_MAXMEMBERS = 10 but I still get memory errors)

Regards,

Nilesh D

Share
Former Member Mar 06, 2017 at 09:11 AM
0

Using allocations I would have to use multiple for loops for EMPLOYEE,POSITIONS,ACT_TYPE,ACCOUNT since there will be multiple members in WHAT region with 1 member in the USING region.

Am I on the correct lines?

Thanks for the pointers on this.

Regards,

nilesh D

Show 1 Share

No! You have

*DIM_NONAGGR instead of *DIM for this case! No FOR/NEXT loops!

Vadim Kalinin Mar 06, 2017 at 09:16 AM
0

Also not clear: I see in your code:

*WHEN COSTCENTRE
*IS CC_NONE

Is the data to be multiplied is in CC_NONE?

Can you provide a report with data to be multiplied and percent?

Share
Former Member Mar 06, 2017 at 09:28 AM
0

working.png

I have attached a screen shot.

Section A is my employee cost

Section B is the % to be multiplied with

Section C is the destination slice

Hope this helps.

Regards,

Nilesh D

working.png (137.6 kB)
Share
Vadim Kalinin Mar 06, 2017 at 09:37 AM
0

Something like:

*SELECT(%TIME_HOR001%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'") //- 2017.Jan to 2017.Dec
*SELECT(%TIME_HOR002%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'") //- 2018.Jan to 2018.Dec
*SELECT(%A_TYPE%,ID,ACT_TYPE,"IS_SELECTABLE = 'Y'")  //- 1011,2011,3011 etc
*SELECT(%CC_R%,ID,COSTCENTRE,"CCATEGY = 'NSP','CSP','AM','AO'") //-CC_1,CC_2,CC_3 .. CC_1300
*SELECT(%FUNC_A%,ID,FUNC_AREA,CALC=N AND ID<>NO_FA) //- AC0054,AB0052 etc BUT NOT NO_FA!!!!!!!!

*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET CC_SENDER = BAS(CS_ALLCC)

*RUNALLOCATION
*FACTOR=USING
*DIM COSTCENTRE WHAT=CC_NONE; WHERE=%CC_R%; USING=%CC_R%
*DIM FUNC_AREA WHAT=NO_FA; WHERE=%FUNC_A%; USING=%FUNC_A%
*DIM AUDITID WHAT=CALC_LABOUR; WHERE=FCST_INTERIM_1; USING=GLB_FCT_PERC
*DIM_NONAGGR ACCOUNT WHAT=BAS(OC_TOT_EMP_COST); WHERE=BAS(OC_TOT_EMP_COST); USING=SKF_FCT_PERC
*DIM_NONAGGR ACT_TYPE WHAT=%A_TYPE%; WHERE=%A_TYPE%; USING=NO_ACTTYPE
*DIM_NONAGGR EMPLOYEE WHAT=BAS(ALL_EMP); WHERE=BAS(ALL_EMP); USING=NO_EMPLOYEE
*DIM_NONAGGR POSITIONS WHAT=BAS(TOT_POS); WHERE=BAS(TOT_POS); USING=NO_POS
*ENDALLOCATION

Hope that NO_POS is not in BAS(TOT_POS) and NO_EMPLOYEE is not in BAS(ALL_EMP)

Share
Former Member Mar 06, 2017 at 10:39 AM
0

Thank you for this but I believe Many to Many allocation isnt supported in BPC NW and the result data is incorrect. I have experienced this in the past and have read on a few blogs on this forum.

I will anyways give this a try.

Regards,

Nilesh D

Share
Former Member Mar 07, 2017 at 12:40 AM
0

This works like a charm :) - and I have learnt something new. This keyword is going to definitely be very handy *DIM_NONAGGR

thanks again for your help on this.

Regards,

Nilesh D

Show 5 Share

Former Member

Sorry I meant to say the answer you posted was correct and my question was answered - Sorry about that

Former Member

Then correct it, now it looks strange!

Unaccept incorrect answer and accept the correct one!

Former Member

Hi Vadim, Sorry how do I accept your answer as the correct one? I dont see any action option there. Please let me know

Under my answer starting with "Something like: <code>"

You will find "Accept"