Ranking query optimization in analytic applications

• US 6,640,226 B1
• Filed: 06/19/2001
• Issued: 10/28/2003
• Est. Priority Date: 06/19/2001
• Status: Expired due to Term

• Current Assignee: Informatica LLC (Informatica, Inc. (California))
• Original Assignee: Informatica Corporation
• Inventors: Shringeri, Sanjatha, Hsu, Joy
• Primary Examiner(s): Rones, Charles
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US09/885,666
• Time in Patent Office: 861 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/5, 707/6, 707/7, 707/8, 707/9, 707/10, 707/101, 707/102, 707/103, 707/104
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/24578 using ranking
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A method of optimizing ranking queries in analytic applications. The method involves dividing a ranking query constructed by a user into multiple sub-commands. These sub-commands are based on the relationship between the foreign key and ranking attributes of a table of data and are run as a batch of commands. As usable data is extracted from each sub-command, it is stored and the foreign key is used as a filter for the next query in the batch. This eliminates accessing irrelevant data and facilitates the query operation. The extracted data is joined as the last step of the process in order to minimize the number of join operations performed by the query while the batch is being executed, thereby facilitating the query process.

Claims

1. A method for facilitating access to database information defined by an analytic application, said method comprising the steps of:

a) receiving operational data from a data source;

b) generating metrics from said operational data using said analytic application;

c) receiving a multi-level ranking query from a user, wherein said multi-level ranking query requires successive rankings of said metrics;

d) dividing said multi-level ranking query into a first part that defines a first subset of the metrics from said operational data and a second part that defines a second subset of the metrics from said operational data;

e) executing said first part of said multi-level ranking query, wherein said first part executes a first ranking of said first subset only;

f) executing said second part of said multi-level ranking query, wherein said second part executes a second ranking of metrics from said first ranking; and

g) joining stored usable data extracted from a first table of data that is generated as a result of said first ranking with stored usable data extracted from a second table of data that is generated as a result of said second ranking.

2. The method as recited in claim 1, wherein said method further comprises the steps of:

dividing said ranking query into a plurality of sub-commands;

executing a first or said plurality of sub-commands;

generating a first table of data as a result of executing said first sub-command;

extracting usable data from said first table of data as a result of executing said first sub-command;

storing said usable data extracted from said first table of data;

executing a second of said plurality of sub-commands;

generating a second table of data as a result of executing said second sub-command;

extracting usable data from said second table of data as a result of executing said second sub-command;

storing said usable data extracted from said second table of data;

joining said stored usable data extracted from said first table of data with said stored usable data extracted from said second table of data; and

presenting said joined data to said user.

3. The method of claim 2, wherein said steps are performed automatically.

4. The method of claim 2, wherein said joining of stored data is performed after said first sub-command and said second sub-command.

5. The method of claim 2, wherein said extracted data from said first table of data is used to limit the size of said second table of data.

6. The method of claim 5, wherein the foreign key from said first table of data is used as a primary key for said second table of data.

7. A computer system comprising:

a bus;

a memory unit coupled to said bus; and

a processor coupled to said bus, said processor for executing a method for facilitating access to database information defined by an analytic application, said method comprising the steps of;

a) receiving operational data from a data source;

b) generating metrics from said operational data using said analytic application;

c) receiving a multi-level ranking query from a user, wherein said multi-level ranking query requires succesive rankings of said metrics;

d) dividing said multi-level ranking query into a first part that defines a first subset of the metrics from said operational data and a second part that defines a second subset of the metrics from said operational data;

e) executing said first part of said multi-level ranking query, wherein said first part executes a first ranking of said first subset only;

f) executing said second part of said multi-level ranking query, wherein said second part executes a second ranking of metrics from said first ranking; and

g) joining stored usable data extracted from a first table of data that is generated as a result of said first ranking with stored usable data extracted from a second table of data that is generated as a result of said second ranking.

8. The computer system as recited in claim 7, wherein said method further comprises the steps of:

dividing said ranking query into a plurality of sub-commands;

executing a first of said plurality of sub-commands;

generating a first table of data as a result of executing said first sub-command;

extracting usable data from said first table of data as a result of executing said first sub-command;

storing said usable data extracted from said first table of data;

executing a second of said plurality of sub-commands;

generating a second table of data as a result of executing said second sub-command;

extracting usable data from said second table of data as a result of executing said second sub-command;

storing said usable data extracted from said second table of data;

joining said stored usable data extracted from said first table of data with said stored usable data extracted from said second table of data; and

presenting said joined data to said user.

9. The computer system of claim 8, wherein said steps are performed automatically.

10. The computer system of claim 8, wherein said joining of stored data is performed after said first sub-command and said second sub-command.

11. The computer system of claim 8, wherein said extracted data from said first table of data is used to limit the size of said second table of data.

12. The computer system of claim 11, wherein the foreign key from said first table of data is used as a primary key for said second table of data.

13. A computer usable medium having computer-readable program code embodied therein for causing a computer system to perform the steps of:

a) receiving operational data from a data source;

b) generating metrics from said operational data using said analytic application;

c) receiving a multi-level ranking query from a user, wherein said multi-level ranking query requires successive rankings of said metrics;

d) dividing said multi-level ranking query into a first part that defines a first subset of the metrics from said operational data and a second part that defines a second subset of the metrics from said operational data;

e) executing said first part of said multi-level ranking query, wherein said first part executes a first ranking of said first subset only;

f) executing said second part of said multi-level ranking query, wherein said second part executes a second ranking of metrics from said first ranking; and

g) joining stored usable data extracted from a first table of data that is generated as a result of said first ranking with stored usable data extracted from a second table of data that is generated as a result of said second ranking.

14. The computer usable medium as recited in claim 13, wherein said method further comprises the steps of:

dividing said ranking query into a plurality of sub-commands;

executing a first of said plurality of sub-commands;

generating a first table of data as a result of executing said first sub-command;

extracting usable data from said first table of data as a result of executing said first sub-command;

storing said usable data extracted from said first table of data;

executing a second of said plurality of sub-commands;

generating a second table of data as a result of executing said second sub-command;

extracting usable data from said second table of data as a result of executing said second sub-command;

storing said usable data extracted from said second table of data;

joining said stored usable data extracted from said first table of data with said stored usable data extracted from said second table of data; and

presenting said joined data to said user.

15. The computer usable medium of claim 14, wherein said steps are performed automatically.

16. The computer usable medium of claim 14, wherein said joining of stored data is performed after said first sub-command and said second sub command.

17. The method of claim 14, wherein a foreign key from said first table of data is used as a primary key for said second table of data.