SAP Analytics Cloud - Performance statistics and zero records

[Noel_Munday]

Abstract

What would the impact be on performance and memory/disk usage in SAP Analytic Cloud if we executed a data action, such as a currency conversion, and systematically increased the data volume until we reach 1 billion records?

This blog hopes to give some insight into answering that question. 

Considerations

Performance results are extremely dependent on the overall landscape of the environment with many variables impacting the results such as initial sizing, SAC version, private or public tenant, network speed, model design, number of members in dimensions, authorisation configuration, complexity of the data action etc.

However, if we treat the results of this test prudently and within context it would still be interesting to know what the results of such as test are and can start forming a baseline for our expectations.

The results in a nutshell

The smallest public SAP Analytic Cloud test tenant was instantiated with the minimal viable model required to execute a basic currency conversion data action which converts a local currency (LC) measure, where the LC is stored as an attribute on the company code dimension, to a fixed group currency (GC) measure using the standard SAC currency table.

Up until about 8 million records the data action executed consistently around 2 seconds. Then increasing the data volume to 17.7 million records the data action executed in 17 seconds and when increased to 89 million records it executed in 86 seconds.

My aim was to reach 1 billion records but at the 177 million record mark I received an error:

Taking a look at the private version statistics on page 1 of the statistics page I could see my single version and model but the number of records indicated 862 million.

...and on page 2 of the statistics I can see the breakdown of the 862 million records:

You will notice that in the VERSION STATE column there is only one ACTIVE version with 133 million records and the failed and deleted version just above it contains the 177 million records that were generated and deleted due to reaching the tenant capacity limit.

The insight here is that the deleted records are still stored in the tenant as you can see by the VERSION START TIME column where each iteration of the data action execution was performed on the same day and within minutes of each other.

The next logical step, assuming you don't need the zero records for audit or other purposes, is to delete them. However, we need to be aware that simply accessing the model's data foundation and even deleting all the data in the model does not delete the deleted versions, this for the same reason i.e. retaining the integrity of the database records. In the following statistics screen-shot you will notice that the version which contains the 133 million records changed from ACTIVE to DELETED after deleting all the data in the model but did not change the overall record count which remained at 862 million records until you run a small data action to specifically delete zero records and then the physical number of records can be seen to reduce to zero.

The advanced data action delete code that is required to delete the physical records is:

IF RESULTLOOKUP() = 0 THEN
	DELETE()
ENDIF

The insight gained is to understand that when a data action or manual planning takes place the version data in a public version is replicated into temporary private version so that the end user can either PUBLISH or REVERT the results. Whichever action they choose the replicated private version is then deleted and any data action that deletes data will create a zero record which is retained for purposes of database integrity and must be specifically deleted in order to reduce the physical record size and can contribute to improving performance.

 

Test environment

To better understand the conditions under which this test was performed here are some technical details.

Version

Model design

Data action

Data action monitor results

       

Insights learned and conclusion

1. The first conclusion I can draw is that the simple currency conversion data action performance running for 86 seconds over 133 million records is good considering that the total number of records in the model was 862 million including the zero records.
2. I would be interested to know if the runtime performance would improve after the zero records were deleted. I did not continue the test to find out and anyone with additional insights would be welcomed to share those insights.
3. I would also be interested to repeat the test with a totally "clean" model and understand when and how many zero records are generated at each step of the process. I started the model with zero records, manually captured data and also ran copy data actions to generate the increased row numbers but there was no specific DELETE data action. So tracking this statistic would have been beneficial to have known.

So we have some valuable insights and a few more questions to answer.