This structure is called a snowflake schema because
the dimension tables branch out, much like the ice crystals of a snowflake. Figure 3-4
shows a simple star schema.
In many data warehouses, there are multiple fact tables. This may be due to several
factors, two of which are relatively common. First, different fact tables may contain
different dimensions. A fact table that supports manufacturing will have a product
dimension and an assembly line dimension. A fact table that supports sales will have
a product dimension and a customer dimension. Assembly line has nothing to do with
Figure 3-4 A simple star schema showing the fact table and related dimension tables
C h a p t e r 3 : D a t a W a r e h o u s i n g a n d B u s i n e s s I n t e l l i g e n c e 43
sales, and the customer has nothing to do with manufacturing. Note that these fact
tables should, however, have a conformed product dimension table joined to them.
Second, fact tables may be at differently levels of granularity. Fact tables that
contain actual data are typically at lower levels of detail, such as Day. Fact tables
designed to hold forecast or budget values are often at higher levels of detail, such as
Month or Quarter. Therefore, while both the sales and forecast fact tables contain a
Time dimension, they have a different grain and therefore cannot be a single fact table.
One aspect of fact tables that breaks with traditional normalization techniques is that
calculated fields are often included as some of the measures.
Pages:
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83