OBIEE Aggregate BY

OBIEE has many aggregate functions: AVG, AvgDistinct, BottomN, Count, CountDistinct, Count (*) (CountStar), First, Last, Max, Median, Min, NTile, Percentile, PeriodAgo, PeriodToDate, Rank, StdDev, Sum, SumDistinct, TopN.

Default OBIEE "groups" these functions on the lowest level. If you want it in an other "grouping" you can use the "BY" statement in your function definition.

If neccesary you can also define multiple columns (Comma seperated):

In the background OBIEE is translating this to ANSI-SQL:

WITH SAWITH0 AS (select T21.LICENSEPLATE as c1,
T21.MEASUREDATE as c2, T21.FUELCONSUMPTION as
c3, avg(T21.FUELCONSUMPTION) as
c4, count(T21.FUELCONSUMPTION) as
c8, sum(T21.FUELCONSUMPTION) as c9from
TBLFUELCONSUMPTION T21group by T21.LICENSEPLATE,
T21.MEASUREDATE, T21.FUELCONSUMPTION)

select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3
as c3, SAWITH0.c4 as c4,

sum(SAWITH0.c9) over (partition by SAWITH0.c2) / sum(SAWITH0.c8) over
(partition by SAWITH0.c2) as c5,

sum(SAWITH0.c9)
over (partition by SAWITH0.c1) / sum(SAWITH0.c8) over (partition by
SAWITH0.c1) as c6,

sum(SAWITH0.c9) over (partition
by SAWITH0.c1, SAWITH0.c2) / sum(SAWITH0.c8) over (partition by
SAWITH0.c1, SAWITH0.c2) as c7

from SAWITH0order by
c1, c2, c3

Which isn't always the most cost effective way, you might consider using the EVALUATE_AGGR function instead for very complex aggregations.



Till Next Time