JamesCarters
Programmer
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.
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.