patternsqlMinor
Is it good or bad practice to have a fact table relate to the same dimension table multiple times?
Viewed 0 times
samethefactpracticetablebadrelatedimensionmultiplegood
Problem
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.,
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.,
Thanks!
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.,
SubmittedDateKey, 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.,
SubmittedDateDimension, ClosedDateDimension?Thanks!
Solution
I'd use the same dimension table. Especially for a date dimension
This is a Role Playing Dimension also here
I would not let a client tool (or an Oracle forum post!) determine my SQL Server DW design.
This is a Role Playing Dimension also here
I would not let a client tool (or an Oracle forum post!) determine my SQL Server DW design.
Context
StackExchange Database Administrators Q#84385, answer score: 3
Revisions (0)
No revisions yet.