Pre-calculating and Storing Summary Data Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Aggregating Data For The Oracle Warehouse A Method For Pre-calculating And Storing Summary Data

If we are using a vanilla Oracle database without a multidimensional engine, we can create a method for determining new tables that will contain summary data. Then, we can come up with a method for keeping these summary tables correct. Let’s start by examining the table structure of our existing data warehouse. As you saw in Figure 6.3, the fact table is highly de-normalized and there are two facts: quantity_sold and total_cost. (the facts are usually listed last in the fact table, after all of the key columns) You also saw that there are dimensions of attributes relating to customers, salespersons, and items. Now, let’s take a look at how we might create aggregate tables from this model.

Determining The Number Of Aggregate Tables

The first step in creating summary data tables is to identify all the possible dimensions that our Oracle warehouse will use. Let’s start by listing the dimension attributes that might be used in our summary tables, such as:

* Month

* Quarter

* Year

* Customer_name

* Customer_city

* Customer_state

* Customer_region

* Item_name

* Item_Price

* Salesperson_name

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.