Schema and ETL tools for structured and unstructured data

• US 20070011175A1
• Filed: 07/05/2005
• Published: 01/11/2007
• Est. Priority Date: 07/05/2005
• Status: Active Grant

• Current Assignee: Clarabridge Incorporated
• Original Assignee: Claraview, Inc. (Teradata Corporation)
• Inventors: Langseth, Justin, Sohn, Gene, Vivatrat, Nithi
• Granted Patent: US 7,849,049 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: Y10S 707/912 Applications of a database

Abstract

A system and method of making unstructured data available to structured data analysis tools. The system includes middleware software that can be used in combination with structured data tools to perform analysis on both structured and unstructured data. Data can be read from a wide variety of unstructured sources. The data may then be transformed with commercial data transformation products that may, for example, extract individual pieces of data and determine relationships between the extracted data. The transformed data and relationships may then be passed through an extraction/transform/load (ETL) layer and placed in a structured schema. The structured schema may then be made available to commercial or proprietary structured data analysis tools.

Claims

1. A data capture schema comprising:

a set of tables to store data extracted from a plurality of source documents having unstructured data; and

a table to store information about the extracted data,

wherein the plurality of documents are assigned a unique key that identifies the document throughout a software system allowing (i) cross-analysis, (ii) linking of results for further analysis, (iii) drill-down from analytical reports back to the source documents or (iv) drill-down from analytical reports back to transformation information stored in the schema.

2. The capture schema of claim 1, wherein the information is generated from an entity extraction tool, a categorization and topic extraction tool, or a data matching tool.

3. The capture schema of claim, 2, wherein the extracted data includes people names, place names, company names, dates, times, or monetary amounts.

4. The capture schema of claim 1, further comprising a table of associated attributes.

5. The capture schema of claim 4, wherein the attributes includes language of origin or temporal qualities.

6. The capture schema of claim 1, further comprising a table of data identifying the actual location of the information in the documents.

7. The capture schema of claim 1, wherein the capture schema retains information about entities, entity occurrences, the relationship between the entity and entity occurrences and associated attributes with the entity and entity occurrences.

8. The capture schema of claim 1, wherein the capture schema includes information about the plurality of documents, relationships between the plurality of documents and extracted entities and entity relationships.

9. The capture schema of claim 1, wherein the capture schema includes attributes about the plurality of documents.

10. The capture schema of claim 9, wherein the attributes include the source, author, date, time, or language.

11. The capture schema of claim 1, wherein binary data can be stored as a BLOB object and character data can be stored as a CLOB object.

12. The capture schema of claim 1, wherein the capture schema further includes section information.

13. A data analysis schema comprising:

a set of tables that provides structure to unstructured data, the table including master entities, the master entities comprising (i) a group of entities that appear in multiple documents that are the same actual entity, (ii) entities that are spelled differently that are the same actual entity, or (iii) entities that have multiple names that are the same actual entity; and

a table including relationships between entities.

14. The data analysis schema of 13, wherein the entities are grouped by hierarchy.

15. The data analysis schema of 13, wherein dates and numeric amounts are stored in specific columns in standard date and numeric formats.

16. The data analysis schema of 13, wherein the analysis schema can include structured data from structured data sources.

17. An extraction/transformation/load module comprising:

code to migrate data from a capture schema to an analysis schema, the code to migrate including code to map data and code to load data,

wherein the capture schema comprises a set of tables to store data extracted from a plurality of source documents having unstructured data and a table to store information about the extracted data, wherein each of the plurality of documents are assigned a unique key that identifies the document throughout a software system allowing (i) cross-analysis, (ii) linking of results for further analysis, (iii) drill-down from analytical reports back to the source documents, or (iv) drill-down from analytical reports back to transformation information stored in the schema,

wherein the analysis schema comprises a set of tables that provides structure to unstructured data, the table including master entities, the master entities comprising (i) a group of entities that appear in multiple documents that are the same actual entity, (ii) entities that are spelled differently that are the same actual entity, or (iii) entities that have multiple names that are the same actual entity and a table including relationships between entities.

18. The extraction/transformation/load module of claim 17, further comprising code to:

(i) match entities to corresponding master entities;

(ii) group relationships together that represent the same relationships or events into a single master relationship;

(iii) create and/or maintain entities into their corresponding hierarchical groupings;

(iv) create and/or maintain relationships into their corresponding hierarchical groupings;

(v) create and/or maintain keyword hierarchy;

(vi) create and/or maintain attribute hierarchy;

(vii) create and/or maintain concept/topic hierarchy;

(viii) create and/or maintain document folder hierarchy;

(ix) extract document source information from document attributes into its own data structure;

(x) extract attributes relating to document sources into a separate data structure;

(xi) create and/or maintain document source hierarchy;

(xii) create standard system time dimension for time-series analysis;

(xiii) identify date and numeric types of entities and conversion of date and numeric values into corresponding native data types where appropriate; and

(xiv) identify and/or removal of duplicate source documents to avoid double-counting.