Skip to Content
0
Sep 18, 2023 at 11:25 AM

SAC - Foreach performance

136 Views Last edit Sep 18, 2023 at 11:27 AM 2 rev

Hey
I have a complex model involving several dimensions.
I have prices uploaded in specific junctions, however the quantities of possible combinations is endless.
The measure price "PRC" is set as average for all dimension.

The model structure priority is set on measures.

I wanted to issue out the last booked price out of the uploaded data (on jan 2024).

which I successfully did when I used several "FOREACH.BOOK" as below:

CONFIG.GENERATE_UNBOOKED_DATA=OFF

MEMBERSET[d/Date]=%02_P_DATE% TO %03_P_DATE%
MEMBERSET[d/Measures]="PRC"
MEMBERSET[d/ACC_GP]="SLS_DRCT"

FLOAT @LAST_PRICE  // last price

FOREACH.BOOKED[d/CUSTOMER],[d/PROD]
FOREACH.BOOKED[d/MAT_GRP],[d/_USE]
FOREACH.BOOKED[d/CURRENCY],[d/SALES_ORG]
FOREACH.BOOKED [d/Date]
	IF RESULTLOOKUP([d/Measures]="PRC", [d/ACC_GP]="SLS_DRCT") > 0
THEN 
	@LAST_PRICE=RESULTLOOKUP([d/Measures]="PRC", [d/ACC_GP]="SLS_DRCT")
	DATA([d/Measures]="PRC", [d/ACC_GP]="SLS_DRCT",[d/Date]="202401",[d/AUDIT_ID]="#")=@LAST_PRICE
ENDIF
ENDFOR
		ENDFOR 		ENDFOR 	 	ENDFOR<br>

last-price.png

the problem is that the DA run for more than 6 minute and tends to crush.
but if I take out those multiplied FOREACH.BOOKED, the DA works for few seconds but the results are completely wrong as below:

CONFIG.GENERATE_UNBOOKED_DATA=OFF

MEMBERSET[d/Date]=%02_P_DATE% TO %03_P_DATE%
MEMBERSET[d/Measures]="PRC"
MEMBERSET[d/ACC_GP]="SLS_DRCT"

FLOAT @LAST_PRICE  // last price

//FOREACH.BOOKED[d/CUSTOMER],[d/PROD3]
//FOREACH.BOOKED[d/MAT_GRP],[d/_USE]
//FOREACH.BOOKED[d/CURRENCY],[d/SALES_ORG]
FOREACH.BOOKED [d/Date]
	IF RESULTLOOKUP([d/Measures]="PRC", [d/ACC_GP]="SLS_DRCT") > 0
THEN 
	@LAST_PRICE=RESULTLOOKUP([d/Measures]="PRC", [d/ACC_GP]="SLS_DRCT")
	DATA([d/Measures]="PRC", [d/ACC_GP]="SLS_DRCT",[d/Date]="202401",[d/AUDIT_ID]="#")=@LAST_PRICE
ENDIF
ENDFOR
	//	ENDFOR 		ENDFOR 	 	ENDFOR<br>

last-price-wrong.png

correct-wrong.png

is there a better way to do it?

Thanks

Deb

Attachments

last-price.png (42.4 kB)
correct-wrong.png (28.0 kB)