Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Conforming dimension feedback

Conforming dimension feedback

Conforming dimension feedback

I have several db's that need to be warehoused that have 'owner' as a conforming  dimension. In many cases a fact can have many owners so I was thinking of adding an ownership group dimension with a bridge table to owners.

The real problem I can't quite get my head around is the owners themselves. Owner can, and often do, own some or all of another owner. This is represented as a jagged hierarchy that changes over time e.g.

owner   child  share  start       end
A       C      .5     01/01/2001  31/12/2004
B       C      .5     01/01/2001  31/12/2004
A       C      .6     01/01/2004  31/12/2007
B       C      .4     01/01/2004  31/12/2007

In addition to this all other properties of an owner can change over time, eg brand name, status etc.

It seems to me the best thing to do is explode the owner hierarchy into another bridge table by date and link that to the correct form of the owner that covers the date in the bridge table.

Any thought would be welcome as I every time I think about this I get more certain I'm missing something fundamental.

RE: Conforming dimension feedback

The following is the bridge table which i can best think of, for your situation

Owner_Bridge_Key    Fact    Owner    Brand    Weightage    Active / Inactive Flag    Start Date    End Date
1            F1    O1    B1    2        1            1/1/2008    
1            F1    O2    B1    2        1            1/1/2008    
2            F1    O2    B2    1        1            1/1/2008    
3            F2    O1    B1    2        0            1/1/2008    5/1/2008
3            F2    O3    B1    2        0            1/1/2008    5/1/2008
4            F3    O1    B1    4        1            1/1/2008    
4            F3    O3    B1    4        1            1/1/2008    
4            F3    O4    B1    4        1            1/1/2008    
4            F3    O6    B1    4        1            1/1/2008    
5            F3    O5    B2    1        1            1/1/2008    
6            F2    O3    B1    1        1            5/2/2008    
7            F2    O4    B2    1        1            5/2/2008    

If there is any change in the relation of Fact & Owner or the status of brand you have to deactive the active / Inactive Flag to 0 and populate the end date.
While populating the fact table you have to consider only the active records. But while reporting you have to consider all active & Inactive records based on the key assigned in the fact table or you have to update the corresponding keys in the fact table based on the requirement and consider only the active records in the briage table.

And inorder to accomodate "Owner can, and often do, own some or all of another owner" you can do this in the above bridge table itself or as you send you can maitain anotehr table its based on the no of the records you have and the cardinality and frequency of the changes.     

Let me know your thoughts!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close