Role-playing dimensions

Hi,

We have this debate in our group about representing a single dimension that appears several times in the same fact table. For example, our factSale table may have ShipDateKey, OrderDateKey, and ReturnDateKey.

(Solution A)
One side in our group are advocating in creating a separate date dimension for each role (either by a physical table or through a view). Hence,there will be 3 additional date dimension which may be called dimShipDate, dimOrderDate and dimReturnDate with the corresponding "ShipDateKey", OrderDateKey, and "ReturnDateKey" as their PK respectively. These are exact replications of the date dimension. This idea was actually directly lifted from Laura Reeves manual (Dimensional Modeling: Intermediate and Advance Topics c.2010 Starsoft Solutions pages 170-173).

(Solution B)
My solution was to use just one Date dimension table and make each FK's in the factTable takes the role as it's name. Our dev tem are not too keen on creating dimension tables whose keys doesn't match the names of their corresponding FK's in the fact table.
My question to everyone is what was your solution in this type of a problem. I'm sure most here had to one way or another had to create a relations more than once from a fact to a dimension (first one guilty would be the date dimension). Which one of the above did you implement?

You would never create additional tables, period.

Solution B is the one most commonly used (usually a prefix to the normal key name). You can consider creating views if you do not have a reasonable BI layer that would resolve the confusion for end users (which is probably the context for Laura's suggestion). If you have an end-user friendly BI layer (full metadata layer), and your dev team is made up of even marginally compentent technical people, don't bother with the views.

I've never even heard of solution A as an option. You are much smarter than the rest of your group. :-)

BoxesAndLines wrote:I've never even heard of solution A as an option. You are much smarter than the rest of your group. :-)

The top of this page displays "Kimball Forum", and here is a quote from Mr. Kimball (chapter 5):
"Even though we cannot literally join to a single date dimension table, we can build and administer a single date dimension table behind the scenes. We create the illusion of two independent date tables by using views. We are careful to uniquely label the columns in each of the SQL views. For example, order month should be uniquely labeled to distinguish it from requested ship month. If we don't practice good data housekeeping, we could find ourselves in the uncomfortable position of not being able to tell the columns apart when both are dragged into a report."

I think Dr. Kimball and I are in complete agreement. :-)