Modelling Hierarchies with dynamic levels

General
Enthusiast

Modelling Hierarchies with dynamic levels

Hi,

Would like to invite suggestions from experts on modeling hierarchies with dynamic levels.

I have read through a lot of articles which discuss about LEFT RIGHT numbering approach , mapping approach

and denormalized table structures for modeling this kind of data. Please share your experiences regarding the way you implement such kind of hierarchires.

Thanks

Manik

4 REPLIES
Enthusiast

Re: Modelling Hierarchies with dynamic levels

One technique I've used involves something called a "bridge" table. Essentially, we load a dimension table with all of the parent or child objects in the hierarchy. Then, we build this bridge table that includes a FK to the parent, an FK to the child, a field that indicates how many levels below the root node that this node is, and then two flags that indicate if it is a root (no other parents) or a leaf (no other children).

I probably didn't explain this too clearly, but it seems to work pretty well.
Enthusiast

Re: Modelling Hierarchies with dynamic levels

Thanks for the inputs..I have a fair amount of idea about this approach and its explained in Kimballs tool kit as well. I just wanted to know if you faced any challenges while loading the Bridge table..Like loading the Levels to the root and all. You use ETL tools to load that or plain SQL queries.

Enthusiast

Re: Modelling Hierarchies with dynamic levels

We load it using BTEQ and recursive SQL. Our hierarchies aren't that large so it wasn't a performance issue.
Enthusiast

Re: Modelling Hierarchies with dynamic levels

Thanks a lot  :)