SQL for getting tree from Bridge
3 posters
Page 1 of 1
SQL for getting tree from Bridge
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
epekarik- Posts : 8
Join date : 2012-07-27
Age : 61
Location : Cincinnati, OH
Re: SQL for getting tree from Bridge
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/
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/
Re: SQL for getting tree from Bridge
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
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)
epekarik- Posts : 8
Join date : 2012-07-27
Age : 61
Location : Cincinnati, OH
Re: SQL for getting tree from Bridge
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.
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.
![-](https://2img.net/i/empty.gif)
» Dimension for property tree
» One-to-many as Bridge
» difference between factless fact and bridge table
» Bridge Table
» The use of a bridge table
» One-to-many as Bridge
» difference between factless fact and bridge table
» Bridge Table
» The use of a bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|