A new feature introduced with Oracle 9i Release 2 is the concept of OLAP Materialized Views. This new type of materialized view uses the GROUP BY … ROLLUP feature to potentially calculate all the possible aggregates for a particular fact table and its dimensions, storing the aggregates and the base detail level data within the same materialized view.
A typical example of an OLAP Materialized View is (examples from OTN documentation)
CREATE MATERIALIZED VIEW sales_hierarchical_cube_mv
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT country_id, cust_state_province, cust_city, prod_category, prod_subcategory, prod_name, calendar_month_number,day_number_in_month, day_number_in_week,GROUPING_ID(country_id, cust_state_province, cust_city,prod_category, prod_subcategory, prod_name,calendar_month_number, day_number_in_month,day_number_in_week) gid, SUM(amount_sold) s_sales,COUNT(amount_sold) c_sales, COUNT(*) c_star
FROM sales s, products p, customers c, times t
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
AND s.time_id = t.time_id
GROUP BY
ROLLUP(country_id, (cust_state_province, cust_city)),
ROLLUP(prod_category, (prod_subcategory, prod_name)),
ROLLUP(calendar_month_number, (day_number_in_month,
day_number_in_week))
...;
This creates a OLAP Materialized View, corresponding to a cube within a relational OLAP star schema, which contains all possible aggregates along all dimensions and hierarchies. This Materialized View could be created manually, or through the OLAP Summary Adviser recently introduced with Oracle 9i Release 2 Enterprise Manager.
The user could then issue a query against the base tables, choosing a particular slice of the cube, Oracle would then redirect their query to the OLAP Materialized View, using the query rewrite mechanism;
SELECT month, division, sum_sales FROM
(SELECT year, quarter, month, division, brand, item, SUM(sales) sum_sales,
GROUPING_ID(grouping-columns) gid
FROM sales, products, time
WHERE join-condition
GROUP BY
ROLLUP(year, quarter, month),
ROLLUP(division, brand, item)
)
WHERE division = 25
AND month = 200201
AND gid = gid-for-Division-Month;
Even without the OLAP materialized view, the users query would run faster than a query without the ROLLUP clause, as a new Oracle feature known as ‘group pruning’ quickly removes unneeded groups of data from the result sets and therefore works with a much smaller set of data. However, by precomputing the aggregates and placing them in an OLAP Materialized View, results can be instantaneous with query performance approaching that of dedicated MOLAP servers such as Oracle Express and Oracle 9i OLAP.
As long as your database contains enough space to store all possible aggregates for a fact table and its dimensions, this seems an excellent way to precompute all possible combinations of dimensions, levels and hierarchies, much in the same way that products such as Oracle 9i OLAP precompute aggregates as part of a data load. However, there’s a couple of ‘gotcha’s’ that are worth bearing in mind before converting all your standard materialized views to OLAP materialized views.
- To qualify for query rewrite, user queries must use the same GROUP BY … ROLLUP mechanism within their queries. Normal queries using just GROUP BY won’t get rewritten.
- Watch out for data explosion! A fact table with more than six or so dimensions could take up an awful amount of disk storage, so take a lesson from the MOLAP world and keep the number of dimensions in check. Alternatively, limit the GROUP BY … ROLLUP clause to just those dimensions you wish to precalculate, and consider introducing a WHERE clause to limit the amount of levels you wish to consider.
- Discoverer queries will never qualify for query rewrite, as the Discoverer Query Engine changes the initial SQL produced for a query (which could well use ROLLUPs) to use a series of in-line views which will break the query rewrite mechanism.
However, if you’re using a tool other than Discoverer (for example, BI Beans) and you’re looking to get sub-second response times from your data warehouse star-schema, it’s worth evaluation OLAP Materialized Views to see if it can improve your query response times.
5:40:45 PM 
|
|
