Data Warehouse Concepts - Data Warehouse Systems: Design and Implementation - page 73

Table 3.2 Comparison between operational databases and data ware-
ho u ses

Aspect Operational databases Data warehouses

1 User type Operators, o ce employees Managers, executives

2 Usage Predictable, repetitive Ad hoc, nonstructured

3 Data content Current, detailed data Historical, summarized data

4 Data organization According to operational
needs

According to analysis needs

5 Data structures Optimized for small

transactions

Optimized for complex
queries

6 Access frequency High From medium to low

7 Access type Read, insert, update, delete Read, append only

8 Number of records
per access

Few Many

9 Response time Short Can be long

10 Concurrency level High Low

11 Lock utilization Needed Not needed

12 Update frequency High None

13 Data redundancy Low (normalized tables) High (denormalized tables)

14 Data modeling UML, ER model Multidimensional model

OLAP systems are not so frequently accessed as OLTP systems. For example,
a system handling purchase orders is frequently accessed, while performing
analysis of orders may not be that frequent. Also, data warehouse records
are usually accessed in read mode (lines 5-8). From the above, it follows
that OLTP systems usually have a short query response time, provided the
appropriate indexing structures are defined, while complex OLAP queries can
take a longer time to complete (line 9).
OLTP systems have normally a high number of concurrent accesses and
therefore require locking or other concurrency management mechanisms to
ensure safe transaction processing (lines 10-11). On the other hand, OLAP
systems are read only, and therefore queries can be submitted and computed
concurrently, with no locking or complex transaction processing requirements.
Further, the number of concurrent users in an OLAP system is usually low.
Finally, OLTP systems are constantly being updated online through trans-
actional applications, while OLAP systems are updated off-line periodically.
This leads to different modeling choices. OLTP systems are modeled using
UML or some variation of the ER model studied in Chap. 2 . Such models
lead to a highly normalized schema, adequate for databases that support
frequent transactions, to guarantee consistency and reduce redundancy.
OLAP designers use the multidimensional model, which, at the logical level
(aswewillseeinChap. 5 ), leads in general to a denormalized database
schema, with a high level of redundancy, which favors query processing (lines
12-14).