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
"I believe Many to Many allocation isnt supported in BPC NW" - wrong! Search better :)
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?
2800*300=840000 - for sure you will get out of memory!
Try allocation!
Hi Vadim,
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
Hi Vadim,
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
No! You have
*DIM_NONAGGR instead of *DIM for this case! No FOR/NEXT loops!
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?
Hi Vadim,
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.
Thank you for your time.
Regards,
Nilesh D
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)
Hi Vadim,
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.
Please correct me if wrong.
I will anyways give this a try.
Regards,
Nilesh D
Hi Vadim,
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
Sorry I meant to say the answer you posted was correct and my question was answered - Sorry about that
Then correct it, now it looks strange!
Unaccept incorrect answer and accept the correct one!
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"