hey everybody on this video we will look at fuzzy grouping and fuzzy matching on ssis
quick background on what we'll be working with we have two tables one is employees we have some
names and we have them put in their colleges and some have spelled it the same some have not
and what we want to do is base it off of a another table called universities that has the correct
official name of these schools and we're going to use ssis and fuzzy matching to do this
so let's hop over into visual studio and the first thing we'll do is add a data flow task because
that's where we'll be doing this logic move into here first thing we'll need is a source and the
source we're going to work with in this case is our employees table so let's add an oledb source
i'm going to call this employees i have my connection manager already set here if you don't
you'll have to add one and the name of the view that we're going to pull from will be employees
and this is again what it looks like okay next fuzzy look up and if you don't see this right away
it's it could be tucked under other transforms so let's go into here and do fuzzy lookup and we'll
drag this in let's take a look at this editor what this is saying here again is we want a
connection manager and the reference table we want to use in this case is going to be
universities as we mentioned we're going to try to match and really clean up the data
from that college column in employees where some of the people maybe did it differently
match them together and clean it up we'll select that reference table here and we can store the
new index if we want and it'll store it actually in our sql server database and we can reuse it
in the future so if this is something you plan on doing over and over again this this will be
a good option but for this case we're not going to do that here you could use an existing one
if we did happen to store that in the past so we won't store it but we'll create a new one
now columns here's where we need to decide what we want to match and as we mentioned
we want college to match with university we can delete this id here and let's select university
it's going to try to match college to university and the return value will be this university
value that we get here which is what we want because that's the formal correct name
now if we go to advanced this is where we can set how close we want it to match it's using some sort
of algorithm to match the values together and here's where you can set the similarities you
can go all the way up to 99 which means it needs to be essentially identical but in our case we'll
just say we want it to be about 20 percent similar and the number of matches per lookup we just want
one match within this threshold and we'll leave all these as is you can customize this if you want
now let's select ok in order to see what this data returns i'm going to just put on a derived column
here we're not going to do anything i just want to add a data viewer because then it'll pop up and
show us what the results look like okay so we're all set i'm going to save and run this package and
here's our output here's our data viewer let's take a look alright so we can see here this
worked as we wanted so for example john put penn state as his college and we were able to match it
to the pennsylvania state university it had a similarity of this which as we if you recall
we said it has to be at least 20 so we actually could have gone up a little higher it determined
it's this confident in that match and the same with some of these other ones indiana indiana
university this one was you know not completely filled out michigan state and i think this one has
a lower confidence because it could have matched here potentially as well these were spot on so we
got one for one and now what you could do is take this value of university and as you move forward
in your package you can use this value instead of whatever they put in which could have been
you know maybe a user input that is vulnerable to inconsistencies all right so that's fuzzy lookup
let's take a look now at fuzzy grouping okay fuzzy grouping very similar to the lookup except in this
case it'll return a key to you that allows you to group certain things together let's take this out
and insert fuzzy grouping and then match this up here and see what happens now let's
take a look through this editor again similar connection we're connecting to the same database
columns what is the column that we want to group together
in this case we're not looking it up on anything we're just grouping things together so in this
one we want to group on college you can see the different attributes here that are available
we can say fuzzy lookup meaning it doesn't have to be exactly the same but we want it to be close
or you can say i want these to be only grouped if they are exactly the same value so you can see
here the minimum similarity is one meaning it has to be 100 the same but let's do fuzzy we'll say
the minimum is zero well maybe we can bump this up to twenty percent you know has to be at least
twenty percent similar to be a group and now if we go to advanced we'll see a couple options here
the input key column name it just gives some default and this may not necessarily
be something you need to customize but this will make more sense once we see the output this is
basically saying here's the input key here's the output key and the output key is going to be that
group value similarity threshold meaning and this is very much similar to what we're doing here this
is the minimum this is the threshold tweak these to get the values that you want and i think a lot
of that depends on your data itself let's click ok here and again i'm going to add a data viewer so
we can see the actual results so let's run this and see all right so a couple different columns
here and as we can see here these match together so these two for example they decided it decided
these are very close these are supposed to be a match so let's match them together with this value
these two match them here and they decided this is probably a better match university of mish because
it completely matches both we don't know if this one should be the full name so they're going to
give it an output of that one and if we sort by key out here's where we can see the ones that
were grouped and this is where these keys come in so everyone's going to have a unique key in it's
a unique record but the key out is saying which essentially which group is this record a part of
and in this case these two are grouped together we have two penn state grouped here and two indiana
so now the output of this editor of this component is going to give you this information that you can
work with so maybe you want to group these you want to do something with these groups you know
i don't know maybe you have different logic for different groups and you can work off of that
so that'll do it for this video pretty straightforward use of
fuzzy grouping and fuzzy look up it's a nice way to clean up the data that you have coming
through your pipeline and hopefully be able to clean up your bad data during your etl process
thanks as always for watching if you found this helpful feel free to like the video it really does
help the channel you know let youtube know this is content that is worthwhile if you have any
questions leave a comment happy to help and i'll see you at the next video thanks for watching