Data Modelling Customer Address

I need to model a table in my target which sources data from ERP for customer addresses.

The source tracks history which means each time there is a change, the old record is retained.

How do I model this in my target. Should this be a address dimension and what should be the surrogate key called.

Thanks

Hi - can I suggest you read up about Slowly Changing Dimensions? This should allow you to model your requirements

Regards,

Hi ,

since you are intrested in capturing the history of address ahcanges you should use put the address ina dimension table and use SCD 2

http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2

thanks

Himanshu

You have two ways to go with this.

One way is to include address information within the customer dimension. In which case the suggestions of using type 2 make sense.

The alternate way is to treat address as its own dimension, which I believe is what you were suggesting originally. Address as its own dimension is essentially a 'junk' dimension. The natural key to the address dimension is the address itself. You would then either snowflake it off the customer dimension or relate it to the customer via fact tables, or both. Such a dimension is always type 1 and an address cannot be updated… a different address is another row in the table. If you want history and do not want to make customer a type 2 dimension, create a factless fact table that tracks the customer/address relationship over time.

Hi,

This is my first post in the forum, though I have been following this space for couple of years and feeling much excited to be a part now.

On this thread, my take too is in making the address a part of customer dimension (Just treating it as an attribute).

Best Regards,
Dhar

Thanks for giving all the options. These are best practises and I would go for the Factless Fact Tables. Thanks again.