foreach - long runtime

[#1]

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

[saurabh_sonawane]

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.