As for aggregates, you should always implement them as materialized views, period. First, no matter what business intelligence tool your end-users select, query rewrites can be accomplished and are desirable. Second, regardless of which aggregation method you implement from the previous section, they all will work equally well against a materialized view (since it's nothing more than a locally replicated table). There are no downsides to implementing aggregates as materialized views, therefore you should always do so. And for those of you who already have a data warehouse built, go back and create materialized views on your pre-existing aggregate tables. This way, you too can get query rewrites even though you're not using any other materialized view features.
Here are the basic implementation guidelines:
Create Oracle dimensions for each dimension table.
Enable dimension primary key constraints with NOVALIDATE (if they don't exist).
Enable fact primary key constraint with NOVALIDATE (using existing unique index).
Enable fact to dimension foreign key constraints with NOVALIDATE.
Create materialized view logs on dimensions.
Create a materialized view log on the base fact table.
Create materialized views with query rewrite enabled for aggregates.
Create star transformation bitmap indexes and statistics on materialized views.
Use Oracle Enterprise Manager's Summary Advisor to gauge effectiveness.
Detailed below are the above steps applied to this book's simple data warehousing data model shown in Figure 7-6:
Create Oracle dimensions for each dimension table:
CREATE DIMENSION time_dim LEVEL curdate IS period.period_date LEVEL month IS period.period_month LEVEL quarter IS period.period_quarter LEVEL year IS period.period_year LEVEL week_num IS period.week_number HIERARCHY calendar_rollup( curdate CHILD OF month CHILD OF quarter CHILD OF year) HIERARCHY weekly_rollup( curdate CHILD OF week_num) ATTRIBUTE curdate DETERMINES period.day_of_wk;
Enable dimension primary key constraints with NOVALIDATE (if they don't exist):
alter table period add constraint period_pk primary key (period_id) novalidate; alter table location add constraint location_pk primary key (location_id) novalidate; alter table product add constraint product_pk primary key (product_id) novalidate;
Enable fact primary key constraint with NOVALIDATE (using existing unique index):
alter table pos_day add constraint pos_day_pk primary key (PERIOD_ID, LOCATION_ID, PRODUCT_ID) using index pos_day_pk novalidate;
Enable fact to dimension foreign key constraints with NOVALIDATE:
alter table pos_day add constraint pos_day_fk1 foreign key (period_id) references period(period_id) novalidate; alter table pos_day add constraint pos_day_fk2 foreign key (location_id) references location(location_id) novalidate; alter table pos_day add constraint pos_day_fk3 foreign key (product_id) references product(product_id) novalidate;
Create materialized view logs on dimensions:
create materialized view log on period WITH SEQUENCE, ROWID ( PERIOD_ID, PERIOD_NAME, LEVELX, CURRENT_FLAG, PERIOD_DATE, PERIOD_WEEK, PERIOD_MONTH, PERIOD_QUARTER, PERIOD_YEAR, DAY_NUMBER_OF_WK, DAY_NUMBER_OF_MTH, HOLIDAY_FLAG, WEEKEND_FLAG, WORKDAY_FLAG, DAY_OF_WK, WEEK_NUMBER ) INCLUDING NEW VALUES;
Create a materialized view log on the base fact table:
create materialized view log on pos_day WITH SEQUENCE, ROWID ( PERIOD_ID, LOCATION_ID, PRODUCT_ID, SALES_UNIT, SALES_RETAIL, GROSS_PROFIT ) INCLUDING NEW VALUES;
Create materialized views with query rewrite enabled for aggregates:
create materialized view mv_pos_week parallel (degree 1) nologging BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE as select /*+ parallel(pos_day,1) full(pos_day) */ period.wk_id period_id, location_id, product_id, sum(nvl(sales_unit,0)), sum(nvl(sales_retail,0)), sum(nvl(gross_profit,0)) from pos_day, (select a.period_id wk_id, b.period_id d1_id, c.period_id d2_id from period a, period b, period c where a.levelx='WEEK' and b.levelx='DAY' and c.levelx='DAY' and a.period_date = b.period_date and a.period_date + 6 = c.period_date and exists (select 1 from pos_day where period_id between b.period_id and c.period_id ) ) period where period_id between period.d1_id and period.d2_id group by period.wk_id, location_id, product_id;
Create star transformation bitmap indexes and statistics on materialized views:
CREATE BITMAP INDEX MV_POS_WEEK_B1 ON MV_POS_WEEK (PERIOD_ID) PCTFREE 1 NOLOGGING; CREATE BITMAP INDEX MV_POS_WEEK_B2 ON MV_POS_WEEK (LOCATION_ID) PCTFREE 1 NOLOGGING; CREATE BITMAP INDEX MV_POS_WEEK_B3 ON MV_POS_WEEK (PRODUCT_ID) PCTFREE 1 NOLOGGING; analyze table mv_pos_week estimate statistics for table for all indexes for all indexed columns sample 20000 rows;
Use Oracle Enterprise Manager's Summary Advisor to gauge effectiveness (shown in Figure 7-7)