42 Slowly Changing Dimension in SSIS

hi friends my name is akil ahmad i'm a senior software engineer in this particular video tutorial i'll be talking about slowly changing dimension in ssis so the agenda of today's video tutorial is how to use slowly changing dimension for incremental data load in ssis [Music] there are actually three types of slowly changing dimensions those can be used for incremental data load the first one is acid type one which is also called as the changing attribute so the scd type one is used to overwrite the existing data in the destination table with the data from the source table so in this particular case we don't give any history for the existing data we actually override the existing data the second one is the std type 2 which is also called as the historical attribute so the std type 2 is used to maintain the history of the existing data along with the new data in the dimension table or we can say as the destination table so in this particular case we can insert a new record for sld type 2 and we can use the started and ended in dimension table to identify the active record so a null value in the end date column actually denotes the active records so we will see this in the demo and then the third one is the std fixed attribute so the lcd fixed attribute is used if you want not to change the data for a column over a period of time so then we can use the scd fixed attribute so for example if you want a column to remain static over a period of time then we can use the sct fix attribute for such cases so we will see all these three types in the upcoming demo in this video tutorial we will be using emp underscore source table as the transactional or source table that contains data that we will be using to update or insert to the dim employee or the destination table so emp source is the source table that contains emp id which is business key and which can be linked to the emp id in the destination table and there are columns like first name last name designation so we will be inserting around five records in the source table and then you know we will be modifying these records to see like how the acidity type one two and historical attribute will work okay so emp id in both tables is the business key or you can see surrogate key last name will be actually changing type attributes so we will be modifying the last name data so it should be updated in the destination column and the designation will be the sad type 2 which means the historical attribute so whenever we will update the designation for a particular employee in the source table a new record should be inserted to the employee table and then we will be using the start date and end it to identify the most recent or the active record so let's jump to the demo so this is my sql server management studio and we will be dropping and recreating the emp source table which contains around four records employee id first name last name and designation and then we are inserting five records to the emp source table which is a transactional table and then we will be inserting data to the dim employee table which contains the same columns four columns from source along with id as an identity column and then started and ended column those will be used to identify the historical records for std type 2 so in the sct type 1 we will update the person's last name in the source table so it should be overwritten in the destination table as it is and there should not be any historical record kept in this particular case and then in sct type 2 if you will update the designation of a column then a new record should be inserted to the to this particular table and then we will be using the start date and end date to maintain the historical record so we will see how we will be doing that in ssis so let's jump to the sql server data tools to create the size package so this is my sql server data tools 2013 that i will be using to create the ssis package and now let's drag and drop the data flow task into the control flow window and double click data flow task to configure it as we are going to the data from this emp source from the transactional table so let's drag and drop the otp source into the data flow task and then double click on db source to configure it from the table or view select the emp source table which contains these data file records click close okay and now let's drag and drop the slowly changing dimension into the data flow task and now connect the otp source with the slowly changing dimension double click slowly changing dimension to configure it click next we start configuring the slowly changing dimension so under the table or view we actually need to select the dim employee table which is the dimension table or the destination table so from under table or view select the dim employee table so in this particular window we just need to select the business or surrogate so employee id is the business key in this particular case so we will mark it as business key and then we can leave the rest of the columns as it is and now click next and now under this particular window actually we need to select what are the std type one columns and what are the city type 2 or historical columns okay so the first column the last name column will be a changing attribute the sad type 1 column which can be used to update the existing data in the destination or dimension column so let's select the last name as the changing attribute which is the sct type one so whenever we will update the value of last name in the source table it will actually overwrite in the destination table or the dimension table the second one sad type 2 which is a historical attribute the designation is the historical attribute so let's select the historical attribute for designation so whenever the value of designation will be changed in the source table it will insert a new record in the dimension table and then it will update the end date with the current date and will insert a new record with the null value as the end date so in null value in the end date column it actually represents the active records and then we can mark the first name as the fixed attributes so the fixed attribute record should not change over a period of time so for example a person's first name should not be changed however the last name can change if a person for example if a girl got married so she can you know change her last name and the designation of employee can be changed if he got promoted to the you know new designation however the first name should be static and it should not change over a period of time so we can mark first name as the fixed attribute so now click next under the fixed attribute fill the transformation if changes are detected in a fixed attribute so for example if you want that if someone change the value in the first name then the transformation should fail because we don't want the first name to be changed over a period of time so we can leave the tip as it is then now click next and then to maintain the historical attribute we can use the start date column that we created in the destination column this one is started and ended so we can use these columns here we started column and then ended and then variable to set the date values so we can select system started time ssis variable to insert the current date whenever a new record will be inserted in in the case of historical attribute and now click next and we can you know disable this particular case in this particular example now click next now click finish so we are done with configuring the slowly changing dimension type 1 type 2 and the fixed attribute as well so now you know if you run this particular query select restart from employee source and dimension table so right now the div employee the destination table is empty as we have not inserted any record to this particular table yet so if we will execute the size package so it will actually insert all records from the source table into the destination table because you know because right now the destination table was empty so it has copied actually all records from source table to the destination table so if you will execute this particular query so you can see that the records from source table have been copied to the destination table along with the started values the current date values inside the start date and the ended values in the list of now which means all records are the active records so now let's test the lcd type one so the std type one is the changing attribute so for example in this particular source table there is a record jyoti singh so suppose jyoti singh got married to this defender so uh the last name can be updated of from sim to binary because uh she changed her last name so let's update this particular record employer d104 so now if you redone the query so now you can see that in the source table the last name of employee id 104 has been changed from sim to bandari but in the destination table it is still saying so you know if you will rerun the ssis package so it should update the record in the destination table as well so now if you will rerun this particular query so you will notice that the value of the fundary has been updated for this record employee id 104 so this is acd type one changing attribute when a if a record got changed from the source table then it will be updated to the destination table as well and then the std type 2 is the historical attribute so for example if i record uh in the source table one zero and rc for example if it if this person got promoted from dba to senior dba so we should insert a new record in the destination table and then you know expire this record here and that we should mark it and get here then and then insert a new record with all the values from here and the designation should be changed to senior dba so let's update the record in the source table [Music] and now if you will rerun the select query here so you can see that this person's designation got changed from deep dba to senior dba so you know if you will re-execute the ssis package so a new record should be inserted to the destination table so let's rename okay and now if you will re-execute the select query here so if you will see that the ashish record got expired here the earlier record because we updated the android value here but we also inserted a new record for him 101 and the new records designation is senior dpi and it has the and it has null value so it means that this is the most decent and the active records for aashish so this is the sct type 2 historical attribute so we are giving a history of this particular person in scd type 2 the third one is the scdfx attribute so it means that we cannot modify the value of the first name for a particular record so for example uh for one zero two value for kerdi barber course if you are going to modify the value of first name so it should not be changed in the destination column and according to the current configuration the exercise bracket should fail because we cannot modify the value of first name so let's see if a change happen in the first name column for employee id 102 so then ssis package should actually fail so we have updated the value of 102 from kodi to abhishek and now let's try to execute the slice package again so if you will click on the start button so you can see that the slowly changing dimension transformation got failed and if you try to rerun the query so you will see that no change should be happening in the destination table so for one zero two the the employee first name is not changed because it's a fixed attribute so it cannot be changed so what we can do is you know if uh such kind of scenario happens in the in future so if you want that our exercise package should should run successfully it should not get filled but it should not update the value of the first name so what we can do is just modify the slowly changing dimension and then we can simply remove this stick fill the transformation if changes are detected so we can just uncheck this and then click then you know save the ssis package then we have saved the slowly changing dimension transformation so now what will happen is if you will re-execute the ssis package so the slash package will remain successfully however it won't do anything so now you can see that even the value in the first name column in the source table has been changed to appreciate but the value in the destination table inside the first name is still the old one because the first name is the fixed attribute and the rule says that the fixed attribute should not change over a period of time so guys this is how we can use the slowly changing dimension type 1 for changing attribute slowly changing dimension type 2 for historical attribute and then slowly changing dimension fixed attribute in the exercise packages thanks guys for watching the video and if you like the video then please click the like button and do subscribe to our channel to see more videos on ssis and other related technologies thank you so much

Channel: Learn SSIS

Views: 29,094

Rating: 4.9460917 out of 5

Keywords: Slowly Changing Dimension in SSIS, Slowly Changing Dimension, How to use Slowly Changing Dimension in SSIS, Using Slowly Changing Dimension in SSIS, Incremental data load in SSIS, Incremental data load, SCD in SSIS, SCD, How to use SCD in SSIS, How to use SCD, slowly changing dimension, ssis, how to use slowly changing dimension in ssis, slowly changing dimension type 2

Id: OTmSBhiqA5c

Length: 13min 58sec (838 seconds)

Published: Sun Sep 24 2017