Shulou(Shulou.com)06/02 Report--

This article mainly explains "the concept of dimension table and fact table in data warehouse". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn the concept of dimension table and fact table in the data warehouse.

one

Fact table

1 fact table characteristics

A fact table (Fact Table) is a table that stores factual records, such as system logs, sales records, etc.; the record of a fact table is constantly growing dynamically, so it is usually much larger than other tables.

As the core of data warehouse modeling, fact table needs to be designed according to the business process, and contains the referenced dimensions and metrics related to the business process.

2 can be added, half can be added, no facts can be added

As a fact of measuring business processes, it is generally integer or floating-point decimal values, which have three types: additivity, semi-additivity and non-additivity.

Can be added:

The most flexible and useful facts are fully additive, and the additivity measure can be summarized according to any dimension associated with the fact table. Such as the total amount of consumption

Semi-additive:

Semi-additive measures can summarize some dimensions, but not all dimensions. Differences are common semi-additive facts, and they can operate across all dimensions except the time dimension. (for example, the daily balance is meaningless.)

Cannot be added:

Some measures are completely non-additive, such as ratio. For non-additive facts, a good way is to decompose them into additive components to achieve aggregation.

two

Dimension table

Basic concepts of 1-dimensional table

Dimension table (Dimension Table) or dimension table, sometimes called Lookup Table, is a kind of table corresponding to fact table; it stores the attribute value of dimension and can be associated with fact table; it is equivalent to extracting and standardizing the attributes that often appear repeatedly on fact table and managing them with one table. Common dimension tables are: date table (storing the attributes of week, month, quarter, etc.), location table (including country, province / state, city, etc.), and so on. Dimension is the foundation and soul of dimensional modeling.

There are many benefits to using dimension tables, as follows:

Reduces the size of the fact table.

It is convenient to manage and maintain the dimension, add, delete and modify the attributes of the dimension, and there is no need to change a large number of records of the fact table.

Dimension tables can be reused for multiple fact tables to reduce duplication of work.

2 drill down

Drilling down is the most basic method for business users to analyze data. Drilldown only needs to add a header pointer to the query. The header pointer of the new row is a dimension attribute with an group by expression in sql language. The attribute can come from any dimension associated with the fact table used by the query. Drilldown does not require a pre-existing hierarchical definition or drilldown path.

3 degenerate dimension

Sometimes, the dimension has nothing other than the primary key, for example, when an invoice contains multiple data items, the data item inherits all the descriptive dimensional foreign keys of the invoice, and the invoice has no other items except the foreign key. however, the number of invoices is still the legal dimension key at this data item level. This degenerate dimension is put into the fact table, which clearly indicates that there is no associated dimension table, and the degraded dimension is common in the transaction and cumulative snapshot fact tables.

three

The relationship between fact table and dimension table

At this point, I believe you have a deeper understanding of "the concept of dimension table and fact table in data warehouse". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.