Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

[Solved] Ragged Hierarchy, Bridge Table and SCD2

2 posters

Go down

[Solved] Ragged Hierarchy, Bridge Table and SCD2 Empty [Solved] Ragged Hierarchy, Bridge Table and SCD2

Post  exhortae 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 :


[Solved] Ragged Hierarchy, Bridge Table and SCD2 Captur10


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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  exhortae 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

Back to top Go down

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

Post  ngalemmo Wed Mar 13, 2013 11:00 am

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

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

http://aginity.com

Back to top Go down

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

Post  exhortae 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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