Skip to Content

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 bu 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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Sachin Guptha
    Shashank,

    Thanks for the blog. Have a question.

    If we run the infopackage today (i.e. 7/30/09) for both AUDAT and ERDAT, we will get data for 7/28, 7/29 and 7/30.

    If we run the infopackages tomorrow (i.e. 7/31/09) for both AUDAT and ERDAT, won’t the data be loaded for 7/29, 7/30 and 7/31.

    In this case, there is an overlap as we see.

    Please clarify.

    (0) 
      1. Prabhu Kodeboina
        Nice Blog Shashank..Can you advice me on what should be the approach for Cube.

        I have 0WBS_C11 cube which is getting loaded with full loads of commitments(OCO_OM_WBS_8) ,budgets and actuals daily causing the D drive increase at a more higher rate than usual.Daily thru the PC the entire cube contents are deleted and new full loads are done.. 

        Please advise if pseudo delta can be applied here…

        (0) 

Leave a Reply