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

Addresses in Customer Dimension

3 posters

Go down

Addresses in Customer Dimension Empty Addresses in Customer Dimension

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

Back to top Go down

Addresses in Customer Dimension Empty Re: Addresses in Customer Dimension

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

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

http://aginity.com

Back to top Go down

Addresses in Customer Dimension Empty Re: Addresses in Customer Dimension

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

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

Back to top Go down

Addresses in Customer Dimension Empty Re: Addresses in Customer Dimension

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

Back to top Go down

Addresses in Customer Dimension Empty Re: Addresses in Customer Dimension

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