Method, system, and program for specifying multidimensional calculations for a relational OLAP engine

• US 20040139061A1
• Filed: 01/13/2003
• Published: 07/15/2004
• Est. Priority Date: 01/13/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Malloy, William Earl, Tomlyn, Craig Reginald, Colossi, Nathan Gevaerd, Pirahesh, Mir Hamid
• Granted Patent: US 7,953,694 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Disclosed is a system, method, and program for specifying multidimensional calculations. Selection of a subset of a cube model metadata object that is generated from a facts metadata object and one or more dimension metadata objects is received. The facts metadata object references one or more measure metadata objects. A statement is generated for retrieving multidimensional information using metadata in the cube model metadata object and the measure metadata objects, wherein each of the measure metadata objects specifies one or more aggregations.

Claims

1. A method for specifying multidimensional calculations, comprising:

receiving selection of a subset of a cube model metadata object that is generated from a facts metadata object and one or more dimension metadata objects, wherein the facts metadata object references one or more measure metadata objects; and

generating a statement for retrieving multidimensional information using metadata in the cube model metadata object and the one or more measure metadata objects, wherein each of the measure metadata objects specifies one or more aggregations.

2. The method of claim 1, wherein the statement is a structured query language statement.

3. The method of claim 1, wherein each of the measure metadata objects specifies one or more structured query language expressions.

4. The method of claim 3, wherein each of the structured query language expressions includes a template for building a query language expression.

5. The method of claim 4, wherein the template uses a token notation that references a specific column, attribute, or measure from a list of columns, attributes and measures.

6. The method of claim 3, wherein each of the structured query language expressions includes a list of columns, attributes, and measures.

7. The method of claim 1, wherein the structured query language statement is generated based on the specified one or more aggregations in each of the measure metadata objects.

8. The method of claim 7, wherein the list of aggregations comprises a list of aggregation functions and corresponding dimensions sets.

9. The method of claim 8, wherein the dimension set specifies NULL for a corresponding aggregation function to include all available dimensions other than dimensions specified in another aggregation function in the list of aggregations.

10. The method of claim 7, wherein the measure metadata object specifies one or more structured query language expressions and wherein the structured query language expressions are used as input to an aggregation in the list of aggregations.

11. The method of claim 1, wherein the statement is generated using metadata in a hierarchy metadata object to build ROLLUP clauses.

12. The method of claim 1, wherein a measure metadata object references another measure metadata object.

13. The method of claim 1, wherein generation of the structured query language statement further comprises:

generating a SELECT statement for a grand total query.

14. The method of claim 1, wherein generation of the structured query language statement further comprises:

generating a SELECT statement for a slice of the subset of the cube model metadata object.

15. The method of claim 1, wherein generation of the structured query language statement further comprises:

generating a SELECT statement for the subset of the cube model metadata object.

16. The method of claim 1, further comprising:

separating symmetric measures and asymmetric measures defined in the one or more measure metadata objects;

generating structured query language statements for the symmetric measures;

generating structured query language statements for the asymmetric measures; and

combining the structured query language statements for the symmetric and asymmetric measures into a single structured query language statement.

17. The method of claim 16, wherein the combining comprises use of a join.

18. The method of claim 1, further comprising:

determining whether a measure is compatible with one or more measures; and

if the measure is not compatible with the one or more measures, determining whether any of the measures may be rewritten; and

if any of the measures may be rewritten, rewriting the measures.

19. The method of claim 1, further comprising:

generating a cube metadata object based on the selection of the subset of the cube model metadata object, including generating a structured query language statement for creation of a cube view, wherein the structured query language statement is generated from metadata in the one or more measure metadata objects.

20. The method of claim 1, further comprising:

under control of an application program, using the cube model metadata object and one or more of the measure metadata objects, generating a structured query language statement to retrieve multidimensional information.

21. A system for specifying multidimensional calculations, comprising:

a computer system having at least one program for;

receiving selection of a subset of a cube model metadata object that is generated from a facts metadata object and one or more dimension metadata objects, wherein the facts metadata object references one or more measure metadata objects; and

generating a statement for retrieving multidimensional information using metadata in the cube model metadata object and the one or more measure metadata objects, wherein each of the measure metadata objects specifies one or more aggregations.

22. The system of claim 21, wherein the statement is a structured query language statement.

23. The system of claim 21, wherein each of the measure metadata objects specifies one or more structured query language expressions.

24. The system of claim 23, wherein each of the structured query language expressions includes a template for building a query language expression.

25. The system of claim 24, wherein the template uses a token notation that references a specific column, attribute, or measure from a list of columns, attributes and measures.

26. The system of claim 23, wherein each of the structured query language expressions includes a list of columns, attributes, and measures.

27. The system of claim 21, wherein the structured query language statement is generated based on the specified one or more aggregations in each of the measure metadata objects.

28. The system of claim 27, wherein the list of aggregations comprises a list of aggregation functions and corresponding dimensions sets.

29. The system of claim 28, wherein the dimension set specifies NULL for a corresponding aggregation function to include all available dimensions other than dimensions specified in another aggregation function in the list of aggregations.

30. The system of claim 27, wherein the measure metadata object specifies one or more structured query language expressions and wherein the structured query language expressions are used as input to an aggregation in the list of aggregations.

31. The system of claim 21, wherein the statement is generated using metadata in a hierarchy metadata object to build ROLLUP clauses.

32. The system of claim 21, wherein a measure metadata object references another measure metadata object.

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

generating a SELECT statement for a grand total query.

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

generating a SELECT statement for the subset of the cube model metadata object.

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

generating a SELECT statement for the subset of the cube model metadata object.

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

separating symmetric measures and asymmetric measures defined in the one or more measure metadata objects;

generating structured query language statements for the symmetric measures;

generating structured query language statements for the asymmetric measures; and

combining the structured query language statements for the symmetric and asymmetric measures into a single structured query language statement.

37. The system of claim 36, wherein the combining comprises use of a join.

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

determining whether a measure is compatible with one or more measures; and

if the measure is not compatible with the one or more measures, determining whether any of the measures may be rewritten; and

if any of the measures may be rewritten, rewriting the measures.

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

generating a cube metadata object based on the selection of the subset of the cube model metadata object, including generating a structured query language statement for creation of a cube view, wherein the structured query language statement is generated from metadata in the one or more measure metadata objects.

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

under control of an application program, using the cube model metadata object and one or more of the measure metadata objects, generating a structured query language statement to retrieve multidimensional information.

41. An article of manufacture including a program for specifying multidimensional calculations, wherein the program causes operations to be performed, the operations comprising:

receiving selection of a subset of a cube model metadata object that is generated from a facts metadata object and one or more dimension metadata objects, wherein the facts metadata object references one or more measure metadata objects; and

generating a statement for retrieving multidimensional information using metadata in the cube model metadata object and the one or more measure metadata objects, wherein each of the measure metadata objects specifies one or more aggregations.

42. The article of manufacture of claim 41, wherein the statement is a structured query language statement.

43. The article of manufacture of claim 41, wherein each of the measure metadata objects specifies one or more structured query language expressions.

44. The article of manufacture of claim 43, wherein each of the structured query language expressions includes a template for building a query language expression.

45. The article of manufacture of claim 44, wherein the template uses a token notation that references a specific column, attribute, or measure from a list of columns, attributes and measures.

46. The article of manufacture of claim 43, wherein each of the structured query language expressions includes a list of columns, attributes, and measures.

47. The article of manufacture of claim 41, wherein the structured query language statement is generated based on the specified one or more aggregations in each of the measure metadata objects.

48. The article of manufacture of claim 47, wherein the list of aggregations comprises a list of aggregation functions and corresponding dimensions sets.

49. The article of manufacture of claim 48, wherein the dimension set specifies NULL for a corresponding aggregation function to include all available dimensions other than dimensions specified in another aggregation function in the list of aggregations.

50. The article of manufacture of claim 47, wherein the measure metadata object specifies one or more structured query language expressions and wherein the structured query language expressions are used as input to an aggregation in the list of aggregations.

51. The article of manufacture of claim 41, wherein the statement is generated using metadata in a hierarchy metadata object to build ROLLUP clauses.

52. The article of manufacture of claim 41, wherein a measure metadata object references another measure metadata object.

53. The article of manufacture of claim 41, wherein the operations for generation of the structured query language statement further comprise:

generating a SELECT statement for a grand total query.

54. The article of manufacture of claim 41, wherein the operations for generation of the structured query language statement further comprise:

generating a SELECT statement for a slice of the subset of the cube model metadata object.

55. The article of manufacture of claim 41, wherein the operations for generation of the structured query language statement further comprise:

generating a SELECT statement for the subset of the cube model metadata object.

56. The article of manufacture of claim 41, the operations further comprising:

separating symmetric measures and asymmetric measures defined in the one or more measure metadata objects;

generating structured query language statements for the symmetric measures;

generating structured query language statements for the asymmetric measures; and

combining the structured query language statements for the symmetric and asymmetric measures into a single structured query language statement.

57. The article of manufacture of claim 56, wherein the combining comprises use of a join.

58. The article of manufacture of claim 41, the operations further comprising:

determining whether a measure is compatible with one or more measures; and

if the measure is not compatible with the one or more measures, determining whether any of the measures may be rewritten; and

if any of the measures may be rewritten, rewriting the measures.

59. The article of manufacture of claim 41, the operations further comprising:

generating a cube metadata object based on the selection of the subset of the cube model metadata object, including generating a structured query language statement for creation of a cube view, wherein the structured query language statement is generated from metadata in the one or more measure metadata objects.

60. The article of manufacture of claim 41, the operations further comprising:

under control of an application program, using the cube model metadata object and one or more of the measure metadata objects, generating a structured query language statement to retrieve multidimensional information.