In a star schema, can a dimension table such as customer_dim have a column that references another dimension?

For example:



closed as unclear what you're asking by Tom V, Marco, Colin 't Hart, mustaccio, McNets Apr 25 '17 at 13:01

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    What is the context of the question? Is this an academic question about the difference between a pure star schema and a snowflake schema? A practical question of whether it makes sense to model the data this way as opposed to putting the address information into the customer dimension? Something else? – Justin Cave Apr 24 '17 at 23:11

When dimensions have references to other dimensions, we call that a Snow Flake instead of a Star schema. Depending on how you query your model later on, one approach might be more efficient than the other. It is possible to transform a Snow Flake in a Star Schema by flattening everything in the dimension linked to the fact table(s) – customer_dim in your example.

Some tools (like Microstrategy) behaves very well with a Snow Flake model. On the contrary Oracle Business Intelligence Enterprise Edition will require you to model your business model as a star schema.

For ad-hoc query performance, both approach should be benchmarked. A pure star schema reduces the number of joins so it might be faster. In term of data quality and master data management, the snow flake is probably easier to handle.

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