In relational implementation, the dimensional designs are mapped to a relational set of tables. You can implement the design into following two methods:
A star schema model can be depicted as a simple star: a
central table contains fact data and multiple tables radiate out from it,
connected by the primary and foreign keys of the database. In a star schema
implementation, Warehouse Builder stores the dimension data in a single table
or view for all the dimension levels.
For example, if you implement the Product dimension using a star schema, Warehouse Builder uses a single table to implement all the levels in the dimension, as shown in the screenshot. The attributes in all the levels are mapped to different columns in a single table called PRODUCT.
The snowflake schema represents a dimensional model which is also composed of a central fact table and a set of constituent dimension tables which are further normalized into sub-dimension tables. In a snowflake schema implementation, Warehouse Builder uses more than one table or view to store the dimension data. Separate database tables or views store data pertaining to each level in the dimension.
The screenshot displays the snowflake implementation of the
Product dimension. Each level in the dimension is mapped to a different table.
Ralph Kimball, the data warehousing guru, proposes three cases where snowflake implementation is not only acceptable but is also the key to a successful design:
Large customer dimensions where, for example, 80 percent of the fact table
measurements involve anonymous
Financial product dimensions for banks, brokerage houses, and insurance
companies, because each of
Multienterprise calendar dimensions because each organization has
idiosyncratic fiscal periods,
Ralph Kimball recommends that in most of the other cases, star schemas are a better solution. Although redundancy is reduced in a normalized snowflake, more joins are required. Kimball usually advises that it is not a good idea to expose end users to a physical snowflake design, because it almost always compromises understandability and performance.