Temporal Dimensional Modeling

---------------------------------------------------------------------
-- 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';