BW Performance Tuning - Key Indicators and Recommendations Part 1

[KamalMehta]

Performance tuning plays a key role in any BW system . With due course of time the Performance of the system in terms of Data Extraction, Data loading, Reporting etc. degrades. This can be due to number of reasons which can relates to Extraction, Modeling, Designing etc. 

I would give it a honest try to explain some of these the way i can .

I would take some practical scenario's to explain the same.

1. Long Running Processes in Data Loading : Here basically we need to identify the long running jobs related to Info package and DTP. I have already explain various steps which we can take into consideration for improving the performance of Extractors i.e. Info package  in my blog http://scn.sap.com/community/data-warehousing/business-content-and-extractors/blog/2013/09/30/perfor...

       Lets, see what can be done to improve the performance of DTP where ever required.

Using ST13 we identify the various Process chain run times by doing this we get to know the steps which are consuming more time. By doing this we realized that one of the DTP was taking a lot of time .It was Full load DTP from DSO to Info cube and it was taking more than 9 hours on daily basis. As per the requirement it can't be changed to Delta.

1. Analysis of this DTP job highlights several issues firstly we can see most of the time spent in each data packet is in the process "Conversion of characteristics to SIDs“ updating data target Info Cube.  For each characteristic and dimension, BW uses a number range to uniquely identify a value (SIDs and DIM IDs). If the system creates a high amount of new IDs periodically, the performance of a data load may decrease.  Setting up number range buffering on objects in this target cube will certainly help.

     

2. The processing mode of DTP job DTP_4HZBZVP9XPQUDLEKEHY1CL05H is in serial mode using only one batch processor.

3. The maximum 16 fields in the Semantic Groups are switched on for error handling.

  

4. Uncompressed requests to date in the target cube and large dimension tables for a couple of the dimension tables.

    

Solution :

           1. Setting up number range buffering on the objects in the large dimension tables /BIC/DZXXXXX238 and /BIC/DZXXXXX23D will reduce the time spent in “Conversion of Characteristics to SIDs”.

   Checking for number range buffering on dimension tables /BIC/DZXXXXX238 and /BIC/DZXXXXX23D.

   RSDDIMELOC (cube dimension -> NRO) 

INFOCUBE	DIMENSION	NUMBRANR	OBJECT
ZXXXXX23	ZXXXXX238	0003039	BID0003039
ZXXXXX23	ZXXXXX23D	0003041	BID0003041

TRNO (checking to see if buffering is set up).  In this case no buffering has been set up.

OBJECT	BUFFERING	NUMBRANR	TXT
BID0003039		0003039	DIMIDs dimension ZXXXXX238
BID0003041		0003041	DIMIDs dimension ZXXXXX23D

Activate number range buffering for all dimensions and info objects with a high number of rows, based on the rules in Note 857998.

2. Increase parallel processing for this DTP job by increasing the number of batch processors from 1 to 3 and if possible further again.

3. Check if error handling is necessary for all the fields switched on for this DTP job as it slows down performance.  If not switch them off.

4. Compress the Info cube on periodic basis taken into consideration the retention period as per the project requirement. For Large Dimension Tables check if the Info cube dimensions can be changed i.e. Line Item Dimensions can be created, New Dimensions can be created for characteristics as per the data in the Info cube etc.

Key Indicators :

1. In the DTP monitor if considerable time is spent in "Conversion of Characteristics values to SID's" then go for Number Range Buffering. Once done check and compare the DTP run time to understand the difference.

2. Check the number of Background jobs per DTP .Make sure to keep the number of parallel processes from the DTP screen. Normally its being kept as '3'. It varies from DTP to DTP taking into consideration the routines if any .

3. Check if Error Handling is maintained for the DTP using Table RSBKDTP. Identify why it is necessary to maintain the same and is it required to have "Semantic Groups switched on for all those fields" as it does tends to decrease the performance.

4. If Dimension Table size is more then check whether Info cube design can be changed and can it be remodeled checking whether Line Item , High Cardinality dimensions can be created or not.

Thanks for reading