Skip to Content

Applies to:

SAP BW 7.3.

Author Bio

/wp-content/uploads/2013/01/dp_153596.png

Author(s)     :  Jai Gupta

Company    :   Infosys Limited

Created on  :  9 January 2013

Jai Gupta works as Senior Systems Engineer in Infosys Limited. He has 2.10 years of experience in

SAP ABAP/BW/BO.


Introduction

In SAP BW 7.3, we have a standard functionality provided by the SAP wherein we can create the delta at the data source level for Non-SAP source systems like File system, UD connect, DB connect etc. This functionality is similar to Generic Delta functionality available in SAP source systems for Generic data sources.

Overview

As shown in the screen shot below, in SAP BW 7.3 from now on, in the extraction tab at the data source level there will be two additional features available for the delta extraction of after images and delta images from the source system.

  • FIL0 (Delta with After Images): Only the after images will be fetched from the system
  • FIL1 (Delta with Delta Images): Delta images i.e. both after and before images will be fetched

We can choose either of them based on our requirement. After choosing either of the option new option of Generic Delta will be shown on the screen as shown below.

In the adjacent drop down box to the Generic Delta option, we will have the option to choose the criteria on which we want delta to work. We can choose Date, Time stamp, Time stamp – Short, Time stamp (UTC), Numeric Pointer.

After choosing Date, new fields will appear on screen as shown above. In the Delta field, we have to specify the name of the date field present in the source system upon which we want to build our delta in the EDWH. We can also specify the Time zone according to our requirements to avoid the mismatch in different time zones. In the fields, Lower Interval and Upper Interval, we can specify the safety intervals required while fetching the data.

Live Example:


In this example, we will load the delta load via File in Sap BW for the after images data and try to analyze this new functionality.

Data Flow:


In the fig above we can see the data flow that is created for verifying the new delta functionality in SAP BW 7.3.

File Format

The format of the file which will be used for data load is shown below:

A ‘.csv’ file is created in the above format with field details as mentioned below:


CustNo, Item Qty, Item Price, Country, Date

Data source:

While creating the data source, we have keep CUSTNO as key field and ZDATE available for selection as it is necessary to maintain the field as selection field on which we will create the delta later.

Data source Extraction Tab:

For maintaining Delta, we have to maintain some entries in the Extraction Tab, please refer to the screen shot below regarding the same:

Here, in delta process we selected the method FIL0 Delta with After Images, also we have selected field ZDATE as Delta field and have given the lower interval and upper interval as 1 and 0 respectively.

Write Optimized DSO:


Create a WO DSO with the details as shown below:

After creating the WO DSO, create the transformation & DTP’s (Both Full & Delta) with the Data Source that was created in the above step.

Standard DSO:

Create a standard DSO with the details as shown below:


After creating the Standard DSO, create the transformation & DTP’s (Both Full & Delta) with the WO DSO that was created in the above step.

Data Load Procedure:

For data loads, create two info packages one for delta and another for full load as shown below:

First do the full load to extract all the records present in the ‘.csv’ file which was created earlier and check whether the records have been fetched successfully or not.


After that, initialize the delta Without Delta Transfer via delta info package and check the initialization entry for the info package as shown below.

After this do the full load in the WO DSO as shown below:

After the full load, initialize the delta as shown below:

Untitled.png

Here, change the Processing Mode to No Data Transfer; Delta Status in Source: Fetched as shown above. After this execute the DTP and check the status in the monitor screen as shown below.

After the initialization in WO DSO, follow the same procedure in Standard DSO as well that was followed in WO DSO. First do the full load, and then initialize the delta as shown above in case of WO DSO.


The only difference in case of standard DSO will be to activate the data which is not done in case of WO DSO.

After this see the output for the data in the Standard DSO before the delta load.


Here for customer ‘10’, the value for Price is ’50.00’ and Quantity is ‘3.000’. Now, we will change these values in our file data and also change the date to ‘today date – 1’ i.e. ‘20130103’ as we have chosen lower interval value as ‘1’ while creating the data source.

In the file data shown above, values for Item Price will be changed from ‘50’ to ‘20’ and Quantity will be changed from ‘3’ to ‘1’. Hence, as per after image concept the data of Customer ‘10’ will be replaced with the delta after images i.e. for Customer ‘10’ Item price will get replace from ‘50’ to ‘20’, Quantity will get replace from ‘3’ to ‘1’ for the date ‘20130103’.


So, after changing the data in the file, run the delta load in the data source and check the records fetched in the data source.

In the request monitor we can see that only 1 record is fetched in the delta load.Untitled.png

After this run the delta in WO DSO & check the header status as shown below.

Untitled.png

Here, it can be seen that only single record is fetched from the data source.

After this run the delta in Standard DSO as shown below.

Untitled.png

Here also, only single record is fetched from the WO DSO.


Activate the Delta request in the Standard DSO and check the output as shown below.

Untitled.png

As we can see, in the above output the data for Customer ‘10’ has been replaced by the after image of the delta data.Hence, we are successfully able to test the new delta functionality provided in SAP BW 7.3 for non – SAP source systems.

Advantages:

No need to write the ABAP code for delta generation from now on wards for Non-SAP source systems as we can do it directly by using this functionality.


To report this post you need to login first.

12 Comments

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

  1. Tammy Powlas

    This belongs in the BW space and not the BI space

    When you are done editing it I will try to move it but in the future please post your blogs in the BW space

    Tammy (Moderator)

    (0) 
    1. JAI GUPTA Post author

      Hi Tammy,

      Sorry for posting this document in wrong space but I am not editing anything currently, Don’t know why it is showing  here that the document is currently edited by me, Can u please look into this and let me know?

      Regards,

      Jai

      (0) 
  2. Gaurav Saini

    Hi Raj,

    A very good document. Useful information.
    But i’m having some doubts can you please clear them.

    1. How is delta working here. Can you please explain this a bit.

    2. how is  “selection possible” working here, can you please explain me a bit what is happening in the backend. ( i mean the delta process )

    I have also tried your approach….i am very much satisfied with your doucument as i’m able to extract the delta records, but when i’m changing the lower limit i’m not getting expected result.

    Can you please explain the logic behind this Flat file delta load.

    Thankyou,

    Gaurav

    (0) 
    1. JAI GUPTA Post author

      Hi Gaurav,

      Here we are only extracting the records which are generated after our last run based on date field, i.e if I do a load today and run the delta tomorrow then only those records that are generated between yesterday and tomorrow will be fetched from the source irrespective of fetching all the records. So in this process in the back ground time stamp is generated for my today’s run and when I run the load again on tomorrow then based on this time stamp data source will select only those records from the file for which date values in the column ‘zdate’, present in the .csv file ,is greater than the time stamp, everything else will be ignored.

      For lower interval and upper interval you have to be cautious in selecting them as they are used to alter the time stamp based on the user requirement, to see the time stamp open the delta info package go to scheduler at upper left corner of your screen and choose Initialization options for source system.

      Regards,

      Jai

      (0) 
  3. Andrés Echeverría

    hello Jai,

    It is a very good example for the BW version 7.3

    I have a little problem, I have in other customer, a version BW 7.0 and the delta after-image functionality is not same.

    You have some example of delta loads with flat file, can be performed with non-SAP source systems in BW 7.0 version?

    Regards,

    Andrés Echeverría R.

    (0) 
    1. Suyash Pandey

      Its not supported in BW 7.0.

      You can not have delta functionality from flat files.

      (0) 
  4. seshagiri g

    Hi Jai,

    I tried to use the delta functionality using time stamp as delta field with oracle in the back end.

    When i specify the delta field in the extraction tab and tried to activate it, I am getting the error as Data format of delta field xxx is not compatible with timestamp.

    I tried with separate source fields with different data types as CHAR and DECIMAL but no success.

    Please let me know what should be data type in Oracle so that BW can read the time stamp value.

    Regards

    Seshu

    (0) 
    1. seshagiri g

      Thanks Jay,

      the issues is resolved when i have changed the data type of the timestamp field in the proposal tab of the datasource.

      Regards

      Seshu

      (0) 

Leave a Reply