Stellar scheme [fact 1: n dimension] ... how?

Question

I am new to data warehouses and hope this is a simple question about creating an asterisk scheme:

If I have a fact table where a fact record naturally has a one-to-many relationship with one dimension, how can you model an asterisk scheme to support this? For instance:

• Fact Table: Point of Sale Record (Dimension - DollarAmount)
• Size Chart: Promotions (These promotions are valid when the sale was made)

The situation is that I want one Point of Sale entry to be associated with several different Promotions. These Shares cannot be their own measurements, as there are many many promotions.

How to do it?

Answer 1

In cases where you really have a “multi-valued” dimension, the Bridge table is usually the solution that Kimball recommends.

Your “Promotion” dimension is simply a record of each advertisement with its attributes (start date, end date, coupon code, POS promo code, ad name, etc.). Here, the relation from the promo to the product is not modeled, since it will be reflected in the fact table.

Promotion / Discount Size will look like (1 line for a unique planned share)

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

Your Trading Fact will look like this:

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

The bridge table "Promotion Group" will then be a set of combinations:

Promotion Group Dim ID
Promotion Dim ID

, , , , . , .

, , Bridge, , , , .

Answer 2

.

"" , .

, , .

, JOIN, Sale to Promotion .

", " "- , ? 365 . , . A Sale .

-, - . ?

, . , .

Answer 3

, . , , ( ).

. - , , . , $4.13, " № 3". "" .