Big Data, Data Warehouses, and Business Intelligence Systems - Database Processing: Fundamentals, Design, and Implementation - page 542

Figure 12-8
Characteristics of
Operational and Dimensional
Databases

Operational Database Dimensional Database

Used for structured transaction
data processing

Used for unstructured analytical
data processing

Current data are used Current and historical data are

used

Data are inserted, updated, and
deleted by users

Data are loaded and updated
systematically, not by users

data marts. The data marts receive all their data from the data warehouse—they do not add or
maintain any additional data.
Of course, it is expensive to create, staff, and operate data warehouses and data marts,
and only large organizations with deep pockets can afford to operate a system such as an EDW.
Smaller organizations operate subsets of this system. For example, they may have just a single
data mart for analyzing marketing and promotion data.

Dimensional Databases
The databases in a data warehouse or data mart are built to a different type of database design
than the normalized relational databases used for operational systems. The data warehouse
databases are built in a design called a dimensional database that is designed for efficient
data queries and analysis. A dimensional database is used to store historical data rather than
just the current data stored in an operational database. Figure 12-8 compares operational
databases and dimensional databases.
Because dimensional databases are used for the analysis of historical data, they must be
designed to handle data that change over time. For example, a customer may have moved
from one residence to another in the same city or may have moved to a completely different
city and state. This type of data arrangement is called a slowly changing dimension , and in
order to track such changes, a dimensional database must have a date dimension or time
dimension as well.

The Star Schema
Rather than using the normalized database designs used in operational databases, a
dimensional database uses a star schema. A star schema , so named because, as shown
in Figure 12-9, it visually resembles a star, has a fact table at the center of the star and

By The WAy There is a more complex version of the star schema called the snowflake
schema . In the snowflake schema, each dimension table is normalized,
which may create additional tables attached to the dimension tables.

Figure 12-9
The Star Schema

PRODUCT
(Dimension Table)

PRODUCT
(Dimension Table)

PRODUCT_SALES
(Fact Table)

PRODUCT_SALES
(Fact Table)

CUSTOMER
(Dimension Table)

TIME
(Dimension Table)

CUSTOMER
(Dimension Table)

TIME
(Dimension Table)