You are sales manager of North-East region in 2010 to Oct 23 2011 and did total sales of 1 million. The table that stores your information looks like….
Dim Sales Person
Sales Person Key =99
Sales Person Name = Tony Peterson
Sales Person Designation = Sales Manager
Sales Region = North-East
Phone = 203 203 9999
Date Of Birth = 9/9/1977
Sales fact
Transaction Date Key
Sales Person Key
Product Key
Product Qty
Product $Amount
As of Oct 23 management asks for the sales amount by sales person and region. The result would look like: Tony Peterson, North-East, and 1 Million.
From 24th Oct 2012 there is change and you have been transferred to South-East region. So some IT guy updated the sales region in Sales Person table to South-East.
Sales Person
Sales Person Key =99
Sales Person Name = Tony Peterson
Sales Person Designation = Sales Manager
Sales Region = South-East
Phone = 203 203 9999
Date Of Birth = 9/9/1977
As you do your sales next few month you did a sales of .4 million in South-East. On Jan 2012 management again asked for the sales figure by employee and region. What will the report look like?
Tony Peterson, South-East, and 1.4 Million.
Is the report correct? The answer is obvious total sales figure is incorrect for South-East as it will show 1.4 millions. You only did .4 million in the South-East and not 1.4. The reason it is incorrect is due to the fact that somebody updated the dimension record with the current region.
Now that we know the problem and exact cause of it, we need to look for a solution. By this time most you will say …this is no big deal it’s a classic issue of slowly changing dimension. Let me create a type II dimension… So I can preserve history of transfer.
Dim Sales Person II
Sales Person Key =99, 100
Sales Person SSN=000-00-9999, => 000-00-9999
Sales Person Name = Tony Peterson, => Tony Peterson,
Sales Person Designation= Sales Manager, => Sales Manager
Sales Region = North-East, => South-East
Phone = 203 203 9999,=> 203 203 9999
Date Of Birth = 9/9/1977, => 9/9/1977
Now since there are two records for same sales person you can join with sales facts and you can separate the regions. The correct facts are as follows.
Tony Peterson, North-East, and 1.0 Million
Tony Peterson, South-East, and 0.4 Million
Observe the Type II dimension carefully. You will note that
– The key identifier (PK) for the Sales person will keep changing. Note it changed from 99 to 100.
– Instead of having 1 one row per salesman now it has two or even more with time.
– The Sales Region is time variant fact about the sales person
– We are indeed storing time variant facts in type II dimension.
So Type II dimension is really more of a fact table. So is storing time variants facts a good design at all? Why not store the facts in a fact table? Read other articles on pitfalls of Type II dimension.