Page 167 -

HAN 11-ch04-125-186-9780123814791
2011/6/1

130 Chapter 4 Data Warehousing and Online Analytical Processing 3:17 Page 130 #6

Table 4.1 Comparison of OLTP and OLAP Systems
Feature OLTP OLAP

Characteristic operational processing informational processing
Orientation transaction analysis
User clerk, DBA, database professional knowledge worker (e.g., manager,
executive, analyst)
Function day-to-day operations long-term informational
requirements decision support
DB design ER-based, application-oriented star/snowflake, subject-oriented
Data current, guaranteed up-to-date historic, accuracy maintained
over time
Summarization primitive, highly detailed summarized, consolidated
View detailed, flat relational summarized, multidimensional
Unit of work short, simple transaction complex query
Access read/write mostly read
Focus data in information out
Operations index/hash on primary key lots of scans
Number of records
accessed tens millions
Number of users thousands hundreds
DB size GB to high-order GB ≥ TB
Priority high performance, high availability high flexibility, end-user autonomy
Metric transaction throughput query throughput, response time

Note: Table is partially based on Chaudhuri and Dayal [CD97].

support requires historic data, whereas operational databases do not typically maintain
historic data. In this context, the data in operational databases, though abundant, are
usually far from complete for decision making. Decision support requires consolidation
(e.g., aggregation and summarization) of data from heterogeneous sources, resulting
in high-quality, clean, integrated data. In contrast, operational databases contain only
detailed raw data, such as transactions, which need to be consolidated before analy-
sis. Because the two systems provide quite different functionalities and require different
kinds of data, it is presently necessary to maintain separate databases. However, many
vendors of operational relational database management systems are beginning to opti-
mize such systems to support OLAP queries. As this trend continues, the separation
between OLTP and OLAP systems is expected to decrease.

4.1.4 Data Warehousing: A Multitiered Architecture
Data warehouses often adopt a three-tier architecture, as presented in Figure 4.1.