如何连接1-N关系的事实表和维表 [英] How to connect a fact and dimension table that are in 1-N relationship

问题描述

我有一个包含一些度量和维度键的购买事实表.然后，还有另一个表:折扣表.Purchase FactTable 与 Discount Table 是 1-N 关系(对于每次购买，我可能会购买几件打折商品).折扣表有一些我想汇总的属性(描述、注释)和一些数值(例如:以 $ 为单位的折扣).

• 如果我从这个折扣表中创建一个维度，我会得到一个错误总和计数中的购买计数(夸大，每一行打折商品).
• 如果我从这个折扣中创建一个单独的事实表，我如何查询这两个事实表(目前它们不共享任何维度，所以我不能使用一致/共享维度进行钻取).
• 如果我创建一个退化维度，我们将回到第一个场景.

桥接表是否可以解决我的问题:BridgedKey、DiscountKey、$ 折扣、其他措施"..

附言这个 link 有助于选择桥接表，但我我不确定它是否适用于我的情况(因为我需要从折扣表中汇总一些数字属性).

谢谢，

解决方案

根据我的经验，有两个事实表 purchase 和 pruchase_detail 的设计会更受欢迎.

PURCHASE 每次购买有一行带有属性

 purchase_id -- 唯一 ID购买日期顾客ID...

PURCHASE_DETAIL 每次购买都有 1:N 行并存储定价详细信息.

 purchase_id -- 对应的购买account_type -- 描述销售价格和所有折扣类型的维度数量....

金额有适当的标志；销售价格为正，折扣为负.

示例

 purchase_id account_type 金额1 售价 1001折1 -51折2 -1

通过这种设计，您可以安全地COUNT 次购买(在 PURCHASE 表上)，总价(在 PURCHASE_DETAIL 上)并制作所有种类或详细的折扣报告.

I have a Purchase FactTable with some measures and dimension keys. Then, there's another another table: Discount Table. Purchase FactTable is in a 1-N relationship with Discount Table (for each purchase I might have bought several discounted items). Discount table has some attributes (description, note) and some numeric values (for example: discount in $) that I would like to roll-up.

• If I create a dimension out of this Discount Table, I'll get a wrong number of purchase counts in a sum count (inflated, one row for every discounted item).
• If I create a separate fact out of this Discount Table, how can I query both of these Fact tables (at the moment they don't share any dimensions, so I can't use a conformed/shared dimension for the drill-across).
• If I create a degenerate dimension, we're back to the first scenario.

Will a bridged table with: BridgedKey, DiscountKey, discount in $, other "measures" .. solve my issue?

p.s. this link helped to opt for a Bridged table but I'm not sure that it will work in my case (because I would need to roll-up some numeric attributes from Discount Table).

Thanks,

解决方案

My experience will favor a design with two fact tables purchase and pruchase_detail.

PURCHASE has one row per purchase with attributes

 purchase_id  -- unique ID 
 purchase_date  
 customer_id
 ...

PURCHASE_DETAIL has 1:N row per purchase and store the pricing details.

 purchase_id -- corresponding purchase
 account_type -- dimension describing sales price and all discount types
 amount
 ....

The amount has a proper sign; sales price positive, discounts negative.

Example

 purchase_id    account_type    amount
 1              sales price     100
 1              discount1       -5
 1              discount2       -1

With this design, you may safely COUNT purchases (on PURCHASE table), SUM total prices (on PURCHASE_DETAIL) and make all kind or detailed discount reports.

这篇关于如何连接1-N关系的事实表和维表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！