TABLE OF CONTENTS This material was provided by Michael L. Gonzales from his book, The IBM Data Warehouse (Gonzales 2003), as well as his article "The SQL Language of OLAP," (Gonzales 2004).
Most SQL programmers work with OLTP (Online Transaction Processing) databases and have had no exposure to Online Analytic Processing (OLAP) and data warehousing. OLAP is concerned with summarizing and reporting data, so the schema designs and common operations are very different from the usual SQL queries.
As a gross generalization, everything you knew in OLTP is reversed in a data warehouse:
OLTP changes data in short, frequent transactions. A data warehouse is bulk-loaded with static data on a schedule, and the data remains constant once it is in place.
An OLTP database wants to store only the data needed to do its current work. A data warehouse wants all the historical data it can hold. For example, as of 2005, Wal-Mart had a corporate data warehouse with more than half a petabyte of data online. The definition of a petabyte is 2^50 = 1,125,899,906,842,624 bytes = 1,024 terabytes, or roughly 10^15 bytes.
OLTP queries tend to be for simple facts. Data warehouse queries tend to be aggregate relationships that are more complex. For example, an OLTP query might ask, "How much chocolate did Joe Celko buy?" while a data warehouse might ask, "What is the correlation between chocolate purchases, geographic location, and wearing...
