SQL Aggregates

system · October 7, 2006, 4:29am

Why do we use aggregate functions for measures in universe?

spark_saaar (BOB member since 2006-09-09)

system · October 7, 2006, 5:33am

We use it to project the measure…accordingly with the associated dimensions…in the report.

Like summarized and detail data…

BO_Chief (BOB member since 2004-06-06)

system · October 7, 2006, 8:54am

It should be mandatory to use a SQL aggregate function (such as SUM, AVG, MAX, MIN, etc.) for measures IMHO. For SAP HANA this is a MUST.
Anyway, one uses SQL aggregate function in measures to force a Group BY in the generated SQL statement so that the DBMS returns already aggregated rows (meaning: fewer database records). SQL aggregation at the database level will in almost all scenarios perform significantly better than projection at the report level.

Consider for example:

SELECT 
  SUM (MyFactTable.Measure)
FROM
  MyFactTable

This will return only one row with the grand total, all the processing will be done on the database server (and not on your local workstation)

Now consider the following example:

SELECT 
  MyFactTable.Measure
FROM
  MyFactTable

This will return as many rows as there are in the fact table (potentially millions of records), if you are interested in the grand total, you will have to deal with the millions of records and sum them up on your local workstation to get the one grand total instead of putting the load on the database server.

Note:
For a measure you can define the aggregation in the SELECT box within Designer and the projection in propeties of a measure in Designer. These are two different settings.
Aggregation defines which aggregation is used in the generated SQL code.
Projection defines how a measure is rolled up after the data has been returned to a report.

Aggregation and projection go hand in hand, as a rule of thumb use the following settings:

AGGREGATION     Projection
-------------------------------
SUM                sum
MIN                min
MAX                max
COUNT              sum
AVG                NONE*
COUNT DISTINCT     NONE**

Averages can only be calculated correctly at the report level using a local report variable to ensure that the average is calculated correctly when drilling in a report! See also 2-Phase calculation in Essbase for example, a feature which still does not exist in Business Objects Designer.
Since XI R3.x one can use the option of database delegated measures (a.k.a. smart measures).

** Count Distinct can only be calculated when all detail information is available at time of counting distinct, aggregation cannot accour without running a Count DISTINCT against the detail level again.
Since XI R3.x one can use the option of database delegated measures (a.k.a. smart measures).

[Edited by Andreas: incorporated Steve’s statement below, added COUNT DISTINCT in the table, added option for database delegated measures in XI R 3.x] and added comment about SAP HANA.

Andreas (BOB member since 2002-06-20)

system · October 7, 2006, 4:26pm

Thank you so much for giving detailed answers to my question!

spark_saaar (BOB member since 2006-09-09)

system · November 2, 2006, 7:21pm

Hi Andreas,
How come you have such kool mind and all the time in the world to reply step by step process.
You are the man of the forum. Really your help is more than appreciation.

Thanks,
raaj

look_raaj (BOB member since 2005-06-14)

system · November 24, 2006, 8:42pm

Hey Andreas…What would be the setting for “Count”?

spark_saaar (BOB member since 2006-09-09)

system · November 25, 2006, 4:13am

Hi
Andreas had clearly explained about aggregation and Projection.
My doubt might be silly… but would like to get it clarified…
Andreas had given a thumb rule that is to be followed. I would like to know what would happen if the aggreagation and Projection dont match…
say. i set the aggregation to sum and projection to count or max… i.e anything other than sum. How will the data be displayed in the report?

Thanks…
Ahila

ahilas (BOB member since 2006-03-29)

system · November 25, 2006, 5:12am

Why don’t you try it?

Here’s a sense of reality: It’s very rare that you would ever use anything other than Sum in the Projection area. I’ve worked on hundreds of universes. It’s very rare in a business application that you would have anythong other than Sum or Count for aggregation. Yes, occasionally I’ve used Avg. I may have used Min or Max a few times, but I can’t think when.

If you do a Count(table.colum) or Count(*), you would still use Sum for projection. Why? Because counting counts doesn’t make any sense.

To complete Andreas’ table:

AGGREGATION     Projection 
------------------------------- 
SUM                sum 
MIN                min 
MAX                max 
AVG                NONE* 
COUNT              sum

Count(disctinct table.colum) or Sum(distinct table.colum) are still the same as Count and Sum.

Steve Krandel (BOB member since 2002-06-25)

system · January 23, 2007, 1:54pm

Is there any way to force an object to recalculate on the client machine? We have a need for metrics such as Cost per Day (cost/day), and we need the object to recalculate if the user does any sort of reaggregation of the data. For example, our initial report may look like this:

Product _____ Cost per Day
1 __________ 100
2 __________ 50

If the user were to remove the Product from the table, we would need the object to recalculate instead of some sort of aggregation.

I would ultimately want to see a Cost per Day of somewhere between 50 and 100, depending on the Cost and Day measures.

Any thoughts?

RyanT (BOB member since 2006-01-24)

system · January 23, 2007, 4:28pm

Nope. This is not possible. Anything with a percentage or average or division needs to be a report-level if you intend to show different levels of detail.

No, there is no workaround. It is simply a limitation of most (not all) BI reporting tools.

Steve Krandel (BOB member since 2002-06-25)

system · January 23, 2007, 4:30pm

Nuts…

…but thank you for the response.

RyanT (BOB member since 2006-01-24)

system · May 26, 2010, 3:50pm

Thanks for the info’ Andreas. Your detailed explanation cleared so many questions.

SSridhar (BOB member since 2008-07-24)

system · October 22, 2012, 1:49pm

Hi all,

When we use any aggregation function, the micro cube deal with the aggregation and I thought if I use database delegated the behavior would be the same.
My database is SAP BW and when we refresh the report, the universe create a MDX and send it to BW.

My question is: When the universe create a MDX with database delegated, it create many MDX or only one (I have many blocks)?
How universe can deal with this situation and when he do a aggregation?
In this situation the micro cube take care of the aggregation?

Thank you very much.
Gustavo

PS: Andreas, thank you!

defaro (BOB member since 2006-09-01)