Usage Of ABAP in SAP BW/BI

[Former Member]

1.    Introduction

The purpose of this article will focus on the concepts and strategiesthat constitute best practices regarding the use of ABAP programming in BW. Inevery project, the use of ABAP programming has had a profound effect on theability to model the correct business processes within BW. During the design and blueprint stage, a core functional process is the determination of the
corresponding business logic found in R/3 or legacy data systems and how to accomplish the same logic in BW. Fortunately, SAP has provided several structured paths to follow in order to implement this business logic. In this article,it can be determined what’s, where’s and how’s of ABAP programming in BW.Additionally, it also gives focus on SAP recommendation to improve ABAP Code execution. These "tips" on ABAP and BW are written with BW version3.x and R/3 4.6C in mind, but most of them will apply to any versions of R/3 from 4.x and up ,and any versions of BW from 2.0 and up. This article will begin with an overview of the prospective involvement of ABAP code, where we can find this code, and how best to include coding for specific areas without
impacting other functionality. Our examples for this article will include R/3 data source modifications and the creation of generic extractors in R/3 for use with BW. Remember that in SAP BW, most everything is interconnected, and we share a great deal of data amongst several data targets. Therefore, we will need to be specifically aware of how we proceed with our data or record updates and where we decide to place ABAP programming that will manage these updates. Finally, it can be examined that what possibilities exist for using ABAP to customize our BW environment.

2.     What can we do with ABAP in BW

Between SAP R/3 and BW, there are distinct differences between how we can use ABAP programming in these environments and what we can expect as an outcome of development. In order to understand how ABAP is used in BW, we should have a general understanding of the infrastructure of BW. Generally speaking, in BW, we are dealing with Objects that we connect with some type of configuration, such as the linking of an ODS with an InfoCube, or the creation of source system definitions and datasources. What we will NOT be doing on a general basis in BW is building screens and multiple user exits in ABAP to gain
additional functionality. This is the distinct difference in BW as it relates to R/3. In BW, we will use ABAP to transform our data as requirements dictate, as well as provide additional processing during extraction and updates.

Following is the area in BW where we use ABAP

Technical   Area	Description
BEx User   Exit	Allows the creation and population of variables and calculations for   key figures and variables on a runtime basis. This user exit is called each   time BEx is executed.
R/3 User   Exit	This user exit is found in R/3 under CMOD and contains additional   programming that is needed to fill field additions to extract structures. We   can also create additional business logic against data prior to its transfer   to BW.
BW   Transfer rules	These rules are invoked as data is transferred from R/3 to BW, or from   within BW as export data sources and InfoSources are created.
BW Update   Rules	These rules are invoked as data is being updated into data targets.   Whether ODS Objects or InfoCubes, each update results in the execution of an   Update Rule.
BW Start Routines	Start routines in BW Update Rules are used to manage whole subsets of   data. This is different than a simple update rule in that update rules are   data element specific.

3.     ABAP Programming Options in R/3 for BW

Option 1  

Add fields to append structure for an existing extract structure.

Add corresponding ABAP logic to fill these fields.

Option 2

Create a table or view that represents our data set.

Use Transaction RSO2 to create a generic extractor.

Either write custom ABAP program to fill that table or add code to function module or user exit to fill fields or modify data in structure during extraction.

For Option 1

This is the most straightforward modification in ABAP and is commonly employed to add data requirements that do not exist in the standard extractor. We do this using the basic concept of append structures for our additional fields. Once we have added our fields and activated our extract structure, we will be able to see these fields in the user exit.A commonly overlooked step in
adding fields to extract structuresis actually making the newly added fields visible to the BW target system. Once the fields are added to the extract structure  and you have saved and activated both the append structure and the extract structure, you will need to go to transaction code RSA6 -> Post Processing Datasources and find the datasource you modified. Double click on that datasource and make sure you uncheck the "hide" check box for the added fields. Otherwise, these fields will not show up in the newly replicated datasource in BW.At this point, we can look at how we can populate these  fields with data in the R/3-to- BW user exit in R/3. Using transaction code CMOD, go to your BW project, i.e., "Z_BW".This project will be found within the customer namespace. Once you have found the correct project, choose "display components" and hit F7. This will display the BW user exit components. You should find four user exits:-

User Exit	Description
SAPLRSAU_001	Transactional data user exit
SAPLRSAU_002	Master Data User exit
SAPLRSAU_003	Optional
SAPLRSAU_004	Optional

The configuration of these user exits may differ from system to system.To provide an example, let'sconsider a standard-delivered data extractor found within Logistics Cockpit. This extractor is responsible for extracting purchase order item data found in R/3. For our example, let’s assume you have added several fields to the 2LIS_02_ITM extract structure.

You now want to add your programming logic to fill these fields and then re-initialize BW. You will double click on SAPLRSAU_001 and you will be taken to the correct include file for the top-level program SAPLRSAU_001. Once there, you will double click on the include ZZRSAU01. This include is where all your customer logic will be written. Once you begin extraction and the extract structure has been filled with the initial data from standard extracts, you will find yourself within the When logic and ready to begin to append data to each record. In this user exit, you will loop through each record and append the necessary data. Then, in the final step within the loop, you will add all the new data elements to the actual extract structure and return to the next record. Loop like this through each record until you are finished with each record in the initial extract structure.

   For Option 2 :-

• Calling transaction SE11 and setting the radio button to View, we input the Z****** name for our view and click Create.
• When prompted for a development class, input the correct development class and click save. This will bring us to the
  initial view definition screen.
• Begin by adding the fields you need, supplying the base table names as well. You will need to complete all the steps
  required to finish the view.
• Create a custom program to fill the base table as per the business logic.
• Once done and active, check to see how many records your view returns and compare that number to the number of records
  found in the base table(s).
• Once you have your view completely finished, you can go to transaction RSO2.
• Input the view name, making sure the correct options are checked for using a view instead of an extract structure/
  click the save button.
• If you’re successful, you can go to RSA3 and run the extract checker for you new data source.
• You should double check the RSA3 output
  with the view definition as a unit test. This will give you a preliminary fitness test for the generic data source.

4.     How to improve performance

There are several recommendations by SAP, which can improve the   performance of the code execution. Some are
below:-

4.1    Use of selection criteria:-

Instead of selecting all the data and doing the processing during the selection, it is advisable to restrict the data to the selection criteria itself, rather than filtering it out using the ABAP code.

    Not recommended:-

                        Select * from /bi0/mcustomer.

                    Check : soursystem = ‘I2P’ and
                                   objvers = 'A'.

                   Endselect.

    Recommended:-

              Select * from /bi0/mcustomer where
                    soursystem = ‘I2P’ and                                    

                    objvers= 'A'.

           Endselect.

One more point to be noted here is of the select *. Often this is a lazy coding practice. When a programmer gives select * even if one or two fields are to be selected, this can significantly slow the program and put unnecessary load on the entire system. When the application server sends this request to the database server, and the database server has to pass on the entire structure for each row back to the application server. This consumes both CPU and networking resources,especially for large structures.

Thus it is advisable to select only those fields that are needed, so that the database server passes only a small amount of data back.

Also it is advisable to avoid selecting the data fields into local variables as this also puts unnecessary load on the server. Instead attempt must be made to select the fields into an internal table.

Use of aggregate functions

Use the already
provided aggregate functions, instead of finding out the minimum/maximum values using ABAP code.

4.2    Use of aggregate functions :-

Use the already provided aggregate functions, instead of finding out the minimum/maximum values using ABAP code.

Not recommended

            Maxamount = 0.

           Select * from /bic/mzordods where
`               soursystem = ‘I2P’.

             Check mzordods-zamount>maxamount.

             Maxamount = mzordods-zamount.

            Endselect.

Recommended

        Select max(zamount)
          from /bic/mzordods into maxamount

          where soursystem =’I2P’.

The other aggregate functions that can be used are min (to find the minimum value), avg (to find the average of a Data interval), sum (to add up a data interval) and count (counting the lines in a data selection).

Use of Views instead of base tables

Many times ABAP programmers deal with base tables and nested selects. Instead it is always advisable to see whether there is any view provided by SAP on those base tables, so that the data can be filtered out directly, rather than specially coding for it.

4.3    Use of the into table clause of select statement:-

Instead of appending one record at a time into an internal table, it is advisable to select all the records in a single shot.

Not recommended:-

     Refresh :     itab_cust_t

     select *   from /bi0/tcustomer

      into itab_cust_t

     append itab_cust_t.

   clear itab_cust_t.

Recommended:-

     Refresh : itab_cust_t

     select *   from /bi0/tcustomer

          into table itab_cust_t

4.4    Use of binary search option

When a programmer uses the read command, the table is sequentially searched. This slows down the processing. Instead of this, use the binary search addition. The binary search algorithm helps faster search of a value in an internal table. It is advisable
to sort the internal table before doing a binary search. Binary search repeatedly divides the search interval in half. If the value to be searched is less than the item in the middle of the interval, the search is narrowed to the lower half, otherwise the search is narrowed to the upper half.

Not Recommended

Read table itab_cust_t with key soursystem=’I2P’.

Recommended

Read table itab_cust_t with key soursystem=’I2P’ binary search.

4.5    Appending To internal tables

Instead of using the normal loop-endloop approach for this kind of programming, use the variation of the append command. Care should be taken that the definition of both the internal tables should be identical.

Not Recommended

Loop at itab_cust_t.

Append itab_cust_t to itab_cust_t1.

EndLoop.

Recommended

Append lines of itab_cust_t to itab_cust_t1.

4.6    Using table buffering

Use of buffered tables is recommended to improve the performance considerably. The buffer is bypassed while using the following statements

1.         Select distinct

2.         Select … for update

3.         Order by, group by,
having clause

4.         Joins

Use the Bypass buffer addition to the select clause in order to explicitly bypass the buffer while selecting the data.

4.7    Use of FOR ALL Entries

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

1.         Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

2.         If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

3.         If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at itab_cust_t.

Select single * from /bi0/mcustomer into itab_cust_m

Where soursystem= itab_cust_t-soursystem.

Append itab_cust_m.

EndLoop.

Recommended:-

Select * from /bi0/mcustomer appending table itab_cust_m

For all entries in itab_cust_t

Where soursystem= itab_cust_t-soursystem.

4.8    Proper structure of Where Clause

When a base table has multiple indices, the where clause should be in the order of the index, either a primary or a secondary index.

To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields. One more tip is that if a table begins with MANDT, while an index does not, there is a high possibility that the optimizer might not use that index.

In certain scenarios, it is advisable to check whether a new index can speed up the performance of a program. This will come handy in programs that access data from the finance tables.

Proper use of Move statement

Instead of using the move-corresponding clause it is advisable to use the move statement instead. Attempt should be made to move entire internal table headers in a single shot, rather than moving the fields one by one.

Proper use of Inner Join

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

4.9    Use of ABAP Sort instead of Order By

The order by clause is executed on the database server, while the sort statement is executed on the application server. Thus instead of giving the order by in the select clause statement, it is better to collect the records in an internal table and then use the sort command to sort the resulting data set.

5.     Tools provided for Performance Analysis

Following are the different tools provided by SAP for performance
analysis of an ABAP object

1.         Run time analysis transaction SE30

This transaction gives all the analysis of an ABAP program with respect to the database and the non-database processing.

2.         SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on
the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client
environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

6.     Limitation

Generally, when adding code, we can assume some type of run time overhead in the form of processing time or memory impact. Performace of a program is also often limited due to hardware restrictions, which is out of the scope of this article.

7.     Conclusion

The use of ABAP in the BW environment is widespread and is impossible to cover in its entirety within this article. However, it is most certainly possible to get a solid grasp of the concepts required to understand the what, when, and how of ABAP programming in BW.