This discussion is archived
6 Replies Latest reply: Nov 27, 2012 1:03 AM by 933203 RSS

aggregate table being used in lower hierarchy levels too

Vijay Newbie
Currently Being Moderated
Hi,

I am new to OBIEE and going through the aggregate table concept.

I am using the Sample Sales application that comes along with OBIEE 11g. I just moved the XML data to database and modeled my own RPD.

I have made the Products hierarchy (Grand Total->Brand->LOB->Type->Detail) and have set the Revenue from the aggregate table to the Grand total level. When I query the Product hierarchy and revenue, the data is fetched from the aggregate table which is ok. When i drill down to Brand and then to LOB, the data is still fetched from the aggregate table. The data that is displayed is correct but shouldn't it get the data from the detail fact table rather than the aggregate table?

Following is the physical query from the log for Revenue at LOB level:

WITH
SAWITH0 AS (select distinct sum(T31.REVENUE) as c1
from
SAMP_REVENUE_A T31),
SACOMMON1779 AS (select sum(T31.REVENUE) as c1,
T19.BRAND as c2,
T19.LOB as c3,
T19.LOB_KEY as c4,
grouping_id(T19.BRAND, T19.LOB, T19.LOB_KEY) as c5
from
SAMP_PRODUCTS_D T19,
SAMP_REVENUE_A T31
where ( T19.PROD_KEY = T31.PROD_KEY )
group by grouping sets (
(T19.BRAND),
(T19.LOB_KEY, T19.LOB, T19.BRAND))),
SAWITH1 AS ((select distinct 0 as c1,
NULL as c2,
NULL as c3,
'Dim Products Total' as c4,
cast(NULL as DOUBLE PRECISION ) as c5,
NULL as c6,
1 as c7,
NULL as c8,
D1.c1 as c9
from
SAWITH0 D1
union all
select distinct 1 as c1,
D1.c2 as c2,
NULL as c3,
'Dim Products Total' as c4,
cast(NULL as DOUBLE PRECISION ) as c5,
D1.c2 as c6,
1 as c7,
NULL as c8,
D1.c1 as c9
from
SACOMMON1779 D1
where ( D1.c5 = 3 )
union all
select distinct 2 as c1,
D1.c2 as c2,
D1.c3 as c3,
'Dim Products Total' as c4,
D1.c4 as c5,
D1.c2 as c6,
1 as c7,
D1.c3 as c8,
D1.c1 as c9
from
SACOMMON1779 D1
where ( D1.c2 = 'HomeView' and D1.c5 = 0 ) ))
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9
from
SAWITH1 D1
order by c1, c4, c7, c2 NULLS FIRST, c6 NULLS FIRST, c3 NULLS FIRST, c8 NULLS FIRST


Regards.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points