如何连接一个事实,维表是在1-N关系 [英] How to connect a fact and dimension table that are in 1-N relationship
问题描述
我有一个购买FactTable一些措施和维度键。
然后,还有另一个另一个表:折扣表。
购买FactTable是符合折扣表(每次购买我可能会买一些打折的项目)1-N的关系。
折扣表中有一些属性(描述,注)和一些数值(例如:在$优惠)。我想卷起
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.
- 如果我创建一个维出这个折扣表,我会得到一个错误的
在一笔购买计数的计数序号(充气,一行每
打折项目)。 - 如果我创建一个单独的事实,这项优惠的
表,我该怎么查询这两个事实表(目前他们
不共享任何尺寸,所以我不能使用的钻跨越的顺应/共享尺寸)。 - 如果我创建一个退化的维度,我们又回到了第一个场景。
请问一个桥接表:BridgedKey,DiscountKey,折扣在$,其他的措施..解决我的问题。
Will a bridged table with: BridgedKey, DiscountKey, discount in $, other "measures" .. solve my issue?
P.S。
这链接帮助选择桥接表,但我不敢肯定它会在我的情况下工作(因为我需要卷起从折扣表某些数字的属性)。
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).
谢谢,
推荐答案
我的经验将有利于有两个事实表购买
和 pruchase_detail <设计/ code>。
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 有1:n用法元购机行和存储的定价细节
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.
示例
purchase_id account_type amount
1 sales price 100
1 discount1 -5
1 discount2 -1
通过这样的设计,可以安全地 COUNT购买的(购买上表),的 SUM价格总的(上PURCHASE_DETAIL)
并进行各种或详细的折扣报告。
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屋!