Address Dimension /multiple addresses

[Cartesianjoin]

We have a sales fact table and have a question with the customer and address dimension.

A customer can have more than one address for shipping and billing (think large company with many locations).

Each sale need to be tracked to the main parent company and to the specific location or division for shipping and billing in the sale fact table.

We need to have the ability to simply choose the parent name and return all the sales made to them including each divison without the user knowing about the deeper division details.

I think we need to create many customers one for parent/billing and shipping and a parent hierachy inside the customer dimension and a seperate address in the address table for each address.

Only a small amount of our sales approx 25% go to these large companies.


In the end I would have a sales fact table with a customer and address dimension.

Inside the customer dimension would be a column named parent and a column named division. I would not do a snowflake.

The sales fact will look as follows

Sales Fact Table
----------------
SalesCustId
ShippingCustId
ShippingAddressId
BillingAddressId

other columns......


Thanks

 

[BIonHand]

If you are sure that you only want to record top level company and the division that you are shipping to then the structure you are suggesting should work.

You won't be able to track changes to the company structure over time, eg if one company is bought by another, and you also won't be able to show any sort of structure, ie where one division is a parent of another within the same company.



John

http://www.BIonHand.com

Online Business Intelligence