SSIS advanced conversion task-fuzzy search

Bi projects often have some work of extracting, transforming, and processing data (ELT). The most important thing is to process stolen data. Suppose we inject test data into the database in the project, but there is no corresponding data when loading data from another table through a foreign key, then this row is the dirty data. In this case, the sound-Ex, full-text, and similarity in SQL can be used.Algorithm. This strategy requires a lot of time and effort to design algorithms, test and maintain them, and they are all vocabulary-based, with little possibility of reuse. You may also give up on your own processing and throw it to some experienced experts to do the work, or you may add some new data in the table to achieve the goal of external Association, however, this method is called Lazy-add ). Because manual data addition may lead to spelling errors, for example, writing the title name President incorrectly into a parallel parsedent, incorrectly writing further as a future, and incorrectly writing the present as a parent.

Fuzzy search and fuzzy grouping provide a new method to process such stolen data. This type of conversion makes data processing simple, compatible, scalable, and reusable, which can significantly reduce errors. If your table contains stolen data or you start to process the data, you will use fuzzy grouping to find redundant data. Fuzzy grouping analyzes a column of data in the table and sums up similar data. Assuming that they are misspelled by a word, the similarity between them is calculated, the data with this similarity can be used to clean the data in the table. Fuzzy grouping will be described in the next article.

If you use fuzzy search to correct data, we recommend that you use keyword search first because fuzzy search is very resource-consuming. It creates an index in the data table and the reference table. These indexes can be saved, but this will consume disk space and valuable memory resources during operation. Fuzzy search is a good strategy to obtain a keyword list. Use keyword search to find the keyword with a high frequency, and use the inner connection to fuzzy search to exceed the matching item. If some data does not match, it will be marked as unknow.

Fuzzy search requires at least one column in the input stream to be a string, which is somewhat different from keyword extraction. keyword extraction requires a null-terminated Unicode string. Fuzzy search also needs to be connected to a foreign key table as a reference. The output columns of fuzzy search are as follows:

• Input data: the data contains the data in the input stream and the data that needs to be passed in fuzzy search.
• Reference Table Data: The data contains the data in the reference table.
• Similarity: the data in this column is a floating point number between 0 and 1. It is used to describe the degree of similarity. 1 indicates that the matching is successful.
• Confidence: This column of data is a floating point number between 0 and 1, used to describe the degree of trust of the matching. Confidence is another form of similarity, which is obtained through one-to-one comparison rather than one-to-many comparison. It can obtain more accurate data.

Three tags are available on the fuzzy search editing page.

• Reference Table: Set an ole db connection to the reference table in this label. Before fuzzy search, create an index based on the data in the table. In this tag, you can choose to save the index or use the index saved during the previous run, and maintain the current index. This will delete the previous index and save the index obtained during the current run. Note that if the amount of data processed is large, the index will also become large.
• Column: Set the ing between columns in the input data stream and one column in the reference table in this label. Drag and Drop the mouse to connect them. You can also add a foreign key column in the output data stream. You only need to select this column from available input columns. 1 is the configured Interface
  
  Figure 1

• Advanced: Set the search algorithm in this tag. Maximum number of matches to output per lookup sets the maximum number of matching data in each row. The default value is 1. If the value is greater than this value, more rows will be generated in the result. However, this setting is still required if there are many similar data in the input stream. Similarity threshold is used to set similarity thresholds. Token delimiters is used to set character delimiters. The default Delimiter is a common string delimiter, 2
  
  Figure 2

Although fuzzy search uses some simple settings to implement complicated logic, the results page is not perfect. It takes some time to observe the different results produced by different settings.

The following is an exercise. This exercise is used to display the fuzzy search function. It uses a reference table containing the position name and then enters the individual information table. Of course, the personal information contains the position of the person, but not all of them can match the reference table. Fuzzy queries will find a match and then view the matching results in the target table.

1. Use the following content to create a text file named c: \ import \ empdata.txt. The table contains personal information. We can see that the data in this table is uneven, which is common in the ETL process.

   Empidtitle lname
   00001 executive vice presidenwashington
   00002 exec Vice Pres pizur
   00003 Executive VP Brown
   00005 exec VP Miller
   00006 executive vice prasidenswami
   00007 fields operation Mgr sky
   
   00008 FLDS ops Mgr Jean
   00009 fields ops Mgr Gandi
   00010 fields operations managhinson
   00011 business office managerbrown
   00012bus office manager green
   00013bus off Manager gates
   00014bus off Mgr Hale
   00015bus office mngr Smith
   00016bus office Mgr AI
   00017x-Ray exist ist chin
   00018 Xray snapshot ist abula
   00019 Xray tech Hogan
   00020x-Ray tech Robert son
    

2. Use the following statement to create a reference table in the database adventureworks.

    Create     Table     [  Occupation  ]  (
     [  Occupationid  ]     [  Smallint  ]     Identity (  1  ,  1  )  Not     Null  ,
     [  Occupationlabel  ]     [  Varchar  ]  ( 50  )  Not     Null  
     Constraint     [  Pk_occupation_occupationid  ]     Primary     Key     Clustered 
   (
     [  Occupationid  ]     ASC  
   )  On     [  Primary  ]  
   )  On     [  Primary  ] 
     Go  
     Insert     Into     [  Occupation  ]     Select     '  Exec Vice Pres  '  
     Insert    Into     [  Occupation  ]     Select     '  Fields ops Mgr  '  
     Insert     Into     [ Occupation  ]     Select     '  Bus office Mgr  '  
     Insert     Into     [  Occupation  ]     Select    '  X-RAY tech  ' 

4. Create a package named fuzzylookupexample. Drag a Data Flow task and double-click it to enter the control flow design interface.
   
6. Create a flat file connection named "Employee data", set the data source to c: \ import \ empdata.txt, set the format attribute to ragged right, and select column names in the first data row. 3. Click the columns tab, select 5th rows and 28th rows in the source data columns text box, and click 4. Click the Advanced tab to set the Data Length of the title column to 50 or 5.
   
   Figure 3
   
   Figure 4
   
   Figure 5
   
8. Add a flat file data source and connect it with the employee data. Add an ole db destination select adventureworks Database
   
10. Add a fuzzy lookup to connect the flat file datasource with it, and then connect it with ole db destination
    
12. Double-click the Edit page of fuzzy lookup. On the reference tag page, set it to occupation table 6 in the adventureworks database, in the columns label, connect the title column in the input table with the occupationlabel label in the reference table. 7. Set the similarity threshold in the advanced label interface to 0.5, 8.
    
    Figure 6
    
    Figure 7
    
    Figure 8
    
14. Double-click the ole db destination editing interface, double-click New to view the dynamically generated table, modify the table name fuzzyresults, and click the ings tab. All settings are selected by default.
    
16. Add a data view of the grid type between fuzzy lookup and ole db destination.
    

Run this package, data 9 in data view, and the data in the final table is also like this. We can see that all input data is matched when the similarity threshold is set to 0.5, and occupationid is also included in the table. If you use inner join for selection, you can only find 4 rows of exactly matched data, that is, four rows of data whose similarity value is 1 in the table.

Figure 9