MULTI-DATABASE QUERY SYSTEM AND METHOD

• US 20110055231A1
• Filed: 08/26/2010
• Published: 03/03/2011
• Est. Priority Date: 08/27/2009
• Status: Active Grant

• Current Assignee: Mastercard International Incorporated (MasterCard Incorporated)
• Original Assignee: Mastercard International Incorporated (MasterCard Incorporated)
• Inventors: Threlkeld, Diane Ediger, Huck, Bridget K., Koenig, Edwin R. JR.
• Granted Patent: US 8,533,177 B2
• Time in Patent Office: Days
• US Class Current: 707/751
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

A system for retrieving data from a plurality of data sources is provided. The system includes a first data source for storing first data, a second data source for storing second data, and a server computer in communication with the first and second data sources. The server is programmed to receive a query execution request including a query definition reference and a query parameter definition wherein the query definition reference corresponds to a query definition that includes a first query block and a second query block, create a first executable query based on the first query block and the query parameter definition, create a second executable query based on the second query block and the query parameter definition, execute the first executable query at the first data source to create first query results, and execute the second executable query at the second data source to create second query results.

Claims

1. A system for retrieving data from a plurality of data sources, said system comprising:

a first data source for storing first data;

a second data source for storing second data; and

a server computing device in communication with the first data source and the second data source, the server computing device programmed to;

receive a query execution request comprising a query definition reference and a query parameter definition, wherein the query definition reference corresponds to a query definition that includes a first query block and a second query block;

create a first executable query based on the first query block and the query parameter definition;

create a second executable query based on the second query block and the query parameter definition;

execute the first executable query at the first data source to create first query results; and

execute the second executable query at the second data source to create second query results.

2. The system of claim 1, further comprising a query results database in communication with the server computing device, wherein the server computing device is further configured to store the first query results and the second query results in the query results database.

3. The system of claim 2, further comprising a report generation service configured to:

retrieve the first query results and the second query results from the query results database; and

generate a formatted report based on the first query results, the second query results, and a formatted report definition that is associated with the query definition.

4. The system of claim 1, wherein server computing device is further configured to:

create the first executable query in a first syntax that is compatible with the first data source; and

create the second executable query in a second syntax that is compatible with the second data source, wherein the second syntax is different from the first syntax.

5. The system of claim 1, wherein the query execution request further comprises an execution schedule that includes a plurality of execution times, and the server computing device is further programmed to execute the first executable query and the second executable query at a first execution time of the plurality of execution times.

6. The system of claim 1, wherein the first query block includes at least one control token and a reference to the first data source, and the second query block includes at least one control token and a reference to the second data source.

7. The system of claim 6, wherein the server computing device is further programmed to:

create the first executable query at least in part by replacing the control token of the first query block with a first value from the query parameter definition; and

create the second executable query at least in part by replacing the control token of the second query block with a second value from the query parameter definition.

8. The system of claim 1, wherein the first data source is controlled by a first server, and the second data source is controlled by a second server.

9. The system of claim 1, wherein said server computing device is further programmed to execute the first executable query and the second executable query concurrently.

10. The system of claim 9, wherein said server computing device is further programmed to:

assign the first executable query to a first query execution queue having a first weight;

assign the second executable query to a second query execution queue having a second weight that is less than the first weight; and

execute the first executable query prior to executing the second executable query based on the first weight and the second weight.

20. The method of claim 1, wherein said creating the first executable query comprises creating the first executable query based further on a first syntax associated with the first data source, and said creating the second executable query comprises creating the second executable query based further on a second syntax associated with the second data source, wherein the second syntax is different from the first syntax.

11. A method for retrieving data from a plurality of data sources using a computer system, the computer system in communication with a first data source and a second data source of the plurality of data sources, said method comprising:

electronically retrieving a query definition corresponding to a query definition reference, the query definition including a first query block associated with the first data source and a second query block associated with the second data source;

creating, using the computer system, a first executable query based on the first query block and a query parameter definition;

creating, using the computer system, a second executable query based on the second query block and the query parameter definition;

executing the first executable query against the first data source to create first query results;

executing the second executable query against the second data source to create second query results; and

storing the first and the second query results in a query results database.

12. The method of claim 11, further comprising:

retrieving a formatted report definition associated with the query definition; and

executing, using the computer system, the formatted report definition on the first and the second query results in the query results database to create a formatted report.

13. The method of claim 11, further comprising receiving from a user a query execution request including a reference to a formatted report definition, wherein said retrieving a query definition comprises identifying the query definition that is associated with the received formatted report definition.

14. The method of claim 13, further comprising:

receiving an execution schedule associated with the query execution request, the execution schedule specifying a plurality of executions of the formatted report definition; and

performing said steps of executing the first executable query, executing the second executable query, and storing the first and the second query results at a plurality of times based on the execution schedule.

15. The method of claim 14, further comprising storing in a job database a job entry associated with each execution of the plurality of executions of the formatted report definition.

16. The method of claim 13, wherein the query execution request is a first query execution request, the first query execution request including a reference to a first formatted report definition associated with a first query definition, said method further comprising:

adding the first query execution request to a query execution queue;

receiving, after said receiving the first query execution request, a second query execution request including a reference to a second formatted report definition associated with a second query definition;

adding the second query execution request to the query execution queue; and

executing executable queries based on the second query definition after said storing the first and the second query results corresponding to the first query definition.

17. The method of claim 13, wherein the query execution request is a first query execution request, the first query execution request including a reference to a first formatted report definition associated with a first query definition, said method further comprising:

receiving, after said receiving the first query execution request, a second query execution request including a reference to a second formatted report definition associated with a second query definition; and

concurrently executing the first and the second executable queries and a plurality of executable queries corresponding to the second query definition.

18. The method of claim 11, wherein executing the first executable query and executing the second executable query further comprises concurrently executing the first executable query and the second executable query.

19. The method of claim 11, further comprising:

submitting the first executable query to a first query execution queue having a first weight;

submitting the second executable query to a second query execution queue having a second weight that is less than the first weight; and

executing the first executable query prior to executing the second executable query based on the first weight and the second weight.

21. A computer system for retrieving data from a plurality of data sources, said computer system comprising:

a first computer device;

a query database in communication with the first computer device, the query database configured to store a plurality of query definitions, wherein a first query definition of the plurality of query definitions comprises a first query block including a reference to a first data source and a second query block including a reference to a second data source;

a query service executed by the first computer device, the query service configured to instruct the first computer device to;

create a first executable query based at least in part on the first query block and a second executable query based at least in part on the second query block;

execute the first executable query at the first data source to create first query results; and

execute the second executable query at the second data source to create second query results; and

a query results database configured to store the first query results and the second query results.

22. The system of claim 21, further comprising a query developer user interface configured to receive the first query definition from a user, and transmit the first query definition to the first computer device.

23. The system of claim 21, wherein said query service is further configured to instruct the first computer device to concurrently execute the first executable query and the second executable query.

24. The system of claim 21, further comprising a second computer device, and a report generation service executed by the second computer device, wherein the report generation service is configured to instruct the second computer device to create a formatted report based on a formatted report definition associated with the first query definition, the first query results, and the second query results.

25. A computer-readable medium that includes computer executable instructions for retrieving data from a plurality of data sources, said computer executable instructions configured to instruct one or more computers to:

retrieve a query definition corresponding to a query definition reference, the query definition including a first query block associated with a first data source of the plurality of data sources and a second query block associated with a second data source of the plurality of data sources;

create a first executable query based on the first query block and a query parameter definition;

create a second executable query based on the second query block and the query parameter definition;

execute the first executable query against the first data source to create first query results;

execute the second executable query against the second data source to create second query results; and

create a formatted report based on the first query results, the second query results, and a formatted report definition associated with the query definition.