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
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 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.