Registration of solved cubes within a relational database management system

• US 7,366,730 B2
• Filed: 05/23/2003
• Issued: 04/29/2008
• Est. Priority Date: 04/26/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Greenfield, David, Dorman, Greg
• Primary Examiner(s): Alam; Shahid Al
• Application Number:
  US10/445,113
• Publication Number:
  US 20030208506A1
• Time in Patent Office: 1,803 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-205
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• 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
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

Performing operations on multidimensional data managed by a relational database system is facilitated by registration of solved cubes with a RDBMS. A solved cube is an abstract construct likened to a view or a table on relational data and contains not only base data, but also aggregated measure data that is base data that has been processed across multiple levels of a hierarchical dimension. In an embodiment, a solved cube may provide all the pre-computed aggregated data for a particular context that the solved cube represents. In another embodiment, a solved cube may not provide any pre-computed data, but may provide several procedures, operations and/or instructions that can be executed to compute aggregated data for a particular context that the solved cube represents. A solved cube is registered with a RDBMS by providing metadata that defines a given solved cube. Therefore, a database server is able interpret the metadata, and to perform operations on the given solved cube based on the metadata.

Claims

1. A method for performing an operation based on multidimensional data managed by a relational database management system, 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:

registering, with said relational database management system, metadata that defines a cube, wherein said metadata includes

data that identifies that said cube is a solved cube; and

data that indicates how to handle requests that involve said solved cube, including data that identifies one or more machine-executable procedures which can be dynamically executed at runtime to compute resulting measure data for a particular scenario that the solved cube represents;

receiving, through an OLAP API, a request for an operation to be performed on multidimensional data associated with said solved cube; and

in response to said request, performing the steps of

determining, based at least in part on said metadata, that said solved cube can be used to fulfill said request,

determining, based on said metadata, one or more operations to perform to fulfill said request, and

generating results based on performing said one or more operations including executing at least one of said one or more machine-executable procedures.

2. The method of claim 1, wherein said metadata includes:

data that identifies one or more mechanisms for obtaining all data associated with said solved cube.

3. A computer-readable storage 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 claim 1, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

4. The method of claim 1,

wherein said database contains data that is pre-computed across at least two of said two or more levels of said at least one dimension;

and

wherein said metadata identifies where, in said database, to locate particular data of said pre-computed data.

5. A computer-readable storage 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 claim 4, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

6. The method of claim 1, wherein said metadata identifies columns maintained for each dimension of said plurality of dimensions.

7. A computer-readable storage 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 claim 6, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

8. The method of claim 1, wherein said metadata describes a view.

9. The method of claim 5, wherein said view is defined in one or more SQL statements.

10. A computer-readable storage 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 claim 9, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

11. The method of claim 8, wherein said view calls an executable procedure.

12. A computer-readable storage 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 claim 11, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

13. The method of claim 8, wherein said view uses a table function.

14. A computer-readable storage 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 claim 13, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

15. A computer-readable storage 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 claim 8, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

16. The method of claim 1, wherein said metadata identifies a form associated with said solved cube.

17. The method of claim 16, wherein said solved cube references a column that is associated with a measure, for storing values of the measure that are grouped at levels of said plurality of dimensions.

18. A computer-readable storage 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 claim 17, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

19. The method of claim 16,

wherein said form of said solved cube is a rollup form;

and

wherein said solved cube references columns that are associated with each level of each dimension of said plurality of dimensions, for storing values that are associated with a respective level.

20. A computer-readable storage 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 claim 19, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

21. The method of claim 16,

wherein said form of said solved cube is a rollup form;

wherein said solved cube references a table that includes columns that are associated with each level of each dimension of said plurality of dimensions for storing values that are associated with each level of each dimension, and columns for storing grouping identifiers that are associated with each dimension; and

wherein said grouping identifiers identify a level of a dimension at which pre-computed data in said table is grouped.

22. A computer-readable storage 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 claim 21, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

23. The method of claim 16,

wherein said form of said solved cube is a grouping set form;

wherein one or more levels of said plurality of dimensions are not specified as part of a grouping set associated with the solved cube; and

wherein said solved cube references columns that are associated with each level of each dimension of said plurality of dimensions, for storing values that are associated with a respective level.

24. The method of claim 23, wherein the particular value that is associated with columns that are associated with said one or more levels that are not specified as part of said grouping set is a null value.

25. A computer-readable storage 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 claim 24, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

26. A computer-readable storage 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 claim 24, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

27. The method of claim 16,

wherein said form of said solved cube is a grouping set form;

wherein one or more levels of said plurality of dimensions are not specified as part of a grouping set corresponding to said solved cube;

wherein said solved cube references a table that includes columns that are associated with each level of each dimension of said plurality of dimensions for storing values that are associated with each level of each dimension, and columns for storing grouping identifiers that are associated with each dimension; and

wherein said grouping identifiers identify a level of a dimension at which pre-computed data in said table is grouped.

28. A computer-readable storage 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 claim 27, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

29. The method of claim 16,

wherein said form of said solved cube is an embedded total form;

and

wherein said solved cube references columns that are associated with each dimension of said plurality of dimensions, for storing values that are associated with a respective level of a respective dimension.

30. A computer-readable storage 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 claim 29, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

31. The method of claim 16,

wherein said form of said solved cube is an embedded total form;

wherein said solved cube references a table that includes columns that are associated with each dimension of said plurality of dimensions for storing values that are associated with a respective level of a respective dimension, and columns for storing grouping identifiers that are associated with each dimension; and

wherein said grouping identifiers identify a level of a dimension at which pre-computed data in said table is grouped.

32. A computer-readable storage 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 claim 31, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

33. A computer-readable storage 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 claim 16, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

34. A computer-readable storage 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 claim 1, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

35. The method of claim 1, wherein said receiving a request includes receiving a query that identifies said solved cube.

36. A computer-readable storage 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 claim 35, wherein the computer-readable storage medium is one of volatile memory or non-volatile memory.

37. The method of claim 1, wherein said request is received through an OLAP API.

38. An apparatus for performing an operation based on multidimensional data managed by a relational database management system, 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 apparatus comprising:

one or more processors;

memory operatively coupled to the one or more processors;

means for registering, with said relational database management system, metadata that defines a cube, wherein said metadata includes

data that identifies that said cube is a solved cube; and

data that indicates how to handle requests that involve said solved cube, including data that identifies one or more machine-executable procedures which can be dynamically executed at runtime to compute resulting measure data for a particular scenario that the solved cube represents;

means for receiving, through an OLAP API, a request for an operation to be performed on multidimensional data associated with said solved cube;

means for determining, based at least in part on said metadata, that said solved cube can be used to fulfill said request,

means for determining, based on said metadata, one or more operations to perform to fulfill said request, and

means for generating results based on performing said one or more operations including executing at least one of said one or more machine-executable procedures.

39. The apparatus of claim 38, wherein the means for receiving includes means for receiving said request through an OLAP API.