Click here to Skip to main content
Click here to Skip to main content

SSIS-Fuzzy lookup for cleaning dirty data

, 15 Jan 2013 BSD
Rate this:
Please Sign up or sign in to vote.
SSIS fuzzy lookup for cleaning dirty data.

Introduction

In this tutorial I will show how to use Fuzzy Lookup for cleaning dirty data.

Background

Cleansing data before it is stored in a reporting database is necessary to provide value to consumers of business intelligence applications. Fuzzy logic is an approach to computing based on "degrees of truth" rather than the usual "true or false" (1 or 0) Boolean logic on which the modern computer is based. The idea of fuzzy logic was first advanced by Dr. Lotfi Zadeh of the University of California at Berkeley in the 1960s.

What you need

This script was tested in SQL Server 2008.

Create project

First create the database and table from this script.

-- Create database
create database Test
go
use Test
go
 
create table fuzzyLookupSource
(
      firstName varchar(10),
      LastName varchar(10),
      BirthDate datetime
)
insert into fuzzyLookupSource
select 'Masud','Pervez','02/07/1980' union all
select 'Tamirul','Islam','03/31/1983' union all
select 'Animesh','Chandra','04/09/1980'  union all
select 'Shahriar','Bin Elahi','05/05/1980'  union all
select 'Masud','Rana','04/15/1980' 
GO

create table fuzzyLookupReference
(
      firstName varchar(10),
      LastName varchar(10),
      BirthDate datetime
)
insert into fuzzyLookupReference
select 'Masud','Parvez','02/07/1980' union all
select 'Tamirul','Islam','03/31/1983' union all
select 'Animesh','Chandra De','06/05/1980'  union all
select 'Shahriar','Elahi','04/09/1980'
GO

Then open SQL Server Business Intelligence Development Studio.

Then go to File->New->Project and select Integration Service Project.

Select "Data Flow Task" from "Control Menu" and Drag it on "Control Flow" tab. Then double click it.

Click Connection for new connection or select from existing connection.

Click New button to create new Data Connection or select from left tab.

Select "Server Name","Authentication" and "Database" which will be "Test" for this example. Click Test Connection for checking then click OK.

Select "Table", then click OK.

Select "Fuzzy Lookup" from "Data Flow Transformation" and Drag it on "Data Flow" tab. And connect extended green arrow from “OLE DB Source” to your fuzzy lookup. Double click on “Fuzzy Lookup” task to configure it.

Select "OLE DB Connection" and "Reference Table name" in "Reference Table" tab.

Map Lookup column and Output Column in "Columns tab. Add prefix "Ref_" in output column filed.

Let all value as it is in "Advanced" tab.

Select "Conditional Split" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Fuzzy Lookup” to your "Conditional Split". Double click on “Conditional Split” task to configure it.

Create two output. One is "Solid Matched" which Condition is "_Similarity > 0.85 && _Confidence > 0.8" and another is "Likely Matched" which condition is "_Similarity > .65 && _Confidence > 0.75". Click OK.

Select "Derived Column" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Conditional Split” to your "Derived Column".

Select Output as "Solid Matched" and click OK.

Double click on “Derived Column” task to configure it.

Select another "Derived Column" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Conditional Split” to your "Derived Column 1".

Select Output as "Likely Matched" and click OK.

Double click on “Derived Column 1” task to configure it.

Select another "Derived Column" from "Data Flow Transformation" and Drag it on "Data Flow" tab. And connect extended green arrow from “Conditional Split” to your "Derived Column 2".

Double click on “Derived Column 2” task to configure it.

Select another "Union All" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Derived Column” to your "Union All" and “Derived Column 1” to your "Union All" and “Derived Column 2” to your "Union All".

Double click on “Union All” task to configure it.

Select "SQL Server Destination" from "Data Flow Destination" and Drag it on "Data Flow" tab. and connect extended green arrow from “Union All” to your "SQL Server Destination".

Double click on “SQL Server Destination” task to configure it. Click New for create a New Table or Select from List.

Click OK.

If you execute the package with debugging (press F5), the package should succeed and appear as shown here:

To check what is happining here:

SELECT [firstName]
      ,[LastName]
      ,[Ref_firstName]
      ,[Ref_LastName]
      ,[_Similarity]
      ,[_Confidence]
      ,[_Similarity_firstName]
      ,[_Similarity_LastName]
      ,[_Match]
  FROM [Test].[dbo].[SQL Server Destination]

Conclusion

Fuzzy logic seems closer to the way our brains work. We aggregate data and form a number of partial truths which we aggregate further into higher truths. I hope this might be helpful to you!

References

History

None so far.

License

This article, along with any associated source code and files, is licensed under The BSD License

Share

About the Author

mparvez
Software Developer (Senior) icddr,b
Bangladesh Bangladesh
More than 8 years experience on Programming and Project implementation, I was primarily involved with projects for private organization,Govt.(Bangladesh Army,DG Health,RJSC), NGO (SEDF,WFP). Presently I am working at ICDDR,B and enhancing Hospital Management System developed by Microsoft Dynamic NAV and Windows Mobile Application 5.0
 
An active supporter of Open Source technology, my interested areas are ERP, IT Audit, Data warehouse, BI etc.
 
Playing Guitar for 15 years, my interested music style is Blues Rock,Neo Classical.
 
Certification
 
70-540:Microsoft® Windows Mobile® 5.0 - Application Development
MB7-514:Microsoft Dynamics™ NAV 5.0 C/SIDE Introduction
MB7-516:Microsoft Dynamics™ NAV 5.0 Solution Development
MB7-517:Microsoft Dynamics™ NAV 5.0 Installation and Configuration
MB7-515:Microsoft Dynamics™ NAV 5.0 Financials
70-432:Microsoft SQL Server 2008 - Implementation and Maintenance
70-450:PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
70-448:Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
312-50:Certified Ethical Hacker
 
Web :http://masudparvezshabuz.appspot.com
Blog :http://masudparvezshabuz.wordpress.com
linkedin :http://www.linkedin.com/in/masudparvez
Follow on   Google+

Comments and Discussions

 
QuestionDestination Table Data differs PinmemberSreedevi Pidaparthi6-Nov-14 22:23 
GeneralGreat work mparvez !!!! PinmemberMoumit Mondal18-Jun-14 4:45 
QuestionPackage not executed completey PinmemberSabaresh Sarva26-Mar-14 10:16 
AnswerRe: Package not executed completey PinmemberMember 1114388510-Oct-14 5:11 
QuestionCảm ơn nhiều :D Pinmemberchucphuc12-May-13 19:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411023.1 | Last Updated 15 Jan 2013
Article Copyright 2013 by mparvez
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid