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

• US 7,953,694 B2
• Filed: 01/13/2003
• Issued: 05/31/2011
• Est. Priority Date: 01/13/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Malloy, William Earl, Tomlyn, Craig Reginald, Pirahesh, Mir Hamid, Colossi, Nathan Gevaerd
• Primary Examiner(s): LU, CHARLES EDWARD
• Application Number:
  US10/341,763
• Publication Number:
  US 20040139061A1
• Time in Patent Office: 3,060 Days
• Field of Search: 707/600, 707/759
• US Class Current: 707/600
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Provided 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 computer-implemented method for specifying on-line analytical processing multidimensional calculations, comprising:

accessing, using a computer including a processor, measures that include symmetric and asymmetric measures, wherein each of the symmetric measures have a single aggregation, and wherein each of the asymmetric measures have multiple aggregations, wherein a measure is defined by one or more structured query language expressions and wherein the one or more structured query language expressions are used as input to an aggregation of the measure;

selecting each of the symmetric and asymmetric measures one at a time; and

for the selected one of the symmetric and asymmetric measures,

determining whether the selected measure is compatible with previously selected measures, wherein compatible measures have a same specification of aggregation order for dimensions that the measures reference;

in response to determining that the selected measure is compatible, selecting another of the symmetric and asymmetric measures; and

in response to determining that the selected measure is not compatible,

determining whether one or more measures of the symmetric and asymmetric measures can be rewritten so that the selected measure is compatible with the previously selected measures;

in response to determining that the one or more measures of the symmetric and asymmetric measures can be rewritten, rewriting the one or more measures of the symmetric and asymmetric measures; and

in response to determining that the one or more measures of the symmetric and asymmetric measures cannot be rewritten,

generating a first structured query language statement for the symmetric measures;

generating a second structured query language statement for the asymmetric measures; and

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

2. The method of claim 1, wherein each of the one or more structured query language expressions includes a template for building a query language expression.

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

4. The method of claim 1, wherein each of the one or more structured query language expressions includes a list of columns, attributes, and measures.

5. The method of claim 1, further comprising:

generating a new statement for retrieving multidimensional information, wherein the new statement is a structured query language statement and wherein the structured query language statement is generated based on the one or more aggregations in each of the measures.

6. The method of claim 5, wherein the one or more aggregations is a list of aggregations and wherein the list of aggregations comprises a list of aggregation functions and corresponding dimensions sets.

7. The method of claim 6, 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.

8. The method of claim 5, wherein the new statement is generated using metadata in a hierarchy to build ROLLUP clauses.

9. The method of claim 5, wherein generation of the new statement further comprises:

generating a SELECT statement for a grand total query.

10. The method of claim 5, wherein generation of the new statement further comprises:

generating a SELECT statement.

11. The method of claim 1, wherein the combining comprises use of a join.

12. The method of claim 1, further comprising:

generating a structured query language statement for creation of a cube view, wherein the structured query language statement is generated from metadata in the measures;

executing the structured query language statement to create the cube view; and

querying the cube view.

13. The method of claim 1, further comprising:

under control of an application program, generating a new structured query language statement to retrieve multidimensional information.

14. A computer system for specifying on-line analytical processing multidimensional calculations, comprising:

a processor;

memory connected to the processor; and

hardware logic implementing code and connected to the processor, that, when executed by the processor, performs;

accessing measures that include symmetric and asymmetric measures, wherein each of the symmetric measures have a single aggregation, and wherein each of the asymmetric measures have multiple aggregations, wherein a measure is defined by one or more structured query language expressions and wherein the structured query language expressions are used as input to an aggregation of the measure;

selecting each of the symmetric and asymmetric measures one at a time; and

for the selected one of the symmetric and asymmetric measures,

determining whether the selected measure is compatible with previously selected measures, wherein compatible measures have a same specification of aggregation order for dimensions that the measures reference;

in response to determining that the selected measure is compatible, selecting another of the symmetric and asymmetric measures; and

in response to determining that the selected measure is not compatible,

determining whether one or more measures of the symmetric and asymmetric measures can be rewritten so that the selected measure is compatible with the previously selected measures;

in response to determining that the one or more measures of the symmetric and asymmetric measures can be rewritten, rewriting the one or more measures of the symmetric and asymmetric measures; and

in response to determining that the one or more measures of the symmetric and asymmetric measures cannot be rewritten,

generating a first structured query language statement for the symmetric measures;

generating a second structured query language statement for the asymmetric measures; and

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

15. The system of claim 14, wherein each of the one or more structured query language expressions includes a template for building a query language expression.

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

17. The system of claim 14, wherein each of the one or more structured query language expressions includes a list of columns, attributes, and measures.

18. The system of claim 14, wherein the at least one program further comprises:

generating a new statement for retrieving multidimensional information, wherein the new statement is a structured query language statement and wherein the structured query language statement is generated based on the one or more aggregations in each of the measures.

19. The system of claim 18, wherein the one or more aggregations is a list of aggregations and wherein the list of aggregations comprises a list of aggregation functions and corresponding dimensions sets.

20. The system of claim 19, 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.

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

22. The system of claim 18, wherein the at least one program further comprises:

generating a SELECT statement for a grand total query.

23. The system of claim 18, wherein the at least one program further comprises:

generating a SELECT statement.

24. The system of claim 18, wherein the combining comprises use of a join.

25. The system of claim 14, wherein the at least one program further comprises:

generating a structured query language statement for creation of a cube view, wherein the structured query language statement is generated from metadata in the measures;

executing the structured query language statement to create the cube view; and

querying the cube view.

26. The system of claim 14, wherein the at least one program further comprises:

under control of an application program, generating a new structured query language statement to retrieve multidimensional information.

27. An article of manufacture embodied as a computer readable storage medium including a program for specifying on-line analytical processing multidimensional calculations, wherein the program, when executed by a processor of a computer, causes operations to be performed, the operations comprising:

accessing measures that include symmetric and asymmetric measures, wherein each of the symmetric measures have a single aggregation, and wherein each of the asymmetric measures have multiple aggregations, wherein a measure is defined by one or more structured query language expressions and wherein the structured query language expressions are used as input to an aggregation of the measure;

selecting each of the symmetric and asymmetric measures one at a time; and

for the selected one of the symmetric and asymmetric measures,

determining whether the selected measure is compatible with previously selected measures, wherein compatible measures have a same specification of aggregation order for dimensions that the measures reference;

in response to determining that the selected measure is compatible, selecting another of the symmetric and asymmetric measures; and

in response to determining that the selected measure is not compatible,

determining whether one or more measures of the symmetric and asymmetric measures can be rewritten so that the selected measure is compatible with the previously selected measures;

in response to determining that the one or more measures of the symmetric and asymmetric measures can be rewritten, rewriting the one or more measures of the symmetric and asymmetric measures; and

in response to determining that the one or more measures of the symmetric and asymmetric measures cannot be rewritten,

generating a first structured query language statement for the symmetric measures;

generating a second structured query language statement for the asymmetric measures; and

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

28. The article of manufacture of claim 27, wherein each of the one or more structured query language expressions includes a template for building a query language expression.

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

30. The article of manufacture of claim 27, wherein each of the one or more structured query language expressions includes a list of columns, attributes, and measures.

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

generating a new statement for retrieving multidimensional information, wherein the new statement is a structured query language statement and wherein the structured query language statement is generated based on the one or more aggregations in each of the measures.

32. The article of manufacture of claim 31, wherein the one or more aggregations is a list of aggregations and wherein the list of aggregations comprises a list of aggregation functions and corresponding dimensions sets.

33. The article of manufacture of claim 32, 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.

34. The article of manufacture of claim 31, wherein the new statement is generated using metadata in a hierarchy to build ROLLUP clauses.

35. The article of manufacture of claim 31, wherein the operations for generation of the new statement further comprise:

generating a SELECT statement for a grand total query.

36. The article of manufacture of claim 31, wherein the operations for generation of the new statement further comprise:

generating a SELECT statement.

37. The article of manufacture of claim 27, wherein the combining comprises use of a join.

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

generating a structured query language statement for creation of a cube view, wherein the structured query language statement is generated from metadata in the measures;

executing the structured query language statement to create the cube view; and

querying the cube view.

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

under control of an application program, generating a new structured query language statement to retrieve multidimensional information.