Performance Tuning of Loads where delta is not possible (Pseudo Delta)
Summary:
There are many standard master as well as few transactional data loads in BI which do not support the delta concept. For those load we have to go for full loads daily as the standard data-source misses the delta capability. Many times in generic data-source on tables/views delta not works. These full loads which run daily unnecessarily take a lot of time for processing data thus leading to increase in overall load times. This can be overcome with the concept of Pseudo Delta.
Pseudo Delta:
Pseudo delta can be measured as a customized delta but doing certain modifications to achieve the working concept similar to standard delta. It helps to save the loading efforts and time. This concept can also come useful for DB loads which fairly support delta concept.
Background:
There are generally two kinds of records which are fetched from the data-sources:
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 occur 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 one or two day. So that smaller amount records will be fetching in each load.
Step by Step Approach:
Step1:
If fields AUDAT (Changed ON) and ERDAT (Created ON) are present in standard data-sources, go to the editable version of data-source and mark the fields AUDAT & ERDAT for selection, otherwise we can enhance the data-source for the same. Like that we need to add these filed into generic data-source if those are available in the underlying tables. Replicate the changes to BI.
Step2:
We need to create two info-packages for pulling delta, one based on ERDAT and the other based on AUDAT. In those info-packages the selections will be available for ERDAT and AUDAT.
Step 3:
In the first info-package for ERDAT selection we need to fill it via an ABAP routine. You have to select Type (Variable Change to selection values with background process) to write the ABAP routine, on F4 help you have to select Var Type 6 – ABAP Routine from the selection row.
Refer to below routine to written for the ERDAT Selection. For safety margin we can considered of two days.
DATA: i_idx LIKE sy-tabix.
READ TABLE l_t_range WITH KEY
fieldname = 'ERDAT'.
i_idx = sy-tabix.
Data: zy_date type sy-datum.
zy_date = sy-datum - 2.
l_t_range-low = zy_date.
l_t_range-high = sy-datum.
l_t_range-sign = 'I'.
l_t_range-option = 'BT'.
MODIFY l_t_range INDEX i_idx.
p_subrc = 0.
Step 4:
In similar way for second info-pack, create a routine for field AUDAT. Here also safety margin of two can be considered.
DATA: i_idx LIKE sy-tabix.
READ TABLE l_t_range WITH KEY
fieldname = 'AUDAT'.
i_idx = sy-tabix.
Data: zy_date type sy-datum.
zy_date = sy-datum - 2.
l_t_range-low = zy_date.
l_t_range-high = sy-datum.
l_t_range-sign = 'I'.
l_t_range-option = 'BT'.
MODIFY l_t_range INDEX i_idx.
p_subrc = 0.
Step 5:
Now schedule the info-packages in the daily loads in parallel. Data will fetch for two days thus avoiding any redundant loading.
