Databases Reference
In-Depth Information
time
Dimension table
sales
Fact table
item
Dimension table
time_key
day
day_of_the_week
month
quarter
year
time_key
item_key
branch_key
location_key
dollars_sold
units_sold
item_key
item_name
brand
type
supplier_type
branch
Dimension table
location
Dimension table
branch_key
branch_name
branch_type
location_key
street
city
province_or_state
country
Figure 4.6
Star schema of
sales
data warehouse.
with two measures:
dollars sold
and
units sold
. To minimize the size of the fact table,
dimension identifiers (e.g.,
time key
and
item key
) are system-generated identifiers.
Notice that in the star schema, each dimension is represented by only one table, and
each table contains a set of attributes. For example, the
location
dimension table contains
the attribute setf
location key, street, city, province or state, country
g. This constraint may
introduce some redundancy. For example, “Urbana” and “Chicago” are both cities in the
state of Illinois, USA. Entries for such cities in the
location
dimension table will create
redundancy among the attributes
province or state
and
country
; that is,
.
..., Urbana, IL,
USA
. Moreover, the attributes within a dimension table may
form either a hierarchy (total order) or a lattice (partial order).
/
and
.
..., Chicago, IL, USA
/
Snowflake schema:
The snowflake schema is a variant of the star schema model,
where some dimension tables are
normalized
, thereby further splitting the data into
additional tables. The resulting schema graph forms a shape similar to a snowflake.
The major difference between the snowflake and star schema models is that the
dimension tables of the snowflake model may be kept in normalized form to reduce
redundancies. Such a table is easy to maintain and saves storage space. However, this
space savings is negligible in comparison to the typical magnitude of the fact table. Fur-
thermore, the snowflake structure can reduce the effectiveness of browsing, since more
joins will be needed to execute a query. Consequently, the system performance may be
adversely impacted. Hence, although the snowflake schema reduces redundancy, it is not
as popular as the star schema in data warehouse design.
