I have been working in a Data Warehouse environment for a couple years. From my experience dimensions should not be joined together without a fact table. My understanding is that unless the dimensions are at the same granule level the results could be skewed. Also, joining a bunch of dimension tables together is not best practice and defeats the purpose of a star design. However, in my current environment our developers have decided to join these dimensions together anyway within a materialized view. Is there a downside to this design when the dimensions grow in size? Also, if anyone can point me in the direction of some good resources about this dilemma.
I have researched various Kimball texts and websites. I have gone back through notes from Dimensional Modeling classes but have not found any information on what happens when the star design is abandoned and materialized views are used in place of it. If this is the incorrect area for this question please let me know if there is a better forum.
Example query,
SELECT <COLUMNS>...
FROM D_LANGUAGE_1 DL1
INNER JOIN D_APPLICATION_INFORMATION AI ON(DL1.<JOIN KEYS> = AI.<JOIN KEY>)
INNER JOIN D_DATE D ON(D.DATE_KEY = TRUNC(AI.SUBMISSION_DATE))
LEFT OUTER JOIN D_LANGUAGE_BRG DLB ON(DL1.<JOIN KEY> = DLB.<JOIN_KEY>)
LEFT OUTER JOIN L_LANGUAGE_CAT LC ON (LC.<JOIN_KEY> = DL1.<JOIN_KEY>)
WHERE AI.CURRENT_ROW_IND = 'Y';