Customer dimension with multiple addresses

[exhortae]

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

[ngalemmo]

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?

[exhortae]

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.

[ngalemmo]

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

[exhortae]

Hi,

I'm building a Data Warehouse.

[ngalemmo]

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.

[Jeff Smith]

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

[ngalemmo]

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.

[BoxesAndLines]

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!

[exhortae]

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

[exhortae]

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 .

[hang]

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.

[Sponsored content]