[Solved] Ragged Hierarchy, Bridge Table and SCD2

View previous topic View next topic Go down

[Solved] Ragged Hierarchy, Bridge Table and SCD2

Post  exhortae on Tue Mar 12, 2013 6:17 am

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


Last edited by exhortae on Fri Mar 15, 2013 4:26 am; edited 1 time in total

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: [Solved] Ragged Hierarchy, Bridge Table and SCD2

Post  ngalemmo on Tue Mar 12, 2013 8:28 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: [Solved] Ragged Hierarchy, Bridge Table and SCD2

Post  exhortae on Wed Mar 13, 2013 4:37 am

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

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: [Solved] Ragged Hierarchy, Bridge Table and SCD2

Post  ngalemmo on Wed Mar 13, 2013 11:00 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: [Solved] Ragged Hierarchy, Bridge Table and SCD2

Post  exhortae on Fri Mar 15, 2013 4:26 am

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.

exhortae

Posts : 30
Join date : 2010-08-01

View user profile

Back to top Go down

Re: [Solved] Ragged Hierarchy, Bridge Table and SCD2

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum