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 –
- 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.
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.
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:
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:
- 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.
- 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
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)
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.