Summary: This article will discuss star schema in detail, 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 some dimension tables surround the fact table. The star schema name comes from the appearance of the schema, which looks like a star.
In the star schema, related dimensions are grouped 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.
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_SALESThe primary key of the fact table contains three surrogate keys associated with dimension tables:DATE_IDSTORE_IDandPRODUCT_IDThe fieldUNITS_SOLDis used to store facts. - Surrounding the fact table are dimension tables,
DIM_DATEDIM_STOREandDIM_PRODUCT.
Dimension tables
A dimension table consists of columns that represent dimensions that 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 the data warehouse system generates and manages. For more information on the dimension table, check out the dimension table article.
Fact table
The fact table is at the core of the star schema—the fact table stores facts or measures 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, data can roll it 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.
This article examines the star schema that contains a fact table and several dimension tables. We also gave you some helpful notes about the star schema.
