Question Re: foreach - long runtime in Technology Q&A

foreach - long runtime

dieterzenger — Fri, 07 May 2021 09:17:17 GMT

Hi Experts,

I have to create data model showing shifts of Positions from one cost center to another comparing two different plan cycles. I have in my primary model for each plan cycle the position id a unique record.

I created a new model having current & prior cost center information as separate dimension in the model.

With a Data Action i am able to create a combined records. Unfortunately i was only able doing this with a foreach statement. For my test it worked perfectly, but in the reality its not coming to an end because of high amount of positions and cost centers. Therefore i need some help how to replace the for each with a result lookup.

primary model

target "shift" model

after having run Cross model copy function the content looks like following.

after executing data action with forereach …. the result is like below....

Below data action

MEMBERSET [d/prior_cycle] = %PriorCycle%
MEMBERSET [d/current_cycle] = %CurrentCycle%
MEMBERSET [d/type] = "02"


INTEGER @lv_combi


// //-----------------------------------------------------------------------------------
// //Calculation and Data Writing
// //-----------------------------------------------------------------------------------

//=================================================================================================//
// Step 1 
//=================================================================================================//
   FOREACH.BOOKED [d/Position_], [d/current_costcenter]
 	@lv_combi = 0
  	IF RESULTLOOKUP([d/current_costcenter] = "#") != 0 THEN
  		@lv_combi = 1
  	ENDIF
  	IF @lv_combi = 1 THEN


		@lv_combi = 0
  		FOREACH [d/prior_costcenter]
  			IF RESULTLOOKUP([d/prior_costcenter] = "#") != 0 THEN
  				@lv_combi = 1
  			ENDIF
  			IF @lv_combi = 1 THEN
  		        DATA([d/type] = "14", [d/Account] = "FTE_prior") = RESULTLOOKUP([d/current_costcenter] = "#", [d/Account] = "FTE_prior")
  			ENDIF
  		ENDFOR
  	ENDIF
  ENDFOR  

//=================================================================================================//
// Step 2
//=================================================================================================//
FOREACH.BOOKED [d/Position_], [d/prior_costcenter]
	@lv_combi = 0
	IF RESULTLOOKUP([d/prior_costcenter] = "#") != 0 THEN
		@lv_combi = 1
	ENDIF
	IF @lv_combi = 1 THEN
		@lv_combi = 0
		FOREACH [d/current_costcenter]
			IF RESULTLOOKUP([d/current_costcenter] = "#") != 0 THEN
				@lv_combi = 1
			ENDIF


			IF @lv_combi = 1 THEN
				DATA([d/type] = "13", [d/Account] = "FTE_current") = RESULTLOOKUP([d/prior_costcenter] = "#", [d/Account] = "FTE_current")
			ENDIF
		ENDFOR
	ENDIF
ENDFOR

Thank you

Dieter

Re: foreach - long runtime

dskiendziel — Fri, 07 May 2021 11:23:14 GMT

Dieter,

In my experience, when you bring in multiple dims to the FOREACH you are going to have a long run time on the DA. When combining the dims in the FOREACH statement, you are cycling through every combination of the dims in the FOREACH. Try removing the cost centers and leave the Position. I think your IF statements will handle what you are trying to do in the FOREACH with the Cost Center.

Dan

Re: foreach - long runtime

dieterzenger — Fri, 07 May 2021 14:58:33 GMT

Hi Dan,

i tried your proposal. Not sure if I understood correct, but its now creating a record for each available cost center from master data dimension.

the code is like :

MEMBERSET [d/type] = "02"
MEMBERSET [d/Position_] = "02"
INTEGER @lv_combi


// //-----------------------------------------------------------------------------------
// //Calculation and Data Writing
// //-----------------------------------------------------------------------------------


FOREACH.BOOKED [d/Position_]
	@lv_combi = 0
	IF RESULTLOOKUP([d/current_costcenter] = "#") != 0 THEN
		@lv_combi = 1
	ENDIF
	IF @lv_combi = 1 THEN
		@lv_combi = 0
		FOREACH [d/prior_costcenter]
			IF RESULTLOOKUP([d/prior_costcenter] = "#") != 0 THEN
				@lv_combi = 1
			ENDIF
			IF @lv_combi = 1 THEN
				DATA([d/type] = "24", [d/Account] = "FTE_prior") = RESULTLOOKUP([d/current_costcenter] = "#", [d/Account] = "FTE_prior")
			ENDIF
		ENDFOR
	ENDIF
ENDFOR


FOREACH.BOOKED [d/Position_]
	@lv_combi = 0
	IF RESULTLOOKUP([d/prior_costcenter] = "#") != 0 THEN
		@lv_combi = 1
	ENDIF
	IF @lv_combi = 1 THEN
		@lv_combi = 0
			IF RESULTLOOKUP([d/current_costcenter] = "#") != 0 THEN
				@lv_combi = 1
			ENDIF
			IF @lv_combi = 1 THEN
				DATA([d/type] = "23" , [d/Account] = "FTE_current") = RESULTLOOKUP([d/prior_costcenter] = "#", [d/Account] = "FTE_current")
			ENDIF
	ENDIF
ENDFOR

the result is like

I have added new record type for better analysis . Record type 13 vs 23 and 14 vs 24.

thanks

Dieter

Re: foreach - long runtime

dieterzenger — Fri, 07 May 2021 15:01:47 GMT

result immage was missing

Re: foreach - long runtime

dskiendziel — Fri, 07 May 2021 15:09:53 GMT

I don't think I fully understand your requirement or dimensions. But, can't you limit the cost centers you are impacting by scoping them in the MEMBERSET? Are they grouped hierarchically or through a property?

Re: foreach - long runtime

dieterzenger — Fri, 07 May 2021 15:14:00 GMT

no, because I need to check for each position if this has moved from one cycle to the other, there are around 70k positions, and 4k cost centers. in abap world it would be easy, but with advanced formula i am not yet familiar.....

thanks anyhow.

Re: foreach - long runtime

saurabh_sonawane — Wed, 19 May 2021 13:20:21 GMT

Hi,

You can use an allocation method to assign the value to the cost center after copying the data.

step to be followed

1. use a source dimension as cost-center and target as cost-center

filter account dimension with FTE_prior

step

driver account should be FTE_Current.

Please let me know if you need more information.

Thanks,

Saurabh S.