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

Customer with Multiple Address types

+2
MSR1987
rhaces
6 posters

Go down

Customer with Multiple Address types Empty Customer with Multiple Address types

Post  rhaces Fri Jun 01, 2012 7:22 pm

Hi, in my model every customer can have several address types addressses (Billing address, a Shipping address, a Correspondence addres, etc) but can only have 1 address in each address type. Example:

John Doe | Billing | 101 Road, New York City, NY
John Doe | Shipping | 102 Road, New York City, NY
John Doe | Correspondence | 103 Road, New York City, NY
Jane Doe | Billing | 505 Road, New York City, NY
Other Doe| Billing | 777 Road, New York City, NY
Other Doe| Home | 633 Road, New York City, NY

I think the best way to model this would be having 2 different dimensions (customers and addresses) and have a sort of bridge table with customer_id | address_id | valid_from | valid_to.

Any other suggestions on how to model this example?

Thanks

rhaces

Posts : 2
Join date : 2012-06-01

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  MSR1987 Tue Jun 05, 2012 12:39 pm

Hello,

I think you dont need a bridge table here. There can be one Customer Dimension and Customer Address Dimension(SCD2).

This should be enough...

Thank you!!

MSR1987

Posts : 2
Join date : 2012-06-05

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  ngalemmo Tue Jun 05, 2012 2:45 pm

It is not unusual to have an address dimension that is independent of a customer dimension. After all, an address is what it is, and if you are dealing with a large number of customers, you can same considerable amount of space storing an address once.

To deal with address roles, they are often handled as multiple dimension references in fact tables. After all, the role an address plays is tied to the event for which the address was provided. Any address role associated with a customer is usually a preference rather than how it is actually applied in a transaction.

It also helps if the full address comes in as part of the transaction source data.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  rf001 Sat Jun 16, 2012 9:53 pm

I read the posts but I am still confused.

I am facing a similar situation:
Employees have 3 types of addresses i.e. Contact Address, Permanent Address and Vacations Address. What is the best way to handle it?

1. Fact table references the Employees dimension and the Address dimension also references the Employee dimension.

2. There is a many to many relationship between the fact table and Address dimension. Bridge Table should should be used.

3. Any other way?

regards,
rf001
rf001

Posts : 23
Join date : 2010-12-16

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  Jeff Smith Sat Jun 16, 2012 10:09 pm

Customer Fact Table
CustomerDimKey
CustBillingAddressKey
CustShippingAddressKey
CustCorrespondenceAddressKey
CustHomeAddressKey

Or
If you want to keep an unlimited number of addresses for a customer:
Customer Fact Table
CustomerDimKey
CustAddressKey
AddressTypeDimKey

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  rhaces Sun Jun 17, 2012 9:54 am

rf001 wrote:I am facing a similar situation:
Employees have 3 types of addresses i.e. Contact Address, Permanent Address and Vacations Address. What is the best way to handle it?

After several discussion with other BI Architects and reading what was explained here, we decided that the best way on doing this would be what @ngalemmo said: "To deal with address roles, they are often handled as multiple dimension references in fact table". This basically means that you have to build your Employee dim without address and then have an Address dim, then in the fact do somethiung similar to what @Jeff Smith said: This is how your fact table might end up looking:

EmployeeKey -> FK to EmployeeKey in EmployeeDim Table
ContactAddressKey -> FK to AddressKey in AddressDim Table
PermanentAddressKey -> FK to AddressKey in AddressDim Table
VacationsAddress -> FK to AddressKey in AddressDim Table

Thanks all for your help

rhaces

Posts : 2
Join date : 2012-06-01

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  rf001 Sun Jun 17, 2012 11:38 am

@rhaces, I have the following question..

If a single row in the fact table has the following columns:
EmployeeKey| ContactAddressKey| PermanentAddressKey| VacationsAddress.
100 | 5 | ?? | ??
100 | ??| 2| ??
100 | ??|??| 4

What should be in the places of ??...? should it be nulls?

rf001
rf001

Posts : 23
Join date : 2010-12-16

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  ngalemmo Sun Jun 17, 2012 6:33 pm

Fact table should always have proper FK relationships. No nulls, no outer joins to dimensions. Create a row in the address dimension for unknown addresses (address line 1 = 'Unknown' or something like that) and have fks reference that row when the address is not known.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  Jeff Smith Sun Jun 17, 2012 8:56 pm

Instead of having 3 rows, you would have 1 row with:

100 | 5 | 2 | 4 |

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Customer with Multiple Address types Empty Customer adress tracking

Post  Oleole Mon Jun 18, 2012 10:13 am

Hi,

I use this opportunity to spin a little furter on the topic Customer and adress(es). Say you have your customer dim and adress dim and wanted to track changing relationsship between them (the customer moving and calls in to say he's moving).
Is it so that in these situations we should use factless fact tables to deal with this ?

What if we only had a customer dim including adress(es) (with effective dates and no adress dimension, could that ba considered a fact table as well ?

Regards
'little bit confused'

Oleole

Posts : 12
Join date : 2012-02-15

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  Jeff Smith Mon Jun 18, 2012 11:06 am

Fact tables and dimension tables are different and not interchangeable.

If you had Customer and address in the same dimension, you would either have to have columns for each type of address or have a row for each combination of address and customer. If your Customer and address dimension had 1 row for every combination of customer and address, how would you know which row to put on a call?

If you have a customer and address dimension with, say, 4 sets of address, your dimension table is going to be huge. And let's say you want to aggregate calls by the state where the customer lives, you would have to join your calls fact table with you Customer dimension - 2 of your largest tables.

I have an address dimension and a seperate geography dimension with Zip Code at it's lowest level. This Zip Code dimension lets me by pass joins to the customer table or even an address table to aggregate facts to the Zip code, city, state, county etc.

A lot of it depends on how the data is going to be used. If data gets rolled up to the state, then it makes sense to avoid having to go through a huge dimension table to get to state. But if data never or rarely gets aggregated to zip code or city, then making that join fast has no practical purpose.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Customer with Multiple Address types Empty Is it OK with Customer and adress in same dimension ?

Post  Oleole Tue Jun 19, 2012 3:49 am

My questions really boils down to:
Is it OK to have Customer and adress in same dimension with one row for each combination ? Tracking all history.
(I know it depends on......many factors)
What do we loose doing so ?

Ole

Oleole

Posts : 12
Join date : 2012-02-15

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  ngalemmo Tue Jun 19, 2012 3:59 am

Either way is acceptable. As you said, it depends.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  Jeff Smith Tue Jun 19, 2012 10:14 am

You lose performance. Your Customer table will probably be your largest dimension table. Adding in multiple addresses for each member will make it grow significantly. It will slow the load and slow queries. And it adds to the complexity of your largest dimension table.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  ngalemmo Wed Jun 20, 2012 7:00 pm

Jeff Smith wrote:You lose performance. Your Customer table will probably be your largest dimension table. Adding in multiple addresses for each member will make it grow significantly. It will slow the load and slow queries. And it adds to the complexity of your largest dimension table.

Generally I would agree but, it does depend. If the industry is manufacturing, there may not be large number of customers, so the performance difference doesn't matter. Retail, on the other hand, usually separates customer and address.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

Post  Jeff Smith Wed Jun 20, 2012 7:36 pm

"it does depend'

The design always depends. It depends on how the data is used. It depends on the industry. It depends on the number of rows. It depends on the database software. Something used 1,000 times a day might get designed differently than something used once a month. Something used by the CEO once a month might get designed differently than if it were used once a month by the guy fresh out of college. The capabilities of the reporting software are taken into account. They are all factors.

So when a person asks how they should design something, well it depends.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Customer with Multiple Address types Empty Re: Customer with Multiple Address types

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