Monday, February 18, 2008

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




6 comments:

ashi.kacheria said...

Due to its complex nature, very less information is available on Business Intelligence. A simple Google search gave me around 45,000 results, most of them failing to meet my expectation of a comprehensive coverage of the subject.
One of the useful one, though was http://blog.maia-intelligence.com/ . The information here was extensive, and was explained in clear and concise language. Seems they also have their own BI products. This, although being a good reference point, my search is far from over. If you can suggest me another place where I can find relevant information on Business Intelligence, will be good!

John Minkjan said...

Have you tried Mark Ritmanns blog?
http://www.rittmanmead.com/category/oracle-bi-suite-ee/

sangeetha said...

Can you tell me how cam i implement the
SUM(col) Over (Partition by Col2 order by col3) function in Oracle BI?

John Minkjan said...

Hi Sangeetha,

Have tried using the evaluate function:
EVALUATE( 'SUM(%1) OVER ( PARTITION BY (%2) ORDER BY (%1) ) ' as varchar(200), EMP.ENAME,DEPT.DEPTNO)

regards
John

David said...

Thanks for this information - very helpful for me today.

oops said...

Can you explain how Multiplicity in ROles work?
I know the SQL concepts of one to one, one to many etc. But here i see it for both the tables when designing my RPD. Can you explain that please?