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

Customer & Multiple Addresses

3 posters

Go down

Customer & Multiple Addresses Empty Customer & Multiple Addresses

Post  neotechnical Tue Jul 13, 2010 9:13 am

I have to model below in star schema. A fact has 1 customer . But the customer can have multiple addresses at any point in time.
All the addresses can be active at the same time. However the fact can not have customer address as a field. Thus the relationship is customer has multiple addresses. How can i solve this in dmensional model?

neotechnical

Posts : 3
Join date : 2010-07-13

Back to top Go down

Customer & Multiple Addresses Empty Re: Customer & Multiple Addresses

Post  ngalemmo Tue Jul 13, 2010 1:51 pm

My guess is there are probably roles involved with the customer-address relationship as well...

The customer and address should exist as separate dimensions. Facts, such as orders, should have FK's pointing to the customer and applicable addresses (ship-to, bill-to, etc...).

For reporting customer and address information indepedant of any transactional activity, there should also be a factless fact table associating customer, the role if applicable (shipping, billing, payee, etc...), and the address over time.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Customer & Multiple Addresses Empty Re: Customer & Multiple Addresses

Post  neotechnical Wed Jul 14, 2010 5:01 am

Thanks for the reply. However the case is no role is assigned to address. The source system has customer and multiple addresses for a customer. So i cannot really put FKs in my fact table saying its a mailing address. The requirement is just to store multiple addresses of a customer.

neotechnical

Posts : 3
Join date : 2010-07-13

Back to top Go down

Customer & Multiple Addresses Empty Re: Customer & Multiple Addresses

Post  ngalemmo Wed Jul 14, 2010 3:23 pm

Existence of a role doesn't matter... I was assuming you were pulling from an ERP system which typically has separate customer and address tables and a role based associative entity between them.

Without a role you implement it the same way. You need a factless fact associating customers with addresses and effective dates.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Customer & Multiple Addresses Empty Re: Customer & Multiple Addresses

Post  BoxesAndLines Wed Jul 14, 2010 8:31 pm

How many addresses, 1,2, N?
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Customer & Multiple Addresses Empty Re: Customer & Multiple Addresses

Post  neotechnical Fri Jul 16, 2010 4:58 am

max 5 .. I guess factless fact table is the way to go.. I am planning below

Dimensions : Customer , Address
Fact : Cust_Addresses

neotechnical

Posts : 3
Join date : 2010-07-13

Back to top Go down

Customer & Multiple Addresses Empty Re: Customer & Multiple Addresses

Post  BoxesAndLines Fri Jul 16, 2010 9:23 am

If there is a hard maximum number you could model 5 relationships as well.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Customer & Multiple Addresses Empty Re: Customer & Multiple Addresses

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