Tuning Load Performance - SAP BW

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:

  • Load all master data.
  • Delete the indices of the InfoCube and its aggregates.
  • Turn on number range buffering.
  • Set an appropriate data packet size.
  • Load the transaction data.
  • Re-create the indices.
  • Turn off number range buffering.
  • Refresh the statistics.

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

Number Range Buffering:
When loading data into an InfoCube,BW assigns DIMIDs to dimension tables (see Screen). 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 clickclick to run it.
Function Builder Initial screen

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,and then clickclick to continue.
Test Function Module: Initial screen

Step 3. Screen 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.
Test Function Module: Result screen

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.
Structure editor display

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 clickpen.
Number range Object maintenance

Step 6. The message in Screen indicates that we need to change a system setting so as to change this object. ClickClick to close this window.
Error in Object Editing

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

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.

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

Step 9. ClickClick to continue.
Information

Step 10. Repeat Step 5; Screen should appear as the result.
No range object: change

Step 11. Select Main memory from the Edit/Set-up buffering menu item.
No range object: change

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

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. ClickYes to continue.

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

Result

Data Packet Sizing:
When loading data into BW,we need to specify which one of the following transfer methods to use (Screens):

  • PSA: With this method,data are transferred in the unit of packets as shown in Screen. 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). 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:

  • BW sends a load request IDoc to R/3.
  • 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.
  • 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.
  • 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.
  • During the preceding steps,R/3 sends information IDocs to BW to notify it of the data extraction and transfer status.
  • 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 clicknext to Maintain Control Parameters for the data transfer to execute the function.
Display structure Business Information Warehouse

Step 2. ClickClick to continue.
The table is client Independent

Step 3. Click the New entries button to insert an entry.
Change view control parameters for data transfer

Step 4. Enter the specifications,and then clickClick to save the settings.
New Entries : Overview of added Entries

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

FIELDS IN TABLE ROIDOCPRMS
FIELDS IN TABLE ROIDOCPRMS

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

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


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SAP BW Topics