Star schema

Star schema

The star schema (sometimes referenced as star join schema) is the simplest style of data warehouse schema. The star schema consists of a few "fact tables" (possibly only one, justifying the name) referencing any number of "dimension tables". The star schema is considered an important special case of the snowflake schema.

Model

The "facts" that the data warehouse helps analyze are classified along different "dimensions": the fact tables hold the main data, while the usually smaller dimension tables describe each value of a dimension and can be joined to fact tables as needed.

Dimension tables have a simple primary key, while fact tables have a compound primary key consisting of the aggregate of relevant dimension keys.

It is common for dimension tables to consolidate redundant data and be in second normal form, while fact tables are usually in third normal form because all data depend on either one dimension or all of them, not on combinations of a few dimensions.

The star schema is a way to implement multi-dimensional database (MDDB) functionality using a mainstream relational database: given the typical commitment to relational databases of most organizations, a specialized multidimensional DBMS is likely to be both expensive and inconvenient.

Another reason for using a star schema is its simplicity from the users' point of view: queries are never complex because the only joins and conditions involve a fact table and a single level of dimension tables, without the indirect dependencies to other tables that are possible in a better normalized snowflake schema.

Example

Consider a database of sales, perhaps from a store chain, classified by date, store and product. The image of the schema to the right is a star schema version of the sample schema provided in the snowflake schema article.

Fact_Sales is the fact table and there are three dimension tables Dim_Date, Dim_Store and Dim_Product.

Each dimension table has a primary key on its Id column, relating to one of the columns of the Fact_Sales table's three-column primary key (Date_Id, Store_Id, Product_Id). The non-primary key Units_Sold column of the fact table in this example represents a measure or metric that can be used in calculations and analysis. The non-primary key columns of the dimension tables represent additional attributes of the dimensions (such as the Year of the Dim_Date dimension).

The following query extracts how many TV sets have been sold, for each brand and country, in 1997.SELECT P.Brand, S.Country, SUM (F.Units_Sold)FROM Fact_Sales FINNER JOIN Dim_Date D ON F.Date_Id = D.IdINNER JOIN Dim_Store S ON F.Store_Id = S.IdINNER JOIN Dim_Product P ON F.Product_Id = P.IdWHERE D.Year = 1997 AND P.Product_Category = 'tv'GROUP BY P.Brand, S.Country

See also

*Snowflake schema

External links

* [http://ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx Designing the Star Schema Database by Craig Utley]
* [http://opensourceanalytics.com/2006/04/28/sales-data-mart-dimensional-model-for-retail/ Star Schema for Retail Sales]
* [http://c2.com/ppr/stars.html Stars: A Pattern Language for Query Optimized Schema]
* [http://www.dwoptimize.com/2007/06/aiming-for-stars.html Star schema optimizations]
* [http://datawarehouse4u.info/Data-warehouse-schema-architecture-fact-constellation-schema.html Fact constellation schema]


Wikimedia Foundation. 2010.

Игры ⚽ Нужна курсовая?

Look at other dictionaries:

  • Star-Schema — Das Sternschema ist eine besondere Form eines Datenmodells, dessen Ziel nicht die Normalisierung ist, sondern eine Optimierung auf effiziente Leseoperationen. Hauptanwendungsfeld sind Data Warehouses und OLAP Anwendungen. Die Bezeichnung… …   Deutsch Wikipedia

  • STAR — bezeichnet: Vögel: Star (Plural: Stare), Sturnus vulgaris (von althochdeutsch stara): häufigster Vertreter der Vogelfamilie der Stare Stare (Sturnidae), eine artenreiche Vogelfamilie der Singvögel Personen: Star (Person) (Plural: Stars) (engl.… …   Deutsch Wikipedia

  • Star Wars: Battlefront II — Star Wars Battlefront II Éditeur LucasArts Développeur …   Wikipédia en Français

  • Star Wars: Battlefront 2 — Star Wars: Battlefront II Star Wars: Battlefront II Éditeur LucasArts Développeur Pandemic Studios Date de sortie PC, PS2, Xbox AN 1er novembre 2005 …   Wikipédia en Français

  • Star Wars : Battlefront II — Star Wars: Battlefront II Star Wars: Battlefront II Éditeur LucasArts Développeur Pandemic Studios Date de sortie PC, PS2, Xbox AN 1er novembre 2005 …   Wikipédia en Français

  • Star wars battlefront 2 — Star Wars: Battlefront II Star Wars: Battlefront II Éditeur LucasArts Développeur Pandemic Studios Date de sortie PC, PS2, Xbox AN 1er novembre 2005 …   Wikipédia en Français

  • Star Trek:DS9 — Seriendaten Deutscher Titel: Star Trek: Deep Space Nine Originaltitel: Star Trek: Deep Space Nine Produktionsland: USA …   Deutsch Wikipedia

  • Star Trek - Deep Space Nine — Seriendaten Deutscher Titel: Star Trek: Deep Space Nine Originaltitel: Star Trek: Deep Space Nine Produktionsland: USA …   Deutsch Wikipedia

  • Star Trek DS9 — Seriendaten Deutscher Titel: Star Trek: Deep Space Nine Originaltitel: Star Trek: Deep Space Nine Produktionsland: USA …   Deutsch Wikipedia

  • Snowflake schema — A snowflake schema is a logical arrangement of tables in a relational database such that the entity relationship diagram resembles a snowflake in shape. Closely related to the star schema, the snowflake schema is represented by centralized fact… …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”