Techniques for using database metric results

• US 8,595,217 B2
• Filed: 01/09/2009
• Issued: 11/26/2013
• Est. Priority Date: 01/09/2009
• Status: Active Grant

• Current Assignee: Teradata Corporation
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Phibbs, Paul H., Ruegsegger, Marianne, Draper, Linette
• Primary Examiner(s): MOSER, BRUCE M
• Application Number:
  US12/351,486
• Publication Number:
  US 20100185602A1
• Time in Patent Office: 1,782 Days
• Field of Search: 707/2, 707/3, 707/999.002, 707/999.003, 707/713, 707/721, 707/771, 707/4, 707/999.004, 707/769, 707/715
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24528 Standardisation; Simplification
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24535 of sub-queries or views

Abstract

Techniques for using database metric results are provided. Structure Query Language (SQL) statements are parsed for multiple metric calculations. Each metric calculation is dynamically processed against a database to obtain combined results. The combined results are fed to remaining portions of the SQL statements as a source for or a driver to the remaining portions of the SQL statements.

Claims

1. A method implemented in a non-transitory computer-readable storage medium and processed by a processor to perform the method, comprising:

receiving, by the processor, Structured Query Language (SQL) statements;

identifying, by the processor, a first metric calculation and a second metric calculation included and used in the SQL statements, wherein both the first metric calculation and the second metric calculation are processed as part of the SQL statements and summed as a source input for a target rule that is also processed and included within the SQL statements, the source input is a sum of previous rules; and

processing, by the processor, the first metric calculation and the second metric calculation against a database and using results from both the first metric calculation and the second metric calculation as the source that is fed to the target rule within the SQL statements, and wherein the target rule is processed with the results.

2. The method of claim 1, wherein receiving further includes interacting with a user via a graphical user interface to receive the SQL statements.

3. The method of claim 1, wherein identifying further includes parsing the SQL statements to identify a first rule that defines the first metric calculation and to identify a second rule that defines the second metric calculation.

4. The method of claim 3, wherein identifying further includes identifying the second metric calculation as a different metric from that which is associated with the first metric calculation.

5. The method of claim 1, wherein identifying further includes recognizing two different rules that define the first metric calculation within the SQL statements.

6. The method of claim 1, wherein processing further includes summing the first metric calculation and the second metric calculation to obtain the results that are provided as the source to the target rule when processing the SQL statements.

7. A method implemented in a non-transitory computer-readable storage medium and processed by a processor to perform the method, comprising:

dynamically parsing, by the processor, Structured Query Language (SQL) statements to obtain from the SQL statements a first metric calculation and a second metric calculation both the first metric calculation and the second metric calculation included within the SQL statements;

processing, by the processor, both the first metric calculation and the second metric calculation against a database, as part of the processing associated with executing the SQL statements, and then summing first results from the first metric calculation with second results from the second metric calculation to produce combined results, and wherein the first metric calculation is defined by chained rules; and

using, by the processor, the combined results as input to other portions of the SQL statements while processing the other portions against the database, wherein the input is a sum of previous rules.

8. The method of claim 7, wherein dynamically parsing further includes recognizing the first metric calculation as a first rule that is to be evaluated when the first metric calculation is processed.

9. The method of claim 8, wherein dynamically parsing further includes recognizing the second metric calculation as a second rule that is to be evaluated when the second metric calculation is processed.

10. The method of claim 8, wherein processing further includes processing a second rule chain representing another set of rules when calculating the second metric calculation.

11. The method of claim 7, wherein processing further includes producing the combined results as a source to the other portions of the SQL statements.

12. The method of claim 7, wherein processing further includes producing the combined results as a driver to the other portions of the SQL statements.

13. The method of claim 7, wherein processing further includes processing a first rule chain representing a set of rules when calculating the first metric calculation.

14. A computer-implemented system that executes on one or more processors, the system comprising:

a processor configured with a Structured Query Language (SQL) preprocessor implemented in a non-transitory computer-readable storage medium and executed by the processor of a network; and

the processor configured for accessing a data store implemented in a non-transitory computer-readable storage medium and accessible to the SQL preprocessor;

wherein the SQL preprocessor iterates SQL statements while processing the SQL statements to automatically and dynamically identify and process multiple metric calculations that are defined and included within the SQL statements against the database to produce combined results that feed other portions of the SQL statements while those other portions of the SQL statements are processed, the multiple metric calculations defined by chained rules, and the combined results that feed other portions of the SQL statements is a sum of previous rules.

15. The system of claim 14, wherein the SQL preprocessor iterates the SQL statements to process multiple sets of rules for each of the metric calculations.

16. The system of claim 14, wherein the SQL preprocessor feeds the combined results as a source input to a rule that represents the other portions of the SQL statements.

17. The system of claim 14, wherein the SQL preprocessor feeds the combined results as a driver to the remaining portions of the SQL statements.

18. The system of claim 14, wherein the SQL preprocessor interacts with a user via a graphical user interface to interactively acquire the SQL statements.

19. The system of claim 14, wherein data store is a data warehouse that includes a collection of databases logically organized and accessible as a single unit.