Advance Script Slow Performance in SAC for Planning Data Action/Multi Action

[edwardcs]

We created a simple SAC for Planning model with 4 dimensions: Version, Account, Date, Entity, and 1 measure: Measure.

Then, we created a forecast layout story with Account in Rows, and Version, Date, and Measure in Columns. It is a page story with all account member IDs displayed and 30 columns representing 1 column per 1 year: 5 actual years + 25 forecast years - please refer to XYZ story attachment.

Then, we build data action to calculate 5 accounts as per the below advance scripts:

MEMBERSET [d/MID_ENTITY_PT] = ("Z000")
MEMBERSET [d/MID_ACCOUNT_LT_PT] = BASEMEMBER([d/MID_ACCOUNT_LT_PT].[h/parentId] , "FI_CALCOPS_0015")

//-----------------------------------------------------------------------------------
//Calculation and Data Writing
//-----------------------------------------------------------------------------------
DATA([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0016") = RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0013")
DATA([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0017") = RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_OPSASSUM_0038")
DATA([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0018") = RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_OPSASSUM_0043")
DATA([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0019") = RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_OPSASSUM_0048")
DATA([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0020") = RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0016") * RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0017") * RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0018") / RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = "FI_CALCOPS_0019")

We tested the data action from 4 different locations done by 4 different agents. it takes approx. 12 seconds to complete the overall data action calculation. For us, this is too slow. 

please advise if there is anything that can be improved in any area.

please find attached details of the Model.

thanks in advance.

Best,

Edward.

SAP Analytics Cloud for planning  

[JefB]

How much seconds are being spend on preparation vs execution steps -> you can see in data action monitor?

Can you try adding the account you read from with RESULTLOOKUP() also in the MEMBERSET?
That way the engine doesn't need to fetch them in middle of operation.

Potentially like this:

MEMBERSET [d/MID_ACCOUNT_LT_PT] = BASEMEMBER([d/MID_ACCOUNT_LT_PT].[h/parentId] , "FI_CALCOPS_0015"),BASEMEMBER([d/MID_ACCOUNT_LT_PT].[h/parentId] , "FI_CALCOPS_0010")

[edwardcs]

Hi @JefB ,

Appreciate your reply. I have followed your instructions and added the member IDs from the RESULTLOOKUP() as follows:

MEMBERSET [d/MID_ACCOUNT_LT_PT] = ("FI_CALCOPS_0016", "FI_CALCOPS_0017", "FI_CALCOPS_0018", "FI_CALCOPS_0019", "FI_CALCOPS_0020", "FI_CALCOPS_0013",
"FI_OPSASSUM_0038","FI_OPSASSUM_0043","FI_OPSASSUM_0048")

However, there is no improvement in terms of the duration of total execution until the story shows the result e.g., 12 seconds. From the data action monitor, the total duration time of data action only is 3s. However, I cannot see the 'Preparation to Execute' time information in my data action monitor log. What I have is pending time and it is 1 second.

Please advise.

Best regards,

Edward.