Database Reference
In-Depth Information
Chapter 4
Designing a Data Warehouse
Organizing is what you do before you do something, so that when you do it, it is not all mixed
up.
—A. A. Milne
Designing a data warehouse is one of the most important aspects of a business intelligence solution. If the
data warehouse is designed correctly, all other aspects of the solution will benefit. Conversely, if it is created
incorrectly, it will cause no end of problems.
In this chapter, we show techniques for designing a data warehouse, including different designs and terms
used in the creation process and their proper use. Our focus is on simple practical designs that will get you
building your first data warehouses quickly and easily.
When you have completed this chapter, you should be able to design data warehouses using industry
standards and know the common rules that are considered “best practice” for the design process.
What Is a Data Warehouse?
At its core, a data warehouse is a collection of data designed for the easy extraction of information. It can be in
any form, including a series of text files, but most often it is a relational database. Because of this, most developers
think of a data warehouse simply as a reporting database. And although that is not the most highbrow definition,
it is fairly accurate.
Many developers will have differing opinions on what is the best way to design a data warehouse. But there
are common characteristics you can expect to see in all of them. The first common characteristic is a set of values
used for reports. These are called
measures
. For example, InventoryUnits and SalesDollars can be considered
measures. Another common characteristic found in data warehouses is a set of dimensions. Dimensions describe
the measured data. Examples of dimensions include the dates that the InventoryUnits were documented or the
zip code of the customers who bought a particular product.
We discuss more on both of these subjects in the next few pages.
What Is a Data Mart?
A data mart is also a collection of data. It, too, is designed to allow for the easy extraction of information. The
information in a data mart, however, is more specific than that of a data warehouse. Typically, a data mart is
created for a particular process, such as a sales event or taking inventory.
Data marts can also be designed around departments within the company. But you are typically better off
defining the data mart based on a process, not a department. This is because when you define it with a process,
