SQL for getting tree from Bridge

[epekarik]

Hello,

I have the typical "Kimball" Org Dim and Org Hierachy Bridge table

----Dim-------------------
Parent_SK
Child_SK
Org_Id
Org_Name
--------------------------
-----Bridge---------------
Parent_SK
Child_SK
#_Of_Levels_From_Parent
Top_Flag
Bottom_Flag
--------------------------
There are many "tree"s in the Bridge table. One tree for each org dim row having "#_Of_Levels_From_Parent = 0" once in the table.

Does anyone have an SQL to generate the output of A tree's members if I can provide the Parent_SK where this Parent_SK is the top of the given tree?

greatly appreciated,
Endre

[John Simon]

Hi.
You can check out my blog post on Kimball Hierarchy tables that may provide some insights:
http://jsimonbi.wordpress.com/2011/01/15/kimball-hierarchy-bridge-table/

[epekarik]

John,

In your Bridge table,-- do you have the Product as a child multiple times? As I was taught, a product as a child in the bridge must be existent as many times this product exists in a tree or subtree. Every product is in the Bridge once as the TOP of the tree with 0 from top, and as many times the product can be a child in the tree, generating numerous individual trees.

Prod1
----Prod10
--------Prod100
--------Prod101
-----Prod11

Parent Child Top Bottom From_Top
-------------------------
Prod1 Prod1 Y N 0
Prod1 Prod10 N N 1
Prod1 Prod11 N N 1
Prod10 Prod100 N Y 2
Prod10 Prod101 N Y 2
-------------------------
Prod10 Prod10 N N 0
Prod10 Prod100 N Y 1
Prod10 Prod101 N Y 1
-------------------------
Prod10 Prod10 N Y 0
-------------------------
Prod11 Prod11 N Y 0

or

Parent Child Top Bottom From_Top
-------------------------
Prod1 Prod1 Y N 0
Prod1 Prod10 N N 1
Prod1 Prod11 N N 1
Prod10 Prod100 N Y 2
Prod10 Prod101 N Y 2

[ngalemmo]

The bridge is a fully exploded tree with every possible parent/child relationship. It usually includes a distance value, which tells you how far away (levels) the parent is from the child. If you had a hierarchy A->B->C, the bridge would look like:

Par Child Level Distance
A A 1 0
A B 2 1
A C 3 2
B B 2 0
B C 3 1
C C 3 0

This eliminates the need to use recursive SQL to report off the structure.

There are two ways to generate this. One is a classic recursive tree walk. The other is to start with all members then iterate up through parents. This can be done in a loop, rather than use recursion.

[Sponsored content]