Method, system, and program for use of metadata to create multidimensional cubes in a relational database

• US 7,181,450 B2
• Filed: 12/18/2002
• Issued: 02/20/2007
• Est. Priority Date: 12/18/2002
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Malloy, William Earl, Colossi, Nathan Gevaerd
• Primary Examiner(s): Wong; Dong
• Assistant Examiner(s): Black; Linh
• Application Number:
  US10/323,131
• Publication Number:
  US 20040122844A1
• Time in Patent Office: 1,525 Days
• Field of Search: 707 1-1041
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• 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

Disclosed is a method, system, and program for query processing. Metadata for a facts metadata object and one or more dimension metadata objects that are associated with the facts metadata object is stored. A view with columns for one or more measures in the facts metadata object and one or more attributes in the one or more dimension metadata objects is constructed. Additional metadata that describes roles of columns in the fact and dimension metadata objects is generated.

Also disclosed is a computer-readable medium for storing data for access by a program. A data structure stored in the computer-readable medium includes data for use by the program. The data includes a cube model metadata object that includes a facts metadata object, one or more dimension metadata objects, and one or more join metadata objects that describe how one or more tables in the facts metadata object and one or more tables in the one or more dimension metadata objects are joined. The data also includes a cube metadata object that represents a subset of the cube model metadata object and comprises a view with columns for one or more measures of one of the facts metadata objects and one or more attributes of one or more of the dimension metadata objects and a document that describes roles of columns in the facts metadata object and the one or more dimension metadata objects.

Claims

1. A method for query processing, comprising:

storing metadata for a facts metadata object and one or more dimension metadata objects that are associated with the facts metadata object, wherein the metadata is multidimensional metadata, wherein the facts metadata object includes information about measures, attributes, and related joins, and wherein each of the one or more dimension metadata objects includes one or more of information about hierarchies, attributes, attribute relationships, and related joins;

constructing a view with columns for one or more measures in the facts metadata object and one or more attributes in the one or more dimension metadata objects; and

generating additional metadata that describes roles of columns in the facts metadata object and the one or more dimension metadata objects and relationships of the columns.

2. The method of claim 1, further comprising:

receiving a specification describing the facts metadata object, the one or more dimension metadata objects, and a join metadata object that describes how one or more tables in the facts metadata object and one or more tables in the one or more dimension metadata objects are joined; and

creating the metadata from the specification.

3. The method of claim 1, wherein the metadata comprises metadata objects.

4. The method of claim 1, further comprising:

generating a cube model metadata object from the specification.

5. The method of claim 1, further comprising:

generating a cube metadata object that includes the view and the additional metadata from the cube model metadata object.

6. The method of claim 1, wherein the view may be queried.

7. The method of claim 1, wherein the additional metadata may be queried.

8. The method of claim 1, wherein the metadata is stored in a database catalog.

9. The method of claim 1, further comprising:

generating a structured query language statement to construct the view.

10. The method of claim 1, wherein the view is constructed using aggregate functions of the measures and a ROLLUP operator for each dimension specified in the one or more dimension metadata objects.

11. The method of claim 1, wherein the additional metadata is stored in an extensible markup language document.

12. The method of claim 1, wherein the columns in the view are ordered in accordance with the ordering of columns in the additional metadata.

13. A computer-readable medium for storing data for access by a program, comprising:

a data structure stored in the computer-readable medium, wherein the data structure includes data for use by the program and comprising;

a cube model metadata object that includes a facts metadata object, one or more dimension metadata objects, and one or more join metadata objects that describe how one or more tables in the facts metadata object and one or more tables in the one or more dimension metadata objects are joined; and

a cube metadata object that represents a subset of the cube model metadata object and comprises a view with columns for one or more measures of one of the facts metadata objects and one or more attributes of one or more of the dimension metadata objects and a document that describes roles of columns in the facts metadata object and the one or more dimension metadata objects.

14. The computer-readable medium of claim 13, wherein the facts metadata object comprises a set of measure metadata objects, a set of dimension metadata objects, and a set of join metadata objects.

15. The computer-readable medium of claim 13, wherein each of the dimension metadata objects comprises a set of attribute metadata objects, a set of hierarchy metadata objects, a set of join metadata objects, and a type.

16. The computer-readable medium of claim 13, wherein each of the metadata objects has a set of common properties.

17. The computer-readable medium of claim 13, wherein the data structure further includes:

at least one cube facts metadata object;

at least one cube dimension metadata object; and

at least one cube hierarchy metadata object.

18. The computer-readable medium of claim 13, wherein the cube metadata object specifies the cube model metadata object.

19. A system for query processing, comprising:

a computer system having at least one program for;

(i) storing metadata for a facts metadata object and one or more dimension metadata objects that are associated with the facts metadata object, wherein the metadata is multidimensional metadata, wherein the facts metadata object includes information about measures, attributes, and related joins, and wherein each of the one or more dimension metadata objects includes one or more of information about hierarchies, attributes, attribute relationships, and related joins;

(ii) constructing a view with columns for one or more measures in the facts metadata object and one or more attributes in the one or more dimension metadata objects; and

(iii) generating additional metadata that describes roles of columns in the fact and dimension metadata objects and relationships of the columns.

20. The system of claim 19, wherein the at least one program further comprises:

receiving a specification describing the facts metadata object, the one or more dimension metadata objects, and a join metadata object that describes how one or more tables in the facts metadata object and one or more tables in the one or more dimension metadata objects are joined; and

creating the metadata from the specification.

21. The system of claim 19, wherein the at least one program further comprises:

generating a structured query language statement to construct the view.

22. The system of claim 19, wherein the view is constructed using aggregate functions of the measures and a ROLLUP operator for each dimension specified in the one or more dimension metadata objects.

23. The system of claim 19, wherein the additional metadata is stored in an extensible markup language document.

24. The system of claim 19, wherein the columns in the view are ordered in accordance with the ordering of columns in the additional metadata.

25. An article of manufacture including a program for query processing, wherein the program causes operations to be performed, the operations comprising:

storing metadata for a facts metadata object and one or more dimension metadata objects that are associated with the facts metadata object, wherein the metadata is multidimensional metadata, wherein the facts metadata object includes information about measures, attributes, and related joins, and wherein each of the one or more dimension metadata objects includes one or more of information about hierarchies, attributes, attribute relationships, and related joins;

constructing a view with columns for one or more measures in the facts metadata object and one or more attributes in the one or more dimension metadata objects; and

generating additional metadata that describes roles of columns in the facts metadata object and dimension metadata objects and relationships of the columns.

26. The article of manufacture of claim 25, the operations further comprising:

receiving a specification describing the facts metadata object, the one or more dimension metadata objects, and a join metadata object that describes how one or more tables in the facts metadata object and one or more tables in the one or more dimension metadata objects are joined; and

creating the metadata from the specification.

27. The article of manufacture of claim 25, the operations further comprising:

generating a structured query language statement to construct the view.

28. The article of manufacture of claim 25, wherein the view is constructed using aggregate functions of the measures and a ROLLUP operator for each dimension specified in the one or more dimension metadata objects.

29. The article of manufacture of claim 25, wherein the additional metadata is stored in an extensible markup language document.

30. The article of manufacture of claim 25, wherein the columns in the view are ordered in accordance with the ordering of columns in the additional metadata.