Looking up a table while doing data load is a common scenario in SAP BW.
Here the look up table can be active table of some other DSO.
You can note a point here that cube cannot be used for look up. This is because, a cube has a fact table which is based on star schema. Hence the primary keys in a fact table will be dimension ids instead of the primary key values.
Now coming to our scenario, suppose data is loaded from DSO1 to CUBE1. In this load, a DSO2 table has to be looked up. The look up will happen based on some common fields of DSO1 and DSO2.
Below is the tabular form of the scenario to make it more clear-
|Source||Target||Look Up table|
The look up code can be written in the start, field or end routine based on your specific requirement.
Generally, below criteria is followed-
- Start Routine – The look up in the start routine is mainly written when we are trying to filter out some records based on criteria involving look up table. Another scenario could be storing values from the look up table in some global internal table for using later in the same transformation.
- Field routine – Look up in the field routine is not recommended as it will hamper the performance of the load.
The load times will increase as the field routine is executed record by record. Whereas the start and end routine are executed per package.
Look up in the field routine might be needed for calculations based on field values and the look up table.
- End Routine – This generally takes place if any condition depends on lookup table for performing calculations in the end routine based on some target fields.
Sample ABAP routine for look up-
TYPES: BEGIN OF ty_dso2, /bic/zprod TYPE /bic/oizprod *Select fields needed from the look up * table * Here /bic/zprod is a field in the DSO2 and /bic/oizprod is the * corresponding data element END OF ty_dso2. DATA: ls_dso2 TYPE ty_dso2. *Define work area with structure like above type DATA: lt_dso2 TYPE STANDARD TABLE OF ty_dso2. *Define an internal table with * structure as above type LOOP AT source_package ASSIGNING source_fields. IF <your condition e.g. source_fields-billing qty > 100> SELECT /bic/zprod_del_qty FROM /BIC/ADSO2 INTO TABLE lt_dso2 WHERE <compare primary keys for e.g. product id, customer id and calday in both DSO1 and DSO2 are same> * This performs the look up based on your condition e.g select product delivery * qty from DSO2 if the billing qty for same productid, customer and * calday in DSO1 > 100