SSIS Slowly Changing Dimension (SCD) Part –II

Introduction

In my previous article we try to demonstrate the concept of SSIS Slowly Changing Diagram with example of Changing Attributes (Type-1) example.

http://www.sqlknowledgebank.blogspot.in/2013/08/ssis-slowly-changing-dimension-scd-part.html

In this article we are trying to demonstrate the Type -2 that is Historical Attributes.

What is Historical Attributes

Recalling from our previous article:

Here we need to maintain the history of the records, where some particular value changes. Here we take an example to understand the Type-2.

Here the employee gets promoted, so the job title of the employee changes. In this case we need to maintain the history of the employee. For this type of changes there will be multiple records for the same employee with different job title. Then to indentify the current records, we can either add a column as current flag, which will be 'Y' for the current or latest records, Or else we can add two columns as START_DATE and END_DATE, through which we can maintain history of employee's records. This SCD directs these rows to two outputs: Historical Attribute Inserts Output and New Output. 

EMPID	FIRST_NAME	JOB_TITLE	START_DATE	END_DATE	CURRENT
101	SUBAL	GRADE-C	01-01-2010	01-01-2011	N
101	SUBAL	GRADE-B	01-01-2011	01-01-2012	N
101	SUBAL	GRADE-A	01-01-2012	01-01-2099	Y

How to configure the Type-2 (Historical Attributes)

Step -1 [ Creating Tables ]

Here I am creating two tables with same structure. The table one is tbl_Employee_Details wich contains the original employee records and tbl_Employee_Details_Change which contains the records need to be changed to the tbl_Employee_Details table objects.

Here the three columns are important CURRENT_FLAG, STARTDATE, ENDDATE. In the tbl_Employee_Details_Change contains the Job titles changes of the employee.

-- Base Table

IF OBJECT_ID(N'dbo.tbl_Employee_Details', N'U') IS NOT NULL

   BEGIN

      DROP TABLE dbo.tbl_Employee_Details;

   END

GO 

CREATE TABLE dbo.tbl_Employee_Details

(EMPID           INT,

 FIRST_NAME      VARCHAR(50),

 MIDDLE_NAME     VARCHAR(50),

 LAST_NAME       VARCHAR(50),

 DOB             DATETIME,

 NATIONALITY     VARCHAR(50),

 JOB_TITLE       VARCHAR(20),

 MARITAL_STATUS  CHAR(1),

 EMPLOYEE_GENDER CHAR(1),

 CURRENT_FLAG    INT,

 STARTDATE       DATETIME,

 ENDDATE         DATETIME DEFAULT('01-01-2019'));

            

GO

-- Table for Changing Records

IF OBJECT_ID(N'dbo.tbl_Employee_Details_Change', N'U') IS NOT NULL

   BEGIN

      DROP TABLE dbo.tbl_Employee_Details_Change;

   END

GO 

CREATE TABLE dbo.tbl_Employee_Details_Change

(EMPID            INT,

 FIRST_NAME       VARCHAR(50),

 MIDDLE_NAME      VARCHAR(50),

 LAST_NAME        VARCHAR(50),

 DOB              DATETIME,

 NATIONALITY      VARCHAR(50),

 JOB_TITLE        VARCHAR(20),

 MARITAL_STATUS   CHAR(1),

 EMPLOYEE_GENDER  CHAR(1),

 CURRENT_FLAG     INT,

 STARTDATE        DATETIME,

 ENDDATE          DATETIME DEFAULT('01-01-2019'));

GO      

    

Step – 2 [ Inserting Records ]

-- Inserting Recors in Base Table

INSERT INTO dbo.tbl_Employee_Details

       (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,   

        DOB, NATIONALITY, JOB_TITLE, MARITAL_STATUS,

        EMPLOYEE_GENDER, CURRENT_FLAG, STARTDATE)

VALUES (101, 'SUBAL', 'CHANDRA', 'DAS',

        '12-07-1945', 'INDIAN', 'GRADE-B', 'M',

        'M', 0, '01-01-2010'),

        (102, 'JOYDEEP', NULL, 'DAS',

        '12-17-1974', 'INDIAN', 'GRADE-B', 'M',

        'M', 0, '07-01-2010');

-- Inserting Recors for Change

INSERT INTO dbo.tbl_Employee_Details_Change

       (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,   

       DOB, NATIONALITY, JOB_TITLE, MARITAL_STATUS,

       EMPLOYEE_GENDER, CURRENT_FLAG, STARTDATE)

VALUES (101, 'SUBAL', 'CHANDRA', 'DAS',

        '12-07-1945', 'INDIAN', 'GRADE-A', 'M',

        'M', 1, '08-01-2012');          

Step – 3 [ SSIS Data Flow ]

Step – 4 [ OLE DB Source Configuration ]

Step – 5 [ Slowly Changing Dimension Wizard Configuration ]

Step – 6 [ Run the SSIS Package ]

Step – 7 [ Observation ]

SELECT * FROM tbl_Employee_Details;

Hope you like it.

Posted by: MR. JOYDEEP DAS