2

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

For example:

customer_dim

customer_id
first_name
last_name
email
primary_billing_address_dim 
  • 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
2

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.