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. 

FailOnFixedAttributeChange property in SCD Transformation in SSIS

It is a Boolean value that determines whether the transformation fails when input columns designated as fixed attributes contain values different from their equivalents in the dimension table. This is equivalent to the Fail the transformation if changes are detected in a fixed attribute checkbox on the Fixed and Changing Attribute Options page of the Slowly Changing Dimension Wizard.

If we checked the check box then it will be true.  

If Uncheck then it will be false.

  

See the demo

If we check this box and dimension attribute is differ in that case our package will be fail.

See the DimCustomer and DimCustomer_STG table.

   

Now running this package.  

Our package gets failed.

Now we are unchecking the check box  

Running this package again.  

Package executed successfully.

Implementation of slowly changing Dimension Type-0 The Passive method (Ignore updates) using SSIS

In previous post we saw that how to handle the SCD Type 0. In this post we will see how to load the data into the DimCustomer table and send the notification to the business for the manual intervention.

Read: Implementation of slowly changing Dimension Type-0 ThePassive method (Ignore updates) using SQL script in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2022/05/implementation-of-slowly-changing.html

In the table taking Execute SQL task and doing the insert & ignore records in DimCustomer and inserting the records into the audit table.

Taking Execute SQL Task and doing the configuration  

Below is the SQL Script

--Inserting the records in the Audit table if any changes in PAN_Num or Aadhar_Num INSERT INTO audit_tbl             (app_nm,              audit_msg) SELECT 'DimCustomer Load' AS App_Nm,        'ID : ' + Cast(STG.cus_id AS VARCHAR(20))        + ', Cus_Nm : ' + STG.cus_nm + ', PAN_Num : '        + STG.pan_num + ', Aadhar_Num : '        + STG.aadhar_num   AS Audit_Msg FROM   dimcustomer_stg stg        LEFT JOIN dimcustomer dim               ON dim.cus_id = stg.cus_id WHERE  dim.pan_num <> stg.pan_num         OR dim.aadhar_num <> stg.aadhar_num;  --Inserting the records in the DimCustomer table if any new records comes INSERT INTO dimcustomer             (cus_id,              cus_nm,              pan_num,              aadhar_num) SELECT STG.cus_id,        STG.cus_nm,        STG.pan_num,        STG.aadhar_num FROM   dimcustomer_stg stg        LEFT JOIN dimcustomer dim               ON dim.cus_id = stg.cus_id WHERE  dim.dimcustomer_id IS NULL;

Now we are taking another Execute SQL task which stores the information about the Audit record and storing that in the Object variable. 

Now taking Script task to send the mail to the business.

Read: Process the non 0KB ( non Empty ) files and load thedata into database and filter the 0KB (Empty) files from the source Directoryand mail to business

https://bageshkumarbagi-msbi.blogspot.com/2019/01/process-non-0kb-non-empty-files-and.html

Below C# code we are using to send the mail to the business.

public void Main()         {             // TODO: Add your code here             string smtpAddress = "smtp.gmail.com";             int portNumber = 587;             bool enableSSL = true;             string emailFromAddress = "***************@gmail.com"; //Sender Email Address             string password = "*************"; //Sender Password             string emailToAddress = "**************@gmail.com"; //Receiver Email Address             string subject = "Request for PAN or Aadhar Number update for the Customer";             string body = "Hello Admin, we have request to update the PAN or Aadhar number update for below Customer Please have a look.";             DataTable dt = new DataTable();             var oleDa = new OleDbDataAdapter();             oleDa.Fill(dt, Dts.Variables["User::Audit_Rec"].Value);             if (dt.Rows.Count > 0)             {                 using (MailMessage mail = new MailMessage())                 {                     mail.From = new MailAddress(emailFromAddress);                     mail.To.Add(emailToAddress);                     mail.Subject = subject;                     mail.Body = body;                     mail.IsBodyHtml = true;                       mail.Body += " <html>";                     mail.Body += "<body>";                     mail.Body += "<table>";                     mail.Body += "<tr>";                     mail.Body += "<td>Customer details</td>";                     mail.Body += "</tr>";                     foreach (DataRow row in dt.Rows)                     {                         mail.Body += "<tr>";                         mail.Body += "<td>" + row.ItemArray[2].ToString() + "</td>";                         mail.Body += "</tr>";                     }                     mail.Body += "</table>";                       mail.Body += "Customer details are comma separated";                     mail.Body += "</body>";                     mail.Body += "</html>";                     using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))                     {                         smtp.Credentials = new NetworkCredential(emailFromAddress, password);                         smtp.EnableSsl = enableSSL;                         smtp.Send(mail);                     }                 }             }             Dts.TaskResult = (int)ScriptResults.Success;         }

Package looks like below 

Before running this package records in the table.  

Now running this package.  

Package executed successfully. See the records in the table.  

Also we are getting the mail for PAN and Aadhar update.  

Business will review this request and if needed then they will updated this records manually.