Ragged Hierarchy with Bridge table

Can anyone give sample / suggestion for designing ragged hierarchy with bridge table (link with fact table) for the below requirement,

1. Organization hierarchy is dynamic. (User can change hierarchy at any time, like they can add new hierarchy, remove hierarchy at any level, can change the order of hierarchy)
Ex: Initial hierarchy: Region -- Country – Company – division--department

It can be changed as follows,

1. Region -- Country-- division-- Company--department (changing the order)
2. Region -- Country-- division --department (removing the level)
3. Region -- Country-- Company--division--department (adding the new level and there is no limitation for adding new level)

Thanks,
Baskaran.

Just flatten out all the hierarchical attributes into department dimension and leave the hierarchy configuration to the cube/query. If a new level comes in, just extend the dimension by adding more attribute.

To use a bridge, the hierarchical entities must be in the same dimension table. I usually include an 'organizational unit' dimension which contains codes and descriptions for divisions, business units, departments, cost centers any anything else the company organization may include. You also need something from the source that desribes the hierarchy... a recursive parent/child structure is best. You then explode the recursive structure creating a bridge table with every possible parent/child relationships between all the hierarchy members. Users can then query from any point in the hierarchy, looking up or down, using simple queries (no recursion). The queries don't need to know what the hierarchy looks like... the bridge contains all that information, so you can change the hierarchy without side-effects on existing queries. Those queries would simply reflect the new hierarchy.

To flatten a dynamic hirearchy is problematic. For flattening to work, the levels must be identifiable... the 'division' column always references a division. Having columns named 'level 1', 'level 2' and so forth is basically useless as the meaning of 'level 2' may change over time or may not represent any patricular organizational level at all. When possible I include an attribute in the organization unit table that identifies how the business designates the level of the unit... 'division', 'department', etc. So, if I need to create a flattened structure, the code and description values gets placed in the correct columns in the flattened hierarchy table.

Thanks for your response.

As per my understanding (from your response) is, I have to use parent/child relationships.if my understanding is correct ,I have the following issue for parent child relationship,

The problem here is ,There is no static parent child relationship in this model ..

like Region is parent of country now,but user can change it any time like county can be parent of region or country can have division as parent and This kind of relationship changes can be done for all level in this hierarchy.So all relation here is dynamic.



Please advise,how to handle this scenario using parent child relationship structure and how to maintan history of hierarch changes

if you provide any example that would be great helpful..

Thanks,
Baskaran

The beauty of flattened dimension lies in its simplicity that allows using data to drive the hierarchies instead of having hierarchies fixed by the schema. It also simplifies the SCD process when the type 2 change is involved. Dimensional modeling is highly extendable because adding attributes to dimension can facilitate new hierarchies without changing schema structure.

Change is normal.

If you go with a bridge, it is simple to accomodate change, as well as change history, by including an effective period on each row in the bridge.

To update the bridge, generate a new exploded hierarchy into a temp table. Do a full outer join of current rows in the bridge and new temp table. If there is a row in the bridge that is not in the new table, expire the bridge row. If there is a row in the new table that is not in the bridge, add it to the bridge. Where the two match, do nothing.