Aggregation, Roll-Ups and STAR Schemas Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Aggregating Data For The Oracle Warehouse

Aggregation, Roll-Ups, And STAR Schemas
Let’s illustrate some methods for pre-calculating aggregate tables by looking at a sample STAR schema design. In Figure 6.3, you can see that our base data warehouse consists of a highly de-normalized fact table and numerous dimension tables.

Figure 6.3 A sample STAR schema.

Now, let’s assume that we have already defined and populated our STAR schema that contains the Total_cost for each order for each day. While it is now easy to see the total for each order, rarely do the users of a decision support system require this level of detail. Most managers would be more interested in knowing the sum of sales or units sold--aggregated by month, quarter, region, and so on. Even with a STAR schema, these types of aggregations would be hard to compute at runtime from our Oracle warehouse with an acceptable response time.

Essentially, we can either aggregate at runtime using ROLAP technology or pre-aggregate the data offline, thus making the totals available without realtime computation. While realtime data aggregation may provide up-to-the-minute detail, the expense of purchasing a ROLAP engine and the slow response time may make the data warehouse designer look for other methods for providing summary data. One simple alternative to realtime aggregation is to write SQL to pre-aggregate data according to the dimensions that end users may want to see. In our example, let’s assume management wants to aggregate total cost by region, state, item type, and salesperson. Because we have four possible dimensions, we can generate a list of the following six aggregate tables to precreate. Assume that all of the tables would have a month_year field as their primary key.

This is an excerpt from "High Performance Data Warehousing".

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.