Overview of SQL Server database dimension tables and fact tables

Overview of SQL Server database dimension tables and fact tables:

Fact table

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

The resulting data, the fact table usually contains a large number of rows. The main feature of the fact table is that it contains numeric data (facts), and these digital information can be aggregated to provide the relevant units as historical data, each fact table contains a multiple-part index that contains the primary key of the correlation latitude table as a foreign key, and the dimension table contains the attributes of the fact record. The fact table should not contain descriptive information, nor should it contain any data other than the numeric metric field and the related indexed field that corresponds to the corresponding item in the Latitude table.

There are two measures contained in the fact table: one is a measure that can be cumulative, and the other is a non cumulative measure. The most useful measure is the cumulative measure, and the cumulative number is significant. Users can get summary information through cumulative measures, such as. You can summarize the sales of a specific set of stores in a specific time period. Non-cumulative measures can also be used for fact tables, where single summary results are generally meaningless, for example, when temperature is measured at different locations in a building, it makes no sense to add a temperature to all the different positions in the building, but averaging is meaningful.

In general, a fact table is associated with one or more latitude tables, and users can use one or more dimension tables when they use the fact table to create cubes.

Dimension table

A dimension table can be viewed as a window for the user to analyze data. The latitude table contains the attributes of fact records in fact tables, some provide descriptive information, and some specify how to summarize fact table data to provide useful information to the analyst, and the dimension table contains a hierarchy of attributes that help summarize the data. For example, a dimension table that contains product information typically contains hierarchies that divide the product into categories such as food, beverages, and non-food items, each of which is further subdivided until the product reaches the lowest level.

In a dimension table, each table contains fact attributes that are independent of other dimension tables, for example, the Customer dimension table contains data about the customer. Column fields in a dimension table can divide information into hierarchical hierarchies.

Conclusion:

1, the fact table is the content you want to pay attention to;

2. The dimension table is the angle you observe the transaction, from which angle you observe the content.

For example, sales of goods in a region are viewed from a regional perspective. The fact table is the sales table, and the dimension table is the regional table.