How to model many to many relationships in data warehouse dimmensions

Question

I'm starting to learn about data warehouses and have run across a scenario I can't wrap my head around. I was attempting to sketch out a data warehouse that would track monthly sales for different stores. If the source OLTP database for stores has a many-to-many relationship between store's attributes and store, how would I represent the store dimension in the data warehouse?

Dimension's Source OLTP Database:

Data Warehouse ERD - How would the many-to-many attribute relationhip be represented:

Answer 1

If your database uses a star-schema build around the fact table, it could be tempting as a first guess to bring the attributes into the fact table:

• This would make sense if the attributes would be unique in relation to the elementary metrics, i.e. if each unit sold could be unambiguously assigned to one value of that attribute. This would make sense if there would be one attribute for each store, or for each product, since the summation of metrics in the fact table would stay consistent.
• This does not make sense if each unit sold could be assigned to multiple values of the same attribute at the same time. In fact the attributes here remain derived from the store and there is no way to bring this in the facts without either losing some relationship or duplicating units in the metrics. So you'd end-up keeping them related to the store dimension. As a consequence, you cannot break down the metrics according to these attributes, you could only filter according to these attributes.

The latter approach does not allow to really handle the attributes in the same way as dimensions. A slightly different approach could be to go for a dimension attribute_combination:

• You would then create a unique id for each unique combination of attributes that you encounter in a store. In this case, you could derive from the store dimension this attribute_combination and use it as a dimension.