How to find Top 10 routines in SAP BW system taking maximum time during data loading…
SAP BW 3.5x / Business Intelligence 7.3x.
Author(s) : Jai Gupta
Company : Infosys Limited
Created on : 05 November 2012
Jai Gupta works as Senior Systems Engineer in Infosys Limited. He has 2.7 years of experience in
SAP ABAP/BW. He has worked on Various Support and implementation Projects.
Business Case Scenario:
In SAP BW system needed to find out the top 10 routines which are taking maximum time during data loading in the system in accordance to the system environment like work load of the system at that point of time, parallel processing implemented in DTP ‘s or not etc.
This document specify the procedure followed to find out the top 10 routines in transformations that took maximum time for execution in SAP BW system. The approach followed is one of the alternative among many that can be chosen to perform this particular task.In this approach, we will hit the database tables to find out the transformations along with their DTP ID’s where we had routines currently and then using existing or creating new statistics queries built on top of standard SAP BI statistics Multiprovider, we can found out the time taken by the routines during execution in the transformations in accordance to the system environment.
Step1. Finding the transformations which have routines written in them
In step1, we will go to se11 and give the table name as ‘RSTRANRULE’ , then click on display button as shown below:
After clicking on the contents icon as shown above, a screen will appear. In this screen enter version as ‘A’ and do the necessary modifications as shown in the screen shot below:
The field Rule type highlighted above will determine whether the transformations has routine written into it or not.
Click on the multiple selection button and insert the values as shown in the screen shot below:
After entering the values as shown above, click on execute to display the list of transformations which have routines in them.
The output will be displayed as below:
Take the dump of this output into an excel and remove the duplicates transformation’s id from it.
Step2. Find the Source objects and Target Objects of the transformations found in above step.
After this go to the table ‘RSTRAN’ and in the field highlighted below enter all the transformation id’s values that you currently have in the excel.
Put version as ‘A’ and execute the table.
In the above output, there are two fields both having description ‘Object Name’, the first one will be the ‘source objects’ and second one will be the ‘target objects’. Now, take the dump of the output in the excel and save the file.After that, copy the ‘target object’ values from the excel and enter them in the table ‘RSBKDTP’ as shown below:
Follow the same procedure for copying and pasting the source objects in the source object field from the excel as done for target objects.
After pasting both source objects & target objects values as shown in the screen above, execute the table screen. Take the dump of the output in the excel and save the file.
In the saved excel copy the DTP ID’s values as shown below.
Transpose these values in a new excel as shown below:
Paste special and transpose the values from columns to rows in the new excel
After transposing the values the columnar data will get transformed into row data as shown above.Save this excel in the ‘.csv’ format.
Open the excel in notepad and replace the commas ‘,’ with semicolon ‘;’ as shown above and save the file .
This file of ‘DTP ID’s’ will be used later as an input in the query variable to get the run time statistics of the routines present in these transformations.
Step3. Create a Bex Query using query designer on top of DTP Statistics Mutliprovider (0TCT_MC22)
Note: For creating a Bex query you can create the same in development system and transport it to production or create a Y query in production depending upon your requirements.
Open Bex query designer and create a new query on the Multiprovider ( 0TCT_MC22) as shown below.
Enter the TOP10 Condition as shown above.
Execute the query and on the variable screen, enter the DTP ID’S values that was saved earlier Step2.Open the .csv excel saved earlier in the notepad, copy and insert those values as shown in the screen below:
After entering those values click on ‘OK’ button, query will execute and the output screen will be displayed as shown below:
In the above output screen, we have DTP ID’s, DTP Command, Source Object, Target object and Calendar day.Here DTP Command specifies the various steps that are performed when a DTP is executed. With reference to above fig the details of some of these steps is mentioned below:
- U_CUBE : Time taken in updating data to cube.
- ERROR_FILTER : Time taken in filter values being read inside the DTP.
- TRFN : Time taken in the execution of transformation.
- X_DATASTORE : Time taken in extracting data from the source.
- Not assigned : Time taken in performing the miscellaneous activities like process allocation, start time, end time, message update time , status update time etc.
In the above screen shot, we can see the time taken by all the steps in a DTP execution. Based on our requirement we should restrict the DTP Command to ‘TRFN’ while creating the query but that would not have been useful as you can notice that the DTP Command that took most of the time is ‘Not assigned’. The time taken depends on the various factors like parallel processing, no of records coming from the source object, current load on the system etc.
Hence, it becomes essential to consider the time taken in ‘Not assigned’ step as well to have a complete overview about the routines which took maximum time in their execution according to the current system scenario’s.
For all the steps discussed above, an ABAP program can be made to automate the work and display the list of TOP 10 routines taking maximum time in the SAP BW system in accordance to the current system environment. PFB the ABAP code written which caters to above methodology.
Good article, but some of your images are not working, it seem you need to load them again.
I can see all the images in the document loading fine. It can be a web explorer problem i will check and let you know..
,HI Jai ,
Nice Document . Am Also able to see the images . It will really help to optimize routines.
A useful article, particularly for Platform Optimization Projects.
Nice workaround for that problem. You did good Research buddy.
Nice post and a good work....
A useful work done by u it will help to me very well nice job...
Very good and helpful article. Nice work.
Hi Jai Gupta,
Nice post and a good work....
Its very nice post. it will help for a lot
Very helpful .