Performance Improvements for DataStore Objects

Posted by Klaus Kuehnle

A lot of time and effort has been invested in SAP BW 7.30 to improve the performance of operations on DataStore Objects, such as request activation. The most important improvements are:

1. database partitioning for DataStore Objects
2. mass lookups in activation of requests in DataStore Objects
3. dynamic flag “unique data records”
4. faster request activation in DataStore Objects on databases with massively parallel processing architecture
5. lookup into DataStore Objects in transformation

The following sections describe these points in more detail.

(1) Database Partitioning

The E fact tables of InfoCubes can be partitioned, by month or fiscal period, on databases that support range partitioning (for more details, see here). In SAP BW 7.30, this is now also possible for the active tables of standard DataStore Objects. More precisely, if a standard DataStore Object has at least one key field referring to InfoObject 0CALMONTH or InfoObject 0FISCPER, then the active table of this DataStore Object can be partitioned by one of these key fields. The advantage of this partitioning is faster access to data due to partition pruning, provided that the partition criterion is part of the selection criterion (‘where’ clause).

To activate partitioning, go to the menu and choose “Extras → DB Performance → DB Partitioning (in edit mode of a standard DataStore Object).

A popup appears where you can select a key field that refers to the InfoObject 0CALMONTH or InfoObject 0FISCPER (you cannot select anything if there is no field referring to one of these InfoObjects).

Any changes made here will become effective after activating the DataStore Object.

Note that you can only change the partitioning settings if the DataStore Object is empty. This means that you should decide whether you want a DataStore Object to be partitioned before you load any data into it.

(2) Mass Lookups

Requests are activated in a standard DataStore Object by splitting the data in the activation queue into packages. These packages are then activated independently of each other (and usually simultaneously).

For each data record of the activation queue, the system performs a lookup into the active table to find out whether a record already exists in the active table with the same key (i.e. whether the activation will cause an insert or an update in the active table).

In SAP BW 7.30, the lookup is no longer performed record by record, but for all records in a package in one go. This decreases the activation runtime by 10-30%, depending on the database.

(3) “Unique Data Records” Can be Set Dynamically

The last section was about lookups into the active table as part of the activation. This lookup is performed to find out whether the active table already contains records with the same key as a record that you want to activate. However, if the active table does not contain any records with the same key as one of the records that you want to activate (for example, if data for a new year or a new region is activated), then these lookups can be omitted. This reduces the runtime of the activation, particularly if the active table contains a lot of records.

You can set the flag “Unique Data Records” in the settings of a standard DataStore Object to guarantee that no record in the activation queue will ever have the same key as any record in the active table during activation. The system will then omit the lookup into the active table. Since this setting is very restrictive, there will probably not be many occasions where you can risk using this flag.

In SAP BW 7.30, you can set this option for one single activation request only. In other words, you can specify that for the current activation request, none of the keys in the activation queue occur in the active table, without making any statement on other activation requests. The system then will omit the lookup for this activation request only.

To select this option, click “Change...” next to the text “Use DataStore Setting (Unique and Changed Data Records)” in the popup where you choose the requests to be activated.

Another popup appears. Choose “New, unique data records only”.

Confirm the dialog. The system will now run the activation without lookup.

For activation processes scheduled as part of a process chain, you also have the option to force the system to omit the lookup for init requests. Choose “Change...” next to the text “Uniqueness of Data: Use DataStore Settings” on the maintenance screen of your variant for an activation.

A popup appears where you can choose “Init requests always return unique data records”.

Confirm the dialog. The system will now omit the lookup for init requests.

(4) New Activation Method for Databases with Massively Parallel Processing Architecture

The traditional activation of requests in a standard DataStore Object is done by splitting the data of the activation queue into packages that are activated independently of each other (and usually simultaneously). The system ensures that records with identical keys are placed into the same package and stay in their original sequence. It is very important that records with identical keys are activated in the correct sequence to guarantee correct results. Therefore a package is activated sequentially - record by record.

Usually there are not many records with identical keys in an activation run. SAP BW 7.30 offers you a new activation method that can be used on databases with massively parallel processing architecture (these are currently (January 2011) “IBM DB2 for Linux, UNIX, and Windows” and “Teradata Foundation for SAP NetWeaver BW”).

This new activation method finds all the records in the activation queue that do not have unique keys and activates them in the traditional way. All the other records have unique keys in the activation queue and can be activated regardless of the sequence. These records are activated simultaneously using a few SQL statements. This means that the majority of records are no longer read from the database to the application server, processed and written back to the database. Instead they are processed directly in the database. This results in a considerable improvement of the activation runtime, in databases with a massively-parallel-processing architecture.

This new activation method is used automatically by the system (i.e. no user action is needed), provided that certain conditions are met (list not complete):

• this new activation method is supported for the database used (currently only “IBM DB2 for Linux, UNIX, and Windows” and “Teradata Foundation for SAP NetWeaver BW”, as mentioned above)
• SID generation during activation is not requested
• the option “unique data records” is not set
• the aggregation behaviors of all (load) requests that are activated in one go are identical
• the option “do not condense requests” is not set

The performance gains depend on the data as well as on the hardware. In scenarios with very suitable conditions, performance can improve 2 to 3 times.

(5) Lookup into DataStore Objects in Transformation

In SAP BW 7.30, you can define a transformation rule to fill a field in the transformation target by reading the value from a DataStore Object (similar to reading from master data). More information on this can be found in Documentation in section “Read from DataStore Object”.

This new method of reading from DataStore Objects in a transformation is intended to replace the routines written by users as transformation rules to read values from DataStore Objects. Besides making it more convenient to read from DataStore Objects, this replacement will result in performance gains in many cases (depending on the implementation of the replaced user routine). This is because data is read from the DataStore Object once per data package instead of record by record.

Disclaimer:http://www.sdn.sap.com/irj/sdn/index?rid=/webcontent/uuid/b0b72642-0fd9-2d10-38a9-c57db30b522e