Customer dimension with multiple addresses

View previous topic View next topic Go down

Customer dimension with multiple addresses

Post  exhortae on Sat May 05, 2012 2:41 pm

Hi,

I'm trying to build a Customer dimension from a source system where a customer may have multiple addresses. In the sources system the addresses are persisted in a table following this data model

Col 1 :NATURAL_CUSTOMER_KEY
Col 2 :ADDRESSE_SEQUENCE_NUMBER
Col 3 : ADDRESSE

Code:

NATURAL_CUSTOMER_KEY          ADDRESS_SEQUENCE_NUMBER                ADDRESS
      00001                                1                            13 Road Snowf
      00001                                2                            45 Street Arvest
      00001                                3                            75 Bd Garybonst

I need to add that there is no way in the source system to link a fact to a specific address, and that the business users will never constrain a request using an address. They also don't care about history preserving concerning the customer addresses. They want to see all of his addresses (even if they are not valid anymore).

I wonder if the following design can solve my problem





Thanks

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

Post  ngalemmo on Sun May 06, 2012 3:44 am

What has the address have to do with the fact? As you stated, a: you can't link a fact to an address, & b: they don't care about history.

Why do they need address? What are they trying to do with it?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer dimension with multiple addresses

Post  exhortae on Sun May 06, 2012 7:41 am

Hi,

It's a third world loan company (micro finance) , they need the address in case the customer don't pay back the loan so they can go at his place and claim the money he owes.

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

Post  ngalemmo on Sun May 06, 2012 6:23 pm

Are you building a data warehouse or are you creating a system to track and collect on loans? If it is the latter, fine.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer dimension with multiple addresses

Post  exhortae on Mon May 07, 2012 10:40 am

Hi,

I'm building a Data Warehouse.

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

Post  ngalemmo on Mon May 07, 2012 1:14 pm

Why would a user use the DW to look up the address for one person? Wouldn't they use the loan system?

Yes, you could create a factless fact table that tracks address a person may have had. You can even have effective dates and such, but the purpose of a DW and its design is to provide retrospective analytics, not to service loans.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer dimension with multiple addresses

Post  Jeff Smith on Mon May 07, 2012 1:34 pm

Wouldn't a bridge table be able to handle this? Customer_Key and Address_Key?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

Post  ngalemmo on Mon May 07, 2012 3:02 pm

I don't think so. Bridges provide information relating to an event, such as the diagnosis associated with a hospital admission. The list of addresses does not seem to relate to anything other than the customer.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer dimension with multiple addresses

Post  BoxesAndLines on Tue May 08, 2012 8:50 am

If it's not needed for reporting I would go the normalized route. A customer can have many addresses. An address can be used by many customers. Three tables, Customer, Address, and Customer Address, done!
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

Post  exhortae on Wed May 09, 2012 3:03 pm

ngalemmo wrote:but the purpose of a DW and its design is to provide retrospective analytics, not to service loans.


I hear you, but that company also want to do operational work with the data warehouse to build. They also consider the address as an attribute of the customer dimension

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

Post  exhortae on Wed May 09, 2012 3:04 pm

BoxesAndLines wrote:If it's not needed for reporting I would go the normalized route. A customer can have many addresses. An address can be used by many customers. Three tables, Customer, Address, and Customer Address, done!


One customer per address, I also think that the normalized route is the simplest choice .

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

Post  hang on Wed May 09, 2012 6:13 pm

exhortae wrote:One customer per address, I also think that the normalized route is the simplest choice .
In your case, you don't have other choice but to put them in a association/bridge table since address is a multivalued attribute to customer.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Customer dimension with multiple addresses

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