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]

If you don't have huge nr. of data in your editable version (planning area) or huge dimension members/hierarchies/...etc. and the 'preparing to execute' step is still so slow, I'd suggest you to raise an incident for SAP.

There's a general SAP note 3261092  explaining some more, but also a recent SAP note 3432553  saying "The SAC development team is investigating how to improve the preparation step including this issue."

[N1kh1l]

@edwardcs 

If you add an attribute to your account dimension say SRC_ACC and maintain FI_CALCOPS_0013 as value against FI_CALCOPS_0016 and so on for other similar account reads, you could replace the first 4 DATA() and RESULTLOOKUP pairs to just 1.

DATA() = RESULTLOOKUP([d/MID_ACCOUNT_LT_PT] = [d/MID_ACCOUNT_LT_PT].[p/SRC_ACC])

Br.

Nikhil

[edwardcs]

Hi Nikhil, thanks. I believe with your approach the number of steps executed will be less and hence preparation to execute time will be faster. However, seems to me this is more a workaround than a best practice. cmiiw

[N1kh1l]

This is one of the best practices covered discussed in Data Action documentation.

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/fa558b0ff273475c8f3cf...

Nikhil

[edwardcs]

Hi Nikhil, tried your solution. It is slightly but not consistently increase the speed of the data action total execution, however it does not improve the 'preparing to execute' timing which is still around 10 seconds. Please advise.

[N1kh1l]

Preparing to execute may also depend on the tenant sizing and resources available. You can try enabling the planning area for your model in the model preferences and see if that impacts the performance.

[edwardcs]

Hi Nikhil, I have tried enabling the planning area for the model in the model preferences and it does not improve performance. should we conclude that this issue due to tenant sizing or resource availabiltiy? @SAPSupport 

[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.