fir_dan

 Profile Answers by fir_dan 

• Jul 21st, 2008

 

Aggregation at Microcube level means the data is pulled from the database and aggregated on the BO server. For example if you want total sales of a product category all the records of the that paticular product category are brought into the cube and BO then aggregates it to sum. Even thought report displays only the aggregated value if you look at the data provider all the records of that product category will be available.

Aggregation at the database level means the aggregation is performed at the database which means only the aggregated value will be send from the database. The details of the records which contributed to the aggregation will not be available for BO. In the above example the total sales of each product category ie if there are five product categories then only five records will be made available to BO.

If there is aggregate function like sum at the defintion of the object at the universe level then its a database aggregation and if there is a aggregate function at the report level its a microcube aggregation.

If you view the sql of the report there will be considerable difference ie for database aggregation the sql will contain aggregate function with a group by clause and the microcube aggregation will not have it.

Rajnish_kaushal

 Profile Answers by Rajnish_kaushal 

• Apr 13th, 2009

 

Aggregation : In SQL we have functions e.g. Sum , min , max , count etc for aggregating the data. We can use these functions to create objects in universe for aggregating at database level. Also when defining objects in universe, we have options to defined how BO Server should do aggregation.

We should try to do as much aggregation as possible at database level. this will mean less amount of data is coming into the browser.

Also for averages / count , it is advisable to take extra care while deciding where aggregation should happen. Calculating averages at database level may give different results than Aggregation done by BO Server.


Regards

Rajnish Kaushal

devgod

 Profile Answers by devgod 

• May 23rd, 2009

 

Aggregation can be of the following type:

Table Level: Commonly known as Marts.L1/L2 marts at monthly/yearly levels.

Query Level: Make use of Database aggregate functions like SUM, COUNT, MAX,
MIN to reduce number of dimensions and thus reducing number of rows returned by
the query.

Projection Level: Once BO has data in the micro cube we can assign projection
level aggregation. This is useful in Drill down functionality.

E.g  A table contains name of the school, class and names of all the
students in the class. want to count number of students in the class hence at
query level the measure will be defined as COUNT(Student_name). Hence the
resultset in microcube of BO will be like:
School Class Count
X    A    10
X    B    20
But I want to show number of students in school X. which will be 10+20. Hence
projection level agg will SUM and not COUNT. Projection level agg is set at
object level for measure in properties tab.