Dimensional Model Question

[rcoope]

Has anyone ever faced a problem where the data in a column of the source system table breaks down into multiple dimensions in the star schema?

I am working through the design of a data mart based on our Ordering System. The 'destination'column of each order can be a customer id, a route number, or a location number. The attributes of each of these are completely different, and I would not want to combine them into one dimension.

Would the fact table have separate columns for each each type of destination? If so, what happens if we add a new destination type?

I have also thought about snow flaking the destination dimension, putting the destination type, and the foreign key of each destination in the first dimension. Secondary dimension tables with destination specific attributes would be then be added for each type of destination.

I read an article on Kimball's website that talked about hot swapping dimensions, but I couldn't really understand how to implement it.

Any thoughts?

 

[seidub]

"Role tables" may help .... see one of Mr. Kimball's articles -

http://www.dbmsmag.com/9708d05.html

Good luck

 

[rcoope]

Thanks.
For any of you that face this same problem, I found an article on 'Unity' dimensions.

http://www.intelligententerprise.com/010613/warehouse1_1.shtml