Star schema [fact 1:n dimension]...how?

Question

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:

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

How do I do this?

Answer 1

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

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.

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

Your Sales Fact would look like:

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

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.

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.