System and method for multiple pass cooperative processing

• US 6,820,073 B1
• Filed: 03/20/2002
• Issued: 11/16/2004
• Est. Priority Date: 06/20/2001
• Status: Expired due to Term

• Current Assignee: Microstrategy Incorporated
• Original Assignee: Microstrategy Incorporated
• Inventors: Yuan, Jun, Sahasrabudhe, Sadanand, Wang, Xinyi, Hurwood, William, Bedell, Jeffrey A., Li, Benjamin Z.
• Primary Examiner(s): ALAM, SHAHID AL
• Application Number:
  US10/101,488
• Time in Patent Office: 972 Days
• Field of Search: 707/3, 707/5, 707/6, 707/9, 707/10, 707/103.R, 707/104.1, 707/2, 707/4, 706/12, 704/257, 709/203, 709/217, 703/223, 717/108, 717/126, 717/174
• US Class Current: 707/754
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A system for collaborative processing, comprising a controlling module with access to at least one relational database capable of performing a first set of functions on the data in the database and at least one external analytical engine, the external analytical engine being external to the relational database and being capable of a second set of functions on the data in the database. The controlling module is capable of iteratively processing a multi-step calculation including generating SQL statements to the relational database, passing preliminary results to an external analytical engine and saving data back into the relational database for further processing until the multi-step calculation is performed.

Claims

1. A system for collaborative processing, comprising:

a controlling module with access to at least one relational database capable of performing a first set of functions on the data in the database and at least one external analytical engine, the external analytical engine being external to the relational database and being capable of a second set of functions on the data in the database;

wherein the controlling module performs the following steps;

receiving a request to generate a report based on data in the relational database, the request including at least one multi-step calculation to be performed on data in the relational database;

generating a first SQL statement to resolve a first step of the multi-step calculation on first data in the relational database;

receiving results from the first SQL statement;

passing data generated by the first SQL statement to the external analytical engine, wherein the external analytical engine is directed to perform at least one operation on the data;

receiving externally-operated data from the external analytical engine after the at least one operation;

inserting the externally-operated data into a relational database;

generating a second SQL statement to resolve another step in the multi-step calculation, the second SQL statement operating on at least part of the first data and at least part of the externally-operated data; and

generating a report in response to the request after the second SQL statement has been resolved.

2. The system of claim 1 wherein the externally-operated data is inserted into the at least one relational database in which the first data is stored.

3. The system of claim 1 wherein the externally-operated data is inserted into a second relational database different from the relational database in which the first data is stored.

4. The system of claim 1 wherein the controlling module performs the following additional steps:

receiving results from the second SQL statement;

passing data generated by the second SQL statement to the external analytical engine, wherein the external analytical engine is directed to perform at least one operation on the data;

receiving second externally-operated data from the external analytical engine after the at least one operation;

inserting the externally-operated data into a relational database; and

generating a third SQL statement to resolve another step in the multi-step calculation, the third SQL statement operating on at least part of the first data and at least part of the second externally-operated data.

5. The system of claim 4 wherein the controlling module performs the receiving, passing, receiving, inserting and generating steps at least one additional time, thus generating a fourth SQL statement prior to generating the report result.

6. The system of claim 1 wherein the first SQL statement comprises a fetch operation.

7. The system of claim 1 wherein the fist SQL statement comprises a filtering operation.

8. The system of claim 1 wherein the first SQL statement comprises a calculation operation.

9. The system of claim 1 wherein the first SQL, statement comprises an aggregation operation.

10. The system of claim 1 wherein the operation performed by the external analytical engine is an operation that the at least one relational database is incapable of performing.

11. A method for collaborative processing in system with access to a relational database capable of performing a first set of functions on the data in the database and at least one external analytical engine, the external analytical engine being external to the relational database and being capable of a second set of functions on the data in the database, the method comprising the steps of:

receiving at a controlling module external to a relational database a request to generate a report based on data in the relational database, the request including at least one multi-step calculation to be performed on data in the relational database;

generating a first SQL statement to resolve a first step of the multi-step calculation on first data in the relational database;

receiving results from the first SQL statement;

passing data generated by the first SQL statement to the external analytical engine, wherein the external analytical engine is directed to perform at least one operation on the data;

receiving externally-operated data from the external analytical engine after the at least one operation;

inserting the externally-operated data into a relational database;

generating a second SQL statement to resolve another step in the multi-step calculation, the second SQL statement operating on at least part of the first data and at least part of the externally-operated data; and

generating a report in response to the request after the second SQL statement has been resolved.

12. The method of claim 11 wherein the eternally-operated data is inserted into the at least one relational database in which the first data is stored.

13. The method of claim 11 wherein the eternally-operated data is inserted into a second relational database different from the relational database in which the first data is stored.

14. The method of claim 11 further comprising the steps of:

receiving results from the second SQL statement;

passing data generated by the second SQL statement to the external analytical engine, wherein the external analytical engine is directed to perform at least one operation on the data;

receiving second externally-operated data from the external analytical engine after the at least one operation;

inserting the externally-operated data into a relational database; and

generating a third SQL statement to resolve another step in the multi-step calculation, the third SQL statement operating on at least part of the first data and at least part of the second externally-operated data.

15. The method of claim 14 wherein the receiving, passing, receiving, inserting and generating steps are performed at least one additional time, thus generating a fourth SQL statement prior to generating the report result.

16. The method of claim 11 wherein the first SQL statement comprises a fetch operation.

17. The method of claim 11 wherein the first SQL statement comprises a filtering operation.

18. The method of claim 11 wherein the first SQL statement comprises a calculation operation.

19. The method of claim 11 wherein the first SQL statement comprises an aggregation operation.

20. The method of claim 11 wherein the operation performed by the external analytical engine is an operation that the at least one relational database is incapable of performing.

21. A machine readable medium, the machine readable medium being readable to execute a method for collaborative processing, the method comprising:

receiving at a controlling module external to a relational database a request to generate a report based on data in the relational database, the request including at least one multi-step calculation to be performed on data in the relational database;

generating a first SQL statement to resolve a first step of the multi-step calculation on first data in the relational database;

receiving results from the first SQL statement;

passing data generated by the first SQL statement to the external analytical engine, wherein the external analytical engine is directed to perform at least one operation on the data;

receiving externally-operated data from the external analytical engine after the at least one operation;

inserting the externally-operated data into a relational database;

generated a second SQL statement to resolve another step in the multi-step calculation, the second SQL statement operating on at least part of the first data and at least part of the externally-operated data; and

generating a report in response to the request after the second SQL statement has been resolved.