Chapter 22. Advanced Data Flow Transformations

In This Chapter

• The Slowly Changing Dimension Wizard and Transform (SCD)

• Data Cleansing Components

• Text Mining and Document Warehousing Components

"I KINDA THINK I WAS BEING A BIT VAGUE."

JEFF BERNHARDT

Business Intelligence is a broad and deep field with complex and technical requirements. Take a step back and just think about the Extract, Transform, and Load (ETL) processes for a minute. Think about how many steps it takes to get dirty, duplicate, and out-of-date data from the transactional store, clean it up, transform it into a different schema, aggregate it, deduplicate it, and all the other steps you might have before pushing it into a ROLAP or MOLAP store. Integration Services provides a broad set of features for addressing these steps. To this point, the tasks and transforms covered have been mostly focused around the transformation stage of the ETL and integration processes. This chapter focuses on those components that address some of the more advanced, esoteric, or ETL-specific processes. Some of these components, such as the Slowly Changing Dimension transform and the Fuzzy Lookup transform, are very important tools that require a little more coverage than the average stock component, and so they are covered separately in this chapter.

The components in this chapter fall into three categories.

• Slowly Changing Dimension Wizard and transform

• Data cleansing components

• Text mining and document warehousing components

The Slowly Changing Dimension Wizard provides a simple way to implement a solution to the common problem of how to update dimensions. The data cleansing components represent some cutting-edge algorithms for discovering similarities and groupings. The text mining and document warehousing components provide a way to work with nonformatted or semiformatted data. These are some of the features and components that move Integration Services beyond simple ETL. Also, excepting the Slowly Changing Dimension Transform, these components ship only in the Enterprise Edition of SQL Server.