Data warehousing – bridge tables, what, why, eh?

-- We don't need row counts...
SET NOCOUNT ON;

-- Create an example source data table
DROP TABLE IF EXISTS [#SourceTable];
CREATE TABLE [#SourceTable] (
	[AppointmentID] INT NOT NULL
	,[ClinicianID1] INT NULL
	,[ClinicianID2] INT NULL
	,[ClinicianID3] INT	NULL
);  
GO  

-- Insert some test rows into our source data table
INSERT INTO [#SourceTable] VALUES (1,4,3,5);  
INSERT INTO [#SourceTable] VALUES (2,4,1,5);  
INSERT INTO [#SourceTable] VALUES (3,4,3,5);  
INSERT INTO [#SourceTable] VALUES (4,4,2,5);  
INSERT INTO [#SourceTable] VALUES (5,5,1,3);  
GO

-- Create a 'fake' fact table
DROP TABLE IF EXISTS [#FactAppointment];
CREATE TABLE [#FactAppointment] (
	[AppointmentKey] INT NOT NULL PRIMARY KEY
	,[AppointmentID] INT NOT NULL
	,[SomeOtherDimensionKey] INT NOT NULL DEFAULT(-1)
);
GO

INSERT INTO [#FactAppointment] VALUES (1,1,1);
INSERT INTO [#FactAppointment] VALUES (2,2,6); 
INSERT INTO [#FactAppointment] VALUES (3,3,3); 
INSERT INTO [#FactAppointment] VALUES (4,4,8); 
INSERT INTO [#FactAppointment] VALUES (5,5,7);
INSERT INTO [#FactAppointment] VALUES (6,6,2);
GO

-- Create a 'fake' clinician dimension table
DROP TABLE IF EXISTS [#DimClinician];
CREATE TABLE [#DimClinician] (
	[ClinicianKey] INT NOT NULL PRIMARY KEY
	,[ClinicianID] INT NOT NULL
	,[Name] VARCHAR(50) NOT NULL
);
GO

INSERT INTO [#DimClinician] VALUES (1,1,'Bob');  
INSERT INTO [#DimClinician] VALUES (2,2,'Sue');  
INSERT INTO [#DimClinician] VALUES (3,3,'Jim');  
INSERT INTO [#DimClinician] VALUES (4,4,'Jane');  
INSERT INTO [#DimClinician] VALUES (5,5,'Pete'); 
GO

-- Create a 'fake' bridge table
DROP TABLE IF EXISTS [#Bridge];
CREATE TABLE [#Bridge] (
	[AppointmentKey] INT NOT NULL
	,[ClinicianKey] INT NOT NULL	
);
GO

INSERT INTO [#Bridge] VALUES (1,1);  
INSERT INTO [#Bridge] VALUES (1,2);  
INSERT INTO [#Bridge] VALUES (2,1);  
INSERT INTO [#Bridge] VALUES (3,3);  
INSERT INTO [#Bridge] VALUES (3,5);
INSERT INTO [#Bridge] VALUES (3,4);
INSERT INTO [#Bridge] VALUES (4,5);
INSERT INTO [#Bridge] VALUES (4,1); 
INSERT INTO [#Bridge] VALUES (6,1);
GO

-- Now the real parts, first we'll unpivot the data and load 
-- into a temp table for ease of use later on
DROP TABLE IF EXISTS [#temp_unpivoted];
CREATE TABLE [#temp_unpivoted] (
	[AppointmentID] INT NOT NULL	
	,[ClinicianID] INT NOT NULL
	,[ClinicianNumber] INT NOT NULL
)

INSERT INTO
[#temp_unpivoted] (
	[AppointmentID]
	,[ClinicianID]
	,[ClinicianNumber]	
)

SELECT
-- We'll need the business key field so we can join 
-- onto the fact table later on...
[src].[AppointmentID]
	
-- These columns we'll need so we can join onto
-- the dimension table later on...
,[unpvt].[ClinicianID]
,[unpvt].[ClinicianNumber] -- This is optional, but might be useful in some cases

FROM
[#SourceTable] [src]

-- Unpivot the columns into rows, you could use UNPIVOT (see below) here instead of 
-- using 'CROSS APPLY'. However, in this example I've added an additional column using
-- the VALUES statement for the 'ClinicianNumber' which might be useful (or not) later
-- on. This also preserves NULL's as CROSS APPLY applies the join to all rows (which 
-- is why it keeps NULL values from columns). However, in a bridge table this isn't 
-- something needed or wanted. It's not a problem here though as the INNER JOIN to the 
-- dimension later on will eliminate these NULL's. If you're being picky or don't
-- need additional columns like my example, just use UNPIVOT instead (see below for
-- commented out example)
CROSS APPLY (
	VALUES
	(1, [src].[ClinicianID1])
	,(2, [src].[ClinicianID2])
	,(3, [src].[ClinicianID3])
	-- and so on for however many columns in the source table...
) [unpvt] (
	-- You might want to use 'ClinicianNumber' if you need/want to know the ordinal 
	-- number in relation to the source data table, for instance if it has business 
	-- relevance (e.g. 1 = primary clinician, 2 = others, etc...). This is why I often
	-- end up using CROSS APPLY instead of UNPIVOT for certain bridge tables ;-)
	[ClinicianNumber]
	,[ClinicianID]
)

-- Alternatively, this would be the equivalent using UNPIVOT. Note that we can't specify 
-- additional columns like we can with CROSS APPLY (which allows us to use the VALUES 
-- statement). We could still derive the 'ClinicianNumber' from the name of the original 
-- column saved in the 'ClinicianIDColumnName' field later on, but too much effort ;-)
/*
UNPIVOT (
	[ClinicianID] FOR [ClinicianIDColumnName] IN (
		[src].[ClinicianID1]
		,[src].[ClinicianID2]
		,[src].[ClinicianID3]
	)
) [unpvt] 
*/

WHERE
[unpvt].[ClinicianID] IS NOT NULL

-- Now we can join this unpivoted data onto the fact/dim tables to get the keys we need
-- and build a temp table ready for merging into the final 'real' bridge table
DROP TABLE IF EXISTS [#temp_bridge];
CREATE TABLE [#temp_bridge] (
	[AppointmentKey] INT NOT NULL
	,[ClinicianKey] INT NOT NULL
)

INSERT INTO
[#temp_bridge] (
	[AppointmentKey]
	,[ClinicianKey]
)

SELECT
[FactKey] = [fact].[AppointmentKey] -- Fact table primary key
,[DimensionKey] = [dim].[ClinicianKey] -- Dimension table primary key

FROM
[#temp_unpivoted] [unpvt_bridge]

-- Join our unpivoted data onto the fact table so 
-- we can get a fact table primary key
INNER JOIN 
[#FactAppointment] [fact]
ON
[unpvt_bridge].[AppointmentID] = [fact].[AppointmentID] -- Join on the fact business key

-- Join our unpivoted data onto the dimension table so 
-- we can get a dimension table primary key
INNER JOIN 
[#DimClinician] [dim]
ON 
[unpvt_bridge].[ClinicianID] = [dim].[ClinicianID] -- Join on the dimension business key

-- Finally, we merge the changes into the bridge table (note that we don't/can't do any
-- update part as we're joining on the 'key' fields of the table, so there are no fields
-- to update. We only insert new rows or delete rows no longer applicable to the range
-- of data we're now inserting
MERGE
[#Bridge] [trg]

USING
[#temp_bridge] [src]
ON
[trg].[AppointmentKey] = [src].[AppointmentKey]
AND [trg].[ClinicianKey] = [src].[ClinicianKey]

-- Insert any new rows
WHEN NOT MATCHED BY TARGET THEN INSERT (
	[AppointmentKey]
	,[ClinicianKey]
) VALUES (
	[src].[AppointmentKey]
	,[src].[ClinicianKey]
)

-- We only want to delete rows in the target bridge table where we've got appointments that are
-- in the source data, but where there's a different clinician. If we just deleted all rows here 
-- with no condition then we'd delete all the other rows in the entire bridge table for appointments
-- that are not in the incoming source data! ;-)
WHEN NOT MATCHED BY SOURCE AND EXISTS(	
	SELECT 
	1 
	
	FROM 
	[#temp_bridge] [src_data]

	WHERE
	[src_data].[AppointmentKey] = [trg].[AppointmentKey] -- Only for appointments in the target AND source!
)
THEN DELETE;

-- TEST ONLY - check what happened after the merge. We should have ALL rows from the source in there
-- where there was an 'AppointmentID' match, with any existing rows that matched but had a different
-- 'ClinicianID' now gone. Secondly, we need to check any 'AppointmentID' rows that were not in the 
-- source are still there. Which means for our example, there should still be a single row in the final
-- bridge table with 'AppointmentKey' = 6 and 'ClinicianKey' = 1, as that appointment wasn't in the 
-- incoming source data so we must leave it! ;-)
SELECT
[b].[AppointmentKey]
,[b].[ClinicianKey]

FROM
[#Bridge] [b]

ORDER BY
[b].[AppointmentKey]
,[b].[ClinicianKey]

-- Drop temp tables...
DROP TABLE IF EXISTS [#SourceTable];
DROP TABLE IF EXISTS [#FactAppointment];
DROP TABLE IF EXISTS [#DimClinician];
DROP TABLE IF EXISTS [#Bridge];

DROP TABLE IF EXISTS [#temp_unpivoted];
DROP TABLE IF EXISTS [#temp_bridge];