Wednesday, April 25, 2012

Writing quick lookup transformation code for SAP BW.

When working with data in BW you will often need to do a lookup in the masterdata to be able to add the corresponding attribute coming from the communication structure during the transformations in SAP BW. Logically you want to have a lookup which is fast during runtime. In the following example we will give you a basic description how this code should look like, to ensure the performance of the transformation during the ETL process within BW.
This blog also explains the difference in types of internal tables which is important during coding to improve the performance even more.
The given example is devided in two parts. The start routine, in which we select the masterdata from the table, and the transformation part, where we use the selected masterdata to convert the data from the source attribute to the target attribute.


 *$*$ begin of global - insert your declaration only below this line  *-*

TYPES: BEGIN OF ty_netw,
             l_netw       TYPE  /BI0/OINETWORK,
             l_wbs_elemt  TYPE  /BI0/OIWBS_ELEMT,
       END OF ty_netw.

DATA:  wa_netw    TYPE ty_netw,
       t_netw     LIKE TABLE OF  wa_netw.

*$*$ end of global - insert your declaration only before this line   *-*

********* Filling Internal table for NETWORK *******

    INTO TABLE t_netw
  SORT t_netw BY l_wbs_elemt.


Transformation routine:

********** Begin transformation code ***************

    clear wa_netw.
    READ TABLE t_netw INTO wa_netw WITH KEY
               l_wbs_elemt  = COMM_STRUCTURE-WBS_ELEMT
                   BINARY SEARCH.
    IF sy-subrc EQ 0.
      RESULT = wa_netw-l_netw.
      RESULT = ''.
    RESULT = ''.

********** End transformation code ***************

Internal table types explained

Hashed Tables:

These tables are very useful for programming exits in BI.It is managed with an internal hash procedure. You can imagine a hashed table as a set, whose elements you can address using their unique key. Unlike standard and sorted tables, you cannot access hash tables using an index. All entries in the table must have a unique key which is stored in the table definition.

Sorted Tables:

These tables are sorted by specific non-unique keys.If these keys are used, the read from the table is faster.

Standard Tables

These tables are not restricted with respect to sorting or reading access with respect to keys.

Which internal table is the best to use:

By definition, internal tables are user defined structured data types. These internal tables can be created as a replica of data base table, using some or all fields of one or more tables. These internal tables are created only during runtime. You should determine whether you use a sorted table or a hashed table by the uniqueness condition:
  • Use a hashed table if the table has a unique key and if all accesses use the complete key. This is actually rarely fulfilled.
  • In all other cases, when the key is non-unique or when you need also accesses to ranges determined by a leading part of the key, then a sorted table is better.
  • Performance-wise standard tables should be avoided, if they can become large.