attributes depending on more than 1 dimension (2 dimensions)

suppose you have
- a discount code attribute
- a status attribute
which depend on the combination of the dimension customer & the dimension product.

You want to be able to relate these attributes to a fact table invoice details.

What are the possibilities to model this?
- 1) create a new dimension table, containing the customer SK, product SK and both attributes?
- 2) create a new dimension table, containing the customer SK, product SK and snowflake to 2 new dimensions (discount & status)
- 3) create a new reference table, such as in 2), but als relate the new discount & status dimensions directly to the fact?
- 4) create a new reference table, such as in 2), but als relate the new discount & status as one dimension to the fact?
....

Thanks for your help on this one!

... there must be surely somebody who has some ideas about this one?

C'mon guys ... don't let me down here.

Model the fact table and dimension tables based on how the data is to be used, not on what facilitates them being populated. Don't break the Star because it's easier to populate. I can see breaking the star to add functionality.

Create a new dimension with the new attributes. In the staging area, create the tables that you need to correctly populate the final fact tables.

Create a junk dimension of discount code and status. Add that to the fact. The dependency to customer and product is managed through the fact table.

@Jeff,

could you please elaborate, possibly with an example? - I do not exactly understand what you mean.

@BoxesandLines,

what about customer & product combinations which are not covered by the relation through the fact table? I can imagine that these definitions are also required in the data warehouse, no?

That is certainly a question you need to ask!

element wrote:
what about customer & product combinations which are not covered by the relation through the fact table? I can imagine that these definitions are also required in the data warehouse, no?

You need to forget about the customer/product dependancy... there is none. The particular discount code and status are contexts for the invoice line. You are not developing a process to derive discount and status, but rather collecting such information so it can be reported. Simply store the related values in a junk dimension.

It’s totally clear for me that you have to create the Status and Discount dimensions and relate them to your fact table.

But what invoice data can you get from the source system?
1. If Discount code and Status are coming from the OLTP system, it’s fine.
2. If invoice line contains just Customer and Product data then you have to store and use in the Staging area some map information how Customer and Product data define Status and Discount.