February 3, 2012

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.



Hope it helps...

8 comments:

  1. Definitely !!!

    Thanks a ton Venkateswarlu.

    I looked into a lot of other sites for its (aggregate and compression) differences but my search ended up here with a convincing and more understandable note.

    Thanks a lot
    Karthik.

    ReplyDelete
  2. Thanks venkateshwarlu....good stuff ......compared to other blogs

    ReplyDelete
  3. I am William..
    I just browsing through some blogs and came across yours!
    Excellent blog, good to see someone actually uses for quality posts.
    Your site kept me on for a few minutes unlike the rest :)
    Keep up the good work!Thanks for sharing a important information onsapbwbi

    ReplyDelete
  4. I am robert..Good work buddy... keep continuing... it shows yours commitment towards sapbwbi field...

    ReplyDelete
  5. Excellent One Venkatesh,Was going through n number of pages,but u explained the concept in simple manner.

    ReplyDelete
  6. Dear all any body explain me about non cumulative key figure with examles

    ReplyDelete
  7. Dear all i have one question that what is partition why we need to this?

    ReplyDelete