Customer dimension with multiple addresses

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

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?

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.

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

Hi,

I'm building a Data Warehouse.

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.

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

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.

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!

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

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