Performance Tuning of Loads where delta is not possible

Summary:

There are many standard master as well as few transactional data loads in BI which do not support the delta concept. An example can be the 0NOTIFICATION Load where in we have to go for full loads daily as the standard datasource misses the delta capability. Also many times we have to build customized datasource on tables/views where we do not have a particular field on which we can build the delta. These full load which run daily unnecessarily take a lot of time for processing data thus leading to increase in overall load times. This can be overcomed with the concept of Pseudo Delta.

Pseudo Delta :

Pseudo delta can be considered as a customized delta but doing certain modifications to achieve the working concept analogous to standard delta. It helps a great deal in saving the loading efforts and time. This concept can also come handy for DB loads which fairly support delta concept.

Background:

There are generally two kind of records which are fetched from the datasources:

1) Newly/Freshly created records which have the ERDAT (Created ON) field populated with the date on which the record is created. Here the AUDAT (Changed ON) field is blank

2) Modified/Changed records which generally has AUDAT populated with the current date of change and ERDAT with the date on which it was created.

So there arises two possibilities on which we need to pull delta one based on newly created (ERDAT) and secondly based on modified versions (AUDAT). The idea is to pull the records based on the selection of ERDAT as well AUDAT for current date. We can consider a safety margin of two days. So only fewer records will be fetch avoiding the adhoc records fetched in each load.

Step by Step Approach:

Step1:

In standard datasources you need to check whether the fields AUDAT (Changed ON) and ERDAT (Created ON) are present. Generally they are present in almost all the standard datasourcres. From RSA6, go to the editable version of datasource and mark the fields AUDAT & ERDAT for selection. Replicate the changes to BI. For customized datasources we need to check in the underlying tables/views whether these fields are present and they should be included in the datasource design.

Step2:

Now the selections will be available in the infopackage of the corresponding datasource. We need to create two infopackges for pulling delta, one based on ERDAT and the other based on AUDAT.

Step 3:

In the first infopackage for ERDAT selection we need to fill it via an ABAP routine. Please refer to below routine to be written for the ERDAT Selection. We will consider a safety margin of two days.

DATA: l_idx LIKE sy-tabix.
READ TABLE l_t_range WITH KEY
fieldname = 'ERDAT'.
l_idx = sy-tabix.
 
Data: w_date type sy-datum.
 
w_date = sy-datum - 2. 

l_t_range-low = w_date.
l_t_range-high = sy-datum.
l_t_range-sign = 'I'.
l_t_range-option = 'BT'.
MODIFY l_t_range INDEX l_idx.

 
p_subrc = 0.

Step 4:

In similar way for second infopack, create a routine for field AUDAT. Here also safety margin of two can be considered.

DATA: l_idx LIKE sy-tabix.
READ TABLE l_t_range WITH KEY
fieldname = 'AUDAT'.
l_idx = sy-tabix.
 
Data: w_date type sy-datum.
 
w_date = sy-datum - 2. 
l_t_range-low = w_date.
l_t_range-high = sy-datum.
l_t_range-sign = 'I'.
l_t_range-option = 'BT'.MODIFY l_t_range INDEX l_idx.
 p_subrc = 0.

Step 5:

Now these two infopackages can be scheduled for daily loads in parallel. They will fetch data only for two days thus avoiding any redundant loading.

Pros: It help in saving almost 70 to 80 percent of loading time.

Note: In case of DB Loads or loads from Oracle, necessary timestamp fields can be considered for building the pseduo delta on.