Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Data Modelling Customer Address

5 posters

Go down

Data Modelling Customer Address Empty Data Modelling Customer Address

Post  hunain 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

Back to top Go down

Data Modelling Customer Address Empty Re: Data Modelling Customer Address

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Data Modelling Customer Address Empty Re : Data Modelling Customer Address

Post  hkandpal 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

Back to top Go down

Data Modelling Customer Address Empty Re: Data Modelling Customer Address

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

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

http://aginity.com

Back to top Go down

Data Modelling Customer Address Empty Data Modelling Customer Address

Post  Dhar 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

http://MuralidharKintada.com

Back to top Go down

Data Modelling Customer Address Empty Re: Data Modelling Customer Address

Post  hunain 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

Back to top Go down

Data Modelling Customer Address Empty Re: Data Modelling Customer Address

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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