如何对二维共有的位置进行建模？
我正在创建数据模型并需要指导。
我有 2 个维度：客户、产品
和 2 个事实表：销售、购买。
两个维度都有位置列。假设用户选择伦敦，那么它应该显示来自伦敦的所有客户和产品。因此，我认为创建一个名为“位置”的新维度并使其成为客户和产品的父级更有意义。然而，这使其成为雪花模式。
或者，我可以修改 ETL（将 LocationID 放入事实表中），以便使 Location 维度直接指向事实表，而不是将 Location 作为客户和产品的父级。这将使其成为星型模式。
选择后一个选项有负面影响吗？
I am creating a data model and need guidance.
I have 2 dimensions: customer, product
And 2 fact tables: sales, purchases.
Both the dimensions have Location column. Suppose user selects London then it should show all customer and products from London. So I think it makes more sense to create a new dimension called Location and make it the parent of customer and product. This however makes it a snowflake schema.
Alternatively rather than making the Location as parent of customer and product, I can amend the ETL (to have the LocationID into the fact tables) so as to make the Location dimension to directly point into the fact tables. This will make it star schema.
Is there any negative impact of choosing the later option?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
拥有一个直接链接到事实表的单独维度是实现此目的的“标准”方法
Having a separate dimension that links directly to your fact table is the “standard” way of implementing this
如果客户和产品都有一个位置，那么您就有一个“角色扮演维度"。您可能希望按 Customer.Location 进行过滤，或者您可能希望按 Product.Location 进行过滤。在 Power BI 中，您需要两份维度副本，一份与产品相关，一份与位置相关 (参见视频）。
这也会创建一个雪花模式，这不是问题。如果您更喜欢星型模式，那么您可以将位置数据复制到每个维度中。
您不会将位置放在事实表上，因为这样您就无法区分客户的位置和产品的位置。
If both Customer and Product have a location, then you have a "role playing dimension". You might want to filter by Customer.Location, or you might want to filter by Product.Location. In Power BI you need two copies of your dimension here, one related to Product and one related to Location (see video).
This would also create a snowflake schema, which is not a problem. If you'd prefer a star schema then you would copy the location data into each dimension.
You wouldn't put location on the fact table because then you couldn't distinguish between the Customer's location and the Product's location.