Overview of SQL Server database dimension tables and fact tables

Source: Internet
Author: User
Tags numeric

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.