Hitachi Consulting BI Blog

DSV joins vs. Dimension Usage [Reed]

I had a client who added a new column to a table in the DSV and then coverted existing joins from a different column to the new column. (e.g., OldDateKey --> NewDateKey). Because the DSV was changed to reflect the new join, they assumed that everything would properly. But the cube kept using the old join. The problem is that the DSV only shows potential pathways. The "real" joins between the fact table and the dimension table take place in the Dimension Usage tab. Without changing the join column in the Dimension Usage, changing the DSV doesn't do anything. The reason has to do with role-playing dimensions. In the DSV you can have multiple join possibilities--the classic example is from fact table OrderDateKey, ShipDateKey, DeliveryDateKey columns to a single DateKey in the time dimension. Any of these joins is "possible", but for a specific cube dimension, you need the Dimension Usage to specify the actualized relationship. So, get the DSV right, sure. But don't forget that the Dimension Usage is where the real "joins" take place. - Reed