Star Schema

Summary: in this article, we will discuss star schema in details including fact and dimension tables and some important notes about using star schema.

Introduction to star schema

Star schema is a dimensional design for a relational database often used in a data warehouse system. The star schema has a fact table at the center, and the fact table is surrounded by a number of dimension tables. The star schema name comes from the appearance of the schema, which looks like a star.

In the star schema, related dimensions grouped together as columns in dimension tables and used to store the context of the facts stored in the fact table.

Star schema example

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

Star Schema Example

Star Schema Example

Let’s take a look at the star schema example above in 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 are dimension tables, DIM_DATE DIM_STOREand DIM_PRODUCT.

Dimension tables

A dimension table consists of columns that represent dimensions which provide the context needed for studying the facts. A dimension table typically stores characters that describe facts. A dimension table often has many columns, each for an attribute of interest.

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

Fact table

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

The fact table contains surrogate keys as a part of its primary key. Those keys are the foreign key of the dimension tables.

As shown in the diagram above, the FACT_SALES  fact table consists of 1 fact named UNITS_SOLD. For more information on the 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 depends on the level of detail that the fact table stores.
  • The more dimension tables that star schema has, the more reporting possibilities it provides.

In this article, we’ve examined the star schema that contains a fact table and a number of dimension tables. We also gave you some helpful notes about the star schema.

  • Was this tutorial helpful ?
  • YesNo