Use of Aggregates,Compression, Roll Up and Partitioning in SAP BI

Aggregates:

Aggregates are used to improve query performance. Say you have cube with 30 characteristics and everytime you run query on this cube, it is hitting 10 characteristics frequenty.

So to improve the query performace create Aggregate on those characteristics. Instead of seraching for data in Cube, query will hit the Aggregate first.

Compression:

As we all know, we have two tables in info cube for transaction data(F-table and E-Table). F-table will store facts data and E-table will store compressed data. COmpression also used to improve the query performance and loading performance.

Query Performance:

Compression is nothing but removing request number an aggregating key figure values based characteristics data. We can get same sales documnet in different request(lets assume we got same sales document 5 times into cube in different request). When we compress it will become one record based on sales document number, so when we execute query system has to pick only one record instead of 5 records. this will improves query performance.

Loading Performance:

It is recommended to delete and re-create the index when we load the data into cube. Deleting index will delete the index for data in Ftable and re-creates. If you have huge uncomressed data in cube(F-table is high), delete and create index steps will take log time to complete.

Roll Up:

This is nothing but updating the lastest transaction data to aggregates which is loaded to Info Cube (if you have any aggregates on cube).

Partitioning:

This is also used to improve the query performance and we can do partitioning in two ways

i) Logical partitioning

ii) Physical partitioning(database level partitioning)

refer below links for clear information about partitioning.

http://forums.sdn.sap.com/thread.jspa?threadID=154800

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/607a8ad7-3a2d-2e10-9697-907f7277c483?QuickLink=index&overridelayout=true

Hope it helps...