Star schema dimension table design [closed]

Question

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 

Answer 1

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.