Public Sector – Database Profiling, Cleansing, and Matching
Problem
The criminal incident database of the City of Cleveland, Ohio required standardization and de-duplication due to database and software constraints and problems from previous conversions, data entry, and changing standards.
Client challenge
The client did not have an easy method to identify potential duplicate records or an automated method of determining the surviving record for record consolidation. Additionally, the client did not have full awareness of nonconforming legacy data within the system. The client was also looking to leverage postal validation which was something not currently available within their system.
Attevo solution
Attevo conducted data profiling of the database and designed a cleansing and matching project using the Trillium Software System. The Attevo solution better informed the City about its data, created transformation logic to conform the data to the City’s data rules and standards, and executed matching using City established matching criteria.
Attevo also customized a Perl script to process updates in addition to the Trillium Software updates. An ongoing stored procedure was established to assist in future data cleansing. Upon completion of the project, a detailed report of data findings and potential matching records was provided to enable the City to improve its data quality on an ongoing basis.
Results
- 2.9 million total affected records were profiled
- 210,000 City and State records were enriched from postal validation and cleansing
- 290,000 records automatically consolidated
- 1,000,000 potential duplicates identified for City review
- 2,000 non-conforming records discovered during data profiling were marked for deletion
- 600 common City name misspellings were marked for review and standardization through an on-demand stored procedure
- 10,000 City man-hours saved, valued at $500,000
Type
Data Cleansing and Matching
Geographic areas served
USA - Cleveland
Partner products used
Trillium Software System
To speak with an Attevo consultant, please email us or telephone us at one of our office locations.
