My first Publication Agile-Data-Warehouse-Design-eBook | Page 177

156
Chapter 5
Figure 5-14
Enhanced star
schema for
customer orders
Avoid the Snowflake Schema Anti-pattern
A snowflake
schema is a star
with normalized
dimensions
Resist the urge to
snowflake. For most
dimensions there
are no advantages
A snowflake schema is a dimensional model where one or more dimensions have
been normalized, producing additional lookup tables known as outriggers. If this is
done to each of the dimensions that surround a fact table, the simple star begins to
look more like a snowflake, as in Figure 5-15.
Once the model is in a familiar ER modeling tool you (or the DBAs) may be
tempted to introduce snowflaking to reduce data redundancy and simplify dimen-
sion maintenance. However, snowflake schemas are not generally recommended.
They are too complex for user presentation (if required by your BI tool), offer no
significant space savings (see Figure 5-8), exhibit poor dimension browsing per-
formance, and negate the advantages of bitmap indices. There are legitimate
reasons for snowflaking very large dimensions, covered in Chapter 6, but resist any
3NF (third normal form) urges brought on solely by using an ER modeling tool.