O'Reilly logo

Oracle® DBA Guide to Data Warehousing and Star Schemas by Bert Scalzo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Avoid Snowflakes

Look again at the dimensions in Figure 4-1. These three tables are clearly denormalized (i.e., in zero normal form), as they should be. But DBA instincts being what they are, far too often mistakenly attempt to normalize star schema designs. In effect, they try to apply OLTP logic to their data warehouse. In data warehousing parlance, such forced normalization attempts are called snowflakes. Let's look at a snowflake example to avoid.

First, the PRODUCT dimension clearly violates third normal form (i.e., a non-key column depends on another non-key column) in three places:

  • MFG_NAME depends entirely on MFG_CODE.

  • CATEGORY_NAME depends entirely on CATGEORY_CODE.

  • SUBCATEGORY_NAME depends entirely on SUBCATEGORY_CODE.

Likewise, the LOCATION ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required