Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Online Analytical Processing and Oracle
Alternatives To OLAP Data Representation

On the surface, it appears that SQL can be used against two-dimensional tables to handle three-dimensional time-series problems. It also appears that SQL can be used to roll up aggregations at runtime, alleviating the need to do a roll up at load time, as with a traditional database. While this implementation does not require any special multidimensional databases, two important issues remain unresolved:

* Performance--Joining a table against itself--especially when comparing ranges of dates--may create many levels of nesting in the SQL optimization and poor response time.

* Ability--Most end users would not be capable of formulating this type of sophisticated SQL query.

If you strip away all of the marketing hype and industry jargon, you can see that a data warehouse and a multidimensional database can be easily simulated by pre-creating many redundant tables, each with precalculated roll-up information. In fact, the base issue is clear--complex aggregation needs to be computed at runtime or data load time.

---

This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm