In a datawarehouse, we often come across dimensions which
are combination of type 1 and type 2. We
would need to preserve history on certain columns and overwrite history on
other columns. In this example we will see how we can perform type 1 and type 2
update on a dimension in one transaction using MERGE and Try Catch available in
T-SQL.
/* Dimension Table for
Customer */
CREATE TABLE
[dbo].[CustomerDim](
[CustomerKey]
[int] IDENTITY(1,1) NOT NULL,
SourceCustomerID
int,
[FirstName]
[nvarchar](50) NULL, -- Type 1
[MiddleName]
[nvarchar](50) NULL, -- Type 1
[LastName]
[nvarchar](50) NULL, -- Type 1
[BirthDate]
[date] NULL, -- Type
1
[MaritalStatus]
[nchar](1) NULL, -- Type 2
[YearlyIncome]
[money] NULL, -- Type
2
[NumberChildrenAtHome]
[tinyint] NULL, --
Type 2
StartDate
Date,
EndDate
Date
)
/* Staging Table to
Hold Customer Data */
CREATE TABLE
[dbo].[StagingCustomer](
SourceCustomerID
int,
[FirstName]
[nvarchar](50) NULL,
[MiddleName]
[nvarchar](50) NULL,
[LastName]
[nvarchar](50) NULL,
[BirthDate]
[date] NULL,
[MaritalStatus]
[nchar](1) NULL,
[YearlyIncome]
[money] NULL,
[NumberChildrenAtHome]
[tinyint] NULL
)
GO
/* Procedure to Peform
Type1, Type2 Updates on CustomerDim */
CREATE PROC
usp_CustomerDim_Type1Type2UPdates
AS
BEGIN TRY
BEGIN TRAN -- Type 1 and Type 2 Updates will be in One Transaction
-- Type 2 Inserts.
INSERT INTO [dbo].[CustomerDim]
(
SourceCustomerID
,
[FirstName]
,
[MiddleName]
,
[LastName]
,
[BirthDate]
,
[MaritalStatus]
,
[YearlyIncome]
,
[NumberChildrenAtHome],
StartDate,
EndDate
)
SELECT -- These columns are returned from OUTPUT Clause.
SourceCustomerID
,
[FirstName]
,
[MiddleName]
,
[LastName]
,
[BirthDate]
,
[MaritalStatus]
,
[YearlyIncome]
,
[NumberChildrenAtHome],
GETDATE() - 1 as StartDate,
'20500101' as EndDate
FROM
(
-- Perform Type 2 Merge.
Merge [dbo].[CustomerDim]
cust
Using [dbo].[StagingCustomer]
stg
on cust.SourceCustomerID =
stg.SourceCustomerID
WHEN NOT MATCHED THEN -- New Records
INSERT
(
SourceCustomerID
,
[FirstName]
,
[MiddleName]
,
[LastName]
,
[BirthDate]
,
[MaritalStatus]
,
[YearlyIncome]
,
[NumberChildrenAtHome],
StartDate,
EndDate
)
VALUES
(
stg.SourceCustomerID ,
stg.[FirstName] ,
stg.[MiddleName] ,
stg.[LastName] ,
stg.[BirthDate] ,
stg.[MaritalStatus] ,
stg.[YearlyIncome] ,
stg.[NumberChildrenAtHome],
GETDATE()-1,
'20500101'
)
WHEN MATCHED -- Type 2 Changes
Check
AND EndDate = '20500101' -- Current
Record.
/* Check for Changes to Type 2 Columns */
AND EXISTS
(
SELECT cust.[YearlyIncome],
cust.[NumberChildrenAtHome],
cust.[MaritalStatus]
EXCEPT
SELECT stg.[YearlyIncome],
stg.[NumberChildrenAtHome],
stg.[MaritalStatus]
)
THEN
UPDATE -- Set EndDate
for Records having Type 2 Change.
Set EndDate = GETDATE()-1
OUTPUT -- This will be used
to insert recrods to CustomerDim Table.
$Action as
MergeAction,
stg.SourceCustomerID ,
stg.[FirstName] ,
stg.[MiddleName] ,
stg.[LastName] ,
stg.[BirthDate] ,
stg.[MaritalStatus] ,
stg.[YearlyIncome] ,
stg.[NumberChildrenAtHome]
) as Type2Merge
WHERE
MergeAction = 'Update';
-- Type 1 Updates.
;Merge [dbo].[CustomerDim]
cust
Using [dbo].[StagingCustomer] stg
on cust.SourceCustomerID =
stg.SourceCustomerID
WHEN MATCHED
/* Check for Changes to Type 1 Columns */
AND EXISTS
(
SELECT
cust.[FirstName] ,
cust.[MiddleName] ,
cust.[LastName] ,
cust.[BirthDate]
EXCEPT
SELECT
stg.[FirstName] ,
stg.[MiddleName] ,
stg.[LastName] ,
stg.[BirthDate]
)
THEN UPDATE
Set
[FirstName]
= stg.[FirstName] ,
[MiddleName]
= stg.[MiddleName]
,
[LastName]
= stg.[LastName],
[BirthDate]
= stg.[BirthDate]
;
COMMIT --
COMMIT Type 1 and Type2 Changes.
END TRY
BEGIN CATCH
--ROLLBACK
ROLLBACK TRAN; -- Rollback Type 1 and
Type2 Changes.
THROW --
THROW Error.
END CATCH
GO – END of PROCEDURE
-- Testing.
-- Insert New Records
truncate table
[dbo].[StagingCustomer]
Insert Into
[dbo].[StagingCustomer] values
( 1,'John', '', 'Doe' , '19750101','M',100000,1),
( 2, 'Jack', '', 'Smith' , '19750101','M',100000,1),
( 3, 'Mary', '', 'Smith' , '19750101','M',100000,1)
exec usp_CustomerDim_Type1Type2UPdates
-- Perform TYpe 2 Change
truncate table
[dbo].[StagingCustomer]
Insert Into
[dbo].[StagingCustomer] values
( 1,'John', '', 'Doe' , '19750101','M',200000,1)
exec usp_CustomerDim_Type1Type2UPdates
-- Pefrorm Type 1
Change
truncate table
[dbo].[StagingCustomer]
Insert Into
[dbo].[StagingCustomer] values
( 1,'John2', '', 'Doe' , '19750101','M',200000,1)
exec usp_CustomerDim_Type1Type2UPdates
-- Show the records
select *
from [dbo].[CustomerDim]
|
CustomerKey
|
SourceCustomerID
|
FirstName
|
MiddleName
|
LastName
|
BirthDate
|
MaritalStatus
|
YearlyIncome
|
NumberChildrenAtHome
|
StartDate
|
EndDate
|
|
1
|
1
|
John2
|
|
Doe
|
1/1/1975
|
M
|
100000
|
1
|
12/10/2015
|
12/10/2015
|
|
2
|
2
|
Jack
|
|
Smith
|
1/1/1975
|
M
|
100000
|
1
|
12/10/2015
|
1/1/2050
|
|
3
|
3
|
Mary
|
|
Smith
|
1/1/1975
|
M
|
100000
|
1
|
12/10/2015
|
1/1/2050
|
|
4
|
1
|
John2
|
|
Doe
|
1/1/1975
|
M
|
200000
|
1
|
12/10/2015
|
1/1/2050
|
