[Solved] Ragged Hierarchy, Bridge Table and SCD2

Hi,

I'm trying to follow the Kimball's design tips on modeling ragged hierarchies and I have come to the following model :





I have a salary fact table and an employee ragged hierarchy, I have two employee dimensions (only scd1) and two bridge employe_reports_to tables (in case someone want to descend or ascend the hierarchy).


Now I'm trying to deal with scd2 change in the ragged hierarchy, i have added the effective_date and expiration_date columns and I have dealt with it in the etl (ie expire lines that are not valid anymore and add new lines to the bridge table).

The problem I'm facing now, is how to link the new lines in the bridge table with new facts, while keeping the link between the old lines in the bridge table and the old facts ?

Thanks

You would choose the relationship that was in effect for the desired point in time. The desired point in time could be based on a time value obtained from the fact table.

ngalemmo wrote:You would choose the relationship that was in effect for the desired point in time. The desired point in time could be based on a time value obtained from the fact table.

Hi,

Basically The join would be something like

Code:


Bridge
    INNER JOIN Fact
        ON Bridge.ID_DM_Employe = Fact.ID_DM_Employe AND Fact.Some_Date BETWEEN Bridge.DT_Effective AND Bridge.DT_Expiration
.....


Wouldn't it be bad (performance wise) to have this kind of join if the bridge table gets big (and from what I understand it will get big overtime).


Thanks

Have you tried it? Bridges tend to perform very well.

ngalemmo wrote:Have you tried it? Bridges tend to perform very well.

No I haven't tried it (yet) on a real project. I'm kind of afraid of the row count explosion due to the scd2 handling in the bridge table.

Thank you for your time.