星图[事实1:n维度] ...如何? [英] Star schema [fact 1:n dimension]...how?

问题描述

我是数据仓库的新手，我希望对构建星型架构有一个简单的问题:

I am a newcomer to data warehouses and have what I hope is an easy question about building a star schema:

如果我有一个事实表，其中一个事实记录自然具有一个单一维度的一对多关系，那么如何对星型模式进行建模以支持它呢?例如:

If I have a fact table where a fact record naturally has a one-to-many relationship with a single dimension, how can a star schema be modeled to support this? For example:

• 事实数据表:销售点条目( 测量单位为DollarAmount)
• 维度表:促销(这些 是在以下情况下有效的促销活动吗? 出售了)

• Fact Table: Point of Sale entry (the measurement is DollarAmount)
• Dimension Table: Promotions (these are sales promotions in effect when a sale was made)

这种情况是，我希望将一个销售点条目与多个不同的促销相关联.这些促销活动无法确定其范围，因为促销活动很多.

The situation is that I want a single Point Of Sale entry to be associated with multiple different Promotions. These Promotions cannot be their own dimensions as there are many many many promotions.

我该怎么做?

推荐答案

对于真正具有多值"维的情况，通常使用桥表是Kimball建议的解决方案.

For cases when you truly have a "multi-valued" dimension, a Bridge Table is usually the solution that Kimball recommends.

您的促销"维度只是每个促销的记录，并带有其属性(开始日期，结束日期，优惠券代码，POS促销代码，广告名称等).从促销到产品的关系不在此处建模，因为它将反映在事实表中.

Your "Promotion" dimension simply is a record of each promotion, with its attributes (start date, end date, coupon code, POS promo code, Ad Name, etc). The relationship from promo to product isn't modeled here, since it will be reflected in the fact table.

促销/折扣维度看起来像(每个唯一的计划促销有1行)

Promotion/Discount Dimension would look like (1 row per unique planned promotion)

Promotion Dim ID
Promo Code
Coupon Code
Promo Start DTTM
Promo End DTTM
... etc ...

您的销售情况如下:

Tran Date
Tran Line #
Customer Dim ID
Product Dim ID
Promotion Group Dim ID
Net Sale Price
Average Cost
Discount Amount

您的促销组"桥接表将成为组合的集合:

Your "Promotion Group" bridge table would then be the set of combinations:

Promotion Group Dim ID
Promotion Dim ID

如果发生有3个促销的销售，您只需创建与每个促销相关的组ID，然后将组ID放在事实表中.这与医疗报告系统处理多种诊断的方式非常相似.

If a sale occurs that has 3 promotions on it, you simply create group ID that relates to each promo, then put the group ID on the fact table. It's very similar to the way that medical reporting systems deal with multiple diagnoses.

请注意，通过使用Bridge表，您可以轻松地对销售进行重复计算，因此，我建议使用此方法的报告应由了解模型的人员来开发.

Note that by using a Bridge table, you can easily double count sales, so I advise that reports using this method be developed by folks that understand the model.

这篇关于星图[事实1:n维度] ...如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！