Microsoft SQL Server 2012 Bible

Designing a Data Warehouse Using a Star Schema

Data warehouse design technique is referred to as a Star Schema. As mentioned earlier, a data warehouse is composed of facts and dimensions. The layout of these tables resembles a star, as shown in Figure 51.1.

Figure 51.1 The diagram view of the AdventureWorks data warehouse database shows one fact table and several dimensions resembling a star.

You see from the diagram that the one fact is central to multiple dimensions. Each dimension is representative of a way that the measures in the fact can be grouped, aggregated, or categorized. For example, you could use the SalesAmount column from the FactInternetSales table and aggregate it by Customer to see the total sales for each customer.

A fact table usually consists of two types of columns: The surrogate key column, which is defined in the Surrogate Key section of this chapter, and the facts (or measures) column.

The dimension tables, on the other hand, contains a primary key (surrogate key), and alternate key, and one or more attributes. The alternate key is not always present in the dimension. It is, in most cases, the primary key for each row from the operational system. The attributes are the data that categorizes the dimensions. For example, a student dimension may include attributes for name, address, city, state, and e-mail address. Dimensions in many instances are denormalized ...