Dimension Question

Suppose that I have a dim_person table and that dim_person lives in a city which belongs to a state.

Should the dim_person table have all of the attributes of city and state in a data warehouse? For example, a column for state and city in the dim_user table. Would this still be true if city and state have a large number of attributes (50+)?

There are normally multiple addresses associated with a Person (or Party). Most folks will try to manage the party address via a factless fact table. I also include a geography dimension separate from the address information for a party. It is in the geography dimension where I would maintain a hierarchy and not in the person dimension.

davVisitor wrote:Suppose that I have a dim_person table and that dim_person lives in a city which belongs to a state.

Should the dim_person table have all of the attributes of city and state in a data warehouse? For example, a column for state and city in the dim_user table. Would this still be true if city and state have a large number of attributes (50+)?

Snowflaking is not an evil. Just create a region dimension (with the city-state-region hierarchy) and reference it from within the dim_person.