Type 6 SCD Example in SQL Server

Problem

In a data warehouse, one important concept is to retain historical data. This data is typically not available in operational systems. One approach in data warehouses is the use of Slowly Changing Dimensions (SCDs). What are the SCD options and are there any new approaches?

Solution

There are a few common types of SCDs and they differ based on how the attributes of dimensions behave when changes occur.

These are the common options:

  • Type 0 – Only the first version is retained. No further modifications are made.
  • Type 1 – Only the last version is retained. Previous versions will be overwritten.
  • Type 2 – When there is a change, a new record will be added per the current version. Previous versions will be set to previous versions and end-dated. This SCD is very common because it offers greater flexibility than other SCDs.
  • Type 3 – Only the previous version is maintained in the same row version as another column. The challenge in this technique is that only the previous version is kept.

Type 6 SCD

A Type 6 SCD is a hybrid approach that combines features of Types 1, 2, and 3 Slowly Changing Dimensions. It preserves historical records like Type 2 while also storing previous attribute values in the same row, as in Type 3. Although Type 2 SCD preserves full history, analytical queries often become complicated because historical records must be joined or ranked to identify previous or first values.

Type 6 SCD simplifies reporting by storing current, previous, and original values directly within the dimension table.

Sample Scenarios

Let’s look at a scenario regarding the employee dimension table in the HR domain. Employees’ designations change over time. During analysis, HR would want to compare the average ratings of employees promoted from Associate Software Engineer to Senior Software Engineer with those of employees promoted from Software Engineer to Senior Software Engineer.

Let us assume that the following is the Fact table.

CREATE TABLE FactEmployeePerformance (
   PerformanceSK INT IDENTITY(1,1) PRIMARY KEY,
   EmployeeSK INT NOT NULL,                    -- Foreign Key to Employee Dimension
   PerformanceRating DECIMAL(3,2) NOT NULL,    -- Fact Measures
   ProjectsCompleted INT,                      -- Additional Measures
   AttendancePercentage DECIMAL(5,2),          -- Additional Measures
   ReviewDate DATE NOT NULL                    -- Date Information
)

To find out the above data, Ratings are available as a measure in a Fact table and Employees data in the Employee Dimension. In this scenario, if you have Type 2 SCD, the previous information will be available in the previous record, as shown below.

query results

As mentioned in the scenario, to get the previous, you need to run the following query.

SELECT
    CurrentEmp.EmployeeName,
    PreviousEmp.Designation AS PreviousDesignation,
    CurrentEmp.Designation AS CurrentDesignation,
    CurrentEmp.StartDate AS CurrentDesignationStartDate ,
    Performance.[PerformanceRating]
FROM DimEmployee CurrentEmp
INNER JOIN DimEmployee PreviousEmp
    ON CurrentEmp.EmployeeName = PreviousEmp.EmployeeName
    AND PreviousEmp.EndDate = DATEADD(DAY, -1, CurrentEmp.StartDate)
INNER JOIN FactEmployeePerformance Performance ON CurrentEmp.EmployeeSK = Performance.EmployeeSK 
WHERE CurrentEmp.IsActive = 1;

Output:

query results

Similarly, there can be a scenario where I want to compare current employees with their first designation. The following complex query returns the necessary results.

WITH RankedEmployees AS (
    SELECT
        EmployeeSK,
        EmployeeName,
        Designation,
        StartDate,
        IsActive,
        ROW_NUMBER() OVER (
            PARTITION BY EmployeeName
            ORDER BY StartDate ASC
        ) AS FirstRank,
        ROW_NUMBER() OVER (
            PARTITION BY EmployeeName
            ORDER BY StartDate DESC
        ) AS CurrentRank
    FROM DimEmployee
)
 
SELECT
    FirstEmp.EmployeeName,
    FirstEmp.Designation AS FirstDesignation,
    CurrentEmp.Designation AS CurrentDesignation
FROM RankedEmployees FirstEmp
INNER JOIN RankedEmployees CurrentEmp
    ON FirstEmp.EmployeeName = CurrentEmp.EmployeeName
INNER JOIN FactEmployeePerformance Performance ON CurrentEmp.EmployeeSK = Performance.EmployeeSK 
WHERE FirstEmp.FirstRank = 1
    AND CurrentEmp.CurrentRank = 1;
query results

You see that both queries are complex. Given that a data warehouse is an analytical platform for people who make strategic decisions, it is very unlikely that they will execute such complex queries; they need a better solution. To solve this issue, a Type 6 SCD has been introduced.

Architecture of Type 6 Dimension Table

The following is the structure for the Type 6 SCD for the above Employee dimension scenario.

CREATE TABLE DimEmployee_Type6SCD (
    EmployeeSK INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT NOT NULL,
    EmployeeName VARCHAR(100) NOT NULL,
    CurrentDesignation VARCHAR(100) NOT NULL,  -- Current Information
    PreviousDesignation VARCHAR(100) NULL,     -- Historical Information
    FirstDesignation VARCHAR(100) NULL,        -- Original / First Designation
    StartDate DATE NOT NULL,                   -- SCD Tracking Columns
    EndDate DATE NULL,                         -- SCD Tracking Columns
    IsActive BIT NOT NULL                      -- SCD Tracking Columns
);
type 6 scd results

Querying Type 6 Dimensions

Unlike earlier Type 2 SCD cases, querying in Type 6 SCDs is much simpler.

SELECT EmployeeName,
       CurrentDesignation,
       PreviousDesignation,
       FirstDesignation, 
    Performance.PerformanceRating
FROM DimEmployee_Type6SCD Employee INNER JOIN [dbo].[FactEmployeePerformance] Performance On
Employee.EmployeeSK = Performance.EmployeeSK
WHERE IsActive = 1

The above simple query will return all employees with the necessary details as shown below.

type 6 scd results

If you wish to query a specific designation, querying is much simpler as shown below.

SELECT EmployeeName,
       CurrentDesignation,
       PreviousDesignation,
       FirstDesignation 
FROM DimEmployee_Type6SCD
WHERE PreviousDesignation = 'Associate Software Engineer' 
AND CurrentDesignation = 'Senior Software Engineer' 
AND IsActive =1

Advantages of Type 6 SCD

Even though Type 2 SCD is the most common usage, Type 6 SCD is commonly used when you need compare same dimension attribute that was changed.

An obvious advantage of the Type 6 SCD is the improvement in analytics through simple queries, while maintaining the flexibility of the Type 2 SCD. Apart from the given scenario on employee’s ratings, there are few other cases where Type6 SCDs are employed. When customers risk levels change from Low to Medium and High. In the academic world, when you want to analyze academic qualifications, such as Undergraduate, Masters and Doctorate.

Limitations and Considerations of Type 6 SCD

The main disadvantage of Type 6 SCD is the increased complexity in the ETL process. Additional logic is required to maintain current, previous, and original values consistently. The table may also contain some redundant data because historical attributes are repeated across multiple rows. Considering that the ETL is a daily process, this will not have much impact.

Following is the T-SQL to populate Type 6 SCD:

INSERT INTO DimEmployee_Type6
(
    EmployeeID,
    EmployeeName,
    CurrentDesignation,
    PreviousDesignation,
    FirstDesignation,
    StartDate,
    EndDate,
    IsActive
)
SELECT
    d.EmployeeID,
    d.EmployeeName,
    s.Designation AS CurrentDesignation,  -- Current Value
    d.CurrentDesignation AS PreviousDesignation,  -- Previous Value
    d.FirstDesignation,  -- Preserve Original Value
    GETDATE(), -- SCD Tracking
    NULL,
    1
FROM DimEmployee_Type6 d
INNER JOIN StgEmployee s
    ON d.EmployeeID = s.EmployeeID
WHERE d.IsActive = 0
    AND s.Designation <> d.CurrentDesignation;

However, when considering the Type 6 SCD, you need to consider which attributes can be considered for Type 6 SCD. You should not implement Type 6 SCD for all the attributes in a Dimension table. Also, you can later extend the Type 6 SCD to include any required attributes, as all the required data is in the Dimension table. You need to consider dimension attributes that are changing over time. If there are attributes that are changing in a rapid nature, you should not consider it for Type 6 SCD.

Summary

This article demonstrated how Type 6 Slowly Changing Dimensions can simplify analytical reporting while preserving historical data. By combining the strengths of Type 2 and Type 3 SCDs, Type 6 provides a practical approach for scenarios that require both historical tracking and simplified querying.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *