Skip to Content

REG: Aggregates, compression, roll up and partition

hello gurus,

I am new to BI. Could any one tell me in what scenarios do we use agregates, compressions. roll ups and infocube

partitioning. I could browse these online and know what is their purpose but I do not know in what scenarios we

use them. Kindly let me know.Thanks in advance

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Feb 02, 2012 at 02:48 AM



    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 characeirstics. instead of seraching for data in Cube, query will hit the Aggregate first.


    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:

    1) 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 Perfromance:

    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 bu updating the lastest transaction data to aggregates(if you have any aggregates on cube).


    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 partitining)

    Physical Vs Logical Partitioning



    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 02, 2012 at 04:16 AM

    Compression: When you compress, BW does a group by on dimensions and a sum on measures... this eliminates redundent information. Compressed infocubes require less storage space and are faster for retrieval of information.

    Aggregates: Independent structures where summary data is stored within separate, transparent InfoCubes. Transparency: Users do not notice if aggregate is hit or not. Improved query performance by reducing the amount of data to be read from DB.

    Aggregates can improve query performance considerably, but keep in mind that they also impact the load performance.

    Rolup: Updating the latest data to aggregates.

    Partition: Improves the performance needs without the need of disturbing the data that is already existing in the infocubes. Once partition is done we can also redefine partitions( Repartitioning) without deleting the data in the infocubes

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 02, 2012 at 04:26 AM


    Answer to all of these things is to improve the PERFORMANCE.

    Aggregates helps you to get the precalculated aggregated values and use it in query instead of doing the aggregation at query runtime. Which improves the query execution performance. For the same purpose BIA indexes are also used.


    This activity is necessary for using aggregates and BIA as you load the data in infocube, it doesn't get loaded to aggregates or BIA automatically. So as to populate data in aggregates and BIA we need Rollup.


    This helps in deleting request ID from loaded request and aggregates the data based on other attribute values and moves it to E table. Due to compression at the time of query execution system need not access lot of requests and then do the aggregation. It helps to improve the query performance as well as loading performance.

    infocube partitioning.

    There can be physical partitioning or logical partitioning and both of them helps to improve the query performance, this is used in scenarios when there is lot of transaction data available and you can differentiate this data per yearwise e.g. One infocube for year 2006, one for 2007 and so on.And all of these cubes are combined under multiprovider.



    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 02, 2012 at 05:19 AM


    Aggregates, Compression , Partitioning- all these are Enhances performance of reporting(OLAP processor).

    Aggregates - Also called Mini cubes.

    Aggregates are subsets of InfoCube data, where the data is pre-aggregated and stored in an InfoCube structure.

    Suppose - Query having report on Customer - with all customer sales. In IC - data is stored in detail level with more items. If you execute report on top of IC - OLAP processor has to do aggregate all these items and display data. It takes more time .i.e., Front end time will become more.

    In order to avoid this - If we create aggregate on Customer - aggregates will do this aggregation activity. When user executes report - Front end time will get reduced.

    ROOLUP - In order to transfer data from IC tables to Aggregate tables - Roll up in mandatory.

    Corns - Data redundancy will happen.

    Compression - In IC data will stored in Request level( for every load one Request) in Un- compressed fact table.

    If we compression - hen data will migrate from - F fact table tpo e- fact table.

    Partitioning - By default F- fact table, DSO tables and PSA table gets partitioned based on Request ID. Except E- Fact table.

    So, partitioning is applicable only for E-fact table. Compression is mandatory for Partitioning.

    Partitioning criteria is -0CALMONTH or 0FISCPER.



    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.