diff between Select SUM (Table. Field) & Select Table. F

system · May 7, 2008, 11:32am

Hi All,

If a report needs to query a field which provides Amount Balance data, what are the differences between Select SUM (Table. Field) and Select Table. Field in this measure object design?

regards
Vkchowdary

vkchowdary (BOB member since 2008-05-07)

system · May 7, 2008, 11:42am

I think the difference is in the way you want to pull the data out onto the report or the cubes.

If you use SUM(Table.Field) and use it on the report, it will get grouped by the other dimension objects.
If you use only Table.Field, the output data won’t be grouped by.

BO_Stuffed (BOB member since 2008-03-29)

system · May 8, 2008, 12:55pm

If your query is like this:

select
  year, amount 
from 
  mytable

then BO gets all the rows (like millions) and adds them up to display maybe 3 rows for 2006, 2007 and 2008. This takes a lot of time from the database and from BO .

But if your query is like this:

select
  year, sum(amount)
from 
  mytable
group by year

then BO gets only three rows because the database has already added all the millions of rows. All BO has to do is receive those 3 rows and show them. This is much quicker !

By the way, this is basic SQL stuff. Maybe a good idea to take a course in SQL ?

HenkK (BOB member since 2004-03-02)

system · May 8, 2008, 2:26pm

This is really a universe design question… in fact, it’s very similar to this topic already posted: sum() in select statement is mandatory for measure object?

Dave Rathbun (BOB member since 2002-06-06)

system · May 9, 2008, 9:37am

It is in quicker in BO, but keep in mind that your query might be slower becausing the data gets grouped on the database and not in your report.

Andy

andy_v_belgium (BOB member since 2008-03-04)

system · May 9, 2008, 9:41am

The query runtime might be slightly higher if any, but you will save on the network traffic and the total rendering time within your report, thereby the total runtime will be way less!

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.
NEW in XI R3.x: There is a new projection available since XI R3.x called database delegated measure, a.k.a. smart measure, which will require a refresh of the query though.

** 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.
NEW in XI R3.x: There is a new projection available since XI R3.x called database delegated measure, a.k.a. smart measure, which will require a refresh of the query though.