Some useful T-Code for BI performance tuning

T-Code	Description
SM66	Global Work Process Overview
ST02	Tune Summary
ST06	System monitor
STAD	SAP workload
ST05	SQL Trace
SE30	ABAP Trace
ST12	Single transaction analysis(including ST05/ST30)
RSMO	BW Load monitor
DB02	DB Load overview
ST04	DB Performance snapshot
RSBATCH	BI Background management
RSODSO_SETTINGS	Maintenance of runtime parameter of DSO
RSRV	Analysis and repair BI Objects
ST03	Workload in system
RSRT	Query Monitor
RSRTRACE	Configure Trace Tool

Disadvantages of AGGREGATES, COMPRESSION, IC PARTIONING, INDEXES, LINE ITEM DIMENSIONS

Disadvantages of 

(1) AGGREGATES

Even though Aggregates are used for performance, but it will decrease the same when you create more aggregates. 

Until Rollup takes place the query won't hit the aggregate Cube

Aggregates - its main disadvantage is that it stores data physically as a redundant, more aggregates will cause waste of memory.

(2) COMPRESSION

Once Cube is compressed, then all the request number will be removed and hence deletion by request id is no more possible.

Compression- compressed request cannot be got back to normal, deletion is difficult

(3) Partition

Partition Handling for several thousand partitions is usually impacting DB performance.

IC partition  - partition cannot be done after data loaded (3.X) but repartition is possible in BI 7.0

(4) INDEXES

If you don't drop the index before loading then the data load will be slow

If you don't create the index before the reporting then the reporting will be slow

Index- for large volume of data create and delete index consume lot of time

(5) LINE ITEM DIMENSIONS

This can be set when you have one only characteristic in the Dim table.

Line item - more number of line item cannot be used as number char used will be reduced.

Performance

Performance

1. Performance Optimization with Aggregates
2. Performance Optimization with SAP NetWeaver BI Accelerator
3. BEx Monitor
4. Non-Cumulatives