Method and system for query transformation for managing information from multiple datasets

• US 8,694,532 B2
• Filed: 09/19/2005
• Issued: 04/08/2014
• Est. Priority Date: 09/17/2004
• Status: Active Grant

• Current Assignee: First American Financial Corporation
• Original Assignee: First American Data Co. LLC (First American Financial Corporation)
• Inventors: Freire, Adriano
• Primary Examiner(s): TIMBLIN, ROBERT M
• Application Number:
  US11/231,345
• Publication Number:
  US 20060080313A1
• Time in Patent Office: 3,123 Days
• Field of Search: 707/705, 707/758, 707/759, 707/760, 707/769, 707/707, 707/971, 707/790, 707/770
• US Class Current: 707/769
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/90335 Query processing

Abstract

A method and system for users to connect, extract, query and transform data from multiple databases, independent of their format, type, and location. The system comprises a setup portion, a meta database, and a user portion. The setup portion establishes datasets for use by the system with the user portion. The meta database stores the datasets. The user portion allows a user to input a query and processes the query to gather information from at least one data source and generate a response.

Claims

1. A method for managing data comprising:

identifying a plurality of disparate data sources, each of the disparate data sources comprising data source information and metadata that describes the data source information;

accessing the metadata from the disparate data sources;

generating a listing of fields from the metadata, wherein the fields correspond to data source information fields in one or more of the disparate data sources;

receiving a selection of a field from the listing of fields, thereby resulting in a selected field;

creating a metadata dataset comprising one or more selected fields, and identifying one or more data sources from the disparate data sources based on the metadata dataset;

for each of the one or more identified disparate data sources, generating (i) a query based on a query language of the identified disparate data source and (ii) an API request that conveys the query to the identified disparate data source, wherein generating the query is based on a user request or the metadata dataset, wherein generating the API request is based on the user request or the metadata dataset, and generating the query request and the API request comprises using the metadata dataset to correlate information requested in the user request with the data source information;

submitting each of the one or more generated queries to the one or more identified disparate data sources through the API request;

receiving, in response to the one or more generated queries, a data source response from the one or more identified disparate data sources; and

preparing a data source response to the user request for information.

2. The method of claim 1, wherein the step of accessing one or more disparate data sources comprises accessing at least two different types of data sources selected from the group consisting of databases, data files, web services, web pages, computer services, data streams, scanned documents, geographic data, GIS data.

3. The method of claim 1, wherein receiving the selection of the selected field from the listing of fields comprises receiving a drag-and-drop action from a graphical user interface where the selected field is moved from a graphical representation of the listing of fields to a graphical representation of the metadata dataset.

4. The method of claim 3, wherein the graphical representation of the metadata dataset comprises a dataset tree.

5. The method of claim 1, wherein preparing a data source response to the user request for information comprises:

transforming each of the data source responses into a formatted data source response according to the metadata dataset, wherein the metadata dataset maps a data source information field in the data source response to a formatted information field in the formatted data source response; and

consolidating the formatted responses into a response to the user request for information.

6. The method of claim 5, wherein the step of transforming comprises transforming each of the data source responses into hierarchical or tabular data.

7. The method of claim 5, wherein the step of generating the query and the API request comprises generating a web service request.

8. The method of claim 5, wherein the step of receiving the user request comprises receiving a SQL request based on the metadata dataset.

9. The method of claim 5, wherein consolidating the formatted responses into the response to the user request for information comprises using the metadata dataset to join information in each of the formatted responses into the response to the user request for information.

10. A computer system comprising:

a processor; and

a memory coupled to the processor;

a computer readable medium having instructions embedded therein, the instructions configured to cause the processor to perform the operations of;

identifying a plurality of disparate data sources, each of the disparate data sources comprising data source information and metadata that describes the data source information;

accessing the metadata from the disparate data sources;

generating a listing of fields from the metadata, wherein the fields correspond to data source information fields in one or more of the disparate data sources;

receiving a selection of a field from the listing of fields, thereby resulting in a selected field; and

creating a metadata dataset comprising one or more selected fields, and identifying one or more data sources from the disparate data sources based on the metadata dataset;

for each of the one or more identified disparate data sources, generating (i) a query based on a query language of the identified disparate data source and (ii) an API request that conveys the query to the identified disparate data source, wherein generating the query is based on a user request or the metadata dataset, wherein generating the API request is based on the user request or the metadata dataset, and generating the query request and the API request comprises using the metadata dataset to correlate information requested in the user request with the data source information;

submitting each of the one or more generated queries to the one or more identified disparate data sources through the API request;

receiving, in response to the one or more generated queries, a data source response from the one or more identified disparate data sources; and

preparing a data source response to the user request for information.

11. The computer system of claim 10, wherein receiving the selection of the selected field from the listing of fields comprises receiving a drag-and-drop action from a graphical user interface where the selected field is moved from a graphical representation of the listing of fields to a graphical representation of the metadata dataset.

12. The computer system of claim 11, wherein the graphical representation of the metadata dataset comprises a dataset tree.

13. The computer system of claim 10, wherein preparing a data source response to the user request for information comprises:

receiving a data source raw response from each of the identified disparate data sources;

transforming each of the data source raw responses into a formatted data source response according to the metadata dataset, wherein the metadata dataset maps a data source information field in the data source raw response to a formatted information field in the formatted data source response; and

consolidating the formatted responses into a response to the user request for information.

14. The computer system of claim 13, wherein consolidating the formatted responses into the response to the user request for information comprises using the metadata dataset to join information in each of the formatted responses into the response to the user request for information.

15. A computer program product, the computer product comprising a non-transitory computer readable medium in which program instructions are stored, the program instructions configured to cause a computer system to:

identify a plurality of disparate data sources, each of the disparate data sources comprising data source information and metadata that describes the data source information;

access the metadata from the disparate data sources;

generate a listing of fields from the metadata, wherein the fields correspond to data source information fields in one or more of the disparate data sources;

receive a selection of a field from the listing of fields, thereby resulting in a selected field;

create a metadata dataset comprising one or more selected fields, and identifying one or more data sources from the disparate data sources based on the metadata dataset;

for each of the one or more identified disparate data sources, generate (i) a query based on a query language of the identified disparate data source and (ii) an API request that conveys the query to the identified disparate data source, wherein generating the query is based on a user request or the metadata dataset, wherein generating the API request is based on the user request or the metadata dataset, and generating the query request and the API request comprises using the metadata dataset to correlate information requested in the user request with the data source information;

submit each of the one or more generated queries to the one or more identified disparate data sources through the API request;

receive, in response to the one or more generated queries, a data source response from the one or more identified disparate data sources; and

prepare a data source response to the user request for information.

16. The computer program product of claim 15, wherein receiving the selection of the selected field from the listing of fields comprises receiving a drag-and-drop action from a graphical user interface where the selected field is moved from a graphical representation of the listing of fields to a graphical representation of the metadata dataset.

17. The computer program product of claim 16, wherein the graphical representation of the metadata dataset comprises a dataset tree.

18. The computer program product of claim 15, wherein preparing a data source response to the user request for information comprises:

transforming each of the data source responses into a formatted data source response according to the metadata dataset, wherein the metadata dataset maps a data source information field in the data source response to a formatted information field in the formatted data source response; and

consolidating the formatted responses into a response to the user request for information.

19. The computer program product of claim 18, wherein consolidating the formatted responses into the response to the user request for information comprises using the metadata dataset to join information in each of the formatted responses into the response to the user request for information.