Data Modelling Customer Address

View previous topic View next topic Go down

Data Modelling Customer Address

Post  hunain on Mon Mar 03, 2014 11:34 am

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

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Data Modelling Customer Address

Post  nick_white on Tue Mar 04, 2014 10:35 am

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

Regards,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re : Data Modelling Customer Address

Post  hkandpal on Tue Mar 04, 2014 12:38 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Data Modelling Customer Address

Post  ngalemmo on Tue Mar 04, 2014 1:40 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Data Modelling Customer Address

Post  Dhar on Fri Mar 07, 2014 1:47 am

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


Last edited by Dhar on Fri Mar 07, 2014 3:16 am; edited 1 time in total (Reason for editing : Clarified the reasoning)

Dhar

Posts : 2
Join date : 2014-03-07
Location : Singapore

View user profile http://MuralidharKintada.com

Back to top Go down

Re: Data Modelling Customer Address

Post  hunain on Thu Mar 13, 2014 2:04 am

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

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Data Modelling Customer Address

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum