Aggregate navigation system

• US 20020099692A1
• Filed: 04/27/2001
• Published: 07/25/2002
• Est. Priority Date: 04/27/2000
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Hyperion Solutions Corporation (Oracle Corporation)
• Inventors: Novy, Robert F., Shah, Arun, Ertl, Robert A.
• Granted Patent: US 6,941,311 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 21/6218 to a system of files or objects, e.g. local or distributed file system or database
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• 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

Disclosed is a system, method, and apparatus for calculating metrics by using hierarchical level metadata to describe the various structures within the database. The hierarchical level metadata permit calculation of complex metrics by an analytical server which would otherwise be difficult or impossible. As a result of the way that the analytical server calculates the metrics, slicing and drilling are supported. Additionally, dimension and fact level security are also supported.

Claims

1. A method for calculating a measure in a database having a plurality of stars, each of the stars comprising a fact table and a table for each supported dimension, set of dimension indicators associated with a particular dimension whereby the dimension indicator indicates a level of aggregation associated , said method comprising:

selecting a stargroup associated with the measure, said stargroup comprising one or more stars comprising a set of dimension indicators, each of said dimension indicators associated with a particular dimension, and wherein each dimension indicator indicates a level of aggregation of an associated table comprising aggregated facts with respect to the associated dimension;

selecting a particular star of the one or more stars; and

querying the tables comprising facts.

2. The method of claim 1, wherein selecting the particular star further comprises:

comparing one or more dimension indicators of each of the one or more stars to one or more requested levels.

3. The method of claim 2, wherein comparing one or more dimension indicators of each of the one or more stars to one or more levels further comprises:

comparing each of the one or more stars, wherein each star is associated with a table of aggregated facts of successively lower aggregation.

4. The method of claim 2, wherein selecting the particular star further comprises:

selecting the particular star wherein each of the dimension indicators is equal to or exceed each of the requested levels.

5. The method of claim 1, further comprising:

receiving a request for calculating a metric, wherein said request to calculate a metric is associated with one or more levels of one or more dimensions; and

retrieving a metric definition associated with the metric, wherein the metric definition comprises the measure.

6. A computer-readable medium carrying one or more sequences of instructions for calculating measures, said instructions comprising:

selecting a stargroup associated with the measure, said stargroup comprising one or more stars comprising a set of dimension indicators, each of said dimension indicators associated with a particular dimension, and wherein each dimension indicator indicates a level of aggregation of an associated table comprising aggregated facts with respect to the associated dimension;

selecting a particular star of the one or more stars; and

querying the tables comprising facts and associated dimensions.

7. The computer readable medium of claim 6, wherein the instructions for selecting the particular star further comprises instructions for:

comparing one or more dimension indicators of each of the one or more stars to one or more requested levels.

8. The computer readable medium of claim 7, wherein the instructions for comparing one or more dimension indicators of each of the one or more stars to one or more levels further comprises instructions for:

comparing each of the one or more stars, wherein each star is associated with a table of aggregated facts of successively lower aggregation.

9. The computer readable medium of claim 7, wherein the instructions for selecting the particular star further comprises instructions for:

selecting the particular star wherein each of the dimension indicators is equal to or exceed each of the requested levels.

10. The computer readable medium of claim 6, wherein the sequences of instructions further comprise:

receiving a request for calculating a metric, wherein said request to calculate a metric is associated with one or more levels of one or more dimensions; and

retrieving a metric definition associated with the metric, wherein the metric definition comprises the measure.

11. A system for calculating measures, said system comprising:

a first memory means for storing a star identifier identifying a star associated with a particular fact table; and

a second memory means for storing an array of dimension indicators.

12. The system of claim 11, further comprising:

a third memory means for storing the aggregate fact table, wherein the aggregate fact table is aggregated with respect to at least one dimension at a particular level.

13. The system of claim 12, wherein at least one of the dimension indicators corresponds to the at least one dimension, and wherein at least one indicator is indicative of the particular level.

14. The system of claim 11, further comprising:

a first plurality of memory means, wherein each of the first plurality of memory means stores a first corresponding plurality of stars, wherein each of the first plurality of stars is associated with a corresponding one of a first plurality of aggregate fact tables, and wherein each of the first aggregate fact tables support a first measure;

a second plurality of memory means, wherein each of the second plurality of memory means stores a corresponding one of a first plurality arrays for storing dimension indicators.

15. The system of claim 14, wherein the first plurality of memory means stores the stars based on the degree of aggregation of the first aggregate fact tables associated therewith.

16. The system of claim 14, further comprising:

a third plurality of memory means, wherein each of the second plurality of memory means stores a second corresponding plurality of stars, wherein each of the second plurality of stars is associated with a corresponding one of a second plurality of aggregate fact tables, and wherein each of the second plurality of aggregate fact tables support a second measure;

a fourth plurality of memory means, wherein each of the fourth plurality of memory means stores a corresponding one of a second plurality arrays for storing dimension indicators.