Populating a many-to-many bridge table

Posted on January 25, 2011

2



STOP!

For a simpler, faster and more elegant solution to the following problem, see this: https://jsimonbi.wordpress.com/2011/02/01/populating-a-many-to-many-bridge-table-pt2/

In a recent post on dealing with the many-to-many situation (https://jsimonbi.wordpress.com/2011/01/16/data-modeling-conundrum/) , I illustrated the use of a bridge table in a dimensional model, as prescribed by Ralph Kimball. So if for example we are recording sales, and many salespeople can be associated with a sale, a bridge table would sit between the person dimension table and the sale fact table such as:

many-to-many bridge table

With a bridge table as above, the SalesTeamKey may be used multiple times in connection with different sales. In addition, a sales person may be a member of many sales teams. So we need a distinct list of the sales people in each team. Notice too the Allocation column in the BridgeSalesTeam table. This is so we can work out the SalesAmount for each SalesPerson. In this example, we will just be giving an equal weighting to all team members.

So how do we populate this bridge table?

Let’s use a simple example using on SaleID and PersonID, based on the diagram above.

IF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test
GO
CREATE TABLE Test(SaleID INT, PersonID int)
INSERT INTO Test
VALUES (1,1)
, (1,2)
, (2,1)
, (3,2)
, (4,1)
, (4,2)
, (5,3)
, (5,4)
GO

In this example, we will be using a cursor to step through each SaleID. Then we will use a recursive CTE similar to what we did in the Enumerated Path post: https://jsimonbi.wordpress.com/2011/01/14/enumeratedpath/

We do a self-join of the recursive CTE and use the last enumerated path for each sale to give us the team members involved in each sale. We then load the results into a temp table. Here’s the code:

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO
CREATE TABLE #Results(SaleID INT, PersonId INT, TeamPath VARCHAR(MAX), PersonCnt INT)
GO
DECLARE @Node INT
DECLARE cur1 CURSOR LOCAL FAST_FORWARD FOR
SELECT SaleId
FROM dbo.test a
OPEN cur1
FETCH NEXT FROM cur1 INTO @Node
WHILE @@FETCH_STATUS = 0
BEGIN
;WITH SalePerson AS
 (SELECT SaleID
  , PersonID
  , COUNT(PersonID) OVER (PARTITION BY SaleID) AS Personcnt
  , ROW_NUMBER() OVER (PARTITION BY SaleID ORDER BY PersonID ASC)
    AS PersonRow
  FROM Test
  WHERE SaleID = @Node
  )
, CTE AS (
 SELECT SaleID
  , PersonID
  , PersonRow
  , Personcnt
  , CONVERT(VARCHAR(MAX), PersonID) AS TeamPath
 FROM SalePerson
 WHERE PersonRow = 1
 UNION ALL
 SELECT a.SaleID
  , a.PersonID
  , a.PersonRow
  , a.Personcnt
  , b.TeamPath + '.'
   + CONVERT(VARCHAR(MAX), a.PersonID) AS TeamPath
 FROM SalePerson a
 JOIN CTE b
 ON a.SaleID = b.SaleID
 AND a.PersonRow = b.PersonRow + 1
 WHERE a.PersonRow <= a.Personcnt
 )
INSERT INTO #Results
SELECT a.SaleID
 , a.PersonID
 , b.TeamPath
 , a.Personcnt
FROM CTE a
JOIN CTE b
ON a.SaleID = b.SaleID
AND b.PersonCnt = b.PersonRow
OPTION (MAXRECURSION 0)
FETCH NEXT FROM cur1 INTO @Node
END
CLOSE cur1
DEALLOCATE cur1

If we run the following query we can see the results we’re after and how we got there:

SELECT DISTINCT
    a.SaleID
  , a.PersonId
  , a.TeamPath
  , DENSE_RANK() OVER (ORDER BY TeamPath ) AS SalesTeamID
  , 1/CAST(PersonCnt AS DECIMAL(6,2)) AS Allocation
FROM #Results a

This returns:

Note that SaleIDs 1 and 4 (highlighted in yellow) have the same team members, resulting in the same SalesTeamID.

To load our Bridge Table, we then run the following query:

IF OBJECT_ID('dbo.BridgeSalesTeam') IS NOT NULL
 DROP TABLE dbo.BridgeSalesTeam
GO
CREATE TABLE BridgeSalesTeam(
   SalesTeamID INT
 , PersonID INT
 , Allocation DECIMAL(6,2)
 )
GO
;WITH CTE AS (
SELECT DISTINCT
  a.SaleID
, a.PersonId
, a.TeamPath
, DENSE_RANK() OVER (ORDER BY TeamPath ) AS SalesTeamID
, 1/CAST(PersonCnt AS DECIMAL(6,2)) AS Allocation
FROM #Results a
)
INSERT INTO BridgeSalesTeam
SELECT DISTINCT SalesTeamID, PersonID, Allocation
FROM CTE
GO
SELECT *
FROM BridgeSalesTeam

Returning:

many-to-many bridge table contents

Advertisements
Posted in: Data Modeling, SQL