home
Sql Server. Maintain a type 2 dimension table
Author Nigel Rivett
/*
Maintain a type 2 dimension table
A type 2 dimension holds the history of all changes with a time range for which the row is valid.
Therefore the table has extra columns
z_StartDate -- start date for the row
z_EndDate -- End date for the row - 25000101 if current
When a new entry is received to is processed and matched wit hthe existing data using the row identifier.
If the data is different from the current row then the current row is termiated and a new row inserted.
*/
-- Dimension table
if object_id('dim_Test','U') is not null
drop table dim_Test
go
create table dim_Test
(
dim_Test_id bigint identity(1,1) not null
, Forename varchar(20) not null
, surname varchar(20) not null
, Address1 varchar(20)
, Age int
, z_StartDate datetime not null
, z_EndDate datetime not null
, z_inserted datetime not null
, z_updated datetime not null
)
go
alter table dim_test add constraint PK_dim_Test primary key
(dim_Test_id)
go
create unique index ix_dim_Test_01 on dim_Test
(ForeName, SurName, z_StartDate)
go
insert dim_Test (Forename, Surname, Address1, Age, z_StartDate, z_EndDate, z_Inserted, z_Updated)
select 'John', 'Smith', 'JohnAddress1', 10, '20000101', '25000101', getdate(), getdate()
insert dim_Test (Forename, Surname, Address1, Age, z_StartDate, z_EndDate, z_Inserted, z_Updated)
select 'Fred', 'Blogs', 'FredAddress1', 20, '20000101', '25000101', getdate(), getdate()
insert dim_Test (Forename, Surname, Address1, Age, z_StartDate, z_EndDate, z_Inserted, z_Updated)
select 'Zero', 'Mostel', 'ZeroAddress1', 20, '20000101', '25000101', getdate(), getdate()
declare @inp table
(
Forename varchar(20) not null
, surname varchar(20) not null
, Address1 varchar(20)
, Age int
)
insert @inp (Forename, Surname, Address1, Age)
select 'John', 'Smith', 'JohnAddress1', 10
insert @inp (Forename, Surname, Address1, Age)
select 'Fred', 'Blogs', 'FredAddress2', 20
insert @inp (Forename, Surname, Address1, Age)
select 'Peter', 'Jones', 'PeterAddress3', 30
declare @Output table
(
Type varchar(20)
, Forename varchar(20)
, surname varchar(20)
, Address1 varchar(20)
, Age int
)
declare @CurrentDate datetime
select @CurrentDate = getdate()
merge dim_Test t1
using @inp t2
on t1.Forename = t2.Forename
and t1.Surname = t2.Surname
and t1.z_EndDate = '25000101'
when matched and
( t1.Address1 <> t2.Address1
or t1.Age <> t2.Age
)
then update set
z_EndDate = @CurrentDate
, z_Updated = getdate()
when not matched by target
then insert
(
Forename
, surname
, Address1
, Age
, z_StartDate
, z_EndDate
, z_inserted
, z_updated
)
values
(
t2.Forename
, t2.surname
, t2.Address1
, t2.Age
, @CurrentDate
, '25000101'
, getdate()
, getdate()
)
when not matched by source
then update set
z_EndDate = @CurrentDate
, z_Updated = getdate()
output $action, t2.Forename, t2.Surname, t2.Address1, t2.Age into @Output;
--output $action, coalesce(t2.Forename,t1.Forename), coalesce(t2.Surname,t1.Surname), t2.Address1, t2.Age into @Output;
insert dim_Test
(
Forename
, surname
, Address1
, Age
, z_StartDate
, z_EndDate
, z_inserted
, z_updated
)
select
Forename
, surname
, Address1
, Age
, @CurrentDate -- could be dateadd(ms,2,@CurrentDate)
, '25000101'
, getdate()
, getdate()
from @Output
where Type = 'UPDATE'
and Forename is not null
select * from @Output
select * from dim_Test order by Forename, Surname, z_StartDate
/*
Results
Type Forename surname Address1 Age
-------------------- -------------------- -------------------- -------------------- -----------
INSERT Peter Jones PeterAddress3 30
UPDATE Fred Blogs FredAddress2 20
UPDATE NULL NULL NULL NULL
dim_Test_id Forename surname Address1 Age z_StartDate z_EndDate z_inserted z_updated
-------------------- -------------------- -------------------- -------------------- ----------- ----------------------- ----------------------- ----------------------- -----------------------
2 Fred Blogs FredAddress1 20 2000-01-01 00:00:00.000 2019-11-05 11:30:43.710 2019-11-05 11:30:43.710 2019-11-05 11:30:43.740
5 Fred Blogs FredAddress2 20 2019-11-05 11:30:43.710 2500-01-01 00:00:00.000 2019-11-05 11:30:43.740 2019-11-05 11:30:43.740
1 John Smith JohnAddress1 10 2000-01-01 00:00:00.000 2500-01-01 00:00:00.000 2019-11-05 11:30:43.710 2019-11-05 11:30:43.710
4 Peter Jones PeterAddress3 30 2019-11-05 11:30:43.710 2500-01-01 00:00:00.000 2019-11-05 11:30:43.740 2019-11-05 11:30:43.740
3 Zero Mostel ZeroAddress1 20 2000-01-01 00:00:00.000 2019-11-05 11:30:43.710 2019-11-05 11:30:43.710 2019-11-05 11:30:43.740
*/
home