# US 10,120,916 B2 · In-querying data cleansing with semantic standardization

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 10,120,916 B2
- Document ID: US10120916B2
- Status: Granted
- Kind: B2
- Application number: 13/493,945
- Filed: 2012-06-11
- Published: 2013-12-12
- Granted: 2018-11-06
- Earliest claimed: 2012-06-11
- Assignees: International Business Machines Corporation
- Applicants: Tanveer A. Faruquie; Mukesh K. Mohania; L. Venkata Subramaniam; Charles D. Wolfson
- Inventors: Tanveer A. Faruquie; Mukesh K. Mohania; L. Venkata Subramaniam; Charles D. Wolfson
- Web page: https://patentgrep.com/patent/US10120916B2

## CPC

- G06F 16/254: {Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses}
- G06F 16/215: Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## 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.

## Claims

### Claim 1

A computer implemented method comprising:
integrating a set of standardization functions into database engines to allow access to standardization of data via database queries executed in the database engines against databases;
storing a plurality of standardization tables;
with an extract module, extracting data before a transform portion of an extract, transform, and load process by issuing a first database query from the database queries to a first database engine of the database engines and a second database query from the database queries to a second database engine of the database engines, wherein the first database query includes a first standardization function, and wherein the second database query includes a second standardization function;
with the first database engine,
determining a first context included in the first standardization function; and
invoking the first standardization function within the first database engine to convert a first data value having the first context using a first standard value and a first standardization table from the plurality of standardization tables that includes a context column for the first context;
with the second database engine,
determining a second context using metadata values by:
identifying the metadata values of a column name and a database schema;
mapping the metadata values to the second context using a lookup table; and
identifying a second standardization table from the plurality of standardization tables based on the second context, wherein the second standardization table does not include any context column; and
invoking the second standardization function within the second database engine to convert a second data value having the second context using a second standard value and the second standardization table; and
providing cleansed data in the databases before another transform portion of another extract, transform, and load process by storing the converted first data value and the converted second data value.

### Claim 2 (depends on 1)

The method of claim 1 wherein the first standardization function is a user defined function.

### Claim 3 (depends on 2)

The method of claim 2, wherein the user defined function receives the first context of the first data value as a parameter.

### Claim 4 (depends on 1)

The method of claim 1, wherein determining the second context further comprises:
accessing the metadata values describing the second data value; and
determining the second context with the metadata values.

### Claim 5 (depends on 4)

The method of claim 4, further comprising:
updating a non-standard value with a standardized value.

### Claim 6 (depends on 1)

The method of claim 1 wherein the first standardization table contains information for performing measurement conversion.

### Claim 7

A system comprising: a processor; and a memory containing program code which when executed by the processor is configured to perform an operation, comprising:
integrating a set of standardization functions into database engines to allow access to standardization of data via database queries executed in the database engines against databases;
storing a plurality of standardization tables;
with an extract module, extracting data before a transform portion of an extract, transform, and load process by issuing a first database query from the database queries to a first database engine of the database engines and a second database query from the database queries to a second database engine of the database engines, wherein the first database query includes a first standardization function, and wherein the second database query includes a second standardization function;
with the first database engine,
determining a first context included in the first standardization function; and
invoking the first standardization function within the first database engine to convert a first data value having the first context using a first standard value and a first standardization table from the plurality of standardization tables that includes a context column for the first context;
with the second database engine,
determining a second context using metadata values by:
identifying the metadata values of a column name and a database schema;
mapping the metadata values to the second context using a lookup table; and
identifying a second standardization table from the plurality of standardization tables based on the second context, wherein the second standardization table does not include any context column; and
invoking the second standardization function within the second database engine to convert a second data value having the second context using a second standard value and the second standardization table; and providing cleansed data in the databases before another transform portion of another extract, transform, and load process by storing the converted first data value and the converted second data value.

### Claim 8 (depends on 7)

The system of claim 7, wherein the first standardization function is a user defined function.

### Claim 9 (depends on 8)

The system of claim 8, wherein the user defined function receives the first context of the first data value as a parameter.

### Claim 10 (depends on 7)

The system of claim 7, wherein determining the second context further comprises:
accessing the metadata values describing the second data value; and
determining the second context with the metadata values.

### Claim 11 (depends on 10)

The system of claim 10, further comprising:
updating a non-standard value with a standardized value.

### Claim 12 (depends on 7)

The system of claim 7, wherein the first standardization table contains information for performing measurement conversion.

### Claim 13

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:
integrate a set of standardization functions into database engines to allow access to standardization of data via database queries executed in the database engines against databases;
store a plurality of standardization tables;
with an extract module, extract data before a transform portion of an extract, transform, and load process by issuing a first database query from the database queries to a first database engine of the database engines and a second database query from the database queries to a second database engine of the database engines, wherein the first database query includes a first standardization function, and wherein the second database query includes a second standardization function;
with the first database engine,
determine a first context included in the first standardization function; and
invoke the first standardization function within the first database engine to convert a first data value having the first context using a first standard value and a first standardization table from the plurality of standardization tables that includes a context column for the first context;
with the second database engine,
determining determine a second context using metadata values by:
identifying the metadata values of a column name and a database schema;
mapping the metadata values to the second context using a lookup table; and
identifying a second standardization table from the plurality of standardization tables based on the second context, wherein the second standardization table does not include any context column; and
invoke the second standardization function within the second database engine to convert a second data value having the second context using a second standard value and the second standardization table; and
provide cleansed data in the databases before another transform portion of another extract, transform, and load process by storing the converted first data value and the converted second data value.

### Claim 14 (depends on 13)

The computer program product of claim 13, wherein the first standardization function is a user defined function.

### Claim 15 (depends on 14)

The computer program product of claim 14, wherein the user defined function causes the computer to receive the first context of the first data value as a parameter.

### Claim 16 (depends on 13)

The computer program product of claim 13, wherein, when determining the second context, the computer readable program code further causes the computer to:
access the metadata values describing the second data value; and
determine the second context with the metadata values.

### Claim 17 (depends on 16)

The computer program product of claim 16, further causes the computer to:
update a non-standard value with a standardized value.

### Claim 18 (depends on 13)

The computer program product of claim 13, wherein the first standardization table contains information for causing the computer to perform measurement conversion.