Seperate customer and address dimension 1

Manisha,

The first solution is always easier to implement and understand; but it has a serious drawback. You can’t query the Customer and the Address tables independently (i.e. without involving the Sales fact table). What that translates to is unless there is a fact entry you can’t really establish relationship between a customer and its billing or shipping address.

Bridge table, in that respect a more suitable approach. But is has its drawbacks too.
Bridges usually cause awkward query syntax. It is difficult to sell this concept to your user community. Even if you hide this table from your reporting front end by virtue of views; query performance usually takes hit.

I have had to implement a rather unorthodox solution in a similar situation. I’ll try my best to translate it into your case.

Dimensions:
Customer (Customer_id primary key)
Address (with flags Bill_to, Ship_to) (Customer_id , address_id primary key)
Notice here, the customer_id from customer dimension is part of composite primary key of address dimension.

Now this composite key is associated with Sales fact with referential integrity.
Bill_to_customer_id , Bill_to_address_id (foreign key to Address dimension)
Ship_to_customer_id, Ship_to_address_id (foreign key to address dimension)

I am sure there are a few DW puritans who won’t like this very much.
In fact if there is any better way to do it I also would like to know.

Hope this helps.

Anand.

is it possible to have same address for two customers?
if not i dont think you need two DIM tables, you can have one DIM Customer(CustomerID,AddressSeqID,...etc)

you can increment AddressSeqID for each customer according to his addressess, if he got 10 address you will have the 10 records, with different AddressSeqID.

i guess if using this method you can handle SCD2 very easily, just insert a new record with different AddressSeqID when address changes,

In DIM table the primary key will be a composit key (CustomerID,AddressSeqID), and you need to have both the columns in the Sales Fact
Now this composite key is associated with Sales fact with referential integrity




Kishore MCDBA