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 - History tables
Published: Nov 27, 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.

Date Based with History tables

In this method we add To and From dates to our table in order to show when a value was active, but instead of adding any additional keys to our table we keep only current data in our dbo table and log all older records out to another table, for example maybe using the “History” schema. For example, our company table changes as follows:

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

create table DimCompany
(
companyID tinyint identity not null,
fromDate datetime not null,
toDate datetime not null,
companyName varchar(100) not null
)
go

create table history.DimCompany
(
companyID tinyint not null,
fromDate datetime not null,
toDate datetime not null,
companyName varchar(100) not null
)
go

select * from DimCompany
select * from history.DimCompany



Summary (main points, not exhaustive)

Pros

  • Clean separation of data between current and historic


  • Current data can be accessed with much fewer reads


  • Primary Key can be maintained in Current data


  • Very good if the majority of your queries just want current data


  • Allows for current and historical data to be indexed independently


Cons

  • Multiple tables to keep in sync


  • Can make deployments more complex


  • Misaligned tables could cause code errors


  • Not ideal for Point In Time queries, better for current data and infrequent historical lookups


  • Needs composite Primary Key for historical data which mis-aligns with the dbo table.


Personally I like this approach simply for tracking history rather than for querying it. I think it lends itself well to an audit, but does make for confusing queries when you have to code everything to look at multiple tables when creating a report, for example. You could obviously hide the whole thing behind a view, but unless you use filters to partition your view you may find you don’t get any performance gains compared to using just one table with a date range as per my two previous posts.

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.