database 数据库——事实表和维度表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9362854/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database - fact table and dimension table
提问by Hyman_of_All_Trades
When reading a book for business objects, I came across the term- fact table and dimension table. Is this the standard thing for all the database that they all have fact table and dimension table or is it just for business object design? I am looking for an explanation which differentiates between two and how they are related.
在阅读一本关于业务对象的书时，我遇到了术语事实表和维度表。这是所有数据库都具有事实表和维度表的标准还是仅用于业务对象设计？我正在寻找一种解释来区分两者以及它们之间的关系。
Edited:
编辑：
Why cannot a query just get the required data from the fact table? What happens if all the information are stored in one fact table alone? What advantages we get by creating a separate fact and dimension table and joining it?
为什么查询不能从事实表中获取所需的数据？如果所有信息单独存储在一个事实表中会发生什么？创建单独的事实和维度表并加入它有什么好处？
Sorry for too many questions at a time but I would like to know about the inter-relations and whys.
抱歉一次问太多问题，但我想知道相互之间的关系和原因。
采纳答案by dani herrera
Dimensionand Factare key terms in OLAPdatabase design.
- Fact table contains data that can be aggregate.
- Measures are aggregated data expressions (e. Sum of costs, Count of calls, ...)
- Dimension contains data that is use to generate groups and filters.
- Fact table without dimension data is useless. A sample: "the sum of orders is 1M" is not information but "the sum of orders from 2005 to 2009" it is.
- 事实表包含可以聚合的数据。
- 度量是聚合数据表达式（例如成本总和、调用次数等）
- 维度包含用于生成组和过滤器的数据。
- 没有维度数据的事实表是没有用的。示例：“订单总和为1M”不是信息，而是“2005年至2009年的订单总和”。
They are a lot of BI tools that work with these concepts (e.g. Microsft SSAS, Tableau Software) and languages (e. MDX).
它们是许多使用这些概念（例如Microsft SSAS、Tableau Software）和语言（例如MDX）的 BI 工具。
Some times is not easy to know if a data is a measure or a dimension. For example, we are analyzing revenue, both scenarios are possibles:
有时不容易知道数据是度量还是维度。例如，我们正在分析revenue，两种情况都是可能的：
- 3 measures:
net profit,overheads,interest - 1 measure:
profitand 1 dimension:profit type(with 3 elements: net, overhead, interest )
- 3项措施：
net profit，overheads，interest - 1 个度量：
profit和1 个维度：（profit type具有 3 个元素：净、开销、利息）
The BI analyst is who determines what is the best design for each solution.
BI 分析师负责确定每个解决方案的最佳设计。
EDITEDdue to the question also being edited:
由于问题也在编辑中，因此已编辑：
An OLAP solution usually has a semantic layer. This layer provides to the OLAP tool information about: which elements are fact data, which elements are dimension data and the table relationships. Unlike OLTP systems, it is not required that an OLAP database is properly normalized. For this reason, you can take dimension data from several tables including fact tables. A dimension that takes data from a fact table is named Fact Dimensionor Degenerate dimension.
OLAP 解决方案通常具有语义层。该层向 OLAP 工具提供以下信息：哪些元素是事实数据，哪些元素是维度数据以及表关系。与 OLTP 系统不同，OLAP 数据库不需要正确规范化。因此，您可以从包括事实表在内的多个表中获取维度数据。从事实表中获取数据的维度被命名为事实维度或退化维度。
They are a lot of concepts that you should keep in mind when designing OLAP databases: "STAR Schema", "SNOWFLAKE Schema", "Surrogate keys", "parent-child hierarchies", ...
在设计 OLAP 数据库时，您应该记住很多概念：“STAR Schema”、“SNOWFLAKE Schema”、“代理键”、“父子层次结构”……
回答by stevie_c
That's a standard in a datawarehouse to have fact tables and dimension tables. A fact table contains the data that you are measuring, for instance what you are summing. A dimension table is a table containing data that you don't want to constantly repeat in the fact table, for example, product data, statuses, customers etc. They are related by keys: in a star schema, each row in the fact table contains a the key of a row in the dimension table.
这是数据仓库中拥有事实表和维度表的标准。事实表包含您正在测量的数据，例如您正在汇总的数据。维度表是包含不想在事实表中不断重复的数据的表，例如产品数据、状态、客户等。它们通过键关联：在星型模式中，事实表中的每一行包含维度表中某行的键。
