In-querying data cleansing with semantic standardization

Abstract

The present invention relates to data cleansing, and in particular performing the semantic standardization process within a database before the transform portion of the extract-transform-load (ETL) process. Provided are a method, system and computer program product for standardizing data within a database engine, configuring the standardization function to determine at least one standardized value for at least one data value by applying the standardization table in a context of at least one data value, receiving a database query identifying the standardization function, at least one database value and the context of the data, and invoking the standardization function.

What is claimed is: 
     
         1 . A system comprising: a processor; and a memory containing program code which when executed by the processor is configured to perform an operation, comprising:
 receiving at least one standardization table, wherein each standardization table comprises a first column containing a plurality of text representations of a standard text and a second column containing the standardized text; and   upon receiving a database query identifying a standardization function and at least one data value to be converted to a standard value, converting the at least one data value to a standard value by invoking the standardization function within a database engine, wherein the standardization function uses the at least one standardization table and a context of the at least one data value to convert the at least one data value to the standard value.   
     
     
         2 . The system of  claim 1  wherein the standardization function is a user defined function. 
     
     
         3 . The system of  claim 2 , wherein the user defined function receives the context of a least one data value as a parameter. 
     
     
         4 . The system of  claim 2 , wherein the user defined function further comprises:
 determining a context of the data value.   
     
     
         5 . The system of  claim 4 , wherein determining the context further comprises:
 accessing one or more metadata values describing the data value; and   determining the context with one or more metadata values.   
     
     
         6 . The system of  claim 5 , further comprising:
 updating a non-standard value in a database with the standardized value.   
     
     
         7 . The system of  claim 2  wherein the standardization table contains information for performing measurement conversion. 
     
     
         8 . A computer program product comprising a non-transitory computer readable storage medium storing computer readable program code, which when executed by a computer, causes the computer to:
 receive at least one standardization table, wherein each standardization table comprises a first column containing a plurality of text representations of a standard text and a second column containing the standardized text; and   upon receiving a database query identifying a standardization function and at least one data value to be converted to a standard value, converting the at least one data value to a standard value by invoking the standardization function within a database engine, wherein the standardization function uses the at least one standardization table and a context of the at least one data value to convert the at least one data value to the standard value.   
     
     
         9 . The computer program product of  claim 8 , wherein the standardization function is a user defined function. 
     
     
         10 . The computer program product of  claim 9 , wherein the user defined function causes the computer to receive the context of a least one data value as a parameter. 
     
     
         11 . The computer program product of  claim 9 , wherein a user defined function further causes the computer to:
 determine a context of the data value.   
     
     
         12 . The computer program product of  claim 11 , wherein the operation of the computer to determine the context further causes the computer to:
 access one or more metadata values describing the data value; and   determine the context with one or more metadata values.   
     
     
         13 . The computer program product of  claim 12 , further causes the computer to:
 update a non-standard value in a database with the standardized value.   
     
     
         14 . The computer program product of  claim 8  wherein the standardization table contains information for causing the computer to perform measurement conversion.