Database Reference
In-Depth Information
In summary, a data warehouse contains read-only snapshots of highly consolidated and summarized data
from multiple internal and external sources that are refreshed periodically, usually on a daily or weekly basis.
Companies use data warehouses in support of their decision-making processing, which typically consists of
unstructured and nonrepetitive requests for exactly the type of information contained in a data warehouse.
Data Warehouse Structure and Access
A typical data warehouse structure is shown in Figure 9-13. The central Sales table is called a fact table. A
fact table consists of rows that contain consolidated and summarized data. The fact table contains a multipart
primary key, each part of which is a foreign key to the surrounding dimension tables. Each dimension table
contains a single-part primary key that serves as an index for the fact table and that contains other fields
associated with the primary key value. The overall structure shown in Figure 9-13 is called a star schema
because of its conceptual shape.
297
Dimension tables
Part
PartNum
Description
Class
Warehouse
Price
Customer
CustomerNum
CustomerName
Street
City
State
CreditLimit
Fact table
Other attributes
Other attributes
Sales
PartNum
CustomerNum
RepNum
TimeKey
UnitSales
DollarSales
Dimension table
Dimension table
Other attributes
Rep
RepNum
LastName
FirstName
Rate
Time
TimeKey
DayOfWeek
DayOfYear
WeekInYear
Month
Other attributes
Quarter
Other attributes
FIGURE 9-13
A star schema with four dimension tables and a central fact table
Access to a data warehouse is accomplished through the use of online analytical processing (OLAP) soft-
ware. OLAP software, whether it
'
s part of the DBMS or a separate product, is optimized to work efficiently
with data warehouses.
Users access a data warehouse using OLAP software to answer questions such as the following: How has
the average customer balance changed each year over the past five years? What are the total sales by month
for this year, and how do they compare to last year
s sales?
In posing those types of questions, users perceive the data in a data warehouse as a multidimensional
database. For example, if users
'
questions pertain to the Part, Customer, and Time dimensions, which appear
in Figure 9-13, users might visualize the data warehouse as a multidimensional database in the shape of a
data cube, as shown in Figure 9-14. Each axis in the data cube (Part, Customer, and Time) represents data
from a dimension table in Figure 9-13, and the cells in the data cube represent unit sales and dollar sales data
from the Sales fact table in Figure 9-13.
'
