Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Slowly Changing Dimension Designs - Temporal Tables
Published: Dec 01, 2020
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Let's get started...

Basic Table

This is the basic setup I’ll be using (single line formatting for compactness only):

drop table if exists dbo.FactSalesByMonth, dbo.DimIndustry, dbo.DimCompany
go

create table DimIndustry(industryID tinyint identity, industryName varchar(100) not null)
create table DimCompany(companyID tinyint identity, companyName varchar(100) not null)
create table FactSalesByMonth(salesID int identity, industryID tinyint, companyID tinyint, dateID int, salesAmt money)
go

alter table DimIndustry add constraint pk_industry primary key clustered(industryID)
alter table DimCompany add constraint pk_company primary key clustered(companyID)
alter table FactSalesByMonth add constraint pk_sales primary key clustered(salesID)
go

alter table DimIndustry add constraint fk_salesIndustry foreign key(industryID) references DimIndustry(industryID)
alter table DimCompany add constraint fk_salesCompany foreign key(companyID) references DimCompany(companyID)
go

insert into DimIndustry select 'Technology'
insert into DimCompany select 'Pear Computers'
insert into FactSalesByMonth select 1, 1, 201801, 1000000
insert into FactSalesByMonth select 1, 1, 201802, 1000000
insert into FactSalesByMonth select 1, 1, 201803, 1000000
insert into FactSalesByMonth select 1, 1, 201804, 1200000
insert into FactSalesByMonth select 1, 1, 201805, 1200000
insert into FactSalesByMonth select 1, 1, 201806, 1200000
go

select * from DimIndustry
select * from DimCompany
select * from FactSalesByMonth
go



We’re going to deal with the fact that Pear Computers has changed its name over the years and we need to know what it was on a Point In Time basis.

Temporal Tables

This is a new feature that was added in SQL Server 2016 which deals with this exact scenario of slowly changing dimensions alongside providing robust and thorough auditing. It’s incredibly powerful and easy to use. HOWEVER, it’s also quite a large topic and therefore I’ll go into more detail in some upcoming posts in which I’m going to focus exclusively on the Temporal Table in respect to construction, requirements, considerations, and performance.

Therefore, all I’m intending to do in this post is simply change my Company dimension into a Temporal Table for completeness on the Slowly Changing Dimension Designs topic. To do this we would change the Company table as follows:

if exists
(
    
select *
    
from sys.tables
    
where name = 'DimCompany'
    
and schema_name(schema_id) = 'dbo'
    
and temporal_type <> 0
)
alter table dbo.DimCompany set (system_versioning = off)

drop table if exists dbo.DimCompany, history.DimCompany
go

create table dbo.DimCompany
(
    
companyID tinyint not null,
    
companyName varchar(100) not null,
    
fromDate datetime2 generated always as row start,
    
toDate datetime2 generated always as row end,
    
period for system_time(fromDate, toDate),
    
constraint pk_company primary key clustered(companyID)
)
with (system_versioning = on (history_table = history.DimCompany))
go


This looks complicated and confusing at first glance but, once you’re used to Temporal Tables, this will gradually become second nature.

This then allows us to access the data in the following way (note the new syntax which is also why they’re getting their own set of blog posts soon – for the moment just follow along):

select * from DimCompany
select * from history.DimCompany



select *
from dbo.DimCompany
for system_time as of '2018-02-01'

select *
from dbo.DimCompany
for system_time all



Summary (key points, not exhaustive)

Pros

  • In-built into SQL Server


  • Resilient and accurate


  • Good for auditing


  • Point In Time


  • Hidden to the average user


  • No large code changes needed


  • Performant


  • Allows for different indexing per table


Cons

  • New syntax for date based queries


  • Not ideal if you wish to backdate changes or manually amend historical values


As I mentioned earlier, this is very much glossing over the Temporal Table as they warrant their own posts (and will get them in time)… but they are incredibly powerful if they can work for you. The only disadvantage is that they are strict auditing tables and although they are very good for Point In Time work and Slowly Changing Dimensions, they will not suit those who may need to backdate changes. For example, someone in Sales comes back to you and says “The system says that on 12th last month the company was known as “Bob’s Fish Shop” but actually that didn’t happen until the 13th”… that’s actually extremely hard to correct (as future posts will show) and therefore you might want to avoid these unless you’re sure they’re right for you.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.