Professional SQL Server™ 2005 Integration Services

6.10. Fuzzy Grouping

In the previous section, you learned about situations where bad data creep into your dimension tables. The blame was placed on the "lazy-add" ETL processes that add data to dimension tables to avoid rejecting rows when there are no natural key matches. Processes like these are responsible for state abbreviations like "XX" and entries that look to the human eye like duplicates but are stored as two separate entries. The occupation titles "X-Ray Tech" and "XRay Tech" are good examples of duplicates that humans can see but computers have a harder time with.

The Fuzzy Grouping transformation can look through a list of similar text and group the results using the same logic as the Fuzzy Lookup. You can use these groupings in a transformation table to clean up source and destination data or to crunch fact tables into more meaningful results without altering the underlying data. The Fuzzy Group transformation also expects an input stream of text. It also requires a connection to an OLE DB data source because it creates in that source a set of structures to use during the analysis of the input stream.

The Fuzzy Lookup Editor has three configuration tabs:

• Connection Manager: This tab sets the OLE DB connection that the transform will use to write the storage tables that it needs

• Columns: This tab displays the Available Input Columns and allows the selection of any or all input columns for fuzzy grouping analysis. See Figure 6-26 for a completed Columns tab.

  Each column ...