Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 120

A snowflake schema avoids the redundancy of star schemas by normal-
izing the dimension tables. Therefore, a dimension is represented by several
tables related by referential integrity constraints. In addition, as in the
case of star schemas, referential integrity constraints also relate the fact table
and the dimension tables at the finest level of detail.

Product Category Department

ProductKey
ProductNumber
ProductName
Description
Size
CategoryKey
...

CategoryKey
CategoryName
Description
DepartmentKey
...

DepartmentKey
DepartmentName
Description
...

Promotion Sales Time

PromotionKey
PromotionDescr
DiscountPerc
Type
StartDate
EndDate
...

ProductKey
StoreKey
PromotionKey
TimeKey
Amount
Quantity

TimeKey
Date
Event
WeekdayFlag
WeekendFlag
Season
...

Store

City State

StoreKey
StoreNumber
StoreName
StoreAddress
ManagerName
CityKey
...

CityKey
CityName
CityPopulation
CityArea
StateKey
...

StateKey
StateName
StatePopulation
StateArea
StateMajorActivity
...

Fig. 5.2 An example of a snowflake schema

An example of a snowflake schema is given in Fig. 5.2 . Here, the fact
table is exactly the same as in Fig. 5.1 . However, the dimensions Product
and Store are now represented by normalized tables. For example, in the
Product dimension, the information about categories has been moved to the
table Category , and only the attribute CategoryKey remained in the original
table. Thus, only the value of this key is repeated for each product of the same
category, but the information about a category will only be stored once, in
table Category . Normalized tables are easy to maintain and optimize storage
space. However, performance is affected since more joins need to be performed
when executing queries that require hierarchies to be traversed. For example,
the query “Total sales by category” for the star schema in Fig. 5.1 reads in
SQL as follows: