Saturday, October 2, 2010

Aggregates

An aggregate is a subset of Information. An aggregate is a materialised aggregated view of the data in a Infocube.
 In an aggregate the dataset of an infocube is saved redundantly and persistantly in a consolidated form into
the database.
USE: The objective of using the aggregates is to improve the reporting performance in reporting. Aggregates makes it possible to access infocube data quickly in reporting. Aggregates serve in a similar way to database
indexes to improve performance.
The BW OLAP Processor selects an appropriate aggregate during a query run in the navigation step. If no appropriate aggregate exists, the BW OLAP Processor retrives data from the infocube. Aggregates are multidimensional data structures similar to infocube containing aggregated subset of information, in a summarized from. An aggregate is also called as baby cube for an infocube. An aggregates stores the data of an Infocube redundantly and in consolidated from in the aggregate table RSDDAGGRDIR.
Aggregates are used mainly for one reason primarily to improve the reporting performance. When queries run faster, they may take less processing time and resources in addition to the enduser getting the information back i.e., response more quickly.
Life Cycle of Aggregates:
1. Aggregates are defined by the DBA against an existing Infocube.
2. Aggregates are updated when loading data into Infocubes from Infosource using the same updated rules basic infocube.
3. During data loading, data is aggregated to the specific level of infocube dimension(characteristic)
4. During querying, the OLAP Processor dynamically determines if an aggregate exist to satisfy the query.

Aggregates have 3 names:
1. A system defined 25 digit unique name.
2. A 6 DIGIT integer number
3.A user defined description only
Relationship between Aggregates queries and Infocubes:
Infocube 1:N Aggregates i.e.,
One Infocube maintains more than one aggregate
2. Querystep 1:1 aggregate i.e.,
One Aggregate is used in one query.
When do you use Aggregates:
It is recommed to use aggregates,
1. If an Infocube contains lots of Data
2. If attributes are used in queries often
3. If the execution and the navigation of a query data leads to deploys with a group of queries.
4.If you want to speed up queries reporting the characteristics hierarchies by aggregating data to a specific hierarchy level then we use aggregates.
Aggregation Level:
An aggregation level indicates the degree of details to which the data of the Infocube is compressed. There are 3 levels of aggregates.
1. All characteristic values (*): The data is grouped by all the values of the characteristic or navigation attributes.
2.Hierarchy Level(H): The data is grouped by Hierarchy node.
3.Fixed value(F): The data is filled according to a constant or single value.
Important Information about Aggregates:
An aggregate holds transaction data.
An Infocube can have several aggregates.
Each query can have only one aggregate
Aggregates must be recreated after the changes in the MD Attributes or hierarchies.(change run process)
Aggregates are built against infocubes only but not with ODS Objects.
Aggregates are useful for keyfigures with SUM,MIN,MAX Properties.
Aggregates are selected by OLAP Processor during query processing.
Aggregates can be built with display attributes.
Aggregates are maintained in a table RSDDAGGRDIR.
Switch OFF Aggregates during dataloading to improve the loading performance.
Apply Rollup Process to fill the aggregates with the infocube data.
Switch On Aggregates after ROLLUP to improve the reporting performance
Maintanance of aggregates includes
Create New Aggregate
Activate and Rollup.
Deactivate
Delete
Copy with Template
Aggregate Tree
Pre Analysis of the Aggregate filling.
Swith off aggregates will result in no data loss; structure remains as it is but aggregate or Baby cube wont be fill with data.
aggregation deactivation will result in the data loss for the aggregates, but structure of aggregate remains as it is.
Delete aggregate will result in data loss as well as structure loss.
copy with template allows you to create new aggregates using with the existing aggregates.
To check the data in aggregate(Baby Cube) place the cursor on the specific aggregate, then select
GOTO MENU........> Aggregates data.
The technical name of Aggregate is a 6 digit integer number. Eg: 100426.
Its dimension tables are; /BIC/D100426I, /BIC/D100426P, /BIC/100426U
The fact table of aggregate maintains an adding keyfigure 0factcount(It is the couter for occurance of request)

8 comments:

  1. Hello Dude,

    An aggregate is a collection of items that are gathered together to form a total quantity. In information technology, individual items of data are sometimes aggregated into a database. Unlike marshalling, aggregation doesn't require giving one thing precedence over another thing. Thanks for sharing it......

    Web Grabber

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. thank u Purushotham ...for sharing a nice and helpful document....but i wonder that my sir also given same points in my notes,,,,!

    ReplyDelete
  4. Thanks a lot... very useful information

    ReplyDelete
  5. Hi Reddy,

    Do we have a separate aggregate table for an infocube ? Are these physical tables or logical tables ? Do we need to delete aggregates before the infocube load and create aggregate/roll up aggregates after the IC is loaded ? Please advise.

    Thanks

    ReplyDelete
  6. how can we decide which char info object need to be aggregated?
    if so what is the process?

    ReplyDelete
  7. Hi,

    Thanks for the information, but am facing one issue.

    Rollup is not possible no filled aggregates exist for InfoCube.

    How to clear this issue

    ReplyDelete