Aggregates in OBIEE

Post date: 23-Jul-2010 10:24:56

Aggregate fact tables contain same measure data like in the lowest granularity fact table but summarized on certain level. Aggregates in obiee can be created using aggregate persistence wizard or manually.

For the first option:

http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/aggpersist/aggpersist.htm

http://www.rittmanmead.com/2007/10/26/using-the-obiee-aggregate-persistence-wizard

http://obiee101.blogspot.com/2008/11/obiee-aggregate-persistence-wizard.html

Advanced option is using materialized views, dimensions and query rewrite.

I'll show the second option (manually).

Creating database objects

For this example we'll create database objects, higher level dimension tables, aggregates, indexes, ect. Something about higher dimension tables, it depends how you understand normalized and denormalized structure in business intelligence term. Dimension tables are always denormalized, each level is placed inside it. If you for example query sh.products table you'll see that the lowest level has information about high levels. If you are using dimension operator in OWB to load data into, the result is dimension table with addition that all levels are separately loaded with each with its own ID, primary key. So other aggregation fact tables can reference high level dimension ID from the same dimension. The very similar way is how olap dimension works, see global.channel_dimview. Anyway, we'll create higher dimension level tables for this example purpose.

create table months as

select

distinct

calendar_month_id,

calendar_month_desc,

calendar_year_id,

calendar_year

from times

alter table months

add constraint

months_pk primary key (calendar_month_id);

create table categories as

select

distinct

prod_category_id,

prod_category

from products

alter table categories

add constraint

categories_pk primary key (prod_category_id)

create table years as

select

distinct

calendar_year_id,

calendar_year

from times

alter table years

add constraint

years_pk primary key (calendar_year_id)

create table sales_months as

select

t.calendar_month_id,

sum(s.amount_sold) as amount_sold,

sum(s.quantity_sold) as quantity_sold

from sales s, times t

where s.time_id=t.time_id

group by t.calendar_month_id;

alter table sales_months

add constraint sm_months_fk

foreign key (calendar_month_id)

references months (calendar_month_id)

create bitmap index sm_months_idx

on sales_months (calendar_month_id);

create table sales_year_cat as

select

t.calendar_year_id,

p.prod_category_id,

sum(s.quantity_sold) as quantity_sold,

sum(s.amount_sold) as amount_sold

from sales s, products p, times t

where s.prod_id=p.prod_id

and s.time_id=t.time_id

group by t.calendar_year_id, p.prod_category_id;

alter table sales_year_cat

add constraint syc_years_fk

foreign key (calendar_year_id)

references years (calendar_year_id)

create bitmap index syc_years_idx

on sales_year_cat (calendar_year_id);

alter table sales_year_cat

add constraint syc_categories_fk

foreign key (prod_category_id) references categories (prod_category_id)

create bitmap index syc_categories_idx

on sales_year_cat (prod_category_id);

create table sales_months_cat_ch as

select

t.calendar_month_id,

p.prod_category_id,

c.channel_id,

sum(s.quantity_sold) as quantity_sold,

sum(s.amount_sold) as amount_sold

from sales s, products p, times t, channels c

where s.prod_id=p.prod_id

and s.time_id=t.time_id

and s.channel_id=c.channel_id

group by t.calendar_month_id, p.prod_category_id, c.channel_id;

alter table sales_months_cat_ch

add constraint smcc_months_fk

foreign key (calendar_month_id)

references months (calendar_month_id)

create bitmap index smcc_months_idx

on sales_months_cat_ch (calendar_month_id);

alter table sales_months_cat_ch

add constraint smcc_channels_fk

foreign key (channel_id) references channels (channel_id)

create bitmap index smcc_channels_idx

on sales_months_cat_ch (channel_id);

alter table sales_months_cat_ch

add constraint smcc_categories_fk

foreign key (prod_category_id)

references categories (prod_category_id)

create bitmap index smcc_categories_idx

on sales_months_cat_ch (prod_category_id);

The focus is on how to implement this in obiee, not how these tables are refreshed with data or recreated as a part of the job of ETL process.

Implementation in obiee

Physical layer:

Foreign keys:

SALES.PRODUCT_ID >- PRODUCTS.PRODUCT_ID

SALES.TIME_ID >- TIMES.TIME_ID

SALES.CHANNEL_ID >- PRODUCTS.CHANNEL_ID

SALES_MONTHS_CAT_CH.CHANNEL_ID >- CHANNELS.CHANNEL_ID

SALES_MONTHS_CAT_CH.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID

SALES_MONTHS_CAT_CH.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

SALES_YEAR_CAT.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID

SALES_YEAR_CAT.CALENDAR_YEAR_ID >- YEARS.CALENDAR_YEAR_ID

SALES_MONTHS.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

BMM:

Drag and drop attributes from the physical layer to BMM, for example CALENDAR_YEAR_ID and CALENDAR_YEAR from YEARS physical table to TIMES logical table to create additional logical table sources. We repeat this step for other higher level dimension tables on the physical layer as weel as for SALES_MONTHS, SALES_YEAR_CAT and SALES_MONTHS_CAT_CH aggregate fact tables that contains measures AMOUNT_SOLD and QUANTITY_SOLD.

Dimensions:

On each logical fact table source on the logical fact table SALES we need to set aggregation levels and this is mandatory step for obiee to redirect SQL query on aggregate tables.

Aggregate sources are activated on certain levels of dimension.

Test

If we add CALENDAR_MONTH_DESC, instead of going to SALES (TIME_ID lowest level) and summarize it on the month level, the SQL query is redirected to SALES_MONTHS:

NQQuery-log:

In case of CALENDAR_YEAR the SQL query is also redirected to SALES_MONTHS:

Some other cases:

CALENDAR_MONTH_DESC, PROD_CATEGORY and CHANNEL_DESC:

NQQuery-log:

CALENDAR_YEAR and PROD_CATEGORY:

NQQuery.log: