Page 178 -

HAN
11-ch04-125-186-9780123814791
2011/6/1
#17
3:17 Page 141
4.2 Data Warehouse Modeling: Data Cube and OLAP 141

time sales item supplier
Dimension table Fact table Dimension table Dimension table
time_key time_key item_key supplier_key
day item_key item_name supplier_type
day_of_week branch_key brand
month location_key type
quarter dollars_sold supplier_key
year units_sold

branch location
Dimension table Dimension table
branch_key location_key city
branch_name street Dimension table
branch_type city_key city_key
city
province_or_state
country

Figure 4.7 Snowflake schema of a sales data warehouse.

Example 4.2 Snowflake schema. A snowflake schema for AllElectronics sales is given in Figure 4.7.
Here, the sales fact table is identical to that of the star schema in Figure 4.6. The
main difference between the two schemas is in the definition of dimension tables.
The single dimension table for item in the star schema is normalized in the snowflake
schema, resulting in new item and supplier tables. For example, the item dimension
table now contains the attributes item key, item name, brand, type, and supplier key,
where supplier key is linked to the supplier dimension table, containing supplier key and
supplier type information. Similarly, the single dimension table for location in the star
schema can be normalized into two new tables: location and city. The city key in the
new location table links to the city dimension. Notice that, when desirable, further nor-
malization can be performed on province or state and country in the snowflake schema
shown in Figure 4.7.
Fact constellation: Sophisticated applications may require multiple fact tables to share
dimension tables. This kind of schema can be viewed as a collection of stars, and
hence is called a galaxy schema or a fact constellation.

Example 4.3 Fact constellation. A fact constellation schema is shown in Figure 4.8. This schema
specifies two fact tables, sales and shipping. The sales table definition is identical to that of
the star schema (Figure 4.6). The shipping table has five dimensions, or keys—item key,
time key, shipper key, from location, and to location—and two measures—dollars cost