Implementation of slowly changing Dimension Type-0 The Passive method (Ignore updates) using SQL script in SQL server

Using this approach we are not going to update any records even we are getting any changes values. If we are getting new record in that case we are inserting the new rows in the dimension table. When we are loading the data into the dimension table using ETL in that case if we are getting any updated record in that case either we are failing our ETL or we are redirecting that row to some other destination for the manual intervention.

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	12345	44444
2	Mahesh	22222	66666
3	Suresh	77777	88888

 

For the Customer ID 1 that row is redirected either Audit table or Audit file for manual intervention.

ID	Name	PAN_Number	Aadhar_Number
1	Bagesh Kumar Singh	55555	33333

Business can update this value manually.

Creating 3 tables as below.

CREATE TABLE DimCustomer_STG( DimCustomer_STG_ID int identity(1,1) not null primary key, Cus_ID int not null, Cus_Nm varchar(50) null, PAN_Num varchar(12) null, Aadhar_Num varchar(20) null ) CREATE TABLE DimCustomer( DimCustomer_ID int identity(1,1) not null, Cus_ID int not null primary key, Cus_Nm varchar(50) null, PAN_Num varchar(12) null, Aadhar_Num varchar(20) null ) CREATE TABLE Audit_tbl( Audit_ID int identity(1,1) not null, App_Nm varchar(100) null, Audit_Msg varchar(4000) null, created_dt datetime default getdate(), created_user varchar(50) default current_user )

Now we are inserting some records into the DimCustomer_STG table

insert into DimCustomer_STG (Cus_ID,Cus_Nm,PAN_Num,Aadhar_Num) values (1,'Bagesh Kumar Singh','12345','44444'); insert into DimCustomer_STG (Cus_ID,Cus_Nm,PAN_Num,Aadhar_Num) values (2,'Mahesh','22222','666666');

Now we are writing the SQL script to load the data as below.

Ø  cus_id not exist in the DimCustomer table then we need to insert the record into the table.

Ø  cus_id is exist in the DimCustomer and PAN_Number or Aadhar_Number  changes then we are not updating the existing records. In this case we are inserting the records into the Audit table for the manual intervention.

--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;

Before running this script records in the tables.  

Let’s run the script 

2 new records are inserted into the DimCustomer table. 

Now in the stage table 2 records coms one or existing customer and other is new customer.

Truncating the stage table and loading the new data into it.

TRUNCATE TABLE dimcustomer_stg   INSERT INTO dimcustomer_stg             (cus_id,              cus_nm,              pan_num,              aadhar_num) VALUES      (1,              'Bagesh Kumar Singh',              '55555',              '33333');   INSERT INTO dimcustomer_stg             (cus_id,              cus_nm,              pan_num,              aadhar_num) VALUES      (3,              'Suresh',              '77777',              '88888');

 Data in stage table.  

Now running the above script.  

One record is inserted into the Audit table and one record is inserted into the DimCustomer table.  

In stage we are getting request to update the PAN_Number and Aadhar_Number but is not updated in the DimCustomer table. That record is inserted into the Audit table for Manual intervention.