data warehouse - How model a dimension with composite key?

Question

I have a fact named sales which have FKs to dimensions product and store. Each of these dimensions have information about that dimension alone, but I have some information about a product in a specific store like where a product is in that store.

I am tempted to model a dimension where the primary key is a combination of product and store, it is ok to do that or some better alternative exists?

Answer 1

my thoughts...

1. Having a 3rd dimension for location is definitely a viable option. You could also include store details within this Dim (but still have the location as its level of granularity) and have a Location > Store hierarchy
2. You won't find references to a dimension having a PK with multiple columns because that would break one of the fundamental design principles of dimensional modelling
3. I'm confused/surprised by your statement that your source system is generating surrogate keys? Given that surrogate keys (in this context) are entirely an artefact within a data warehouse, it seems unlikely that a source system would be generating them