---------------------------------------------------------------------
-- Temporal Dimensional Modeling (by Lars Rönnbäck)
-- borrowing concepts from Bitemporal Anchor Modeling
-- http://www.anchormodeling.com
--
-- Note that performance could be improved further if
-- paritioning over currently recorded vs erased data
-- is added, but was left out for the sake of making the
-- script runnable on the free SQL Server Express edition.
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Split dimensions into immutable/mutable parts
-- in order to achieve temporal referential integrity.
-- Add the proper primary key with temporal components.
-- Add a unique constraint ensuring temporal entity integrity.
-- Foreign keys must be declared for table elimination.
---------------------------------------------------------------------
create table dim1Immutable (
dim1ID int not null primary key
);
create table dim1Mutable (
dim1ID int not null foreign key references dim1Immutable(dim1ID),
dim1Value char(42) not null,
dim1ChangedAt datetime not null,
dim1RecordedAt datetime not null,
dim1ErasedAt datetime not null,
primary key (
dim1ID,
dim1ChangedAt,
dim1RecordedAt
),
unique (
dim1ErasedAt,
dim1ID,
dim1ChangedAt
)
)
create table dim2Immutable (
dim2ID int not null primary key
);
create table dim2Mutable (
dim2ID int not null foreign key references dim2Immutable(dim2ID),
dim2Value char(42) not null,
dim2ChangedAt datetime not null,
dim2RecordedAt datetime not null,
dim2ErasedAt datetime not null,
primary key (
dim2ID,
dim2ChangedAt,
dim2RecordedAt
),
unique (
dim2ErasedAt,
dim2ID,
dim2ChangedAt
)
)
---------------------------------------------------------------------
-- If nothing references the fact table no immutable part is needed.
-- Add the proper primary key with temporal components.
-- Add a unique constraint ensuring temporal entity integrity.
-- Foreign keys must be declared for table elimination.
---------------------------------------------------------------------
create table factMutable (
dim1ID int not null foreign key references dim1Immutable(dim1ID),
dim2ID int not null foreign key references dim2Immutable(dim2ID),
measureA money,
measureB money,
measureC money,
factChangedAt datetime not null,
factRecordedAt datetime not null,
factErasedAt datetime not null,
primary key (
dim1ID,
dim2ID,
factChangedAt,
factRecordedAt
),
unique (
factErasedAt,
dim1ID,
dim2ID,
factChangedAt
)
);
go
---------------------------------------------------------------------
-- Create table valued functions that rewind recording time to
-- the given timepoint.
---------------------------------------------------------------------
create function rew_dim1Mutable(
@recordingTimepoint datetime
)
returns table return
select
*
from
dim1Mutable
where
dim1ErasedAt > @recordingTimepoint
and
dim1RecordedAt <= @recordingTimepoint;
go
create function rew_dim2Mutable(
@recordingTimepoint datetime
)
returns table return
select
*
from
dim2Mutable
where
dim2ErasedAt > @recordingTimepoint
and
dim2RecordedAt <= @recordingTimepoint;
go
create function rew_factMutable(
@recordingTimepoint datetime
)
returns table return
select
*
from
factMutable
where
factErasedAt > @recordingTimepoint
and
factRecordedAt <= @recordingTimepoint;
go
---------------------------------------------------------------------
-- Create table valued functions that show information as was
-- at the given timepoints.
---------------------------------------------------------------------
create function pit_dim1Mutable(
@changingTimepoint datetime,
@recordingTimepoint datetime
)
returns table return
select
d1m.*
from
rew_dim1Mutable(@recordingTimepoint) d1m
where
d1m.dim1ChangedAt = (
select
max(sub.dim1ChangedAt)
from
rew_dim1Mutable(@recordingTimepoint) sub
where
sub.dim1ID = d1m.dim1ID
);
go
create function pit_dim2Mutable(
@changingTimepoint datetime,
@recordingTimepoint datetime
)
returns table return
select
d2m.*
from
rew_dim2Mutable(@recordingTimepoint) d2m
where
d2m.dim2ChangedAt = (
select
max(sub.dim2ChangedAt)
from
rew_dim2Mutable(@recordingTimepoint) sub
where
sub.dim2ID = d2m.dim2ID
);
go
create function pit_factMutable(
@changingTimepoint datetime,
@recordingTimepoint datetime
)
returns table return
select
fm.*
from
rew_factMutable(@recordingTimepoint) fm
where
fm.factChangedAt = (
select
max(sub.factChangedAt)
from
rew_factMutable(@recordingTimepoint) sub
where
sub.dim1ID = fm.dim1ID
and
sub.dim2ID = fm.dim2ID
);
go
---------------------------------------------------------------------
-- Run a test query, showing that immutable parts are never touched
-- during execution thanks to table elimination.
---------------------------------------------------------------------
select
avg(measureA),
sum(measureB),
max(measureC)
from
pit_factMutable('2001-01-01', '2001-01-01') fm
join
dim1Immutable d1i
on
d1i.dim1ID = fm.dim1ID
join
pit_dim1Mutable('2001-01-01', '2001-01-01') d1m
on
d1m.dim1ID = d1i.dim1ID
join
dim2Immutable d2i
on
d2i.dim2ID = fm.dim2ID
join
pit_dim2Mutable('2001-01-01', '2001-01-01') d2m
on
d2m.dim2ID = d2i.dim2ID
where
d1m.dim1Value = 'Value1'
and
d2m.dim2Value = 'Value2';