Methods of navigating a cube that is implemented as a relational object

• US 7,171,427 B2
• Filed: 04/25/2003
• Issued: 01/30/2007
• Est. Priority Date: 04/26/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Witkowski, Andrew, Greenfield, David, Dorman, Gregory
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): Black; Linh
• Application Number:
  US10/424,003
• Publication Number:
  US 20040006574A1
• Time in Patent Office: 1,376 Days
• Field of Search: 707 1-1041
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2264 Multidimensional index structures
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99948 Application of database or data structure, e.g. distributed, multimedia, or image

Abstract

A method and mechanism for performing an operation based on multidimensional data in a relational database. A query is generated that includes a first set of instructions to select a portion of multidimensional data for inclusion in a relational cube and a second set of instructions to group the portion of multidimensional data in the relational cube using at least two or more levels of granularity of at least one dimension, thereby causing a relational cube to be generated. Based on a received request for an operation to be performed, the query is modified to include a third set of instructions that represent the requested operation; and the query is submitted to the relational database engine of the relational database management system. Thereafter, the operation is performed against the relational cube.

Claims

1. A method for performing an operation based on multidimensional data in a relational database, wherein the multidimensional data that is stored in the relational database includes a plurality of dimensions and at least one dimension of the plurality of dimensions includes a hierarchy having two or more levels of granularity, the method comprising the computer-implemented steps of:

storing a query that defines a cube, the query including a first set of instructions to select a portion of said multidimensional data for inclusion in said cube and a second set of instructions to cause said cube to contain multidimensional data aggregated at each of said two or more levels of granularity of said at least one dimension;

after the query has been stored, receiving a request for a first operation on the cube defined by said query;

based on the request, modifying the query to generate a modified query that includes a third set of instructions that represent the first operation; and

submitting the modified query to a relational database engine to cause the first operation to be performed against the cube.

2. The method of claim 1, wherein the query and the modified query are written in structured query language (SQL).

3. The method of claim 1, wherein the step of submitting the modified query is performed in response to receiving the request.

4. The method of claim 1, wherein the cube is a multidimensional cube.

5. The method of claim 1, wherein the cube is a multidimensional cube edge.

6. The method of claim 1, wherein the request is a query written in structured query language (SQL).

7. The method of claim 1, wherein the multidimensional data is stored in said star relational database using a star schema.

8. The method of claim 1, wherein the multidimensional data is stored in said relational database using snowflake schema.

9. The method of claim 1, further comprising the computer-implemented step of:

instantiating the cube contemporaneous with the step of generating the query; and

deallocating the cube after the modified query is performed against the cube.

10. The method of claim 1, wherein the step of maintaining the cube as the non-persistent object further comprises the computer-implemented step of:

maintaining the cube as an in-line view.

11. The method of claim 1, further comprising the computer-implemented step of:

maintaining the cube after the modified query is performed against the cube.

12. The method of claim 1, wherein the step of storing the cube as a persistent object further comprises the computer-implemented step of:

storing the cube as a structured query language view.

13. The method of claim 1, wherein the first operation is to generate a page and the method further comprises the computer-implemented steps of:

receiving, from the relational database engine, results based on execution of the first query against the cube; and

based on the results, generating the page.

14. The method of claim 13, wherein the step of generating the page includes:

generating includes one or more columns in the page based on a first set of one or more levels of a first dimension of the plurality of dimensions;

generating includes one or more rows in the page based on a second set of one or more levels of a second dimension of the plurality of dimensions; and

generating includes one or more crosstabs in the page based on a third set of one or more levels of a third dimension of the plurality of dimensions.

15. The method of claim 14, further comprising the steps of:

receiving a second request for a second operation;

based on the second request, modifying the query to produce a second modified query that includes a forth set of instructions that represent the second operation; and

submitting the second modified query to the relational database engine to cause the second operation to be performed against the cube.

16. The method of claim 15, wherein the second operation performs a drill operation on the page.

17. The method of claim 16, wherein the drill operation is a hierarchical drilling operation.

18. The method of claim 16, wherein the drill operation is an order preserving drilling operation.

19. The method of claim 16, wherein the drill operation is an asymmetric drilling operation.

20. The method of claim 15, wherein the second operation performs a pivot operation on the page.

21. The method of claim 20, wherein the pivot operation modifies the page such that the one or more columns are based on the second set of one or more levels of the second dimension of the plurality of dimensions and the one or more rows are based on the first set of one or more levels of the first dimension of the plurality of dimensions.

22. The method of claim 20, wherein the pivot operation modifies the page such that the one or more rows are based on the third set of one or more levels of the third dimension of the plurality of dimensions and the one or more crosstabs are based on the second set of one or more levels of the second dimension of the plurality of dimensions.

23. The method of claim 20, wherein the pivot operation modifies the page such that the one or more columns are based on the third set of one or more levels of the third dimension of the plurality of dimensions and the one or more crosstabs are based on the first set of one or more levels of the first dimension of the plurality of dimensions.

24. The method of claim 15, wherein the second operation generates a calculated measure for the page.

25. The method of claim 15, wherein the second operation generates a calculated member for the page.

26. The method of claim 1, wherein the step of modifying the query is performed by performing one or more operations selected from the group consisting of:

group pruning, partition pruning, bitmap index pruning, predicate replication, view expansion, view merging, and using a UNION rewrite with a materialized view.

27. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in any one of claims 1–

26.