Implementation of slowly changing Dimension Type-1 Overwriting the old value using SCD Transformation in SSIS

Using this approach we are updating the existing records with updated value without maintaining any history. Insert a new record into the dimension table if that record does not exist in the dimension table. It is very easy and saving huge amount of space because we are not maintaining any history.

For example

In the customer dimension below are the records

ID	Name	PAN_Number	Aadhar_Number
1	Bagesh Kumar Singh	12345	44444
2	Mahesh	22222	66666

Now are getting below records in the sating table to update the PAN  and Aadhar number in the customer dimension

ID	Name	PAN_Number	Aadhar_Number
1	Bagesh Kumar Singh	55555	33333
3	Suresh	77777	88888

Now our Customer dimension will be like below. PAN and Aadhar number are not updated for the customer ID 1.

ID	Name	PAN_Number	Aadhar_Number
1	Bagesh Kumar Singh	55555	33333
2	Mahesh	22222	66666
3	Suresh	77777	88888

In the above load we get one existing ID with updated value and one new record. Here is not mainlining any history. If anyone want to know, what was the Pan or Aadhar number of Bagesh (ID: 1) previously. We can’t get it. In this approach only we are getting latest value in the dimension table.

We are using such approach where we know that no historical information is required in the Data ware house and reports.

Advantage of Type 1

·         This is the easiest way to handle the SCD problem.

·         It is saving Hug amount of spaces in the database because we are not saving the Historical information of the dimension.

Disadvantage of Type 1

·         We are not having the historical information. In case someone updating the dimension records we may not able to recover the updated information.

 

Let’s see the implementation of SCD Type 1 using SCD Transformation in SSIS.

Taking a Data Flow task and taking source as OLEDB and doing the configuration.

 

Now we are taking SCD Transformation. Taking the destination table and doing the configuration and selecting the Business key. 

Click next button.

  

Selecting the Dimension columns and Changes type as Channing attribute.

Click on the next button 

Click on next button  

Click on next button.

Click on the finish button. 

Our packages look like below     

Our package is ready to run. Before running the package record in the table.

 

Now

running this package.

Now see the records in the table. 

Records are inserted and updated successfully.