My first Publication Agile-Data-Warehouse-Design-eBook | Page 25

4
Chapter 1
OLTP vs. DW/BI: Two Different Worlds
OLTP and DW/BI
have radically
different DBMS
requirements
Operational systems and data warehouses have fundamentally different purposes.
Operational systems support the execution of business processes, while data ware-
houses support the evaluation of business processes. To execute efficiently, opera-
tional systems must be optimized for online transaction processing (OLTP). In
contrast, data warehouses, must be optimized for query processing and ease of use.
Table 1-1 highlights the very different usage patterns and database management
system (DBMS) demands of the two types of system.
Table 1-1 C RITERIA OLTP D ATABASE D ATA W AREHOUSE
Comparison Purpose Execute individual
business processes
(“turning the handles”) Evaluate multiple business
processes
(“watching the wheels turn”)
Transaction type Insert, select, update,
delete Select
Transaction style Predefined: predictable,
stable Ad-hoc: unpredictable, volatile
Optimized for Update efficiency and
write consistency Query performance and
usability
Update frequency Real-time: when busi-
ness events occur Periodic, (daily) via scheduled
ETL (extract, transform, load).
Moving to near real-time
Update
concurrency High Low
Historical data
access Current and recent
periods Current + several years of
history
Selection criteria Precise, narrow Fuzzy, broad
Comparisons Infrequent Frequent
Query complexity Low High
Tables/joins per
transaction Few (1–3) Many (10+)
Rows per
transaction Tens Millions
Transactions per
day Millions Thousands
Data volumes Gigabytes–Terabytes Terabytes–Petabytes
(many sources, history)
Data Mainly raw detailed
data Detailed data, summarized
data, derived data
Design technique Entity-Relationship
modeling (normalization) Dimensional modeling
Data model
diagram ER diagram Star schema
between OLTP
databases and
Data Warehouses