Search code examples
architecturedata-warehouseolap

How to connect a fact and dimension table that are in 1-N relationship


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,


Solution

  • 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.