Lookup in SAP BW
This article talks about Lookup concept in SAP BW.
For more information, visit the Business Intelligence homepage.
Author: Prasun Saha
Created on: 21st February 2013
Prasun Saha is working for Accenture as an SAP BI Consultant and specializes in SAP BI, Informatica.
1. Lookup definition in SAP BW
2. Technique to use lookup concept in BW
3. Look Up Using Start Routine and Field Level routine
- 3.1 STEPS TO FOLLOW
4. Lookup through END routine:
4.1 STEPS TO FOLLOW
Lookup defination SAP BW:
· Concept of Lookup is same in SAP BW as other Data warehousing tools .
For performing lookup a key column with the source BW table and lookup table must match.
· Lookup table can be any SAP BW infoproviders (like, DSO , MD table,Fact table etc.)
· If the key columns in source or result package are matched with the lookup table key column then those corresponding rows will only be passed to output Target. Others can be taken in reject.
We need to pass a key column from source table and we will get the required data once the key column matches
Technique to use lookup concept in BW:
Lookup normally we can do in Start routine, Field level Routine and in End routine.
In this document we will explain
· lookup through Start and Field routine
· Lookup through END routine
A scenario where we can use Lookup in BW:
Let us say we have 5 characteristics in DSO and we created Info Cube on this DSO. Now user wants one more characteristic extra in InfoCube for Data Analysis. Suppose we have those required Characteristic in BI, but we don’t have it in source (here it is DSO). In this above case by using lookup we can populate the values for the extra characteristic values in InfoCube while loading data from DSO if we have a common key present between DSO and Infocube.
Look Up Using Start Routine and Field Level routine:
In the below example we have a source DSO and we are sending data to a Target Open Hub.
We have to calculate a Key figure “TO be delivered Quantity” which is not coming from our Source DSO.
We will get this field from another DSO “ZM……7 “and it will be our Lookup Table
KEYS BETWEEN SOURCE AND LOOKUP TABEL:
· The matching keys between our Source DSO and Lookup table are
Purchase Document Number ( EBELN)
Item Number of Purchase document (EBELP)
FIELD TO BE RETURNED FROM LOOKUP TABLE:
· It is ‘TO BE DELIVERRED QUANTITY’
· ‘TO BE DELIVERRED QUANTITY’ is a calculated field which is found by doing a minus operation between Quantity and Received quantity of look up DSO ZM….7
· TO BE DELIVERRED QUANTITY’==(Sum of ) Quantity on Purchase document and Item number – ( Sum of ) Received quantity on Purchase document and Item number
· In Transformation write a start routine to select the Lookup Table data and store in an Internal Table.
STEPS TO FOLLOW
Define a structure (Type) of Lookup table and choose the fields to be extracted from Lookup Table.
Declare internal table to store the selected data from Lookup table ( here DSO)
Define Work Area.
· Work area will be referred in Field routine, after finding the matched keys , record wise insertion is done into Target for selected fields.
· Only a single record can hold the work area at a time and insertion of that record is executed through work area.
· Selection of fields like ebeln,ebelp ,quantity(SUM OF QUANTITY) and spl_quantity(SUM OF RECEIVED QUANTITY) are done from Lookup DSO “ZM……7 “ and selected all records are inserted into Internal table gt_zm……7
· In Transformation map the matching keys ( Purchase document number –EBELN and Item Number of Purchase document –EBELP) to target Infoobject (i.e To Be delivered Quantity)
MAPPING FROM SOURCE TO TARGET IN TRANSFORMATION
In Field Routine we have to write the calculation for filed ‘To be delivered quantity’.
ACTUAL DATA POPULATION IN TARGET FOR THE LOOKUP FIELDS WILL HAPPEN THROUGH FIELD ROUTINE
· Quantity is subtracted from Received Quantity( spl _rcvqty) in Field routine to calculate ‘To be delivered quantity’
· Both of these fields were taken from lookup DSO “ZM……7 “and calculated the new field value for ‘TO BE DELIVERRED QUANTITY’ which is populated finally to Target Open hub.
Lookup through END routine:
· In End routine no mapping is required from source to target fields in transformation for lookup.
· All selection of records from look up table and assignment of fields into target and then insertion of records into target everything is done through End Routine.
Here in this example
We have referred the same source DSO and Target Open hub as above example
In target we have 2 fields marked MRP Controller and MRP type .
· These 2 fields are not coming from source DSO, We will populate the value for these 2 fields in Open hub using a END Routine in transformation.
· We will look up a Master data table named as /BIO/P……for the fields MRP Controller and MRP type .
· Matching keys are Material_Plant and Plant between RESULT PACKAGE and Lookup table.
STEP TO FOLLOW
Define a structure (Type) of Lookup table and choose the fields to be extracted from Lookup Table (Master data table pmat_plant).
Declare internal table to store the selected data from LookUP table (Master data table pm…..)
Declaration of Work Area:
Selection from Look up table ( here lookup table is a Master Data table)
· Here matching key is MAT_PLANT, PLANT,SOURCE SYSTEM, OBJERVS.
· On finding the match on keys MAT_PLANT, PLANT,SOURCE SYSTEM, OBJERVS fields
mat_plant, plant ,mrp control and mrp type are returned from lookup table
(Master Data table ).
· All records which are returned from Lookup table are inserted into Internal table called lt_m…. (shown below)
One by one all records of result package are checked with internal table on material_plant number and plant.
· On matching material_plant and plant 2 fields are populated in Target Open Hub these are MRP Control and MRP Type(DISMM)
Happy to read 😆 .
Very Good Explanation
I have a query.Suppose,Instead of a calculation,I want to look up a field from the look up DSO.
So,Can I go for "Read from DSO" option available in transformation rule details.If I opt for this,will any load performance degradation will be there ,compared to writing end routine/field routine?
Bw 7.3 onwards rather than code, we need to use read data from DSO if its suits.
its better option than writing code.
if we use read from DSO then code is not required.
That's a good option. Is that read option will work for reading data from info cube as well?
I am asking this because we are implementing 7.4 as we communicate. Just curious to know since lookup on a multi-dimensional cube is not that easy in earlier versions and we don't prefer that normally. But there are situations where we need to think about this.
Tiill now we have options to read data from DSO and Master data only.
if SAP introduce read from Cube then it will be good for us.
Yes, that would be really useful in many critical situations. Thank you for the information
Have a good weekend
Very detailed and easy to understand. 🙂 Kudos!
No use of HASHED internal tables, multiple database reads which could be consolidated into one.
This will lead to long runtimes. When doing complex abap, get a competent abapper to do it. Don't try to do it yourself.