In this post, we will demonstrate the Slowly Changing Dimension (SCD) transformation that is available for use in a Data Flow task within SQL Server Integration Services (SSIS). We’ll walk through an example of how to use it, explaining the terms as we go. In addition, I’ll demonstrate some “extra effort” kind of modifications that can be made for a more thorough usage. After that, we’ll discuss the pros and cons of using it at all. And we’ll wrap up with some tips on how to tune the transformation for better performance.
Data warehousing is a big subject. This overview is intended to cover some of the most representative issues on a high level: the nature of OLAP systems, star schemas, facts and dimensions, and differing perspectives (Inmon vs. Kimball) on warehouse design.
OLTP vs. OLAP
OLTP systems are the operational databases supporting applications. They are highly normalized, and focused on CRUD operations.
OLAP databases are usually arranged in star schemas and are built for speed in retrieving aggregated data.