Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

 Non-Cumulatives: Performance and Memory Consumption

See note 1548125 Interesting facts about Inventory Cubes

  • As for all basis cubes it is important that as many requests as possible are compressed (see note 590370). For inventory cubes the system has to take into account all uncompressed request when the marker is calculated. This additionally contributes to a long runtime in case of uncompressed cubes.
  • Please only use validity-determining characteristics if its really necessary since too many of them ( or a validity-determining characteristic with a lot of characteristic values) decreases the performance considerably. See also chapter [II.e].
  • Its important to restrict the query as far as possible regarding time. This obviously improves the runtime by simply lowering the number of points in time for which ncum values have to be calculated. Keep in mind that less calculations during the query runtime also decrease memory requirements.

For Queries with non-cumulative key figures it is especially important to define (global) restrictions regarding time:

Sample Query with non-cumulative key figure

 

As described in note 1548125, the query processing changes decisively if non-cumualtive key figures are involved. Therefore it is necessary to take a closer look at how to interpret correctly the query runtime statistics in this case. The following query is very simple and defined on an inventiory cube (copy of 0IC_C03) with just a couple of data records:

There are only two key figures, the non-cumulative key figure 'Quantity Total Stock' and the cumulative key figure Amount. The query has only the global filter 0calyear=2013 and the 2 characteristics 'material' and 'Month' are in the drilldown. The following settings should be used when the query is run in RSRT:

Query Result:

We use the back(F3) button to get the runtime statistcs displayed:

Basically there are three data base accesses (different DataManager IDs):

  • 3 records transported
  • 4 records transported, Read Type=N
  • 4 records transported

 A query with non-cumulative key figues is internally split into two subqueries:

  • one handles the 'normal' cumulative key figures
  • the other one handles the non-cumulative key figures

The system starts with the cumulative key figures, in our case 3 data records are transferred form the data base. For non-cumulative key figures the system needs to read

  • the reference points: Read Type=N; 4 records transported
  • and the relevant 'movements': 4 records transported

There are only 4 materials in the cube, if calmonth is removed from the drilldown we get 4 rows.

Hence the system reads for these 4 materials the reference points and then starts the backward calculation for all requested times. Since the query is restricted to the year 2013 and 0calmonth is in the drilldown, for every reference point 12 stock values to 12 different points in time need to be calculated. The final result set therefore has 4*12=48 records. When we add to this the number of records transferred from the data base for the 'normal' key figure we get 51. This is exactly the value displayed for the internal table SP in the runtime statistics: 3100 OLAP:Read Data = 51 (please see the screenshot above).

In order to be able to do the backward calculation the system needs all movements for the requested time interval. In our case all movements are read where 0calday >= 20130101. Hence, the number of movements which need to be processed depends on the time restriction in the query.

Summary

 

The number of calculated stock values in query   =   'number of reference points'   *   'number of requested points in time'  

Its important to restrict the query as far as possible regarding time. This obviously improves the runtime and the memory consumption by simply lowering the number of points in time for which ncum values have to be calculated. In addition, the number of movements which are used to carry out the 'backward calcuation' is reduced.


SAP Consulting Notes

1548125   Interesting facts about Inventory Cubes

1160520    BIA and non-cumulative queries

 

 

 

 

 

 

 

 

  • No labels