Data Flow->> fuzzy Lookup & Fuzzy Grouping

The role of these two tasks is data cleansing (Cleansing).

Fuzzy Lookup makes similar value matching by referencing another database table or index. This component is useful for standardizing and locating client data that may be wrong. For example, a property field like an address or a city name is useful.

The Fuzzy lookup will not only output its matching values, but also output similarity and confidence two attribute columns. Similarity uses a floating-point value between 0 and 1 to indicate the similarity between matching pairs. For example, the similarity between Jerry Chan and Jerry Chen may be 0.89. For confidence, the higher the value, the less the matching pair it can choose.

The Fuzzy Lookup has 4 options to configure the reference table (Reference table):

1) Generate New index: Create a temporary index in memory based on the reference field of the referenced table for data matching, delete the task after it is completed;

2) Generate new Index + Store new index option: equivalent to establishing an index in the database;

3) Generate new Index + Store new index OPTION + maintain Stored index option: In this case the Maintain Stored index option will build a trigger on the reference table to catch the update to Synchronize updates to the newly created index;

4) Use Existing Index

Fuzzy Lookup Transformation: Capable of joining to external data based on data similarity,
The Fuzzy Lookup transformation is a core data cleansing tool in SSIS. This transformation
is perfect if you have dirty data input so want to associate to the data in a table in your
Database based on similar values. Later in the chapter, you'll take a look at the details of the
Fuzzy Lookup transformation And what happens behind the scenes

Fuzzy Grouping Transformation: The main purpose is de-duplication of similar data. The
Fuzzy Grouping Transformation is ideal if you had data from a single source and you know
You have duplicates the. Need to find.

Data Flow->> fuzzy Lookup & Fuzzy Grouping