1

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 |
|----------|       |----------|
2

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/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.