Populating the Bridge table (Tsql)

I am trying to create a bridge table between my fact table and my company table

OrderFact
CustomerKey (FK)
DateKey (FK)
CompanyKey(FK)
ItemKey (FK)
ItemQty

CompanyBridge
ParentCompanyKey (FK)
SubsidiaryCompanyKey (FK)
DepthFromParent
LowestFlag
TopmostFlag

CompanyDimension
CompanyID (PK)
CompanyCode
CompanyName

This is trying to mimic the strcture outlined in the Kimball Group Reader P 357.

The bridge table should contain one record for each seperate path from each node in the company tree to itself and every node below it.

I am struggling with the Tsql statement to generate this - especially the DepthFromParent field.

Any help would be greatly appreciated.

Check out my blog:

http://jsimonbi.wordpress.com/2011/01/15/kimball-hierarchy-bridge-table/

Here is another way to do it, using a loop. This particular code was written for Netezza, but should be easy enough to translate to other dialects.

The loop appends successive generations until there are no more. The code calculates level and distance from parent. You can always add other things if you need it.



CREATE OR REPLACE PROCEDURE NG_EXPLODE_TREE()
RETURNS INTEGER
EXECUTE AS CALLER
LANGUAGE NZPLSQL AS
BEGIN_PROC
declare dist integer;
todo integer;
BEGIN
/*
This sample procedure demonstrates how to create an exploded hierarchy brige from a recursive
tree structure without using recursion.

This example using three tables.

NGBASE contains the source recursive tree
NGTREE is the work table used by the process
NGEXPLODE is the resulting exploded bridge


Table structures are as follows:
NGBASE represents any source table. It would contain a child and parent key.
create table ngtree (child integer, parent integer, distance integer);
create table ngexplode (child integer, parent integer, level integer, distance integer);

Data types for child and parent are purely for this example. In practice they would reflect the data
types in the source.

*/

delete from ngtree; -- clean things out
delete from ngexplode;

/*
Load the working table with the parent child releationships from the source table. Initialize level to 1.
*/

insert into ngtree
select child, parent, 1 from ngbase;

/*
Starting with level 1 rows, insert new rows based on the parent's parent. Load as new level.
Increment level count and repeat until no more rows are inserted.
*/

dist := 1;
loop
INSERT INTO NGTREE
SELECT
A.CHILD,
B.PARENT,
dist+1
FROM NGTREE A, NGTREE B
WHERE A.DISTANCE = dist
AND A.PARENT = B.CHILD
AND B.DISTANCE = 1; -- get immediate parent

/*
Save the row_count value immediately after the statement.
Executing anything, including an assignment statement will change it.
*/

todo := row_count;
dist := dist + 1;

exit when todo = 0;
end loop;

/*
Created the exploded bridge table. Calculate child level and distance from parent.

Code assumes a root node is coded with a parent key = -1. Adjust accordingly.
*/

insert into ngexplode
SELECT
A.CHILD,
case when A.PARENT = -1 then a.child else a.parent end as parent,
B.DISTANCE AS CHILDLEVEL,
case when a.parent = -1 then 0 else A.DISTANCE end AS DISTANCE
FROM NGTREE A,
(SELECT CHILD, LEVEL FROM NGTREE WHERE PARENT = -1 ) B
WHERE A.CHILD = B.CHILD;
END;
END_PROC;

Wow. This is so much nicer than the examples on the internet using MS SQL "WITH" clause and/or "connect by prior " ORACLE solution. This solution is ANSI SQL standard.

One comment. Should the "(SELECT CHILD, LEVEL FROM NGTREE WHERE PARENT = -1 ) B" be "(SELECT CHILD, DISTANCE FROM NGTREE WHERE PARENT = -1 ) B" ? There isn't a column LEVEL in the NGTREE table.

Thank you!
Endre

Yep.