|Star and snowflake schemas. A star schema features a single fact table, with detail and summary data. The primary key has only one key column per dimension. Each key is generated, and each dimension is a single table. |
The benefit of such a schema is that it offers hierarchies that are easy to understand and easy to define. As a result, you find a reduced number of physical joins, simple metadata, and a low-maintenance data mart. On the other hand, the summary data in the fact table yields poorer performance for summary levels. And huge dimension tables can present a problem.
The snowflake schema offers no level in dimension tables. The tables are normalized by decomposing at the attribute level. Each dimension table has one key for each level of the dimension's hierarchy. The lowest level key joins the dimension table to both the fact table and the lower level attribute table.
So how does it work? The best way is for the query to be built by understanding which summary levels exist, finding the proper snowflaked attribute tables, constraining keys, then selecting from the fact table.