Two Files, One Record, and a Mission

I'm a very stubborn individual when it comes to new things.  If it's not intuitive, I'm going to move on.  If I buy something and it has to be put together, want to be able to figure it out by looking at it.  When things are not intuitive, I get so annoyed.

Technology is even worse - cell phones, computers, it's all the same.  I simply hate reading manuals or documentation.  Seems silly coming from someone who does a lot of training and writes documentation for a software company, but I realize that people learn in so many different ways.  I've downloaded a lot of software over the years, some of which I've immediately embraced due to it's ease of use and some abandoned minutes after opening the application.

I think there are certainly a handful of our customers who share my philosophy of if it isn't easy and intuitive it will be quickly forgotten.  That's why they like Data Integration.  But, for those that don't share that view, a different approach is necessary.  Sometimes using Data Integration requires a shift in thinking.  It's almost like fishing an equation out of a word problem.  You have a specification or analytic requirement that needs to be completed, then you have to retrieve that data.  It has to be put together in a way that it ends up being clear, and more importantly correct.  

I was working with a customer the other day who had 2 files.  One file had one more person in it than the other.  He wanted to know that one person.  Seems easy enough, right?  Turns out, not so much.  In one file, he had a SSN and a full name, in the other was some additional data as well as a first, middle, and last name and a separate ID.  This required the 2 to be merged and then compared for finding that person.  There was no such field to do that.  So, we took the file with the name parts and concatenated them together using a transform.  After a few fumbles, we got that to work beautifully.  We then merged on that new full name to the existing first name and still ended up with a handful of those that didn't match up.  Turns out THOSE had suffixes and we didn't have the suffix in the other file.  We then added a cleanse to clean all of those up, and tried again.  We added source table flag columns in the merge to create quick binary variables with a 1 if it existed and a 0 if it didn't.  With a simple filter after that, we found the one straggler that was missing from the original file.  It should have been so simple, but the data didn't cooperate.  

The good news in all of it, was that we could cleanly create a process using a visual job in Data Integration.  We didn't have to jump through other screens or hoops, just had to think methodically of how it could be done, fish out that equation and execute it using a series of tasks.  Sometimes, you just have to take a step back and look at the big picture to help you see more clearly the answer.  The nice thing, is being able to see it all flow from start to finish - and even a little bit intuitively.