System and method for efficient date retrieval and processing

• US 7,430,562 B1
• Filed: 06/19/2001
• Issued: 09/30/2008
• Est. Priority Date: 06/19/2001
• Status: Expired due to Fees

• Current Assignee: Microstrategy Incorporated
• Original Assignee: Microstrategy Incorporated
• Inventors: Yuan, Jun, Hurwood, William, Bedell, Jeffrey A., Li, Benjamin Z., Martin, Fabrice, Codini, Michael, Sahasrabudhe, Sadanand, Jhaveri, Ashutosh K.
• Primary Examiner(s): AL HASHEMI, SANA A
• Application Number:
  US09/883,499
• Time in Patent Office: 2,660 Days
• Field of Search: 707/2, 707/3, 707/4, 707/103.R, 707101-102, 707/200, 707/10, 707/100
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• 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/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A system and method for efficient data retrieval and processing in an online analytical processing system, a reporting system, a business intelligence system, or a data mining system. The system provides for retrieval and processing of a data result set from a plurality of data sources. The system includes a query structure assembly module, a syntax assembly module, and a process optimization module. The query structure assembly module defines a query structure based upon query assembly rules and a desired data set (such as the set of data for a particular OLAP report). The syntax assembly module defines one or more query language statements based upon the defined query structure. The process optimization module evaluates processing options based upon a database schema associated with the data sources. One or more query language statements may be assembled by the system and run against the data sources to return the desired data result set. A method and medium having processor readable code therein are also described.

Claims

1. A computer-implemented system for retrieval and processing of a data set from one or more data sources comprising:

a query structure assembly module for defining a query structure based upon a plurality of query assembly rules and a desired data set, wherein the plurality of query assembly rules are being used by the query structure assembly module to evaluate the desired data set;

a syntax assembly module for defining at least one query language statement based upon the defined query structure;

a process optimization module for evaluating processing options based upon a database schema associated with the data source, the process optimization module including an intermediate data processing method module for evaluating a plurality of methods for generating intermediate data sets within the data source(s) to determine a method for generating intermediate data sets within the data source(s) for calculating the desired data result set; and

whereby at least one query language statement is assembled and run against the data source(s) to return the desired data result set.

2. The system of claim 1, wherein the process optimization module includes a table selection module for evaluating the size of a selected set of tables from the database schema.

3. The system of claim 1, wherein the process optimization module includes an intermediate data processing selection module for evaluating the reusability of an intermediate data set in returning the desired data result set.

4. The system of claim 1, wherein the process optimization module includes a join path selection module for evaluating the length of at least one join path used in returning the desired data result set.

5. The system of claim 1, wherein the query structure assembly module accesses a query assembly rule associated with a selected database management system.

6. The system of claim 1, wherein the syntax assembly module accesses a syntax description associated with a selected database management system.

7. The system of claim 1, wherein the process optimization module accesses a query assembly rule, a syntax description, or a syntax pattern associated with a selected database management system.

8. The system of claim 1, wherein the system is a component in an online analytical processing systems, a reporting system, a business intelligence system, or a data mining system.

9. A computer-implemented method of generating a query language statement to be run against one or more data sources, comprising the steps of:

generating a query structure based upon a database schema associated with the data source, query assembly rules, and a desired data result set, the query assembly rules being used to evaluate the desired data set;

generating query language syntax based upon the query structure for returning the desired data result set from the data source(s);

evaluating a plurality of query assembly rules, syntax descriptions, or syntax patterns for process optimization;

evaluating a plurality of methods for generating intermediate data sets; and

returning a method for generating intermediate data sets.

10. The method of claim 9, wherein the step of evaluating the plurality of query assembly rules, syntax descriptions, or syntax patterns for process optimization includes evaluating the size of a plurality of sets of identified tables for returning the desired data result set.

11. The method of claim 9, wherein the step of evaluating the plurality of query assembly rules, syntax descriptions, or syntax patterns for process optimization includes evaluating the reusability of intermediate data sets.

12. The method of claim 9, wherein the step of evaluating a plurality of query assembly rules, syntax descriptions, or syntax patterns for process optimization includes evaluating a plurality of join paths used in returning the desired data result set.

13. The method of claim 9, wherein the step of evaluating a plurality of query assembly rules, syntax descriptions, or syntax patterns for process optimization includes evaluating at least one query assembly rule, syntax description, or syntax pattern associated with a selected database management system.

14. The method of claim 9, wherein the step of generating a query structure includes evaluating at least one query assembly rule associated with a selected database management system.

15. The method of claim 9, wherein the step of generating query language syntax includes evaluating at least one syntax description or syntax pattern associated with a selected database management system.

16. The method of claim 9, wherein the method is implemented in an online analytical processing systems, a reporting system, a business intelligence system, or a data mining system.

17. A medium having a processor readable program code embodied therein for retrieving and processing data from one or more data sources comprising:

code for causing the processor to evaluate a plurality of sets of tables within the data source(s) for generating a desired data result set;

code for causing the processor to evaluate at least one intermediate data set for reusability in generating the desired data result set;

code for causing the processor to evaluate a plurality of methods for generating intermediate data sets for use in generating the desired data result set;

code for causing the processor to evaluate a plurality of join paths used for joining tables to return the desired data result set; and

code for causing the processor to assemble at least one query language statement based upon the query structure and the evaluations of the plurality of sets of tables, the at least one intermediate data set, the plurality of methods for generating intermediate data sets, and the plurality of join paths.

18. The medium of claim 17, further comprising code for causing the processor to evaluate at least one query assembly rule associated with a selected database management system.

19. A computer-implemented system for constructing a structured query language statement to be run against at least one database, comprising:

a query structure assembly module for constructing a query structure based upon an evaluation of a desired data set by at least one query assembly rule;

a syntax assembly module for defining at least one query language statement based upon the constructed query structure; and

a process optimization module for evaluating the construction of the query structure and the defining of the at least one query language statement, the evaluation occurring during the construction of the query structure and/or during the defining of the at least one query statement, the process optimization module serving to evaluate a plurality of methods for generating intermediate data sets, and returning a method for generating intermediate data sets.

20. A computer-implemented system for retrieval and processing of a data set from one or more data sources comprising:

a query structure assembly module for defining a query structure based upon a plurality of query assembly rules and a desired data set, the query assembly rules being used by the query structure assembly module to evaluate the desired data set;

a syntax assembly module for defining at least one query language statement based upon the defined query structure;

a process optimization module for evaluating processing options based upon a database schema associated with the data source, the process optimization module including an intermediate data processing method module for evaluating a plurality of methods for generating intermediate data sets within the data source(s);

whereby at least one query language statement is assembled and run against the data source(s) to return the desired data set; and

wherein the intermediate data processing method module determines whether creation of a permanent table, temporary table, view, derived table, or sub-query is the most efficient method for handling intermediate data calculations.

21. A computer-implemented method of generating a query language statement to be run against one or more data sources, comprising the steps of:

generating a query structure based upon a database schema associated with the data source, query assembly rules, and a desired data result set, the query assembly rules being used to evaluate the desired data set;

generating query language syntax based upon the query structure for returning the desired data result set from the data source(s);

evaluating a plurality of query assembly rules, syntax descriptions, or syntax patterns for process optimization;

evaluating a plurality of methods for generating intermediate data sets; and

wherein the step of evaluating a plurality of methods for generating intermediate data sets comprises determining whether creation of a permanent table, temporary table, view, derived table, or sub-query is the most efficient method for handling intermediate data calculations, and returning the most efficient method for handling intermediate data calculations.

22. A medium having a processor readable program code embodied therein for retrieving and processing data from one or more data sources comprising:

code for causing the processor to evaluate a plurality of sets of tables within the data source(s) for generating a desired data result set;

code for causing the processor to evaluate at least one intermediate data set for reusability in generating the desired data result set;

code for causing the processor to evaluate a plurality of methods for generating intermediate data sets for use in generating the desired data result set;

code for causing the processor to evaluate a plurality of join paths used for joining tables to return the desired data result set;

code for causing the processor to assemble at least one query language statement based upon the query structure and the evaluations of the plurality of sets of tables, the at least one intermediate data set, the plurality of methods for generating intermediate data sets, and the plurality of join paths; and

code for determining whether creation of a permanent table, temporary table, view, derived table, or sub-query is the most efficient method for handling intermediate data calculations.

23. A computer-implemented system for constructing a structured query language statement to be run against at least one database, comprising:

a query structure assembly module for constructing a query structure based upon an evaluation of a desired data set by at least one query assembly rule;

a syntax assembly module for defining at least one query language statement based upon the constructed query structure;

a process optimization module for evaluating the construction of the query structure and the defining of the at least one query language statement, the evaluation occurring during the construction of the query structure and/or during the defining of the at least one query statement, the process optimization module serving to evaluate a plurality of methods for generating intermediate data sets; and

wherein the process optimization module'"'"'s evaluation of a plurality of methods for generating intermediate data sets comprises determining whether creation of a permanent table, temporary table, view, derived table, or sub-query is the most efficient method for handling intermediate data calculations, and returning the most efficient method for handing intermediate data calculations.