Sign-Up for Free Exclusive Services:	Portals	|	eNewsletters	|	Web Seminars	|	dataWarehouse.com	|	DM Review Magazine

Covering Business Intelligence, Integration & Analytics		Advanced Search

RESOURCE PORTALS View all Portals WEB SEMINARS Scheduled Events RESEARCH VAULT White Paper Library Research Papers CAREERZONE View Job Listings Post a job INFORMATION CENTER DM Review Home Newsletters Current Magazine Issue Magazine Archives Online Columnists Ask the Experts Industry News Search DM Review GENERAL RESOURCES Bookstore Buyer's Guide Glossary Industry Events Calendar Monthly Product Guides Software Demo Lab Vendor Listings DM REVIEW About Us Press Releases Awards Advertising/Media Kit Reprints Magazine Subscriptions Editorial Calendar Contact Us Customer Service

Plain English About Information Quality: Check Digits as a Tool for IQ   Column published in DM Review Magazine March 2003 Issue     By Larry English Last week I had my hearing checked as part of my routine medical checkup. My doctor had referred me to an audiologist, and his office called to make an appointment for me. This was my first visit to this office, and I used my car navigation system to guide me and found the office without a problem. The Problem However, when I checked in, I found they had no record of my appointment. I confirmed the address; it was correct. This was not an uncommon information quality problem - an appointment mix-up. I started my stopwatch that I use to measure wasted time due to nonquality information. The receptionist conferred with the audiologist about the problem. Six minutes and 23 seconds later, they came out to notify me that they found "the problem." The receptionist who received the call from my doctor and actually booked my appointment was in a sister clinic. (The booking ID was important to identify who was involved in the transaction. However, remember that knowing who performed the process is not to assign blame, but to help analyze the root cause.) Apparently, she had entered a valid but incorrect clinic ID. I was, in fact, at the right clinic (clinic ID 3599), but the number keyed in for my appointment (clinic ID 3596) was for a different clinic. Costs of Information Scrap and Rework Fortunately, the audiologist had an opening, and I was able to have my hearing tested. The actual waste could have been worse, but on this occasion was limited to: Thirteen minutes of clinic staff time (two people times 6 minutes and 23 seconds). Three minutes of time from the booking receptionist of the other clinic. A wasted hour of time for the audiologist at the other clinic who could not fill that suddenly available slot. Six minutes and 23 seconds of customer time (never forget the inconvenience to the customer). Had the audiologist not been open to test my hearing, the waste would have included staff time to reschedule, customer time to reschedule and make an additional trip to the clinic (45-50 minutes) plus transportation costs. Root-Cause Analysis The booking receptionist used a numeric keypad for entering numbers. The numbers 9 and 6 are vertically aligned on top and middle rows of the numeric keypad. Errors of hitting adjacent keys are not uncommon. However, in this case, the number entered happened to be the booking receptionist's own clinic number, making this a more common potential error. However, the root cause was not human error. The root cause was failing to error- proof the process to prevent accidental keying transpositions. It is a well- known fact that humans have a propensity to make certain types of errors when working with numbers, whether keying them into a computer, reading them, reciting them, or entering them on a telephone keypad.1 Errors include: Simple transposition of adjacent digits: "48" is transposed to "84." Reversing double digits: "366" is entered as "336." Dropping or adding a digit: "534" becomes "53" or "5345." Stutter errors of hitting a key twice: "34" becomes "344." Double errors: "22" entered as "33." Double transposition or jump errors: "253" transposed to "352." Phonetic errors: "40" (forty) entered (or heard as) "14" (fourteen).2 Recommended Process Improvement: Check Digits A check digit is a number appended to numeric identifiers to prevent most common keying transposition errors.3 There are multiple algorithms for calculating a check digit value, including mod (modulo) 10 (used for credit card and checking account numbers), mod 11 (ISBN number check digits), mod 9 (U.S. Post Office money orders), mod 7 (UPS and airlines) and UPC-A (UPC code-check digits). Check digits work by calculating a digit from the string of numbers, such that common transpositions would produce a number that would not be a valid identifier. This prevents deposits from being applied to the wrong checking account if a clerk enters "84" instead of "48." In my experience, here is how a check digit would have prevented booking an appointment at the wrong clinic. Using mod 10 as the check-digit algorithm, the two clinic IDs become 35998 (the clinic where my appointment was supposed to be made) and 35964 (the clinic where my appointment was actually made). To calculate the check digit for the number 3599: 1. Multiply the odd-positioned digits (positions 1, 3, 5, ... n) of the ID number by one (1), beginning with the rightmost digit. Note that the check digit is unknown, so use a question mark as a placeholder: 3 5 9 9 [?] x1 x1 x1 3 9 [?] 2. Multiply the even-positioned digits (positions 2, 4, 6, ... n+1) of the ID number by two (2), beginning with the next to rightmost digit: 3 5 9 9 [?] x1 x2 x1 x2 x1 3 10 9 18 [?] 3. Add all of the individual digits from steps 1 and 2 together: 3 5 9 9 [?] x1 x2 x1 x2 x1 3 10 9 18 [?] 3 + (1 + 0) + 9 + (1 + 8) = 22 4. Subtract the result of step 3 (i.e., 22) from the next highest multiple of 10. The remainder is the check digit number and becomes the last digit position of the ID number: 30 - 22 = 8 3 5 9 9 8 Therefore, the calculation of the number for the second clinic is: 3 5 9 6 [?] x1 x2 x1 x2 x1 3 10 9 18 [?] 3 + (1 + 0) + 9 + (1 + 2) = 16 20 - 16 = 4 3 5 9 6 4 If the booking receptionist had entered the incorrect digit "6" for the "9," the resulting number would have an invalid "35968," not matching the "35964" clinic ID number. An additional error would have been made to match an existing clinic ID number. The application could then have alerted the receptionist to verify the number, preventing the appointment mix-up. Benefits of check digits include: The use of check digits eliminates 93 to 94 percent of common data entry and communication errors. Standard check-digit algorithms fail only with multiple or compound errors. Knowing the check-digit algorithm allows you to code a test to validate that a number is a valid calculation of an ID number at the source of entry. Remember, however, that the number could still be an incorrect ID number if multiple errors were made. To test a mod 10 check-digit number, follow the procedure explained in this column, but include the check digit number. The resulting calculation should be a multiple of 10. I recommend using check digits for all primary objects, such as customer ID, account ID, facility ID or other objects that have a high volume of transactions applied to them or have high costs or consequences of errors of misapplication. What do you think? Let me know at Larry.English@infoimpact.com. References: 1. Hamming, Richard. Coding and Information Theory, 2e. New York: Prentice-Hall, 1986. p. 27. 2. See Jonathan Mohr, "Check Digits." Found at: http://www.augustana.ab.ca/~mohrj/algorithms/checkdigit.html . Date accessed: January 18, 2003. 3. English, Larry P. Improving Data Warehouse and Business Information Quality. New York: John Wiley & Sons, 1999. p. 304. ............................................................................... For more information on related topics visit the following related portals... Data Quality. Larry P. English is president and principal of INFORMATION IMPACT International, Inc., Brentwood, Tennessee, and the author of the widely acclaimed book, Improving Data Warehouse and Business Information Quality: Methods for Reducing Costs and Increasing Profits. English is cofounder of the International Association for Information and Data Quality (www.iaidq.org). English is an internationally recognized speaker, teacher, consultant and author and may be reached at larry.english@infoimpact.com or through his Web site at www.infoimpact.com. For more on how to improve your IQ principles and techniques, and prevent your organization from wasting millions in information scrap and rework, join the IAIDQ (visit www.iaidq.org). Solutions Marketplace Provided by IndustryBrains Data Validation Tools: FREE Trial Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here. Backup SQL Server or Exchange Continuously FREE WHITE PAPER. Recover SQL Server, Exchange or NTFS data within minutes with TimeSpring?s continuous data protection (CDP) software. No protection gaps, no scheduling requirements, no backup related slowdowns and no backup windows to manage. Manage Data Center from Virtually Anywhere! Learn how SecureLinx remote IT management products can quickly and easily give you the ability to securely manage data center equipment (servers, switches, routers, telecom equipment) from anywhere, at any time... even if the network is down. Design Databases with ER/Studio: Free Trial ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface. Free EII Buyer's Guide Understand EII - Trends. Tech. Apps. Calculate ROI. Download Now. Click here to advertise in this space

View Full Magazine Issue E-Mail This Column Printer-Friendly Version Related Content Request Reprints

Site Map	Terms of Use	Privacy Policy
(c) 2006 DM Review and SourceMedia, Inc. All rights reserved. SourceMedia is an Investcorp company. Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.