Data Transfer Process (DTP) – Analysis and Performance Optimization

DTP determines the process for transfer of data between two persistent/non persistent objects within BI.

As of SAP NetWeaver 7.0 and above, InfoPackage loads data from a Source System only up to PSA. It is DTP that determines the further loading of data thereafter.


For further details on DTP, its benefits, functionality and use, refer Document: All about Data Transfer Process (DTP) – SAP BW 7



Performance Analysis-Optimization steps

Performance of a DTP can be analyzed primarily in 3 steps –

  1. Extraction
  2. Transformation
  3. Loading (into Data target)


Performance of a DTP can be analyzed in the DTP monitor screen. The ‘Details’ tab in the DTP monitor, details the progress of a DTP with corresponding Time-stamps at each step.


Extraction


A DTP transfers data between two persistent/non persistent objects within BI. Extraction is the process of extracting Data from a persistent Data object.

The time taken by the DTP to complete Extraction mainly depends on the amount of Data available in the source object. Data in DTP is extracted and processed in packages. Typically the first package of a DTP takes the highest amount of time for extraction.


You would observe the time taken for Extraction may vary from few seconds to even hours in few cases. There could be multiple reasons for longer time during extraction. Let’s take a look at probable reasons and their solutions:


Huge Data contained in PSA / DSO change log – While we execute a DTP in Delta mode, it extracts Delta requests from one of the following persistent Data objects:

PSA, DSO’s change log, Info Cube, Master Data Info Object. Extraction takes longer time to complete while the Data contained in the source Data object is huge. In order to improve the performance of Extraction, PSA and Change log requests should be periodically deleted and retained for the minimum required time period.

The following figures depicts the scenario where time taken in extraction can be considerably reduced by regular deletion of PSA requests.

Extraction.png


PSA stack.png



Transformation


Transformation basically includes the processing of Data via ABAP Routines, Formulas, Masterdata read. The use of Start Routine, Characteristics or Field level routine, End Routine OR an Expert routine affects the performance of DTP at each level. However, most of the times these routines become necessary for implementation of the required Business logic. In order to enhance the performance of the DTP, the ABAP code written in the Transformation should be optimized. Each routine has its own purpose and based on the scenario the decision of right fit routine should be made.

Discussion on ABAP performance optimization and Decision on Transformation Routines is not in the scope of this paper. Following papers may be referred for better insight:


http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/609eea32-455e-2c10-c08a-c23adf8c934e?overridelayout=t…

https://help.sap.com/saphelp_nw70/helpdata/en/e3/732c42be6fde2ce10000000a1550b0/content.htm



Loading (into Data targets)


Loading Data to Infocube – It is many a times observed that loading data to Infocube takes a longer time as seen in the below figure:

There are mainly be 2 reasons of longer data load time to the Infocube:

  1. Cube indexes – For better performance, Cube indexes should be dropped each time before loading the data to the Infocube and rebuilt post the data load to the cube. Regular compression of Requests of the Infocube helps improve the Index drop and build time.
  2. Conversion of characteristics to SIDs – For all the Non Masterdata characteristics present in the Infocube, SIDs are generated while the data is being loaded into the Infocube. If this step is taking longer time, we must buffer the number range for the Dimensions in question. The following figure shows the procedure for Number range Buffering.

       Cube taking longer time for Conversion of Characteristics to SIDs

     SID generation.JPG

       Number Range Buffering

       Follow the below steps to find out the Nuber range Buffering available for each Characterisitic-

       T-code: /SSA/BWT (select the Infoprovider under consideration)

       ssa BWT.JPG

  

Here as you can see, DIM7 and DIMC have a huge Number Range level. In order to improve the performance for generation of SIDs, we must increase the value (e.g. 1000 or 10000) for # of numbers in buffer.

number range analysis.JPG

To report this post you need to login first.

2 Comments

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

  1. vijay paravasthu

    Hi Bhushan,

    The document is pretty good and the concept is awesome.

    But the options to increase the value (e.g. 1000 or 10000) for # of numbers in buffer is not explained further.

    And there is something you can disable error stack optio where it will also has great impact on performance when you don’t have error records.

    And also you have one more option called parallel processing.

    It would be great if you can add these into your document and will make much more helpful who are really looking in this direction.

    Regards,

    Vijay

    (0) 

Leave a Reply