I'm new to DW/BI, and this seems like a pretty basic question, but I can't seem to find a good answer.
I have a fact table that would have more than one date associated with it, so I was thinking that I would have multiple date keys back to the same dimension table, e.g.,
ClosedDateKey. Obviously SQL Server can do this - my reservation in this is that when I designed a proof-of-concept schema this way, the client tools (i.e., Tableau, Excel), seemed to get confused about how to map the relationships. (I also found this forum post saying that Oracle's Analytic Workspace Manager explicitly forbids this, which of course implies nothing about my Microsoft world, but suggests that it's a general no-no.)
Is there a best practice in DW/BI about this? Instead of having one dimension table hooked up to each of these keys, should I have multiple identical dimension tables, e.g.,