simple star schema. "images/starp1.gif" Star Schema The snowflake schema is an extension of the star schema where each point of the star explodes into more points. In this schema, the star schema dimension tables are more normalized. The advantages provided by the snowflake schema are improvements in query performance due to minimized disk storage for the data and improved performance by joining smaller normalized tables, rather than large denormalized ones. The snowflake schema also increases the flexibility of the application because of the normalization that lowers the granularity of the dimensions. However, since the snowflake schema has more tables, it also increases the complexities of some of the queries that need to be mapped. Figure 3 below depicts a snowflake schema. Query optimization Performance in data retrieval can be greatly enhanced through the use of multidimensional and aggregation indexes in a star or snowflake environment. Over 90% of data warehousing queries are multidimensional in nature using multiple criteria against multiple columns. For example, end-users rarely want to access data by only one column or dimension, such as finding the number of customers in the state of CA. They more commonly want to ask complex questions such as how many customers in the state of CA have purchased product B and C in the last year, and how does that compare to the year before. To optimize the query, an index can be put on each column that end-users want to query in the dimension tables. When an end-user issues a query, a qualifying count based on index access only can be returned without touching the actual data. According to Bill Inmon, it is much more efficient to service a query request by simply looking in an index or indexes instead of going to the primary source of data. In addition to multidimensional queries, end-users often want to see the data aggregated. A data aggregation is usually a COUNT or SUM, but can be an AVER...
