join between Fact tables?

Category: sql server dw


maca128 on Tue, 18 Apr 2017 11:09:31

Hi experts,

I found this code written by someone else, that already left:


FROM FactOrder fo


INNER JOIN DimCurrency dc on fo.LocaleCurrencyId = dc.Currency_Id


INNER JOIN DimLocale dl on fo.LocaleId = dl.Locale_Id


INNER JOIN DimTime dt on fo.TimeId = dt.Time_Id


INNER JOIN SalesOrderItem si on si.SalesOrder_id = fo.intorderid


INNER JOIN FactRevenue fr on fo.intorderid = fr.orderid and fr.revenueSourceId = 1


INNER JOIN DimTime dtr on fr.timeid = dtr.time_id




It brings me a few doubts:


-Is it normal to join from one Fact table to another Fact table?

-Why would he join to DimTime twice? Does it make any sense? I know he is doing it through fo and then fr…

-Is there any design error or this is normal?



Hilary Cotter on Tue, 18 Apr 2017 11:23:30

1) It could be. It depends on what sort of question he is trying to answer.

2) With this code fragment it is hard to see. I suspect he is doing some filtering in the where clause which you are not displaying.

3) It depends on what questions the designer is trying to answer. We need to see more of the query.

Olaf Helper on Tue, 18 Apr 2017 11:29:20

1) Why not, e.g. if you want to get order Header and Details in one query.

2.) DimTime is onec joined to FactOrder and once to FactRevenue; I guess they have two different date values.

3.) On the first view without know Business requirement on the query I would say: Quite normal query.