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

• US 9,881,103 B2
• Filed: 02/03/2014
• Issued: 01/30/2018
• Est. Priority Date: 09/17/2004
• Status: Active Grant

• Current Assignee: First American Financial Corporation
• Original Assignee: First American Financial Corporation
• Inventors: Freire, Adriano
• Primary Examiner(s): Cao, Phuong Thao
• Application Number:
  US14/171,712
• Publication Number:
  US 20140214809A1
• Time in Patent Office: 1,457 Days
• Field of Search: 707722
• 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:

creating a metadata dataset comprising metadata of a plurality of disparate data sources, wherein the metadata of each of the plurality of disparate data sources describes data source information contained in the disparate data source, and wherein the metadata dataset comprises fields for each type of information to be queried;

receiving a user request for information from the plurality of disparate data sources;

in response to receiving the user request, for each of the plurality of disparate data sources, generating (i) a query based on a query language of the disparate data source and (ii) an API request to convey the query to the disparate data source, wherein generating the query is based on the user request, wherein the operation of generating the query and the API request for each of the plurality of disparate data sources comprises using the metadata dataset to correlate information requested in the user request with the disparate data source'"'"'s information;

submitting each of the generated queries to its corresponding disparate data source using the corresponding API request;

receiving, in response to the generated queries, a raw data source response from each of the plurality of disparate data sources;

mapping, using the metadata dataset, a data source information field in each raw data source response to a formatted information field to transform each of the raw data source responses into a formatted data source response; and

combining the formatted responses into a response to the user request.

2. The method of claim 1, wherein the plurality of disparate data sources comprises at least two different types of data sources from the group comprising databases, data files, web services, web pages, computer services, data streams, scanned documents, geographic data, GIS data.

3. The method of claim 1, wherein a graphical representation of the metadata dataset comprises a dataset tree, and wherein creating a metadata dataset comprises providing a graphical user interface to a user that allows the user to drag fields to a dataset on the dataset tree.

4. The method of claim 1, wherein transforming each of the raw data source responses comprises transforming each of the raw data source responses into hierarchical or tabular data.

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

6. The method of claim 5, wherein combining 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.

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

8. The method of claim 1, wherein the user request is a request to gather data from the plurality of disparate data sources.

9. The method of claim 1, further comprising:

receiving a second user request to write information to the plurality of disparate data sources;

for each of the plurality of disparate data sources, generating (i) a second query based on the query language of the disparate data source and (ii) an API request that conveys the second query to the disparate data source;

submitting each of the generated second queries to the corresponding disparate data sources through the API request; and

writing information to each of the disparate data sources based on the submitted second queries.

10. The method of claim 1, wherein creating a metadata dataset comprising metadata of the plurality of disparate data sources, comprises:

retrieving the metadata from the plurality of disparate data sources;

generating a list of fields from the retrieved metadata, wherein the fields correspond to data source information fields in the plurality of disparate data sources;

receiving a selection of one or more fields from the list of fields, thereby resulting in one or more selected fields; and

creating a metadata dataset comprising the one or more selected fields.

11. The method of claim 1, further comprising:

storing the created metadata dataset in a metadata database, wherein the metadata database does not store data source information contained in the plurality of disparate data sources.

12. A computer program product comprising a non-transitory computer useable storage medium having computer program stored therein for enabling a computer system to perform the functions of:

creating a metadata dataset comprising metadata of a plurality of disparate data sources, wherein the metadata of each of the plurality of disparate data sources describes data source information contained in the disparate data source, and wherein the metadata dataset comprises fields for each type of information to be queried;

receiving a user request for information from the plurality of disparate data sources;

in response to receiving the user request, for each of the plurality of disparate data sources, generating (i) a query based on a query language of the disparate data source and (ii) an API request to convey the query to the disparate data source, wherein generating the query is based on the user request, wherein the function of generating the query and the API request for each of the plurality of disparate data sources comprises using the metadata dataset to correlate information requested in the user request with the disparate data source'"'"'s information;

submitting each of the generated queries to its corresponding disparate data source using the corresponding API request;

receiving, in response to the generated queries, a raw data source response from each of the plurality of disparate data sources;

mapping, using the metadata dataset, a data source information field in each raw data source response to a formatted information field to transform each of the raw data source responses into a formatted data source response; and

combining the formatted responses into a response to the user request.

13. The computer program product of claim 12, wherein the plurality of disparate data sources comprises at least two different types of data sources from the group comprising databases, data files, web services, web pages, computer services, data streams, scanned documents, geographic data, GIS data.

14. The computer program product of claim 12, wherein a graphical representation of the metadata dataset comprises a dataset tree, and wherein creating a metadata dataset comprises providing a graphical user interface to a user that allows the user to drag fields to a dataset on the dataset tree.

15. The computer program product of claim 12, wherein transforming each of the raw data source responses comprises transforming each of the raw data source responses into hierarchical or tabular data.

16. The computer program product of claim 12, wherein the function of generating the query and the API request comprises generating a web service request.

17. The computer program product of claim 16, wherein combining 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.

18. The computer program product of claim 12, wherein the function of receiving the user request comprises receiving a SQL request based on the metadata dataset.

19. The computer program product of claim 12, wherein the user request is a request to gather data from the plurality of disparate data sources.

20. The computer program product of claim 12, the computer program further configured to enable the computer system to perform the functions of:

receiving a second user request to write information to the plurality of disparate data sources;

for each of the plurality of disparate data sources, generating (i) a second query based on the query language of the disparate data source and (ii) a second API request that conveys the second query to the disparate data source;

submitting each of the generated second queries to the corresponding disparate data sources through the second API request; and

writing information to each of the plurality of disparate data sources based on the submitted second queries.

21. The computer program product of claim 12, wherein creating a metadata dataset comprising metadata of the plurality of disparate data sources, comprises:

retrieving the metadata from the plurality of disparate data sources;

generating a list of fields from the retrieved metadata, wherein the fields correspond to data source information fields in the plurality of disparate data sources;

receiving a selection of one or more fields from the list of fields, thereby resulting in one or more selected fields; and

creating a metadata dataset comprising the one or more selected fields.