Addresses in Customer Dimension

View previous topic View next topic Go down

Addresses in Customer Dimension

Post  Glenn on Wed Sep 01, 2010 2:38 pm

As part of an Orders dimensional model, I am attempting to design the Customer Dimension table. The customers have multiple sold-to addresses and multiple ship-to addresses. While the OLTP allows any Order to have any combination of sold-to and ship-to addresses related to that customer (many to many) in practice that is not the case very often. Usually (but not always) when an item is sold to address A, it ships to address C.

The question is: Is it better to have a separate dimSoldToShipTo table (including a role column) and exclude that information from the dimCustomer, or to include the SoldTo and Ship To addresses (multiple combinations for each customer) in the dimCustomer table? The understanding is that either or both would be linked to the fact table via the appropriate surogate key.

The number of customers is usually less than 20,000. While the capacity exists for 9999 ship to and 9999 sold to address per customer, typically there are less than 10 sold-to and less than 10 ship-to addresses per customer.

Glenn

Posts : 4
Join date : 2010-07-19

View user profile

Back to top Go down

Re: Addresses in Customer Dimension

Post  ngalemmo on Wed Sep 01, 2010 4:27 pm

If, other than address, the customer information is common across addresses, I tend to have separate customer and address dimensions. The address dimension would contain one row per unique address.

Fact tables would contain FKs to both with appropriate roles as necessary. If, for dimensional reporting, you need to track addresses for a customer, I would have a factless fact table with customer key, address key, address type (degenerate dimension indicating role... ship to, bill to, etc.) and effective and expiration dates.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Addresses in Customer Dimension

Post  BoxesAndLines on Wed Sep 01, 2010 5:09 pm

Agree with ngalemmo. I'll also add that most businesses don't really report at a street level. They're usually at a higher level, zip + 4, zip, or city. I would investigate this need and add additional dimensions as needed.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Addresses in Customer Dimension

Post  Glenn on Thu Sep 02, 2010 5:20 pm

Thank you very much. I appreciate both of your responses. Now I can go down the right path.

Glenn

Posts : 4
Join date : 2010-07-19

View user profile

Back to top Go down

Re: Addresses in Customer Dimension

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