I have a simple dimension, for example a date dimension, which is static and unchanging over time. In the fact table, I have several dates.
Is it different from a performance perspective to join to one date dimension twice vs. having a second copy of that date dimension and joining to it for the second date and so on?
If I have a fact table that looks like this:
FactId FactNumber FactDate FactOtherDate 1 10 1/1/2016 12/31/2015
...and I want to produce a query like this:
Select Sum(FactNumber), DateDim.Month, OtherMonth = DateDim2.Month From Fact Inner Join DateDim On DateDim.Date = FactDate Inner Join DateDim DateDim2 ON DateDim2.Date = FactOtherDate Group By DateDim.Month, DateDim2.Month
...would it be better to make a second actual table and join to that, rather than joining to the same table twice?
I ask because indexed views won't allow such a query as a view, and that made me think that SQL Server handles it differently and possibly less efficiently. I'm not looking to use an indexed view, just wondering if that restriction provides any insight into this question.