Ralph Bonink
Read all my blogs
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.
Startroutine:
<input type="text" value="Hello World" id="myInput">
*$*$ 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 *******
SELECT NETWORK WBS_ELEMT
FROM /BI0/PNETWORK
INTO TABLE t_netw
WHERE OBJVERS = ‘A’.
SORT t_netw BY l_wbs_elemt.
*********************************************
Transformation routine:
********** Begin transformation code ***************
IF NOT COMM_STRUCTURE-WBS_ELEMT is initial.
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.
ELSE.
RESULT = ”.
ENDIF.
ELSE.
RESULT = ”.
ENDIF.
********** 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.
Receive our weekly blog by email?
Subscribe here:
