DWH 事实表设计（计算度量）
我有一个名为“销售额”的度量，它是通过
- “未调整销售额
- 调整 X
- 调整 Y”
计算得出的。拥有包含三列（UnadjustedSalesValue、AdjX、AdjY）的事实表是一个不错的选择吗？
我认为另一种方法可能是为每个比率设置一个帐户维度或单独的事实表。
I have a measure called Sales which is calculated by
- Unadjusted Sales
- Adjustment X
- Adjustment Y
Is it a good choice to have a fact table with three columns (UnadjustedSalesValue, AdjX, AdjY)?
I think another way could be to have an Account dimension or separate fact tables for each ratio.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
假设这 3 个值具有相同的粒度级别，我会在同一个事实表上使用 4 个度量列：UnadjustedSales、AdjustmentX、AdjustmentY 和 AdjustedSales（或任何有意义的名称）。您是否以计算量度或离散值的形式实际实施调整销售额取决于您的数据和工具集。
原因是，假设您的“调整”是销售税、运输成本或客户折扣等内容，那么报表用户很可能对它们单独以及最终的 AdjustedSales 值感兴趣。如果他们还没有表达出兴趣，他们几乎肯定会表达兴趣，特别是在涉及客户折扣的情况下（即谁获得了多少折扣？）。
话虽如此，目前尚不清楚您指的是哪种调整，所以我不确定这一点对您来说有多重要。但作为一般规则，数据仓库的目标是在尽可能低的粒度级别上提供尽可能多的信息，所以我认为这个原则是合理的。
Assuming that the 3 values are at the same level of granularity, I would go for 4 measure columns on the same fact table: UnadjustedSales, AdjustmentX, AdjustmentY and AdjustedSales (or whatever names make sense). Whether or not you physically implement AdjustedSales as a calculated measure or as a discrete value depends on your data and toolset.
The reason is that assuming that your "adjustments" are things like sales tax, shipping costs or customer discounts, then report users are very likely to be interested in them individually as well as in the final AdjustedSales value. If they haven't already expressed an interest, they almost certainly will, especially if customer discounts are involved (i.e. who is getting how much discount?).
Having said that, it isn't clear what sort of adjustments you're referring to so I'm not sure how important this point is for you. But as a general rule, data warehouses aim to provide as much information at the lowest level of granularity possible, so I think the principle is sound.
除非维度有不同（即这些指标仅在国家级别可用，这些仅在州级别可用），否则我会将它们全部放入同一个事实表中。
这对于非雪花模式世界中的模式设计来说是正确的。仔细考虑为什么您可能想要创建一个 1:1 表，其中关系是强制性的（即一个表中始终有 1 行，而另一个表中恰好有一行）。通常，当存在特殊的技术原因时，例如想要将大列存储在不同的磁盘上，就会出现这些模式。
Unless there is something different about the dimensions (i.e. these metric are only available on a national level, these are only available on a state level) I'd put these all into the same fact table.
This would be true for schema design in a non-snowflake pattern world. Think carefully about why you might want to create a 1:1 table where the relationship is obligatory (i.e. there always is 1 row in the one table and exactly one row in the other). Usually these patterns show up when there is a peculiar technical reason, such as wanting to store a large column on a different disk.