Online analytical processing (OLAP)

• US 7,856,458 B2
• Filed: 01/17/2008
• Issued: 12/21/2010
• Est. Priority Date: 10/18/2002
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Steinhoff, Alfons, Arras, Patrick
• Primary Examiner(s): Trujillo; James
• Assistant Examiner(s): Black; Linh
• Application Number:
  US12/015,551
• Publication Number:
  US 20080183740A1
• Time in Patent Office: 1,069 Days
• Field of Search: 707602-603, 707/803
• US Class Current: 707/803
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and system for generating an n-dimensional matrix presentation of at least part of an m-dimensional database including data records and a least one key dimension. Each key dimension includes data value fields. Each data value field has real data therein. A sorting sequence of fact numbers is calculated. The fact numbers are integers that identify the data records. Entry points for each dimension of the n-dimensional matrix presentation are calculated. Each entry point is calculated to be linked with a corresponding fact number of the sorting sequence. A cardinality of the n-dimensional matrix presentation is calculated in dependence on the calculated sorting sequence and the calculated corresponding entry points. The n-dimensional matrix presentation is generated based on the calculated cardinality and the calculated entry points.

Claims

1. A computer-implemented method for generating an n-dimensional matrix presentation of at least part of an m-dimensional database comprising data records and at least one key dimension, each key dimension comprising a plurality of data value fields, each data value field having real data therein, said method comprising:

a computer calculating a sorting sequence of fact numbers consisting of integers, said fact numbers identifying the data records;

said computer calculating entry points for each dimension of the n-dimensional matrix presentation, each calculated entry point calculated to be linked with a corresponding fact number of the sorting sequence;

said computer calculating a cardinality of the n-dimensional matrix presentation in dependence on the calculated sorting sequence and the calculated corresponding entry points; and

said computer generating the n-dimensional matrix presentation based on the calculated cardinality and the calculated entry points.

2. The method of claim 1, wherein calculating the entry points comprises placing each fact number of the sorting sequence into its corresponding calculated entry point within a column associated with the n-dimensional matrix presentation.

3. The method of claim 2, said method further comprising:

prior to calculating the entry points, said computer transforming the real data of each data value field to corresponding index values;

said computer calculating the entry points using the index values and not using the real data;

after calculating the entry points and prior to generating the n-dimensional matrix presentation, said computer transforming the index values back to the real data for generating the n-dimensional matrix presentation.

4. The method of claim 3, wherein calculating the entry points comprises executing a sequence of linear vector operations on the sorting sequence and on the index values.

5. The method of claim 4, wherein the at least one key dimension is a plurality of key dimensions, and wherein the method further comprises:

said computer generating a reference table comprising a column that includes the sorting sequence of fact values and columns including the index values of each key dimension;

said computer generating, based on the reference table, a sorted sequence table correlating the sorting sequence with the index values of a first key dimension of the plurality of key dimensions, said sorted sequence table having the index values of the first key dimension sorted therein;

said computer generating, based on the reference table, a mapping table correlating the index values of a second key dimension of the plurality of key dimensions with the sorting sequence; and

said computer generating, based on the reference table, a sort position pointer table comprising a count vector whose elements are pointers that point to selected entry points of said entry points, said selected entry points being a function of a count of a total number of distinct data records of the database for each unique index value of the second key dimension.

6. The method of claim 5, wherein executing the sequence of linear vector operations comprises sequentially executing first, second, and third vector operations for each fact number of the sorting sequence in the sorted sequence table, said each fact number being denoted as a given fact number,

wherein the first linear vector operation links the given fact number to a corresponding index value of the second key dimension in the mapping table;

wherein the second linear vector operation links the corresponding index value of the second key dimension, resulting from execution of the first linear vector operation, to a corresponding pointer of the count vector of the sort position pointer table, and following execution of the second linear vector operation the corresponding pointer in the count vector is incremented so as to point to a next entry point of said entry points; and

wherein the third linear vector operation uses the corresponding pointer prior to being incremented, resulting from execution of the second linear vector operation, to point to the entry point corresponding to the given fact number.

7. The method of claim 1, wherein said generating comprises generating a pivot view pertaining to the n-dimensional matrix presentation.

8. The method of claim 1, wherein said generating comprises generating a virtual n-dimensional matrix pertaining to the n-dimensional matrix presentation.

9. A computer program product, comprising a computer readable storage medium having a computer readable program code embodied therein, said computer readable program code upon being executed by a computer implements a method for generating an n-dimensional matrix presentation of at least part of an m-dimensional database comprising data records and at least one key dimension, each key dimension comprising a plurality of data value fields, each data value field having real data therein, said method comprising:

calculating a sorting sequence of fact numbers consisting of integers, said fact numbers identifying the data records;

calculating entry points for each dimension of the n-dimensional matrix presentation, each calculated entry point calculated to be linked with a corresponding fact number of the sorting sequence;

calculating a cardinality of the n-dimensional matrix presentation in dependence on the calculated sorting sequence and the calculated corresponding entry points; and

generating the n-dimensional matrix presentation based on the calculated cardinality and the calculated entry points.

10. The computer program product of claim 9, wherein calculating the entry points comprises placing each fact number of the sorting sequence into its corresponding calculated entry point within a column associated with the n-dimensional matrix presentation.

11. The computer program product of claim 10, said method further comprising:

prior to calculating the entry points, transforming the real data of each data value field to corresponding index values;

calculating the entry points using the index values and not using the real data;

after calculating the entry points and prior to generating the n-dimensional matrix presentation, transforming the index values back to the real data for generating the n-dimensional matrix presentation.

12. The computer program product of claim 11, wherein calculating the entry points comprises executing a sequence of linear vector operations on the sorting sequence and on the index values.

13. The computer program product of claim 12, wherein the at least one key dimension is a plurality of key dimensions, and wherein the method further comprises:

generating a reference table comprising a column that includes the sorting sequence of fact values and columns including the index values of each key dimension;

generating, based on the reference table, a sorted sequence table correlating the sorting sequence with the index values of a first key dimension of the plurality of key dimensions, said sorted sequence table having the index values of the first key dimension sorted therein;

generating, based on the reference table, a mapping table correlating the index values of a second key dimension of the plurality of key dimensions with the sorting sequence; and

generating, based on the reference table, a sort position pointer table comprising a count vector whose elements are pointers that point to selected entry points of said entry points, said selected entry points being a function of a count of a total number of distinct data records of the database for each unique index value of the second key dimension.

14. The computer program product of claim 13, wherein executing the sequence of linear vector operations comprises sequentially executing first, second, and third vector operations for each fact number of the sorting sequence in the sorted sequence table, said each fact number being denoted as a given fact number,

wherein the first linear vector operation links the given fact number to a corresponding index value of the second key dimension in the mapping table;

wherein the second linear vector operation links the corresponding index value of the second key dimension, resulting from execution of the first linear vector operation, to a corresponding pointer of the count vector of the sort position pointer table, and following execution of the second linear vector operation the corresponding pointer in the count vector is incremented so as to point to a next entry point of said entry points; and

wherein the third linear vector operation uses the corresponding pointer prior to being incremented, resulting from execution of the second linear vector operation, to point to the entry point corresponding to the given fact number.

15. A computer system comprising a computer and a computer readable memory unit coupled to the computer, said memory unit containing instructions that when executed by the computer implement a method for generating an n-dimensional matrix presentation of at least part of an m-dimensional database comprising data records and at least one key dimension, each key dimension comprising a plurality of data value fields, each data value field having real data therein, said method comprising:

calculating a sorting sequence of fact numbers consisting of integers, said fact numbers identifying the data records;

calculating entry points for each dimension of the n-dimensional matrix presentation, each calculated entry point calculated to be linked with a corresponding fact number of the sorting sequence;

calculating a cardinality of the n-dimensional matrix presentation in dependence on the calculated sorting sequence and the calculated corresponding entry points; and

generating the n-dimensional matrix presentation based on the calculated cardinality and the calculated entry points.

16. The computer system of claim 15, wherein calculating the entry points comprises placing each fact number of the sorting sequence into its corresponding calculated entry point within a column associated with the n-dimensional matrix presentation.

17. The computer system of claim 16, said method further comprising:

prior to calculating the entry points, transforming the real data of each data value field to corresponding index values;

calculating the entry points using the index values and not using the real data;

after calculating the entry points and prior to generating the n-dimensional matrix presentation, transforming the index values back to the real data for generating the n-dimensional matrix presentation.

18. The computer system of claim 17, wherein calculating the entry points comprises executing a sequence of linear vector operations on the sorting sequence and on the index values.

19. The computer system of claim 18, wherein the at least one key dimension is a plurality of key dimensions, and wherein the method further comprises:

generating a reference table comprising a column that includes the sorting sequence of fact values and columns including the index values of each key dimension;

generating, based on the reference table, a sorted sequence table correlating the sorting sequence with the index values of a first key dimension of the plurality of key dimensions, said sorted sequence table having the index values of the first key dimension sorted therein;

generating, based on the reference table, a mapping table correlating the index values of a second key dimension of the plurality of key dimensions with the sorting sequence; and

generating, based on the reference table, a sort position pointer table comprising a count vector whose elements are pointers that point to selected entry points of said entry points, said selected entry points being a function of a count of a total number of distinct data records of the database for each unique index value of the second key dimension.

20. The computer system of claim 19, wherein executing the sequence of linear vector operations comprises sequentially executing first, second, and third vector operations for each fact number of the sorting sequence in the sorted sequence table, said each fact number being denoted as a given fact number,

wherein the first linear vector operation links the given fact number to a corresponding index value of the second key dimension in the mapping table;

wherein the second linear vector operation links the corresponding index value of the second key dimension, resulting from execution of the first linear vector operation, to a corresponding pointer of the count vector of the sort position pointer table, and following execution of the second linear vector operation the corresponding pointer in the count vector is incremented so as to point to a next entry point of said entry points; and

wherein the third linear vector operation uses the corresponding pointer prior to being incremented, resulting from execution of the second linear vector operation, to point to the entry point corresponding to the given fact number.