database 事实表和维度表的区别？

Difference between Fact table and Dimension table?

提问by Premraj

When reading a book for business objects, I came across the term- fact table and dimension table.

在阅读一本关于业务对象的书时，我遇到了术语事实表和维度表。

I am trying to understand what is the different between Dimension table and Fact table?

我想了解维度表和事实表之间有什么不同？

I read couple of articles on the internet but I was not able to understand clearly..

我在互联网上阅读了几篇文章，但我无法清楚地理解..

Any simple example will help me to understand better?

任何简单的例子都会帮助我更好地理解？

采纳答案by NoChance

This is to answer the part:

这是回答部分：

I was trying to understand whether dimension tables can be fact table as well or not?

我试图了解维度表是否也可以是事实表？

The short answer (INMO) is No.That is because the 2 types of tables are created for different reasons. However, from a database design perspective, a dimension table could have a parent table as the case with the fact table which always has a dimension table (or more) as a parent. Also, fact tables may be aggregated, whereas Dimension tables are not aggregated. Another reason is that fact tables are not supposed to be updated in place whereas Dimension tables could be updated in place in some cases.

简短的回答 (INMO) 是否定的。那是因为 2 种类型的表是出于不同的原因创建的。但是，从数据库设计的角度来看，维度表可以有一个父表，事实表总是有一个（或更多）维度表作为父表。此外，事实表可能会被聚合，而维度表不会被聚合。另一个原因是事实表不应该就地更新，而维度表在某些情况下可以就地更新。

More details:

更多细节：

Fact and dimension tables appear in a what is commonly known as a Star Schema. A primary purpose of star schema is to simplify a complex normalized set of tables and consolidate data (possibly from different systems) into one database structure that can be queried in a very efficient way.

事实表和维度表以通常称为星型模式的形式出现。星型模式的主要目的是简化一组复杂的规范化表并将数据（可能来自不同系统）合并到一个可以以非常有效的方式进行查询的数据库结构中。

On its simplest form, it contains a fact table (Example: StoreSales) and a one or more dimension tables. Each Dimension entry has 0,1 or more fact tables associated with it (Example of dimension tables: Geography, Item, Supplier, Customer, Time, etc.). It would be valid also for the dimension to have a parent, in which case the model is of type "Snow Flake". However, designers attempt to avoid this kind of design since it causes more joins that slow performance. In the example of StoreSales, The Geography dimension could be composed of the columns (GeoID, ContenentName, CountryName, StateProvName, CityName, StartDate, EndDate)

在最简单的形式中，它包含一个事实表（例如：StoreSales）和一个或多个维度表。每个维度条目都有 0,1 个或更多与之关联的事实表（维度表示例：地理、项目、供应商、客户、时间等）。维度具有父级也是有效的，在这种情况下，模型的类型为“雪花”。但是，设计人员试图避免这种设计，因为它会导致更多连接降低性能。在 StoreSales 的示例中，Geography 维度可以由列（GeoID、ContenentName、CountryName、StateProvName、CityName、StartDate、EndDate）组成

In a Snow Flakes model, you could have 2 normalized tables for Geo information, namely: Content Table, Country Table.

在雪花模型中，您可以有 2 个用于地理信息的标准化表，即：内容表、国家/地区表。

You can find plenty of examples on Star Schema. Also, check this out to see an alternative view on the star schema model Inmon vs. Kimball. Kimbal has a good forum you may also want to check out here: Kimball Forum.

您可以在 Star Schema 上找到大量示例。此外，请查看此内容以查看有关星型模式模型Inmon 与 Kimball的替代视图。Kimbal 有一个很好的论坛，您可能还想在这里查看：Kimball 论坛。

Edit: To answer comment about examples for 4NF:

编辑：回答关于 4NF 示例的评论：

• Example for a fact table violating 4NF:

• 违反 4NF 的事实表示例：

Sales Fact (ID, BranchID, SalesPersonID, ItemID, Amount, TimeID)

销售资料（ID、BranchID、SalesPersonID、ItemID、Amount、TimeID）

• Example for a fact table not violating 4NF:

• 不违反 4NF 的事实表示例：

AggregatedSales (BranchID, TotalAmount)

AggregatedSales（BranchID，TotalAmount）

Here the relation is in 4NF

这里的关系是 4NF

The last example is rather uncommon.

最后一个例子比较少见。

回答by Premraj

In Data Warehouse Modeling, a star schemaand a snowflake schemaconsists of Factand Dimensiontables.

在数据仓库建模中，星型模式和雪花模式由事实表和维度表组成。

Fact Table:

事实表：

• It contains all the primary keys of the dimension and associated facts or measures(is a property on which calculations can be made) like quantity sold, amount sold and average sales.

• 它包含维度的所有主键和相关的事实或度量（是可以进行计算的属性），如销售量、销售量和平均销售额。

Dimension Tables:

尺寸表：

• Dimension tables provides descriptive information for all the measurements recorded in fact table.
• Dimensions are relatively very small as comparison of fact table.
• Commonly used dimensions are people, products, place and time.

• 维度表为事实表中记录的所有度量提供描述性信息。
• 作为事实表的比较，维度相对非常小。
• 常用的维度是人、产品、地点和时间。

image source

图片来源

回答by AeyJey

This appears to be a very simple answer on how to differentiate between fact and dimension tables!

这似乎是关于如何区分事实表和维度表的一个非常简单的答案！

It may help to think of dimensions as things or objects. A thing such as a product can exist without ever being involved in a business event. A dimension is your noun. It is something that can exist independent of a business event, such as a sale. Products, employees, equipment, are all things that exist. A dimension either does something, or has something done to it.

Employees sell, customers buy. Employees and customers are examples of dimensions, they do.

Products are sold, they are also dimensions as they have something done to them.

Facts, are the verb. An entry in a fact table marks a discrete event that happens to something from the dimension table. A product sale would be recorded in a fact table. The event of the sale would be noted by what product was sold, which employee sold it, and which customer bought it. Product, Employee, and Customer are all dimensions that describe the event, the sale.

In addition fact tables also typically have some kind of quantitative data. The quantity sold, the price per item, total price, and so on.

将维度视为事物或对象可能会有所帮助。诸如产品之类的东西可以在不参与商业活动的情况下存在。维度是您的名词。它可以独立于业务事件而存在，例如销售。产品、员工、设备，都是存在的东西。一个维度要么做某事，要么做某事。

员工卖，顾客买。员工和客户是维度的例子，他们确实如此。

产品被出售，它们也是维度，因为它们对它们做了一些事情。

事实，是动词。事实表中的条目标记发生在维度表中的离散事件。产品销售将记录在事实表中。销售事件将通过销售的产品、销售的员工以及购买的客户来记录。产品、员工和客户都是描述事件、销售的维度。

此外，事实表通常还包含某种定量数据。销售数量、每件商品的价格、总价等。

Source: http://arcanecode.com/2007/07/23/dimensions-versus-facts-in-data-warehousing/

资料来源：http: //arcanecode.com/2007/07/23/dimensions-versus-facts-in-data-warehousing/

回答by aa8y

I found this answer easier to understand from the perspective of a person who does not know much of the DB/DW terminology.

从一个不太了解 DB/DW 术语的人的角度来看，我发现这个答案更容易理解。

http://databases.about.com/od/datamining/a/Facts-Vs-Dimensions.htm

http://databases.about.com/od/datamining/a/Facts-Vs-Dimensions.htm

I'll recommend going through this first and then going through Emmad Kareem's answer for more granularity. Hope it's helpful.

我会建议先通过这个，然后再通过 Emmad Kareem 的答案以获得更多的粒度。希望它有帮助。

回答by RelativitySQL

Super simple explanation:

超级简单的解释：

Fact table: a data table that maps lookup IDs together. Is usually one of the main tables central to your application.

事实表：将查找 ID 映射到一起的数据表。通常是应用程序中心的主要表之一。

Dimension table: a lookup table used to store values (such as city names or states) that are repeated frequently in the fact table.

维度表：用于存储事实表中频繁重复的值（例如城市名称或州）的查找表。

回答by user5729371

In the simplest form, I think a dimension table is something like a 'Master' table - that keeps a list of all 'items', so to say.

在最简单的形式中，我认为维度表类似于“主”表 - 可以这么说，它保留了所有“项目”的列表。

A fact table is a transaction table which describes all the transactions. In addition, aggregated (grouped) data like total sales by sales person, total sales by branch - such kinds of tables also might exist as independent fact tables.

事实表是描述所有事务的事务表。此外，汇总（分组）数据，如销售人员的总销售额、分公司的总销售额——此类表也可能作为独立的事实表存在。

回答by Shriraj

Dimension tableDimension table is a table which contain attributes of measurements stored in fact tables. This table consists of hierarchies, categories and logic that can be used to traverse in nodes.

维度表维度表是一个包含存储在事实表中的度量属性的表。该表由可用于在节点中遍历的层次结构、类别和逻辑组成。

Fact tablecontains the measurement of business processes, and it contains foreign keys for the dimension tables.

事实表包含业务流程的度量，它包含维度表的外键。

Example – If the business process is manufacturing of bricks

示例 – 如果业务流程是制造砖块

Average number of bricks produced by one person/machine – measure of the business process

一个人/一台机器生产的平均砖块数——业务流程的衡量标准

回答by guest

1. The fact table mainly consists of business facts and foreign keys that refer to primary keys in the dimension tables. A dimension table consists mainly of descriptive attributes that are textual fields.
2. A dimension table contains a surrogate key, natural key, and a set of attributes. On the contrary, a fact table contains a foreign key, measurements, and degenerated dimensions.
3. Dimension tables provide descriptive or contextual information for the measurement of a fact table. On the other hand, fact tables provide the measurements of an enterprise.
4. When comparing the size of the two tables, a fact table is bigger than a dimensional table. In a comparison table, more dimensions are presented than the fact tables. In a fact table, less numbers of facts are observed.
5. The dimension table has to be loaded first. While loading the fact tables, one should have to look at the dimension table. This is because the fact table has measures, facts, and foreign keys that are the primary keys in the dimension table.

1. 事实表主要由业务事实和引用维度表中主键的外键组成。维度表主要由作为文本字段的描述性属性组成。
2. 维度表包含代理键、自然键和一组属性。相反，事实表包含外键、度量和退化维度。
3. 维度表为事实表的度量提供描述性或上下文信息。另一方面，事实表提供了企业的度量。
4. 比较两个表的大小时，事实表比维度表大。在比较表中，显示的维度比事实表多。在事实表中，观察到的事实数量较少。
5. 必须首先加载维度表。在加载事实表时，必须查看维度表。这是因为事实表具有作为维度表中主键的度量、事实和外键。

Read more: Dimension Table and Fact Table | Difference Between | Dimension Table vs Fact Table http://www.differencebetween.net/technology/hardware-technology/dimension-table-and-fact-table/#ixzz3SBp8kPzo

阅读更多：维度表和事实表 | 之间的区别 | 维度表与事实表http://www.differencebetween.net/technology/hardware-technology/dimension-table-and-fact-table/#ixzz3SBp8kPzo

回答by Maheshwar Reddy

Dimension table : It is nothing but we can maintains information about the characterized date called as Dimension table.

维度表：它只是我们可以维护有关称为维度表的特征日期的信息。

Example : Time Dimension , Product Dimension.

示例：时间维度、产品维度。

Fact Table : It is nothing but we can maintains information about the metrics or precalculation data.

事实表：它只是我们可以维护有关指标或预计算数据的信息。

Example : Sales Fact, Order Fact.

示例：销售事实、订单事实。

Star schema : one fact table link with dimension table form as a Start Schema.

星型模式：一个以维度表形式作为起始模式的事实表链接。

enter image description here

在此处输入图片说明