Introduction

When an organization begins a concerted effort to improve its information quality, often it gets stuck in trying to figure out exactly where to start. This case study takes this to heart and gives a specific example of an approach to improving information quality.

Previously, we had discussed the semantic and statistical approaches to information quality and linked them to black box and white box testing. In addition, there is a case study on semantic information quality which is used to contrast this case study (you may want to take a look at these if you aren’t familiar with the subjects).

The example that we have been using is …

dealing with call data for a customer contact center. For simplicity, we can assume that all the call data we need is delivered nightly and is loaded into a single table that looks exactly like the files as they have arrived. This table has the following attributes:

• employee_login_number
• site_name
• department_name
• call_local_start_time
• call_local_end_time

… from this data the business analysts are going to figure out how much to pay and to whom. Also, we need to figure out who is handling the highest call volume (vendors, locations, and employees) on a daily basis so that we can resolve issues and negotiate contracts. Our job is to make sure that the data is accurate enough to do this with confidence.

Also, before we get started, realize that with the semantic and statistical approaches we are trying to do the same thing in different ways. So, while we are doing things differently, there is bound to be some overlap.

The Statistical Approach

With a statistical approach, there are several things to consider:

1. From a statistical point of view, there is nothing special about this dataset. It has very similar characteristics to all the ones that came before it and will come after it. We should try to create an architecture that can be re-used where appropriate.
2. There is a lot that we can infer from the dataset itself. We can learn a great deal of information about the dataset very cheaply through black box testing. Focusing on these areas will maximize re-use as well.
3. We can probably assume that any data that we recieve is of reasonably good quality when the process was first designed. Therefore, we can focus on events where the nature of the data changes substantially.

With these in mind, we can start to design a solution.

The place to start is to ask, “what can go wrong in our data?”. I can think of several situations that might impact the quality of this data:

• The employee_login_number is invalid or NULL.
• The site_name is invalid or NULL.
• The department is invalid or NULL.
• The call_local_start_time is invalid or NULL.
• The call_local_end_time is invalid, NULL, or starts before the call_local_start_time.
• Due to errors outside of our control, the process that created the data malfunctioned. Often, this will show up as duplicate values, irregular frequency or distribution of values

Off the top of my head, I have a number of questions about the data that we will see day to day:

• For each column, is there a distinct list of values (call this the domain) that are valid?
• For each column, is there a distinct pattern of values that are valid?
• For each column, can the values be NULL?
• Is there a distinct key? If so, is it unique?
• For column values and keys, should the frequency for particular values be fairly normal?
• Is there a certain number of rows that should be expected (by key or for the entire dataset)?
• Is there a certain number of keys that should be expected?
• For numeric values, can we do descriptive statistics to tell us if things are off-kilter?

Based on these, I think that we can establish a data model that would allow this metadata to be recorded for multiple processes, which would allow it to be used for reporting and decision-making.

For example, consider a table having the following attributes:

• process_id
• process_run_dt
• distinct_value
• distinct_value_type
• distinct_value_count

This would allow the user to keep track of how many distinct values there were generated by a given process. Over time, this could be very useful in tracking down some sticky problems, and perhaps prevent bad data from ever getting into a data store in the first place.

For each of the meausurement processes we mentioned, they can probably be integrated into the overall data model in a process agnostic way. I apologize for not having more details at this point, I plan to move this to the wiki (at some point) and put in a reference model for doing some of these operations.

Comparisons With Data Profiling

For people with some experience with data management this may sound a lot like data profiling. In fact, a lot of the operations inherent in the statistical approach would probably be considered a part of data profiling as well.

However, there are some key differences between Statistical IQ and Data Profiling that need mentioning:

1. Statistical IQ has an operational focus and needs to be as lightweight as possible. We want to use this to make day-to-day operational decisions about our data without slowing anything down.
2. Statistical IQ does not include data discovery, while data profiling often does.
3. One of the core functions of data profiling is establishing relationships between datasets. Statistical IQ has a very limited view of relationships in order to maximize functionality and reusability.

Similar base concepts, focusing on different areas.

Statistical IQ and Mad Libs

One thing that often gets lost in the re-use discussion is the price of user configuration. All too often, programmers push too much decision making out of their code and on to the operator, making it difficult to use.

The trick with Statistical IQ is that you have to be able to tie a generic statement (”there are 15 distinct values in this dataset”) back to something useful (”there is probably missing data, don’t continue the process”). While this might seem like a challenge, it can be done without a lot of heartburn.

In a recent engagement, I designed a solution where we tied every possible error back to an english description of the problem that was stored in an SQL database. This was done in a very generic way, so that new errors could be added or removed without any configuration required by the developer or operator.

Conclusion

There are different approaches to information quality, each with their own strengths, weaknesses, and costs. The statistical approach is cheaper (especially when you factor in Moore’s Law), but gives a less detailed picture of overall quality. The semantic approach is more expensive, but can be as comprehensive as the situation requires. A balanced approach will use both approaches to deliver the solution that is needed.

The Muncie Star Press wrote about a the terrible effects of poor iformation quality and its impact on a community.  Due to a data processing error, the City of Muncie incorrectly assessed property taxes on non-profit institutions, such as local hospitals and universities.

The inflated assessment was then used for budgeting, and once the problem was discovered there was a huge shortfall between revenue and spending.  Where this really hits home is when the Muncie Police and Fire Departments have to take first responders off the street in order to make up the difference.

Still wondering if information quality is worth looking at in your organization?

When an organization begins a concerted effort to improve its information quality, often it gets stuck in trying to figure out exactly where to start. This case study takes this to heart and gives a specific example of an approach to improving information quality.

Previously, we had discussed the semantic and statistical approaches to information quality and linked them to black box and white box testing (you may want to take a look at these if you aren’t familiar with the subjects, as these are the basis for this article).

The Semantic Approach

A more semantic approach would involve defining exactly what your data represents, and from there determine what it should look like and how it should behave. This sounds pretty easy, right? The problem is that things are often more complicated than they seem.

Let’s look at an example I ran into on a client engagement, dealing with call data for a customer contact center. For simplicity, we can assume that all the call data we need is delivered nightly and is loaded into a single table that looks exactly like the files as they have arrived. This table has the following attributes:

• employee_login_number
• site_name
• department_name
• call_local_start_time
• call_local_end_time

OK, now from this data the business analysts are going to figure out how much to pay and to whom. Also, we need to figure out who is handling the highest call volume (vendors, locations, and employees) on a daily basis so that we can resolve issues and negotiate contracts. Our job is to make sure that the data is accurate enough to do this with confidence.

The Semantic Challenge

The first thing we would need to do is to find out exactly what is going on in the system. Talking with various people in technology and business units, we can define some basic terms. In our case, let’s say that we discover:

• There are multiple contact center locations worldwide and each one has its own “switch” with data in its own local time. All of the locations are owned and operated by vendors.
• All reporting for management is done in Eastern Time (US), but location and employee reporting should be done in local time.
• An agent is signified by a login number in the “switch” (a piece of telephony equipment).
• An agent works in a department, which handles a specific type of call.
• An agent can have multiple logins on the same “switch” for different departments that they work in.
• A “call” will be defined by a valid call record, including a start time and end time
• Each time a call comes in a record will be created with the login number, start time, and stop time (in local time).
• Calls to different departments are paid different rates.

Realize that this is the tip of the iceberg when it comes to business rules. There could easily be 100 more concepts and constraints involved in a decent sized business. Also, understand that this was very rapidly growing (over 100% per year) worldwide business that was intensely focused on customer service. We couldn’t ask the business to slow down. But, we still needed to provide data that was of high quality.

From the problem description, we know that there must be mappings between:

• Logins and agents.
• Locations and vendors.
• Locations and time zones.
• Departments and pay rates.

The Semantic Solution

Off the top of my head, there are a number of things that we can do to test this data. It shouldn’t be too hard to write SQL that would test the referential integrity of the system. For example:

1. Join the call data with each of the mappings, noting what records have no matches.
2. Join the call data with each of the mappings, noting what records have multiple matches.
3. Look for duplicates in the mapping tables.
4. Look for newly added or removed values in the mapping tables.

Next, I would look at some basic validation tests:

1. Each agent should not have more than 3 logins (or some appropriate number) per day.
2. Each agent should only be listed at one facility per day.
3. Each agent should only be listed at one vendor per day.
4. Locations should not disapear or change time zones from day to day.
5. Vendors should not disapear from day to day.
6. The Call Start Time should be earlier than the Call Stop Time.

Last, it would be good to write some sanity checks:

1. After daily processing is complete, the total number of calls should exactly match the sum of the number of calls to each site.
2. After daily processing is complete, the total number of calls to a site should exactly match the sum of the number of calls to each agent at that site.
3. At all levels, the total amount billed should not be more than the total (number of calls) x (highest billing rate).
4. The total number of call time for an agent should not be more than 12 hours in a given day.

Now, this is by no means a complete list of tests that should be run, but it gives you a good idea of what can be looked at.

Conclusion

As you can see, ensuring that the information coming out of this process is accurate is sometimes simple, sometimes complex, and sometimes downright daunting. Most of the solution here requires that custom tests be created, maintained, understood, and reported on (something that we haven’t even discussed). This is a lot of work, and customized work that can’t be easily reused. This is why in most cases I believe this type of testing is only added after an issue had occured.

In part 2, we will discuss a statistical approach to the same dataset.

One of the real challenges about information quality is that the field is still very abstract. In the academic world, the theories (like PSP/IQ) are still being written and discussed. In practice, this means that there isn’t a standard way of doing things. Or, to be more precise, everyone has a “standard” way of doing things, it is just that they are all different. So, let me add my $0.02, perhaps this will help someone in some general way.

Previously, we talked about the semantic and statistical approaches to information quality. Two distinctly different ways of trying to do the same thing. How can we reconcile these two different ideas and actually accomplish something in the real world? The best way I know is to try and fall back to some well established practices and try to adapt them to our needs. While we are working with data instead of applications, I think that these approaches correspond directly to principles from software engineering. For most applications, there are two types of testing:

1. White box testing uses an intimate knowledge of the internals of an application and tests to make sure everything works as expected.
2. Black box testing uses an expectation of the behavior of an application and tests to make sure that it does what it should. A black box test will not test anything internal to the application, just the inputs and outputs.

Both of these types of tests do different types of things and work in different ways. As this terribly obscure but well-written and concise tutorial points out:

• White box testing does quality control, while black box testing does quality assurance.
• Black box testing finds sins of omission, white box testing finds sins of comission,
• Black box testing can be started as soon as the specifications are available, while white box testing must wait until the code is written.
• Black box testing is a lot cheaper than white box testing.
• Both types of testing are needed in order to truly verify that things are working properly.

In my opinion, a semantic approach to information quality is the equivalent of a white-box test. Conversely, the statistical approach is the equivalent of a black-box test.

Next in our series, we will be exporing the semantic approach to information quality with practical examples.