Electrical Engineering
      and Computer Sciences

Electrical Engineering and Computer Sciences

COLLEGE OF ENGINEERING

UC Berkeley

An Interactive Framework for Data Transformation and Cleaning

Vijayshankar Raman and Joseph M. Hellerstein

EECS Department
University of California, Berkeley
Technical Report No. UCB/CSD-00-1110
September 2000

http://www.eecs.berkeley.edu/Pubs/TechRpts/2000/CSD-00-1110.pdf

Cleaning organizational data of discrepancies in structure and content is important for data warehousing and Enterprise Data Integration. Current commercial solutions for data cleaning involve many iterations of time-consuming "auditing" to find errors, and long-running transformations to fix them. Users need to endure long waits and often write complex transformation programs. We present an interactive framework for data cleaning that tightly integrates transformation and discrepancy detection. Users gradually build transformations by adding or undoing transforms, in a intuitive, graphical manner through a spreadsheet-like interface; the effect of a transform is shown at once on records visible on screen. In the background, the system automatically infers the structure of the data in terms of user-defined domains and applies suitable algorithms to check it for discrepancies, flagging them as they are found. This allows users to gradually construct a transformation as discrepancies are found, and clean the data without writing complex programs or enduring long delays.

We choose and adapt a small set of transforms from existing literature and describe methods for their graphical specification and interactive application. We apply the Minimum Description Length principle to automatically extract the structure of data values in terms of user-defined domains. Such structure extraction is also applied in the graphical specification of transforms, to infer transforms from examples. We also describe methods for optimizing the final sequence of transforms for memory allocations and copies. This transformation facility is integrated into a spreadsheet-based data analysis package, allowing flexible analysis of arbitrarily transformed versions of the data.


BibTeX citation:

@techreport{Raman:CSD-00-1110,
    Author = {Raman, Vijayshankar and Hellerstein, Joseph M.},
    Title = {An Interactive Framework for Data Transformation and Cleaning},
    Institution = {EECS Department, University of California, Berkeley},
    Year = {2000},
    Month = {Sep},
    URL = {http://www.eecs.berkeley.edu/Pubs/TechRpts/2000/5793.html},
    Number = {UCB/CSD-00-1110},
    Abstract = {Cleaning organizational data of discrepancies in structure and content is important for data warehousing and Enterprise Data Integration. Current commercial solutions for data cleaning involve many iterations of time-consuming "auditing" to find errors, and long-running transformations to fix them. Users need to endure long waits and often write complex transformation programs. We present an interactive framework for data cleaning that tightly integrates transformation and discrepancy detection. Users gradually build transformations by adding or undoing transforms, in a intuitive, graphical manner through a spreadsheet-like interface; the effect of a transform is shown at once on records visible on screen. In the background, the system automatically infers the structure of the data in terms of user-defined domains and applies suitable algorithms to check it for discrepancies, flagging them as they are found. This allows users to gradually construct a transformation as discrepancies are found, and clean the data without writing complex programs or enduring long delays.  <p>We choose and adapt a small set of transforms from existing literature and describe methods for their graphical specification and interactive application. We apply the Minimum Description Length principle to automatically extract the structure of data values in terms of user-defined domains. Such structure extraction is also applied in the graphical specification of transforms, to infer transforms from examples. We also describe methods for optimizing the final sequence of transforms for memory allocations and copies. This transformation facility is integrated into a spreadsheet-based data analysis package, allowing flexible analysis of arbitrarily transformed versions of the data.}
}

EndNote citation:

%0 Report
%A Raman, Vijayshankar
%A Hellerstein, Joseph M.
%T An Interactive Framework for Data Transformation and Cleaning
%I EECS Department, University of California, Berkeley
%D 2000
%@ UCB/CSD-00-1110
%U http://www.eecs.berkeley.edu/Pubs/TechRpts/2000/5793.html
%F Raman:CSD-00-1110