Aggregating Data For The Oracle Warehouse Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Aggregating Data For The Oracle Warehouse
* Region by state--This table would have region_name, state_name for dimensions, and total_cost as the fact column.

* Region by item type--This table would have region_name, item_type as dimensions, and total_cost as the fact column.

* Region by salesperson--This table would have region_name, salesperson_name as dimensions, and total_cost as the fact column.

* State by item type--This table would have state_name, item_type as dimensions, and total_cost as the fact column.

* State by salesperson--This table would have state_name, salesperson_name as dimensions, and total_cost as the fact column.

* Item type by salesperson--This table would have item_type, salesperson_name as dimensions, and total_cost as the fact column.

The SQL to produce these table can be easily run as a batch task during your end-of-month processing. For example, we can use the following SQL to create the region_by_state summary table for March 1997:

INSERT INTO region_item_type
VALUES
(SELECT “3”, “1997”, region_name, item_type, SUM(total_cost)
FROM fact_table
SEE CODE DEPOT FOR FULL SCRIPT
);

---

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.