[DWH] Star Schema

 

 

Star Schema

Star schema is a dimensional design for a relational database often used in a data warehouse system. There is a fact table at the center of the schema surrounding by a number of dimension tables therefore the name star schema comes from its appearance: it looks like a star. In the star schema, related dimensions grouped together as columns in dimension tables and used to store context of the facts stored in the fact table.

Star schema example

The following is a simple star schema based on dimensions and facts for sale process.

Star Schema Example


Let’s take a look at the star schema example above in a greater detail:

• At the center of the schema we have a fact table called FACT_SALES. The primary key of the fact table contains three surrogate keys associated with dimension tables: DATE_ID, STORE_ID and PRODUCT_ID. The field UNITS_SOLD is used to store facts.
• Surrounding the fact table is number of dimension tables DIM_DATE, DIM_STORE and DIM_PRODUCT.

Dimension tables

A dimension table consists of columns representing dimensions that provide context needed for studying the facts.  A dimension table typically stores characters that describe facts. A dimension table normally has many columns, one per attribute of interest.

The dimension table is not and not necessary in the third normal form (3NF). The primary key of a dimension table is a single surrogate key that is a part of composite primary key of the fact table. It is always a surrogate key that generated and managed within a data warehouse system.  For more information on dimension table, check it out dimension table article.

Fact table

Fact table is at the core of the star schema. Fact table stores measure of interests or facts. Normally facts are in numeric that can be aggregated, summarized or rolled up…

The fact table contains surrogate keys as a part of its primary key referring to the corresponding dimension tables.  As shown in the diagram above, the FACT_SALES includes 1 fact called UNITS_SOLD. For detailed information on fact table, please refer to the fact table article.

Star schema notes

• Star schema can help business analysts to answer questions that might not have been asked during the design process by looking through different dimensions.
• Star schema often stores data at a great level of detail however it can be rolled up at various levels of detail based on aggregations. The capability to study facts only depends on level of detail that the fact table stores.
• The more dimension tables you add to the star schema, the more reporting possibilities it provides.