Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

Data Analysis and Harmonization

By Jeff Voivoda

iUniverse, Inc.

Copyright © 2011 Jeff Voivoda
All right reserved.
ISBN: 978-1-4502-9824-7

Contents

Foreword.........................ixIntroduction.....................xiiiChapter 1........................1Chapter 2........................15Chapter 3........................27Chapter 4........................33Chapter 5........................41Chapter 6........................55Chapter 7........................65Chapter 8........................83Chapter 9........................93Chapter 10.......................105Chapter 11.......................115Chapter 12.......................125Glossary.........................129References.......................135

Chapter One

This Organization Has Problems

Let's suppose for a minute that you are the person responsible for producing and disseminating a voluminous (and dreaded) "monthly" report. By good luck or bad, by knowledge or naivety, you're the poor sap who has to locate, collect, manipulate, crunch, grind, produce, and distribute this information to coworkers, colleagues, stakeholders, and clients.

First things first, let's gather all the required data. Okay, gathering all the required data isn't as easy as it sounds. As a topnotch employee, you know that sales figures come from the sales system. That's housed in the sales department. You also know the inventory totals are stored over in the warehouse system, part of the inventory management department. You can't access that system, but you can get Bob to dump those numbers into a file so you can use them (hopefully, Bob isn't on vacation!). Oh yes, and the expired customers need to be notified that their accounts are about to be closed. Who do you call for that, again? Oh, right, customer service has that data. You better get cracking!

Once you receive the sales figures, don't forget to apply the conversion program because sales volumes are expressed in pieces and the inventory amounts are expressed in components. Also, the part number associated with the pieces in the sales system can contain alphabetic characters, but the part number used for components in the warehouse system is only numeric. And you can't forget to review the product descriptions since the sales system truncates the description field at twenty-five characters, so the forty character description fields from inventory system sometimes just don't make sense. If there are any problems, you'll have to print out the inconsistencies and make certain the report lists both part numbers from both systems and send it to Jane in the quality control (QC) department for her to review and (hopefully) rectify the problems. Of course, all this needs to be completed before the final report is delivered. While that's transpiring, you better start reviewing the list of expired accounts. Last month, we sent four expiration notices to the same person because the addresses were basically the same, except for some slight differences in each record in the database. How embarrassing!

Does This Problem Sound Familiar?

Should we continue this confusing, ineffective scenario, or do you get the picture? I think we can easily see this process needs some streamlining, and with little trouble we can spot the inefficiencies. Although, the poor processing and data issues were exaggerated to make the point, there are indeed some organizations that operate in a haphazard way that is alarmingly similar to this disjointed and ineffective manner described in the scenario. Maybe you've even been part of a mess like this! I know I have!

A few years ago, I was working with a government agency that processed applications and issued certifications to applicants based on the data contained in the applications. There were two basic requirements to get the agency-issued certification: First, the application had to be complete, which meant that all the required data had to be supplied and any complimentary documentation had to be provided. Second, the applicant had to pay a fee to apply and obtain the certification. Sounds reasonable, right? This agency had a major problem: there were three types of certifications that could be obtained, and each type of certification was processed using a different application and hence was stored in one of three different databases! Even though most of the application information was the same or very similar for each applicant (e.g. applicant name, applicant address, and so on), each was stored in its own database. And to make matters worse, the payment information was kept in yet another database in a completely different department. I was part of a team of analysts that came into this agency and harmonized their disparate data sources, streamlined the application process (in this case by allowing electronic submission of a single application form), and cut the length of time from application to certification almost in half!

Inefficient processes are usually the product of poorly structured data and databases. This stands to reason because if you have to access multiple databases on different technology platforms in order to gather, process, and present information, the process itself and the poor data storage strategies will be equally to blame for your issues!

You might say, "So what? What's the harm as long as the work gets done and the reports go out the door on time?" Shame on you! I hope you're not saying that! Don't be lulled into complacency by the mere fact that the work is getting done and the reports are being delivered! The data contained in those reports is questionable, or worse, inaccurate. Accepting erroneous data as accurate and reliable exposes you and your client to many potential risks! For example, let's say you've just laid the concrete foundation of a structure, and you hire an engineer to test the stability of the walls. If the data regarding the soundness of the material is inaccurate, you may build your structure on a faulty foundation. Consider the risk of that scenario! The data provided in any situation needs to be structured, consolidated, accurate, and then made more reliable. This data needs to be harmonized.

The topic of analyzing and harmonizing data is not a new subject. Organizations at all levels and of all sizes have struggled with managing disparate data sources and maintaining multiple naming conventions as well as with a general lack of data reliability and standardization since long before computers came along.

There are many ways to systematically organize data. The process of data harmonization is not a new or complex process. In fact, it follows closely in principle with the process of normalization. If you have ever categorized or classified data into groups and then identified relationships between those groups, guess what? You've already performed data harmonization at a high level! But before you pat yourself on the back too hard, let's dive deeper into this process.

But before we launch into data harmonization and all its glory, let's talk about the concerns that arise by having and enabling stovepipe systems and silos of information. A stovepipe system is a computer system whose functionality and processes are narrowly focused to provide specific data to a specific recipient. I've already cited an example from my past experience earlier in this book (the government agency) in which time and resources were wasted due to information silos. I'm betting you have an example or two you could share as well.

Issues with Information Silos

Generally speaking, here...