Should I use separate address dimensions?

Posted on

Question :

I have a Star Schema model with two Dimensions (User and Store) each of them have addresses, which I store.

Each User and Store can just have one Address. Is it recommended to split the Dimension into a Address Dimension (with a 1:1 relationship)? Are there any technical arguments to handle like this? Or is it a common way to keep the address in the user/store dimension?

Answer :

You should add the address to the user/store dimension as the address is a property of the dimension member, not the fact record.

Adding the address to the dimension allows you to define attribute relationships between country -> city -> user which should improve performance when you create hierarchies using those attributes.

It will usually also allow for easier calculated measures.

As a last point, having the address as attributes on the dimension will allow for easier handling of slowly changing dimensions if a user/store moves to a new address.

This would be an opinion, but I agree with this answer which is along the lines of the question you are asking. I’m copying the answer from the link here in case the link goes cold.

It sounds like in the situation you have that you should just include
columns for the type of address and the source of the address in the
address dimension itself, so it stands alone and you don’t have to go
via a fact to know what kind of thing it is. You wouldn’t need a
separate table with keys as you mentioned- the data can safely be
denormalised in the dimension.

As an aside:

Although many people do have an address table which is separate, the
approach from the Kimball Group would not be to have have ‘address’ or
location dimension as a multi purpose dimension that stands alone- it
provides part of what describes something else (like a company, or a
customer, or even a ‘delivery location’). Instead you’d have the
dimension (e.g Customer) and Within that dimension you’d have a number
of Address fields, named appropriately (CustomerAddress1,
CustomerAddress2, CustomerCity). You may choose to administer the
address centrally for convenience behind the scenes, with the other
dimensions formed by means of views or further ETL, but in the
presentation of the star schema the address table would not be seen
separately. The addresses are still conformed in that they’re called
the same thing and mean the same thing.

However plenty of people go with a separate Address table as you’ve

Leave a Reply

Your email address will not be published. Required fields are marked *