• You need to go back and think about your design. What you are trying to achieve from a Modelling point of view is correct but not the way you are trying to achieve it.

    In your Dimension you should have a surrogate key.

    When you join your fact table to your Dimension lookup the Surrogate key based upon the Employee and date that exist in the fact table that match against the Employee and date in the Dimension. Your Dimension correctly does not have overlapping dates as you are showing Type2 history. Therefore your fact record will along with the employee no., match only against one record. The surrogate key from the Dimension is then added to your Fact table. You now have a FK-PK relationship between the Fact table and Dimension. This should be done in the DW.

    Paul R Williams.