ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ZT:Star and Snowflake Schemas

ZT:Star and Snowflake Schemas

原创 Linux操作系统 作者:lfree 时间:2006-10-22 00:00:00 0 删除 编辑

http://www.oracle.com/technology/obe/10gr2_owb/owb10gr2_gs/owb/lesson3/starandsnowflake.htm


n relational implementation, the dimensional designs are mapped to a relational set of tables. You can implement the design into following two methods:

Star Schema

Snowflake Schema

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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
visitors about whom you collect little detail, and 20 percent involve reliably registered customers about
whom you collect much detailed data by tracking many dimensions

Financial product dimensions for banks, brokerage houses, and insurance companies, because each of
the individual products has a host of special attributes not shared by other products

Multienterprise calendar dimensions because each organization has idiosyncratic fiscal periods,
seasons, and holidays

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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-82928/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292506