SQL Server database dimension table and fact table Overview

Fact table

Each data warehouse contains one or more fact data tables. Fact data tables may contain business sales data, such as cash registration transactions.

The generated data, fact data tables usually contain a large number of rows. Fact data tables are mainly characterized by digital data (FACTS) that can be summarized to provide relevant units as historical data, each fact table contains an index composed of multiple parts. This index contains the primary key of the foreign key's correlated latitude table, and the dimension table contains the fact record feature. Fact data tables should not contain descriptive information, nor contain any data except the numerical measurement field and the index field corresponding to the fact and latitude table.

There are two "metric values" contained in a fact data table: one is a cumulative measurement value, and the other is a non-cumulative measurement value. The most useful measurement value is the accumulative measurement value, and the accumulated number is very meaningful. You can obtain the summary information through the cumulative measurement value, for example. You can summarize the sales of specific products in a group of stores in a specific period of time. Non-cumulative measurements can also be used in fact data tables. A single summary result is meaningless. For example, when measuring the temperature at different locations of a building, it makes no sense to accumulate the temperature of all different locations in the building, but it makes sense to calculate the average value.

Generally, a fact data table must be associated with one or more latitude tables. When you use a fact data table to create a multi-dimensional data set, you can use one or more dimension tables.

Dimension Table

A dimension table can be seen as a window for users to analyze data. A latitude table contains the fact record feature of a fact data table. Some features provide descriptive information and some features specify how to summarize the fact data table data, to provide useful information for analysts, a dimension table contains a hierarchical structure that helps Summarize data. For example, a dimension table containing product information usually contains a hierarchy of products into several categories, such as food, beverage, and non-consumer products. each category of these products is further subdivided multiple times, until each product reaches the lowest level.

In a dimension table, each table contains fact features independent from other dimension tables. For example, a customer dimension table contains data about customers. The column fields in a dimension table can be divided into different levels of structure.

Conclusion:

1. fact tables are what you want to pay attention;

2. The dimension table is the perspective from which you observe the transaction.

For example, the sales volume of commodities in a region is determined from the regional perspective. The fact table is the sales table, and the dimension table is the region table.