I will not go into the details of how the bridge table works. You may find that information on Kimballs web site and in some of his books. The first script creates a CTE which allows you to test some simple examples using the SQL 2005\2008 AdventureworksDW database.
WITH Tree(Parent_ID, Child_ID, Parent_Level, Child_Level, PathFromRoot, PathFromParent, Top_Flag, Bottom_Flag) AS
(--Get the root member
SELECT ParentEmployeeKey, EmployeeKey as Child_ID, 0 AS Parent_Level, 0 AS Child_Level
,Convert(varchar(max),EmployeeKey) as PathFromRoot, Convert(varchar(max),EmployeeKey) as PathFromParent
,Case WHEN ParentEmployeeKey is Null Then 'Y' Else 'N' End as Top_Flag, 'N' as Bottom_Flag
FROM dbo.DimEmployee
WHERE ParentEmployeeKey IS NULL
UNION ALL
SELECT e.ParentEmployeeKey, e.EmployeeKey, Child_Level, Child_Level + 1, PathFromRoot + ' ' + Convert(varchar(max),EmployeeKey)
, Convert(varchar(max),e.ParentEmployeeKey) + ' ' + Convert(varchar(max),e.EmployeeKey) as PathFromRoot,'N', CASE WHEN EXISTS(SELECT 1 from dbo.DimEmployee e2 where e.EmployeeKey = e2.ParentEmployeeKey) THEN 'N' Else 'Y' END
FROM dbo.DimEmployee e
INNER JOIN Tree t
ON e.ParentEmployeeKey = t.Child_ID
)
, Navigation_Bridge(Parent_ID, Child_ID, Parent_Level, Child_Level, Distance_Between_Parent_And_Child, PathFromRoot, PathFromParent, Top_Flag, Bottom_Flag, Source) AS
(SELECT ISNULL(Parent_ID, Child_ID), Child_ID, Parent_Level, Child_Level, Child_Level-Parent_Level, PathFromRoot, PathFromParent, Top_Flag, Bottom_Flag, 0 as Source from Tree
UNION --Create Self Referencing Rows. These should not be used for recursion in the following step.
SELECT Child_ID, Child_ID, Child_Level, Child_Level, 0, PathFromRoot,Convert(varchar(max),Child_ID), Top_Flag, Bottom_Flag, -1 from Tree
WHERE Child_Level > 0
UNION ALL
SELECT A.Parent_ID, B.Child_ID, A.Parent_Level,B.Child_Level, B.Child_Level-A.Parent_Level, B.PathFromRoot
, A.PathFromParent + ' ' + Convert(varchar(max),B.Child_ID)
, A.Top_Flag,B.Bottom_Flag, Source + 1
FROM Navigation_Bridge A INNER JOIN Tree B on B.Parent_ID = A.Child_ID AND B.Child_Level >= 2 And Source != -1
)
--Select * From Navigation_Bridge
--WHERE Parent_ID = 3
/*
--Get the Children of Roberto Taburello (3)
SELECT
FirstName + ' ' + LastName as Name
,* FROM Navigation_Bridge nb
INNER JOIN dbo.DimEmployee e on nb.Child_ID = e.EmployeeKey
WHERE Parent_ID = 3 and Bottom_Flag = 'Y';
--SELECT * FROM DimEmployee
*/
/*
--Get all of the Parents of Roberto Tamburello (3)
SELECT
FirstName + ' ' + LastName as Name
, * FROM Navigation_Bridge nb
INNER JOIN dbo.DimEmployee e on nb.Parent_ID = e.EmployeeKey
WHERE Child_ID = 3;
*/
/*
--Get the Direct Reports of Roberto Taburello (3)
SELECT
FirstName + ' ' + LastName as Name
, * FROM Navigation_Bridge nb
INNER JOIN dbo.DimEmployee e on nb.Child_ID = e.EmployeeKey
WHERE Parent_ID = 3
And Distance_Between_Parent_And_Child = 1;
*/
This next script allows you to create a small table for testing, and has some quick directions to allow you to customize this to your table.
/*
DROP TABLE dbo.Emp
GO
Create Table Emp
(Source_Child_Id int NOT NULL,
Source_Parent_ID int Null
)
Delete from Emp
GO
INSERT INTO Emp Values(1,NULL)
INSERT INTO Emp Values(2,1)
INSERT INTO Emp Values(3,1)
INSERT INTO Emp Values(4,3)
INSERT INTO Emp Values(5,3)
INSERT INTO Emp Values(6,4)
INSERT INTO Emp Values(7,6)
GO
*/
--This sample works on the sample table above
--Tree is a straight Parent child tree. For each node there is a row from it to its direct parent.
--This also computes the Path from the Root to the child, and the child's level of depth from the root
-- as well as from its parent.
--Navigation Bridge is Kimball's navigation bridge with a row for every pair of nodes which are related to one another,
-- either directly or indirectly, as well as a row for each node pointing to itself.
--To use this with another table you simply need a table with a parent/child relationship.
-- Replace Source_Parent_ID with your parentkey
-- Replace Source_Child_ID with your childkey
-- Replace dbo.emp with your table name
WITH Tree(Parent_ID, Child_ID, Parent_Level, Child_Level, PathFromRoot, PathFromParent, Top_Flag, Bottom_Flag) AS
(--Get the root member
SELECT Source_Parent_ID as Parent_ID, Source_Child_Id as Child_ID, 0 AS Parent_Level, 0 AS Child_Level
,Convert(varchar(max),Source_Child_Id) as PathFromRoot, Convert(varchar(max),Source_Child_Id) as PathFromParent
,Case WHEN Source_Parent_ID is Null Then 'Y' Else 'N' End as Top_Flag, 'N' as Bottom_Flag
FROM dbo.Emp
WHERE Source_Parent_ID IS NULL
UNION ALL
SELECT e.Source_Parent_ID, e.Source_Child_Id, Child_Level, Child_Level + 1, PathFromRoot + ' ' + Convert(varchar(max),Source_Child_Id)
, Convert(varchar(max),e.Source_Parent_ID) + ' ' + Convert(varchar(max),e.Source_Child_Id) as PathFromRoot,'N'
, CASE WHEN EXISTS(SELECT 1 from dbo.Emp e2 where e.Source_Child_Id = e2.Source_Parent_ID) THEN 'N' Else 'Y' END
FROM dbo.Emp e
INNER JOIN Tree t
ON e.Source_Parent_ID = t.Child_ID
)
, Navigation_Bridge(Parent_ID, Child_ID, Parent_Level, Child_Level, Distance_Between_Parent_And_Child, PathFromRoot, PathFromParent, Top_Flag, Bottom_Flag, Source) AS
(SELECT ISNULL(Parent_ID, Child_ID), Child_ID, Parent_Level, Child_Level, Child_Level-Parent_Level, PathFromRoot, PathFromParent, Top_Flag, Bottom_Flag, 0 as Source from Tree
UNION --Create Self Referencing Rows. These should not be used for recursion in the following step.
SELECT Child_ID, Child_ID, Child_Level, Child_Level, 0, PathFromRoot ,Convert(varchar(max),Child_ID), Top_Flag, Bottom_Flag, -1 from Tree
WHERE Child_Level > 0
UNION ALL
SELECT A.Parent_ID, B.Child_ID, A.Parent_Level,B.Child_Level, B.Child_Level-A.Parent_Level, B.PathFromRoot
, A.PathFromParent + ' ' + Convert(varchar(max),B.Child_ID)
, A.Top_Flag,B.Bottom_Flag, Source + 1
FROM Navigation_Bridge A INNER JOIN Tree B on B.Parent_ID = A.Child_ID AND B.Child_Level >= 2 And Source != -1
)
SELECT * FROM Navigation_Bridge
--Where Parent_ID = 4
;