Seperate customer and address dimension 1

[mkabre74]

Hi,
I have a data modeling question. Iam designing the sales fact and its dimensions. A sales Order in my case will have the following

Bill to Customer
Bill_to Address
Ship to customer
Ship to address


The Bill to Customer/Address on the sales order could be different from Ship To Customer/Address. A customer can have many bill to and ship to addresses. The same address can be both bill_to and ship_to. Iam also thinking of having a bridge table for storing customer relationships as suggested in the book of Ralph Kimball. Potentially I can have multilevel parent child relationship for customers

Should I have the following dimensions

Customer (Customer_id primary key)
Address (with flags Bill_to, SHip_to) (address_id primary key)

Bridge table (parent_customer_Id, subsidiary_id, no_of_levels, bottom_most_flag, start and end dates)

I would have in the fact

Bill_to_customer_id (foreign key to Customer dimension)
Bill_to_address_id (foreign key to Address dimension)
Ship_to_customer_id (foreign key to Customer dimension)
Ship_to_address_id (foreign key to address dimension)


If I put the customer and address info in one dimension maintaing the bridge table would be difficult as for
address I plan to use SCD2

Thanks in advance
Manisha

 

[avjoshi]

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.

 

[kishleo]

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