Some ideas on data modeling in data warehouse

Several ideas of data modeling in data warehouse are mainly divided into the following.

1. star mode

Star schema is the most commonly used way of dimension modeling. The star pattern centers on the fact table, and all dimension tables are directly connected to the fact table, just like a star. The dimension modeling of star schema consists of a fact table and a group of dimension tables, which have the following characteristics: a. Dimension tables are only associated with fact tables, but there is no association between them; B, the primary key of each dimension table is a single column, and the primary key is placed in the fact table as a foreign key connected on both sides; C, taking the fact table as the core, and the dimension table is distributed in a star shape around the core;

2. Snowflake mode

Snowflake mode is an extension of star mode. The dimension table of snowflake mode can have other dimension tables. Although this model is more standardized than the star model, it is not easy to understand, the maintenance cost is high, and the performance needs to associate multi-layer dimension tables, which is lower than the star model. So it is generally not very common.

Snowflake pattern

3. Constellation mode

Constellation mode is an extension of star mode, based on one fact table, while constellation mode is based on multiple fact tables, and * * * enjoys dimensional information. The two-dimensional modeling method introduced earlier is that multi-dimensional tables correspond to a single fact table, but in many cases, there are multiple fact tables in the dimension space, and a dimension table may also be used by multiple fact tables. In the later stage of business development, most dimension modeling adopts constellation mode.

Constellation model