Conformed Fields or Snowflake? - Shop and Customer Location

Question

I have shop dimension and customer dimension which both has location information.

Should I create location dimension to correlate shop location and customer location? I believe this one is called snowflake schema. I heard that it is hard to maintain.

|----------|       |---------|       |----------|
| Dim_Cust | ----- | Dim_Loc | ----- | Dim_Shop |
|----------|       |---------|       |----------|

Or, should I maintain definition that shop and customer dimension is conformed by each location field? For this one, I denormalized location information to each dimension.

|----------|       |----------|
| Dim_Cust | ----- | Dim_Shop |
|----------|       |----------|

Answer 1

This is more of an outrigger dimension. You area allowed to do this with the Kimball methodology (star schema, not snowflake) but it does add complexity to your data model (happens with date dimensions often when you have a date in another dimension).

I would simply add location information to both the shop and customer dimensions. If you want to do complex querying around which shops are visited by which customers, you could create a denormalised customer/shops dimension which has every used combination of customer and shop. This will make it much easier to query - which is what a good data warehouse will provide. Hope this helps.

Here's some stuff on outrigger dimensions. http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/