Lookup in SAP BW

Applies to:
This article talks about Lookup concept in SAP BW.
For more information, visit the Business Intelligence homepage.

Author: Prasun Saha
Company: Accenture
Created on: 21st February 2013
Author Bio
Prasun Saha is working for Accenture as an SAP BI Consultant and specializes in  SAP BI, Informatica.

Contents:

1.       Lookup definition in SAP BW

2.       Technique to use lookup concept in BW

3.       Look Up Using Start Routine and Field Level routine

1. 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

Step1:

Define a structure (Type) of Lookup table and choose the fields to be extracted from Lookup Table.

Step2:

Declare internal table to store the selected data from Lookup table ( here DSO)

Step3:

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.

Step4:

·         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

Step 5:

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

Step1:

Define a structure (Type) of Lookup table and choose the fields to be extracted from Lookup Table (Master data table pmat_plant).

Step2:

Declare internal table to store the selected data from LookUP table (Master data table pm…..)

Data:

Step 3:

Declaration of Work Area:

DATA:

Step4:

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)

Step5:

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)