infopackage routines delta2
TRANSCRIPT
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 1/13
Making Flat File Data Sources DeltaCapable By Using Routine In Infopackage Selection.
Applies to:
This article applies to SAP BI 7.0. For more information, visit the Business Intelligence homepage.
Summary
This document explains how to make flat file dataSources delta capable by using routine in infopackageselection. We will also see usage and implementation of the routine in infopackage selection.
Author: Anurag Krishna Dev
Company: Infosys Technologies LimitedCreated on: 21 May 2009
Author Bio
Anurag Krishna Dev is an SAP Certified Solution Consultant and working at Infosys Technologies. Hhas been involved in SAP BI consulting practice for 3 years now. He has expertise in executingimplementation and support projects for multiple clients.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 1
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 2/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Table of Contents
Introduction.........................................................................................................................................................3 Flat file data Sources..........................................................................................................................................3 Business Scenario..............................................................................................................................................5
Step 1- Identify the delta field .........................................................................................................................5 Step 2- create flat file dataSource...................................................................................................................6 Step 3- create init info package ......................................................................................................................6 Step 4- create delta infopackage....................................................................................................................7 Step 5 - implement the ABAP routine in selection..........................................................................................7
How to identify the last successful extraction:....................................................................................................8 The program flow of the ABAP routine ...........................................................................................................8 Debugging and testing of ABAP routine.......................................................................................................10 Step 6- Schedule the infopackage................................................................................................................11
Related contents...............................................................................................................................................12 Disclaimer and Liability Notice..........................................................................................................................13
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 2
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 3/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Introduction
This article explains about how to overcome the limitation of flat file dataSources when there is a needto load delta records in SAP BW. The document also explains step by step procedure to write custom ABAP code in infopackage dataselection tab.
Flat file data Sources
File source system is one of the many source systems available in SAP BW through which we can load
data in SAP BW. The process is to create first the file source system as shown in Fig 1. Then create theapplication component under which we need to create the dataSource as shown in Fig 2.
Fig 1
Fig 2
While managing the dataSource go to extraction tab page and under the delta process select “FullUpload (Delta from Infopackage selection only)” as shown in Fig 3.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 3
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 4/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Fig 3
Provide rest of the details and then go to ‘Field’ tab page , here we need to enable the selection anddecide the selection option for the fields based on which we need to implement delta functionality. Asshown in Fig 4.
Enable selectionfunctionality here.
Decideselectionoption here.
Fig 4
Following are the various selections Functionality:
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 4
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 5/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Following are the various selection options:
Provide rest of the regular parameters and activate the dataSource.
Business Scenario
Let’s take an example scenario where a company’s daily sales details happening at its outlets arecaptured in a flat file of which structure is like Fig 5
Plant Billing doc. date Billing document Item Quantity Amount Unit of Measure Currency
WSM0 20090417 1005 1 5 50000 ST INR WSM0 20090420 1006 1 5 50000 ST INR WSM0 20090421 1007 1 5 50000 ST INR WSM0 20090518 1008 1 5 1000 ST INR WSM0 20090520 1009 1 5 1000 ST INR
Fig 5
Subsequently at the end of the business day this file is loaded into SAP BW for sales reporting. Sincethe size of the file will grow rapidly over the time and it’s not practical to full load this file each day.Now the requirement is to implement the delta loading in flat file sources.
We will see how to step by step implement this loading
Step 1- Identify the delta field
As a first step we need to identify the field based on which delta records will be identified. In ourscenario “Billing doc date” is best suited for delta field because we are loading this file on a daily basisand with each day this Billing doc date will change. For some cases time stamp of records creation canalso be taken as delta field because most of the databases have a method for identifying new recordsand changes to database where by each new and changed record carries the time inyear/month/day/hours/minute/second.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 5
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 6/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Step 2- create flat file dataSource
In second step create the flat file data source, don’t forget to enable selection functionality and decidethe selection option for the field “Billing doc date” as shown in Fig 6. Once this is done then activatethe dataSource.
Fig 6
Step 3- create init info package
Now create the init infopackage and load all the historical data latest by the date this init infopackage isrun as shown in Fig 7.
Fig 7
Not to confuse I am saying this init infopackage for semantic purpose, technically it is a full load typeregular infopackage, in our mechanism of delta loading this infopackage will behave as init infopackage.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 6
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 7/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Step 4- create delta infopackage
In next step create another infopackage and we call it delta infopackage. Since in the dataSource wehad enabled the selection option for the field “Billing doc date” so this filed will be available for datafiltering in the data selection tab page of infopackage. Here we have three options for providing valuesfor data filtering
1. Hard coded values
2. ABAP routine
3. OLAP variable
Fig 8
For our scenario we will select the ABAP routine as shown in the Fig 8
Fig 9
Now in next step we need to write the ABAP routine in the place mentioned above which will be thebase line for identifying and filtering the delta records.
Step 5 - implement the ABAP routine in selection
Now we will discuss the mechanism and the logic to write this ABAP routine.
Our idea is to get the last successful extraction date of init or delta infopackage from the systemdatabase. Then we will compare this date with the “Billing doc date” of all the records. It then choosesthose records whose Billing doc date is greater than infopackage last successful extraction date.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 7
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 8/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
How to identify the last successful extraction:
Whenever we execute an infopackage to extract the data, the details are stored in system tables in SAPBW. This includes the details such as the infopackage, the request number generated by the system,the date and time when infopackage was executed, QM status and the number of records extracted.
For our example we will get all these records from system table rsreqdone in SAP BW.
Fig 10
The program flow of the ABAP routine
We will write this ABAP code in delta infopackage; firstly we will check whether this delta infopackage isbeing run for the first time by checking its entry in the table rsreqdone. If Yes ( Means no entry ispresent for this delta infopackage in table rsreqdone) then we will look for last successful extractiondate of the Init infopackage and if No then it means that delta infopackage has already been run one ormore time so in this case we will look for last successful extraction date of delta infopackage.
This last successful extraction date will become the lower limit of filter condition for “Billing doc date” and for high limit we can assign some far future date i.e. 99990101.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 8
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 9/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
No Yes
The Custom ABAP Code to be written in infopackage selection:program conversion_routine.* Type pools used by conversion programtype-pools: rsarc, rsarr, rssm.tables: rssdlrange.* Global code used by conversion rules*$*$ begin of global - insert your declaration only below this line *-**$*$ end of global - insert your declaration only before this line *-** -------------------------------------------------------------------* InfoObject =* Fieldname = BILL_DATE* data type = DATS* length = 000008* convexit =* -------------------------------------------------------------------form compute_BILL_DATE
tables l_t_range structure rssdlrangeusing p_infopackage type rslogdpidp_fieldname type rsfnm
changing p_subrc like sy-subrc.* Insert source code to current selection field*$*$ begin of routine - insert your code only below this line *-*
data: l_idx like sy-tabix.
l_idx = sy-tabix.tables: rsreqdone.data: begin of int_rsreqdone,
infopackage type RSLOGDPID,date type sy-datum,status type RSSTATUS,records type RSNUMRECORD,
end of int_rsreqdone.data : i_rsreqdone like table of int_rsreqdone with header line.data : i_t_rsreqdone like table of int_rsreqdone with header line.data: temp_rsreqdone type standard table of RSREQDONE with headerline,
wa_temp type rsreqdone.select * from RSREQDONE into table temp_rsreqdone.
read table temp_rsreqdone into wa_temp with keyLOGDPID = 'ZPAK_D5I6SMAXD1AXTCZMBDOVLLK63'TSTATUS = '@08\'.
The delta infopackage
Identify the lastsuccessful extraction
date of delta infopackage
Assign this date to Low limitof filter condition for “Billingdoc date” Assign some far future dateto High limit of filtercondition for “Billing docdate”
End
Check whetherany entry ismade for deltainfopackage intable rsreqdone
Identify the lastsuccessful extraction date
of init infopackage
Assign this date to Low limitof filter condition for “Billingdoc date”
Assign some far future dateto High limit of filtercondition for “Billing docdate”
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 9
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 10/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
if sy-subrc = 0.select LOGDPID DATUM TSTATUS RECORDS from rsreqdone into tablei_rsreqdonewhere LOGDPID = 'ZPAK_D5I6SMAXD1AXTCZMBDOVLLK63'and TSTATUS = '@08@'.sort i_rsreqdone by date descending.The delta infopackageread table i_rsreqdone into i_t_rsreqdone index 1 .l_t_range-low = i_t_rsreqdone-date.l_t_range-high = '99990101'.l_t_range-sign = 'I'.
l_t_range-option = 'BT'.modify l_t_range index l_idx .
else.select LOGDPID DATUM TSTATUS RECORDS from RSREQDONE into tablei_rsreqdonewhere LOGDPID = 'ZPAK_D5I6QSO1D806HY82KBOXE1D23'and TSTATUS = '@08@'.sort i_rsreqdone by date descending.
Init infopackage
read table i_rsreqdone into i_t_rsreqdone index 1 .l_t_range-low = i_t_rsreqdone-date.l_t_range-high = '99990101'.l_t_range-sign = 'I'.l_t_range-option = 'BT'.l_t_range-IOBJNM = '0BILL_DATE'.modify l_t_range index l_idx.
endif.p_subrc = 0.
*$*$ end of routine - insert your code only before this line *-*
Debugging and testing of ABAP routine
The ABAP routine written above can be tested and debug by clicking on the “routine testing” iconpresent in the data selection tab page infopackage. As shown in Fig 9
Click here for testingand debugging of ABAP routine
Fig 11Once you click on the icon mentioned above the system will give the output of the routine as a filtercondition applied for data selection, below is the window that comes where we can check the output of the routine
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 10
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 11/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Fig 12
Step 6- Schedule the infopackage
The infopackage execution process will be to first execute the init infopackage for the first time andthen onwards we will schedule the delta infopackage to be executed on a daily basis.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 11
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 12/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
Related contents
Routines in transformation
Several single value selection in InfoPackage Data selection using Routine
Use ABAP Routine in Selection Tab of Infopackage
http://www.help.sap.com
For more information, visit the Business Intelligence homepage.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
©2009 SAP AG 12
7/21/2019 Infopackage Routines Delta2
http://slidepdf.com/reader/full/infopackage-routines-delta2 13/13
Making Flat File Data Sources Delta Capable By Using Routine In Info package Selection.
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
Disclaimer and Liabili ty Notice
This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is notsupported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.
SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document,and anyone using these methods does so at his/her own risk.
SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article orcode sample, including any liability resulting from incompatibility between the content within this document and the materials andservices offered by SAP . You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of thisdocument.