Usage of ABAP in BI Applies to: SAP BW 3.5, SAP BI 7.0 etc., For more information, visit the EDW homepage. Summary This paper has been prepared to give an insight view about the usage of ABAP in BI by illustrating specific examples. Author: Poornima Gayatri Company: Deloitte Consulting Created on: 17 May 2011 Author Bio Poornima Gayatri Chennur is currently working in Deloitte Consulting India Pvt. Ltd. She is working on SAP BW/BI from last 3.10 years. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 1 Usage of ABAP in BI Table of Contents Introduction ......................................................................................................................................................... 3 InfoPackage Routines ......................................................................................................................................... 3 In Extraction Tab ............................................................................................................................................. 3 Use of the Routine ....................................................................................................................................................... 3 Example ....................................................................................................................................................................... 3 In Data Selection Tab ...................................................................................................................................... 5 Use of InfoPackage Routine ........................................................................................................................................ 5 Features ....................................................................................................................................................................... 5 Selections using an ABAP routine (variable type 6) ..................................................................................................... 5 Example ....................................................................................................................................................................... 6 Selections using a variable (variable type 7) ................................................................................................................ 8 Example ....................................................................................................................................................................... 8 Steps for Creating the OLAP Variable ......................................................................................................................... 9 DTP Filter: ......................................................................................................................................................... 13 Example: ....................................................................................................................................................... 14 Routines in Transformations ............................................................................................................................. 16 Start Routine: ................................................................................................................................................ 18 End Routine .................................................................................................................................................. 18 Routine for Characteristics or Key Figures ................................................................................................... 19 Expert Routine .............................................................................................................................................. 19 Example for Start Routine, End Routine Field Routine and Expert Routine ................................................. 20 Start Routine: ............................................................................................................................................................. 20 End Routine ............................................................................................................................................................... 21 Field Routine or Routine for Characteristics or Key Figures ...................................................................................... 22 Expert Routine ........................................................................................................................................................... 24 BI Variables....................................................................................................................................................... 26 Example ........................................................................................................................................................ 27 Related Content ................................................................................................................................................ 31 Disclaimer and Liability Notice .......................................................................................................................... 32 SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 2 Usage of ABAP in BI Introduction In some cases we need to enhance functionalities of BI by using ABAP technology to manipulate and transform the data according to the user’s requirement. We can use ABAP coding majorly in  InfoPackage Routines  Filters in DTP  Start Routine  End Routine  Expert Routine  Field Routine  Customer Exit for Query Variables InfoPackage Routines For InfoPackages Routines can be created at: 1) In Extraction Tab – A routine can be created that determines the name of your file. 2) In Data Selection Tab – Routines can be created at Data Selection tab page to determine the data selection from source systems. In Extraction Tab A routine can be created at Extraction page to determine the name of the file. The data can be loaded either from presentation server or from application server. Use of the Routine If the user changes the flat file which contains data to be loaded time to time, then the file has to be updated manually every time it is being changed. Instead a routine can be created to load the file. Whenever the InfoPackage runs, the routine will be executed and according to the logic, the data will be selected. Example In this example, a scenario is taken, in which the file will be selected using a routine and the data from the file is loaded according to the routine. The file is selected from the presentation server. Whenever the InfoPackage is executed, it will check for the file with .CSV extension and loads the file. In the extraction tab, click on icon ‘Create Routine’. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 3 Usage of ABAP in BI A pop up appears to enter the name of the routine. Enter the Routine name and click on Editor. Editor appears where the code can be written. In this example, the routine is written to check whether a .csv file appears in the folder and the data will be loaded from the file accordingly. The code is given below. Save the routine. Schedule the InfoPackage. The records will be loaded from the file. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 4 Usage of ABAP in BI In Data Selection Tab Use of InfoPackage Routine To load data into BI from the source system periodically and to change the contents of the selection field each time of the load, it is possible to define the selections for the fields in the InfoPackage. The variable change can be implemented with each load by using ABAP routines or (OLAP) variables. The variable selections are not replaced by concrete values until a data request is made. Features We can use either ABAP Routine (Variable type 6) or OLAP Variables (Variable type 7) in the Data selection while loading the data using InfoPackages. Selections using an ABAP routine (variable type 6) An ABAP program can be written to restrict the requested data of an InfoObject or field. Then, the select type should be given as ‘6’. A screen appears on which the name for the ABAP routine can be given. After entering the name, the editor appears and the code can be entered here. The ABAP routine has access to all selection fields and is the last to be processed at runtime. Explicitly following definitions can be made for single value selections and intervals in the routine: For the field l_t_range-option = "EQ" or "BT" and for l_t_range-sign = 'I'. Note that there is no check whether the field contents are meaningful. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 5 Usage of ABAP in BI Example The below example is demonstrated by restricting the data for the filed ‘BUDAT’ in the InfoPackage created for the DataSource ‘0FI_GL_14’.In this example, the InfoPackage will pick up the data from the date which is less than the current date. In the Data Selection Tab of the InfoPackage, select the Type as ‘ABAP Routine’. There will be an automatic pop up to enter the description for the routine. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 6 Usage of ABAP in BI Once it enters into ABAP editor, the code can be written accordingly. Logic for the code to enter the value of the Posting date which is less than current day. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 7 Usage of ABAP in BI Monitor Screen with Selection Criteria The InfoPackage is run on 10/03/2011. So we can see that the date displayed here is 09/03/2011. Selections using a variable (variable type 7) (OLAP) variables are used as placeholders for values of InfoObjects. They are replaced with concrete values during a data request. Then, select type should be given as ‘7’. A screen appears on which you can select the variables. Example The below example is demonstrated by restricting the data using and OLAP Variable for the filed ‘FISCPER (Fiscal Period)’ in the InfoPackage created for the DataSource ‘0FI_AR_4’.In this example, the InfoPackage will pick up the data from the data only for current Fiscal Year. In the Data Selection Tab of the InfoPackage, select the Type as ‘OLAP Variable’. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 8 Usage of ABAP in BI A Pop up appears where we can select the name of the OLAP Variable. Here the name of the variable should be mentioned. Steps for Creating the OLAP Variable 1) Create a new project using CMOD Transaction code. 2) Select SAP enhancement RSR00001 and assign it to the project. 3) Code the logic. 4) Creation of Variable in BEX Designer. Step 1: Create a new project using CMOD Transaction code. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 9 Usage of ABAP in BI Step 2: Select SAP enhancement RSR00001 and assign it to the project. In the Components Screen, there is an Exit available and the name of the Exit is ‘EXIT_SAPLRRS0_001’, and we can write the code as per requirement. Step 3: Once we click on the exit, we can see the below screen. SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com © 2010 SAP AG 10