Introduction
When building a data warehouse with SAP Business Warehouse (SAP BW), data often needs to be manipulated, transformed or enriched. Sometimes this can be implemented using standard master data lookups or formulas (although this is often suboptimal from a performance point to view), but when the logic of business rules become complex and standard out-of-the-box or drag and drop BW functionalities cannot meet the requirements, ABAP routines are usually the solution.
ABAP routines can be included in many places in your data warehouse. Start or end routines, expert routines, InfoObject rules, Data Transfer Process (DTP) filter routines. These objects can all contain custom coding. Poorly written ABAP routines are quite often bottlenecks in the data loading procedure, bad reporting performance in the SAP Business Explorer (SAP BEx) could be linked directly to coding that is executed by customer exit variables.
Using the wrong type of internal table can also lead to process time deterioration. If a record can be accessed directly using a unique key for example, hashed tables have far better performance than any other type of internal table.
A lot of ‘select’ statements can be improved dramatically, for example by selecting only a subset of fields or only a subset of record lines (with a ‘where-clause’) of the source table(s) or by using aggregate functions in the select statement.
Nesting select statements is another typical example of code that consumes a lot of time and effort. This should be avoided and could be replaced by using joins in the select statement or creating a database view, or by reading the necessary data in two times before combining them in-memory.
Identifying and resolving this kind of performance killers is key to optimize your ABAP routines. In this insight you will learn some useful procedures for measuring the performance of your ABAP coding.
Custom Messages in Data Load Monitor
Any type of routine can become large quite easily. An exceeding number of code lines make it harder to pinpoint the part of the code that is killing the performance. The load monitor shows a lot of information about all the steps of the data load. The duration can also be examined. However, it does not contain enough information to analyze a specific step in detail.
To demonstrate the following procedure, a temporary InfoObject ZTEMP is created that receives data from the well-known InfoObject 0BPARTNER. One transformation is created with a start routine.
Custom messages can be added to the load monitor and they allow you to see exactly how much time a statement, or better, a group of statements is consuming. These custom messages are simply created by adding a few lines of ABAP coding to your routine.
First you will need to declare a variable that allows capturing the current time, for example ‘lv_time’. This will tell you exactly at what time a specific part of the routine was executed.
| DATA: lv_time TYPE tims. |
Next, specify the following lines and give the object ‘monitor_rec-msgv2’a custom description that identifies that part of the routine. For example, a description like ‘start fetching data from table xxx’ could be used.
| monitor_rec-msgid = 'RSAR'. monitor_rec-msgty = 'S'. monitor_rec-msgno = '051'. GET TIME FIELD lv_time. CONCATENATE lv_time ':'INTO monitor_rec-msgv1. monitor_rec-msgv2 = 'enter a custom description here'. APPEND monitor_rec TOMONITOR. |
In the above example, message 051 from message class RSAR is used. This message has just 4 parameters &1 to &4, which are be filled by the structure field msgv1 to msgv4 respectively. The message type is set to ‘S’ for success, this is necessary for your routine to finish.
Repeat this step for every part of the code that needs to be measured. This allows a comparison of the timestamps in the data load monitor.
Please keep in mind that each time this code is executed, a new message is added to the data load monitor. So avoid adding this code to a part of the routine that is performed numerously, for example inside a ‘loop at’ statement. This would lead to an explosion of system messages.
Make sure your object is activated and execute the data load. The custom messages can be displayed in the Data Load Monitor (transaction RSMO) by drilling downto the corresponding item. In our example, the custom coding was added in the start routine. The context menu behind ‘start routine’ in the load monitor now displays an extra item; ‘Display Messages’.
This procedure enables you to determine which part(s) of the code consumes the most time in a quick manner with very little effort. Improving these parts may lead to the biggest processing time reductions after code optimization. You could even implement these messages from the very first version of your code if you expect it to become a performance critical part of your data warehouse. Bringing this one step further would be to generate the messages only if a certain parameter is set.
Debugging ABAP routines
When activating a new (or modified) routine, a syntax check is performed automatically. It is thus impossible to activate a BI transformation containing syntax errors. Even with a correct syntax, a routine can still have errors. Most of the time, this type of error is difficult to resolve and you need to debug the routine.
The ABAP debugger is used to get rid of any bugs in your routine. This tool lets you run the routine line by line. Since the concept of transformations is in place (SAP NetWeaver 2004s), data uploads can be simulated. No data is actually transferred to the data target during a simulation, but it does allow you to debug the routines. In the DTP maintenance screen, click ‘Change Breakpoints’ next to the transformation that needs to be debugged.
Image
4 Simulate the Data Transfer Process (DTP)
This allows you to select the location where the program will be halted in your simulation session.
Image
5 Selecting breakpoints in DTP simulation
However, the program breaks in standard code and your custom code cannot be found easily. Therefore breakpoints can be added. The program will then stop at that breakpoint.
Hardcoded Breakpoints
Any type of routine can be debugged by using physical, hardcoded breakpoints. This can be implemented by the following simple statement:
| BREAK-POINT. |
This has one prerequisite that is not always easily overcome; the system needs to be changeable. Most likely this option cannot be used in a productive system to debug on an ad hoc basis. It is not wise to keep this statement in the live version, as this will clutter the system logs or even cause an overflow if you use it e.g. in a field routine.
External Breakpoints in Generated Program
Once a transformation is activated, a program is generated automatically in the background. This program incorporates the entire transformation with all its routines and InfoObject mappings. This program can be viewed, by displaying the transformation in the ‘Administrator Workbench’ (transaction RSA1) and clicking the menu ‘Extras \ Display Generated Program’.
Image
6 Display Generated Program of a Transformation
Locate your custom code and place an external breakpoint. This can be done via the menu: ‘Utilities \ External breakpoints \ Set/Delete External Breakpoint’ or hitting ‘Ctrl+Shift+F9’.
Image 7 Set / Delete External Breakpoints in ABAP Program
This type of breakpoint is user specific and is kept for 2 hours. The ABAP debugger will now stop at this location during simulation.
No Breakpoints
There is another option to debug that does not need any manually placed breakpoints. With the following trick, the custom code that needs to be debugged can easily be found.
Search and select the statement "CALL METHOD lr_exe->(c_meth_execute)” and continue to that line via the menu: ‘Debugging \ Continue to Cursor’ or hit Shift+F8.
| * exceptions raised in transformation inherited by CX_RS_STEP_FAILED CALL METHOD lr_exe->(c_meth_execute) EXPORTING i_master_data_exist = n_check_only_sids i_r_inbound = i_r_inbound i_r_log = i_r_log i_r_request = l_r_request i_r_trfn_cmd = me IMPORTING e_r_outbound = e_r_outbound. |
| CALL METHOD me->start_routine EXPORTING request = l_request datapackid = i_r_inbound->n_datapakid IMPORTING monitor = _lt_msg_s CHANGING SOURCE_PACKAGE = <_yt_sc_1>. |
Continue to that line and go one step further again. This will take you to the custom ABAP code and you can start debugging.
It is recommended to add a breakpoint once you are in your code in debugging mode. This can be simply done by clicking in the left margin, at the level of the desired code line. A ‘stop’ icon indicates that the breakpoint is set.
Endless Loop
When none of the above options can be used, there is a last resort to enable debugging. For example, when debugging DataSource enhancements, an endless loop can be used.
DataSources can be enhanced with custom coding via ‘Project Management of SAP Enhancements’ (transaction CMOD). Add the following lines to your code.
| ***while statement for debugging while 1 eq 1. ENDWHILE. |
Once the program reaches the endless loop, it will never exit this loop. This enables you to debug the code, via the transaction ‘Process Overview’ (transaction SM50). Select the entry that corresponds to your user name and time. Use the menu ‘Program \ Debugging’ to start the debugging session.
Image
9
Process Overview (Transaction SM50)
A new screen called ‘ABAP debugger Control Session’ is opened and the program is stopped at the endless loop.
Image 10 Endless Loop in ABAP Debugger Controls Session
Select the statement following at the endless loop and use the menu ‘Debugger \ Goto Statement’ or hit Shift+F12. The program is now out of the loop and you can start debugging your ABAP code.
Please do not forget to remove or disable (with comments) the endless loop when it is no longer required, as this will program will hang obviously.
This procedure has the same disadvantage as physical breakpoints. The endless loop needs to be hardcoded in a modifiable system.
Conclusion
Often custom ABAP enhancements are the bottlenecks in both the data loading and the reporting aspect in the SAP Business Warehouse data warehouse. This underlines the importance of having optimized coding. Custom messages in the data load monitor make it easier to reveal the programming pitfalls. Several debugging procedures enable us to resolve these coding issues.
