Transaction Data Lookups in SAP BW Routine
In this Blog I will describe about the Transaction Data Lookups in SAP BW. Using this blog a new developer can understand the lookup mechanism and Copy and customize the code from the blog in very easy way. Which will help in their initial time to develop BW data flow.
1. Transactional Data Lookup : We can lookup Master data from the Master data infoobject (P Table) from -.
- Field lookup routine : Not recommended as if there is 40 row record in a packet 100 times lookup. Slow the dataflow performance.
- From SAP BW start routine: Used for data preparation.
- End Routine: Simple and easiest way to lookup transactional data from End routine. I will use this method to lookup.
- Expert routine : This also we can use for transactional data look up.
Transactional Data look up using End Routine:
The requirement is same but scenario is different, in our ADSO which have Order and Material(0Material) . Need to WORKCENTER(0WORKCENTER) and Work Center responsible person(WRKCT_RESP).
But we can not route Via 0MAT_PLANT as PLANT and WORKCENTER has one to Many relationship and if we implement the lookup it will be the first row WorkCentre will be populated which is not restricted.
- Use Order for lookup key from an Transactional data source.
- In our case it is ADSO PPAARBPL.
- Use Order(COORDER) PLANT as lookup key populate PLANT and WORKCENTER(WORKCENTER) from /BIC/APPAARBPL2 table of ADSO PPARBPL.
- Use PLANT and WORKCENTER as lookup key to populate Work Center responsible person(WRKCT_RESP) from P table of 0WORKCENTER infoobject.
1. Add Infoobject which need to populate in the Target i.e. for our case it is ADSO. Activate it.
2. Edit the respective transformation to the target ADSO and add it to Routine .Then open the routine and start to develop lookup program.
4. Find the table behind the ADSO PPAARBPL –
5. Now Start to add the code below code in end routine. –
METHOD end_routine. *=== Segments === FIELD-SYMBOLS: <RESULT_FIELDS> TYPE _ty_s_TG_1. DATA: MONITOR_REC TYPE rstmonitor. *$*$ begin of routine - insert your code only below this line *-* TYPES: BEGIN OF ls_wctr, PLANT TYPE /BI0/OIPLANT, WORKCENTER TYPE /BI0/OIWORKCENTER, WRKCT_RESP TYPE /BI0/OIWRKCT_RESP, END OF ls_wctr, BEGIN OF ls_wct, PLANT TYPE /BI0/OIPLANT, WORKCENTER TYPE /BI0/OIWORKCENTER, PRODORDER TYPE /BI0/OIPRODORDER, END OF ls_wct. DATA: lt_wctr TYPE STANDARD TABLE OF ls_wctr, wa_wctr TYPE ls_wctr, lt_wct TYPE STANDARD TABLE OF ls_wct, wa_wct TYPE ls_wct, **** Populate workcenter from /BIC/APPAARBPL2 ************************* SELECT PLANT WORKCENTER PRODORDER FROM /BIC/APPAARBPL2 INTO CORRESPONDING FIELDS OF TABLE lt_wct FOR ALL ENTRIES IN RESULT_PACKAGE WHERE PRODORDER = RESULT_PACKAGE-COORDER AND PLANT = RESULT_PACKAGE-PLANT. *** Polulate workCenter in DSO***************************************** CLEAR wa_wct. LOOP at RESULT_PACKAGE ASSIGNING <result_fields>. READ TABLE lt_wct INTO wa_wct WITH KEY PRODORDER = <result_fields>-COORDER PLANT = <result_fields>-PLANT . IF sy-subrc = 0. <result_fields>-WORKCENTER = wa_wct-WORKCENTER. ENDIF. ENDLOOP. ***** Populate workcenter fron OBJNR **************************** SELECT PLANT WORKCENTER WRKCT_RESP FROM /BI0/PWORKCENTER INTO CORRESPONDING FIELDS OF TABLE lt_wctr FOR ALL ENTRIES IN RESULT_PACKAGE WHERE WORKCENTER = RESULT_PACKAGE-WORKCENTER AND PLANT = RESULT_PACKAGE-PLANT. CLEAR wa_wctr. LOOP at RESULT_PACKAGE ASSIGNING <result_fields>. READ TABLE lt_wctr INTO wa_wctr WITH KEY WORKCENTER = <result_fields>-WORKCENTER PLANT = <result_fields>-PLANT. IF sy-subrc = 0. <result_fields>-WRKCT_RESP = wa_wctr-WRKCT_RESP. ENDIF. ENDLOOP. *------------------------------------------------------------------
4. The sub steps inside the ABAP code describe below –
- Create local structure.
- create work area of type work structure.
- Lookup on ADSO table using order as a lookup key.
- Populate the PLANT WORKCENTER field in the result set.
- Repeat similar 4 step to populate WorkCentre responsible person in the result set only use prepopulated PLANT WORKCENTER as lookup key.
Now anybody can customize and write their own code which will save the development time for transactional data lookup.
If you use
always check if the itab is empty or the system will read all data from the DB table, which is often a big performance issue.