Ragged Hierarchy with Bridge table

View previous topic View next topic Go down

Ragged Hierarchy with Bridge table

Post  baskaran.s on Sat Oct 22, 2011 11:25 pm

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.

baskaran.s

Posts : 5
Join date : 2011-10-20

View user profile

Back to top Go down

Re: Ragged Hierarchy with Bridge table

Post  hang on Sun Oct 23, 2011 4:33 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Ragged Hierarchy with Bridge table

Post  ngalemmo on Sun Oct 23, 2011 6:02 pm

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

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

View user profile http://aginity.com

Back to top Go down

Ragged Hierarchy with Bridge table

Post  baskaran.s on Wed Oct 26, 2011 8:44 am

Thanks for your response.



Last edited by baskaran.s on Mon Oct 31, 2011 12:01 pm; edited 2 times in total (Reason for editing : Updated)

baskaran.s

Posts : 5
Join date : 2011-10-20

View user profile

Back to top Go down

Ragged Hierarchy with Bridge table

Post  baskaran.s on Mon Oct 31, 2011 12:02 pm

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


baskaran.s

Posts : 5
Join date : 2011-10-20

View user profile

Back to top Go down

Re: Ragged Hierarchy with Bridge table

Post  hang on Tue Nov 01, 2011 1:01 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Ragged Hierarchy with Bridge table

Post  ngalemmo on Tue Nov 01, 2011 5:21 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Ragged Hierarchy with Bridge table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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