6 Replies Latest reply: Nov 27, 2012 3:03 AM by 933203 RSS

    aggregate table being used in lower hierarchy levels too

    Vijay
      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.