16.10 - Designing for OLTP and Designing for Data Warehousing Support - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Differences Between OLTP and Data Warehouse Processing

The following table examines some of the principal differences in OLTP and Data Warehouse processing that are critical to database design:

                                   Attribute                             Processing Type
  OLTP Transaction Data Warehouse Query
Multiple tables scanned? No Yes
Large data volumes? No Yes
Processing intensive? No Yes
Processing time a function of database size? No Yes

As you can see from this side-by-side comparison, OLTP transactions and Data Warehouse queries are dramatically different entities and, by inference, might be expected to require physically different database support.

Data Warehousing Support

Relational databases that support data warehousing are optimized to support decision support applications in all the forms listed below and more.

  • Ad hoc SQL or natural language-generated SQL queries
  • Repeatedly stated queries in the form of macros, embedded SQL applications, or stored procedures
  • Data mining and OLAP explorations

The data in the warehouse is stored in the form of multiple normalized tables that model your enterprise. The decision support applications that explore these data often perform full file scans of multiple large tables, making them processing intensive. Because processing time is directly related to data volume, the responsiveness of the system is heavily affected by the size of the underlying database.

OLTP

OLTP transaction processing operates differently. OLTP systems do not scan multiple tables simultaneously, nor do they access large volumes of data. This makes their consumption of processing and I/O resources minuscule in comparison with decision support query processing. Size is not a factor in system responsiveness.