Tuning Load Performance

When tuning load performance, you should follow a few simple rules:

• If possible, carry out data transformation in transfer rules rather than in update rules. The transformation made in transfer rules applies to all InfoCubes.
• Optimize ABAP programs in transfer rules and update rules, as they will be applied to every record during data loading.
• If the data come from flat files, place them in an application server host machine.
• If the data come from an R/3 system, optimize the extractor.
• Set the instance profile parameter rdisp/max_wprun_time to 0 to allow unlimited CPU time for dialog work processes.
• Consider using the database NOARCHIVELOG mode.

When loading transaction data, you should follow this procedure:

1. Load all master data.
2. Delete the indices of the InfoCube and its aggregates.
3. Turn on number range buffering.
4. Set an appropriate data packet size.
5. Load the transaction data.
6. Re-create the indices.
7. Turn off number range buffering.
8. Refresh the statistics.

Indices and statistics were discussed in both Section 13.3 and Section 12.2.2. Here, we discuss how to buffer the number range and set the data packet size.

13.4.1 Number Range Buffering

When loading data into an InfoCube, BW assigns DIMIDs to dimension tables (see Screen 7.5). To improve load performance, a range of numbers should be buffered. Then, after one number has been assigned to a record in the dimension table, a new number becomes available in memory for the next new record. This technique saves trips to the database, thereby improving load performance. Called number range buffering, it is used widely in SAP. Number range buffering occurs through number range objects.

In this section, we demonstrate first how to find the number range object for an InfoCube dimension and then how to configure a range of numbers.

Work Instructions

Step 1. Run transaction SE37. Enter a function module called RSD_CUBE_GET, and then click to run it.

SCREEN 13.41

Note

The function module RSD_CUBE_GET is an ABAP object used extensively in BW to provide InfoCube-related information. In our example, it will tell us the dimensions of an InfoCube and the number range objects used by these dimensions.

Step 2. Enter the InfoCube name IC_NEWBC2 and other information as shown in Screen 13.42, and then click to continue.

SCREEN 13.42

Step 3. Screen 13.43 shows the output from the function module. The entry E_T_DIME under the column Export parameters is a table that contains the InfoCube dimension information. It has six entries. Double-click the E_T_DIME entry to display the table contents.

SCREEN 13.43

Step 4. The NOBJECT column lists the number range objects, one for each dimension.

In this example, we will use the number range object BID0001770 to buffer a range of numbers for the dimension /BIC/DIC_NEWBC21.

SCREEN 13.44

Step 5. To buffer a range of numbers using the number range object BID0001770, run transaction SNRO, enter BID0001770 as the number range object, and then click .

SCREEN 13.45

Step 6. The message in Screen 13.46 indicates that we need to change a system setting so as to change this object. Click to close this window.

SCREEN 13.46

Step 7. To implement this change, run transction SE03. Select the Set System Change Option, and then click to execute the function.

SCREEN 13.47

Note

Transaction SE03 is called Transport Organizer Tools. It lists functions we can use to configure system behaviors, as well as to display and change ABAP objects in transport requests. A transport request is the vehicle that transports ABAP objects from the development system, to the test system, and/or to the production system. We will discuss this topic further in Chapter 14.

Step 8. Change Not modifiable to Modifiable for General SAP name range. Click to save the change.

SCREEN 13.48

Step 9. Click to continue.

SCREEN 13.49

Step 10. Repeat Step 5; Screen 13.50 should appear as the result.

SCREEN 13.50

Step 11. Select Main memory from the Edit/Set-up buffering menu item.

SCREEN 13.51

Step 12. Enter a number range, such as 500, in the No. of numbers in buffer field. Click to save it.

SCREEN 13.52

Note

This step ensures that BW will keep 500 numbers in the main memory buffer for /BIC/DIC_NEWBC21. Setting the number range too low will not help load performance significantly, whereas setting it too high will consume lot of memory on the application server machine, which will affect other applications on the same machine. Monitor the memory usage during data loading to find an appropriate number range.

Step 13. Click to continue.

Result

A status message Number range object BID0001770 saved will appear at the bottom of Screen 13.52.

SCREEN 13.53

13.4.2 Data Packet Sizing

When loading data into BW, we need to specify which one of the following transfer methods to use (Screens 3.15 and 3.56):

• PSA: With this method, data are transferred in the unit of packets as shown in Screen 4.10. This method uses the transfer structure defined for PSA, although it does not mean the data must stay there.
• IDoc: With this method, data are transferred in the unit of IDocs (Figure 10.2). The data must stop by the ALE inbox before proceeding to their targets. This method has a notable limitation: The transfer structure cannot be wider than 1000 bytes in the character format.

For these reasons, the PSA method is safer and more straightforward than the IDoc method; its use is recommended by SAP. In this section we discuss how to size the data packet to improve load performance when the PSA method is used in data loading.

To understand the technique, we must first understand how data are loaded. If the source system is R/3, BW uses the following procedure to collect and transfer data:

1. BW sends a load request IDoc to R/3.
2. As triggered by the load request IDoc, R/3 starts a background job. The job collects data from the database and saves them in packets of a predefined size.
3. After collecting the first packet of data, the background job launches a dialog work process, if one is available. The dialog work process will send the first data packet from R/3 to BW.
4. If more data must be transferred, the background job continues to collect the second packet of data without waiting for the first data packet to finish its transfer. Once it is complete, the background job will launch another dialog work process, if one is available. The new dialog work process will send the second data packet from R/3 to BW.
5. During the preceding steps, R/3 sends information IDocs to BW to notify it of the data extraction and transfer status.
6. The process continues in this fashion until all requested data have been selected and transferred.

Based on this understanding, we can readily discover why the size of data packets affects load performance.

For the same amount of data, if packets are small, then we will have many packets. They could potentially proliferate to such a degree that data packets use up all available dialog work processes in R/3. Without available dialog processes, later packets will frequently trigger the dispatcher to obtain a free dialog work process, which will greatly affect R/3 performance as a whole.

If packets are large, then we will have few packets. Each will take a long time to complete the transfer process. In such a case, we will have fewer dialog work processes participating in the parallel processing, and the loading operation will take a long time as well.

The procedure to set the size of packets follows.

Work Instructions

Step 1. Run transaction SBIW in the R/3 source system, and then click next to Maintain Control Parameters for the data transfer to execute the function.

SCREEN 13.54

Step 2. Click to continue.

SCREEN 13.55

Step 3. Click the New entries button to insert an entry.

SCREEN 13.56

Step 4. Enter the specifications, and then click to save the settings.

SCREEN 13.57

The specifications are saved in the table ROIDOCPRMS. Its fields are explained in Table 13.7.

To determine the number of data records in a data packet, Plug-In extractor uses the following simple algorithm:

PACKET_SIZE = MAXSIZE * 1000 / TRANSFER_STRUCTURE_WIDTH. IF PACKET_SIZE > 999999, PACKET_SIZE = 999999. IF PACKET_SIZE >= MAXLINES, PACKET_SIZE = MAXLINES.

Note

Refer to OSS Note 0157652, "Main Memory Requirement for Extraction from R/3," for details on how to size R/3 main memory.