Conforming dimension feedback

[JamesCarters]

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.

 

[rajkonig]

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!!!