Applies to:
SAP BW 7.3.
Author Bio
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:
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.
After this run the delta in WO DSO & check the header status as shown below.
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.
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.
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.
- SAP Managed Tags:
12 Comments
