The next version of the DWH Deck will support templates. This is the documentation of the link table template.

Variables/placeholders used:

@Database, the name of the data vault database e.g.: DWH_dv
@MetaDatabase, the name of the meta database e.g.: DWH
@BusinessArea, the name of the business area, implemented as schema e.g.: sales
@Table, the name of the link table e.g.: Sales_link
@SourceTableName, the full identifying name of the related staging table e.g.: [DWH_st].[sales].[Northwind_dbo_Orders]
@ModelTableID, is used to track the model table to the audit table
@Sqn, the suffix that is used for the surrogate key in the link table
@SourceNumber, when there is more than one source for the link table

@@ColumnList, a list of surrogate key columns that identify the related hubs like:

[Product_hub_sqn]
, [Customer_hub_sqn]

@@ColumnTableList, a list of columns with the data type defining the table:

[Product_hub_sqn] int
, [Customer_hub_sqn] int

@@ColumnForeignKeyList, a list defining the foreign key relations to the hubs:

CONSTRAINT fk_Orders_link1 FOREIGN KEY ([Product_hub_sqn]) REFERENCES [Product_hub]
,CONSTRAINT fk_Orders_link2 FOREIGN KEY ([Customer_hub_sqn]) REFERENCES [Customer_hub]

@@ColumnViewList, a list that seeks the related surrogate key in the hub or satellite:

[Product_hub_sqn] =(SELECT MAX(ref1.[Product_hub_sqn]) FROM Product_hub_ProductID ref1 WHERE ref1.ProductID=st.ProductID)
, [Customer_hub_sqn] = (SELECT MAX(ref2.[Customer_hub_sqn]) FROM Customer_hub ref2 WHERE ref2.CustomerID=st.CustomerID)

@@ColumnWhereList, a list of Where statements checking existents in target link table

ISNULL(src.[Product_hub_sqn],0) = ISNULL(trgt.[Product_hub_sqn],0)
AND ISNULL(src.[Customer_hub_sqn],0) = ISNULL(trgt.[Customer_hub_sqn],0)

Link Table Template

The SQL statement used to create the hub table:

USE [@Database]
GO

--save old data (if any) into backup of table with prefix _
IF EXISTS (SELECT 1 FROM [sys].[objects]
WHERE object_id=OBJECT_ID(N'[@BusinessArea].[@Table]') AND type in (N'U'))
BEGIN
IF (SELECT COUNT(*) FROM [@BusinessArea].[@Table])>0
BEGIN
IF EXISTS (SELECT 1 FROM [sys].[objects]
WHERE object_id=OBJECT_ID(N'[@BusinessArea].[_@Table]') AND type in (N'U'))
DROP TABLE [@BusinessArea].[_@Table];
SELECT * INTO [@BusinessArea].[_@Table]
FROM [@BusinessArea].[@Table];
END
DROP TABLE [@BusinessArea].[@Table]
END

--create table
CREATE TABLE [@BusinessArea].[@Table]
( [@Table@Sqn] INT NOT NULL IDENTITY(1,1)
, [AuditID] INT
, @@ColumnTableList
, CONSTRAINT [pk_@Table] PRIMARY KEY
( [@Table@Sqn])
, @@ColumnForeignKeyList);

CREATE UNIQUE INDEX [ux_@Table]
ON [@Database].[@BusinessArea].[@Table]
( @@ColumnList)
INCLUDE ([@Table@Sqn]);

The part of the statement to drop and recreate the table is rather large (as in all data vault tables) This is helpful if you have accumulated some history in your link table and you made a change in the definition. All data is first stored in a new table with the prefix "_" before dropping the table. Once you have created your new link table you can still access the old data and import it manual from this _table to the newly created link table.

The index uses the surrogate key in the "include" part. Since most queries to this table will need this key its covered in the index.

Link View Template

The SQL statement used to create a view which is used to import the data from the staging table:

USE [@Database]
GO

--drop previous view if it exists
IF EXISTS (SELECT 1 FROM [sys].[views]
WHERE object_id = OBJECT_ID(N'[@BusinessArea].[vwImport_@Table@SourceNumber]'))
DROP VIEW [@BusinessArea].[vwImport_@Table@SourceNumber];

--create view
USE [@Database]
GO
--@
CREATE VIEW [@BusinessArea].[vwImport_@Table@SourceNumber]
AS
SELECT src.* FROM (SELECT
@@ColumnViewList
FROM @SourceTableName st WITH (NOLOCK)) src

--check if source (src) row exists in target (trgt)
WHERE NOT EXISTS (SELECT 1
FROM [@BusinessArea].[@Table] trgt WITH (NOLOCK)
WHERE (@@ColumnWhereList)
)

Link ETL Template

The SQL Statement to create a stored procedure that is used to import the data:

USE [@Database]
GO

--drop previous etl-procedure if it exists
IF EXISTS (SELECT * FROM [sys].[objects]
WHERE object_id = OBJECT_ID(N'[@BusinessArea].[uspImport_@Table@SourceNumber]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [@BusinessArea].[uspImport_@Table@SourceNumber]

--create etl-procedure
USE [@Database]
GO
--@
CREATE PROCEDURE [@BusinessArea].[uspImport_@Table@SourceNumber]
AS

--get the audit_id
DECLARE @AuditID INT
INSERT INTO [@MetaDatabase].[dbo].[Audit] (StartDate, ModelTableID)
VALUES( getdate(), @ModelTableID);
SET @AuditID=@@IDENTITY

--insert into target
INSERT INTO [@BusinessArea].[@Table]
( [AuditID]
, @@ColumnList
) SELECT @AuditID, * FROM
[@BusinessArea].[vwImport_@Table@SourceNumber] ;

-- update audit table with the number of rows
UPDATE [@MetaDatabase].[dbo].[Audit]
SET RowsInserted=@@ROWCOUNT, EndDate=getdate()
WHERE AuditID=@AuditID;