Slowly Changing Dimension & SSIS
SQL Server Integration Services (SSIS)
Slow Change Dimension & Visual Studio
The Slowly Changing Dimension (SCD) feature in SQL Server Integration Services (SSIS) is used in data warehousing to manage changes in dimension tables over time. It helps track historical data while maintaining current data, ensuring that changes in dimension attributes are handled properly.
Purpose of the SCD Feature in SSIS for Data Warehousing:
Handling Changes in Dimension Data
- In a data warehouse, dimension tables contain descriptive attributes (e.g., customer name, address, product details).
- When these attributes change, SCD helps decide whether to update, insert, or archive the existing data.
- Maintaining Historical Data
- Some business requirements demand historical tracking of dimension changes.
- Example: If a customer changes their address, the warehouse may need to track their old address for reporting purposes.
Ensuring Data Integrity in Fact Tables
- Fact tables reference dimension tables using surrogate keys. If dimensions change, SCD ensures that fact records remain consistent with the correct version of the dimension data.
Automating Change Detection & Handling in SSIS
- The SCD Wizard in SSIS simplifies the implementation of SCD by automatically detecting changes and applying the chosen update strategy.
Testing Queries for DimEmployee
select * From AdventureWorksDW2019.dbo.DimEmployee;
select *
From AdventureWorksDW2019.dbo.DimEmployee
WHERE ParentEmployeeKey =46
Order By StartDate DESC;
Types of Slowly Changing Dimensions (SCD) in SSIS
SSIS supports three primary types of SCD:
Type 1 (Overwrite the Old Data) – No Historical Tracking
- Updates the dimension record with the new values.
- No historical tracking.
- Example: A customer corrects a typo in their name.
Type 2 (Track History with New Row) – Full Historical Tracking
- Inserts a new row with a different surrogate key while keeping the old row.
- The old row is marked as inactive (with an
EndDateorIsActiveflag). - Example: A customer changes their address, and reports need to reflect both the old and new addresses.
Type 3 (Track Partial History with a New Column)
- Adds a new column to store the previous value while updating the current value.
- Tracks only one previous change.
- Example: Storing both the “Current Region” and “Previous Region” for a sales representative.
Conclusion
In this video, we explored how to implement Slowly Changing Dimensions (SCD) in SSIS using Visual Studio, focusing on the DimEmployee table. We covered SCD Type 1, Type 2, and Type 3, the importance of using a Business Key (EmployeeNationalIDAlternateKey), and how to track historical changes effectively in a data warehouse.
By following this tutorial, you’ve gained the skills to configure SSIS SCD transformation, manage employee record updates, and maintain historical data accuracy—key components of a robust ETL and BI strategy.
If you found this video helpful, don’t forget to like, subscribe, and leave a comment with your questions or suggestions for future SSIS tutorials. Thanks for watching, and happy coding! 🚀💡
Lecturus is a platform that offers training to individuals interested in developing or enhancing their computer skills, as well as a career change or advancement.
Get In Touch
147 Prince St, Brooklyn, NY 11201
- Email: lecturus@outlook.com
- Phone: 929-280-7710
- Hours: Mon-Fri 9 AM - 5 PM
Lecturus is a platform that offers training to individuals interested in developing or enhancing their computer skills, as well as a career change or advancement.
Get In Touch
147 Prince St, Brooklyn, NY 11201
- Email: lecturus@outlook.com
- Phone: 929-280-7710
- Hours: Mon-Fri 9 AM - 5 PM