I. Basic concepts

Dimension modeling is an analysis oriented model, focusing on how to solve the user to complete the analysis requirements quickly, and also has a good response function of large-scale complex query. In order to improve query performance, data redundancy can be appropriately increased. It is a de-normalized design technology contrary to the three normal form (3NF).

Ii. Fact sheet

  • Transaction fact table
  • Periodic snapshot fact table
  • Cumulative snapshot fact table

Dimension table

  • The degradation of dimension
  • Slowly varying dimension

3.1. Slow change dimension is usually handled in several ways

  • Directly overwrite the original value
  • Add attribute column
  • Refer to the design of zipper table, add at least three columns of expiration date, row identifier (status, flag)

3.2. Dimensional modeling can be divided into data organization

  • Star model
  • Snowflake model
  • The constellation model

Granularity of four.

Used to determine what a row in a fact table represents, the smallest unit of activity of the business, or the degree of business detail in different combinations of dimensions.

Modeling tools

  • Excel Mapping

    The most common ones are maintaining data models, relationships, and metadata management through Excel

Add all attributes of the target table and all attributes of the source table, such as field name, table name, field processing logic, table filtering conditions, table association mode, full mode, partition description, subject scope, change record and so on

  • PowerDesigner

A commonly used modeling tool, there are many modeling tools