Star Schema

Star Schema

Overview

A star schema diagram, with fact table in the center, and dimension tables radiating outward from the center.In SQL Server Analysis Services (SSAS), a star schema describes a common data model for relational data warehouses. In a star schema, there are two types of tables: fact tables and dimension tables. In general, a relational data warehouse is a historical repository of a business's transactions. Facts represent those transactions, and dimensions describe the facts. Facts are stored as rows in a fact table and dimension members are stored as rows in dimension tables; some columns in the fact table are foreign keys that reference rows in dimension tables. When diagrammed, the resulting schema resembles a star with the fact table at center, and the dimension tables surrounding the fact table.

Star schemata differ from another popular data warehouse model, the Snowflake Schema in their level of normalization. A popular misconception is that star schemata are not normalized or are "denormalized." Star schemata are normalized, typically to second normal form (2NF); specifically, the dimension tables can contain transitive dependencies, so that some columns of the table are functionally dependent on nonkey columns of the table. Reducing the level of normalization eliminates joins when querying the database, and this can enhance query performance, particularly since relational data warehouses tend to contain large amounts of data.


See Also



Other Languages

This article is also available in the following languages:

Brazilian Portuguese (pt-BR)

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Ed Price MSFT edited Revision 3. Comment: Fixing a link.

  • Ed Price MSFT edited Original. Comment: Just added the SSAS tag.

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Ed Price MSFT edited Original. Comment: Just added the SSAS tag.

  • Ed Price MSFT edited Revision 3. Comment: Fixing a link.

  • Fernando Lugão Veltem edited Revision 6. Comment: added portuguese version

  • Fernando Lugão Veltem edited Revision 10. Comment: remove en-us from title

Page 1 of 1 (4 items)