Common data warehouse schema reference

Multidimensional data model is the most popular data model in data warehouse. The most typical data models of multidimensional data model are star model, snowflake model and fact constellation model. This paper shows the patterns and differences between them through examples.

Second, the star schema (star schema)

The core of star schema is a large central table (fact table) and a set of small subsidiary tables (dimension table). An example of star mode is as follows:

3. Snowflake mode.

Snowflake schema is an extension of star schema, in which some dimension tables are normalized and further decomposed into additional tables (dimension tables). The following figure shows an example of snowflake mode:

From the figure, we can see that the address table is further subdivided into city dimensions. The supplier_type table is further subdivided into supplier dimensions.

4. The fact constellation or galaxy model.

The data warehouse consists of multiple topics, including multiple fact tables, and the dimension tables are public and can be shared. This pattern can be regarded as a collection of star patterns, so it is called galaxy pattern or fact constellation pattern. The following figure shows an example of this pattern:

As shown in the above figure, the fact constellation mode contains two fact tables: sales and delivery, and * * * enjoyment dimension table.

Verb (abbreviation of verb) abstract

The fact constellation pattern is the longest used data pattern in data warehouse, especially in enterprise data warehouse (EDW). This is also a typical feature that distinguishes data warehouse from data mart. Fundamentally speaking, the data model of data warehouse is more to avoid redundancy and data reuse, and it is the most reasonable choice to design data warehouse by using ready-made models. Of course, under the big data technology system, the data model design of data warehouse is still a blind spot and is being explored.

Recently, in Design of Data Warehouse Model under Big Data Technology System, I reviewed the key technologies and data models of traditional data warehouses. Interested parties can discuss and learn together.