Aggregates in OBIEE

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: