SQL for getting tree from Bridge

View previous topic View next topic Go down

SQL for getting tree from Bridge

Post  epekarik on Wed Aug 29, 2012 1:34 pm


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
avatar
epekarik

Posts : 8
Join date : 2012-07-27
Age : 53
Location : Cincinnati, OH

View user profile

Back to top Go down

Re: SQL for getting tree from Bridge

Post  John Simon on Wed Aug 29, 2012 7:15 pm

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/


John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: SQL for getting tree from Bridge

Post  epekarik on Thu Sep 06, 2012 1:32 pm

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


Last edited by epekarik on Thu Sep 06, 2012 2:15 pm; edited 3 times in total (Reason for editing : font issue)
avatar
epekarik

Posts : 8
Join date : 2012-07-27
Age : 53
Location : Cincinnati, OH

View user profile

Back to top Go down

Re: SQL for getting tree from Bridge

Post  ngalemmo on Thu Sep 06, 2012 6:46 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: SQL for getting tree from Bridge

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