Why dimension modeling?

Dimension model was initiated by Ralph kimball, a master in the field of data warehouse. The model is established based on the needs of analysis and decision-making, and the established data model serves the needs of analysis. Therefore, it focuses on how users can complete the analysis requirements more quickly, and at the same time, it has better response performance to large-scale complex queries.

Dimension modeling is the key to the success of data warehouse/business intelligence project. Why do you say that? Because no matter whether our data volume is from GB to TG or PB, although the data volume is getting larger and larger, the success of data presentation must be based on simplicity, and dimension modeling is to always consider how to provide simplicity, be business-driven, and aim at user understanding and query performance.

Dimension modeling: Dimension modeling is a method specially used to model and analyze databases, data warehouses and data markets. Data mart can be understood as a kind of "small data warehouse", which guides us how to build tables in the data warehouse.

Dimension modeling is divided into two tables: fact table and dimension table.

Fact table: Some data that must exist, such as collected log files and order tables, can be used as fact tables.

Characteristics: It is a collection of primary keys, and each primary key corresponds to a record in the dimension table. It exists objectively, and the data to be used is determined according to the theme.

Dimension table: a dimension is the amount of data to be analyzed, and a dimension table is a table created from an appropriate angle. An angle to analyze the problem: time, region, terminal, user, etc.

Three models of dimension modeling

Star schema: the simplest and most commonly used one, centering on the fact table, and all dimension tables are directly connected to the fact table.

Please click to enter a picture description.

Snowflake mode: the dimension table in Snowflake mode can have other dimension tables, which is not easy to maintain.

Please click to enter a picture description.

Constellation model: based on multiple fact tables, and * * * enjoys dimension information, that is, some dimension tables can be * * * shared among fact tables.