System and method of multidimensional query results processing

• US 9,081,849 B2
• Filed: 05/27/2004
• Issued: 07/14/2015
• Est. Priority Date: 05/27/2003
• Status: Expired due to Fees

• Current Assignee: Congos Incorported, International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cushing, David, Cosma, Marius, Warner, Neil, Beffert, Helmut
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): HARPER, ELIYAH STONE
• Application Number:
  US10/855,818
• Publication Number:
  US 20050010566A1
• Time in Patent Office: 4,065 Days
• Field of Search: 707/104.1, 707/204, 707/10, 707/100, 707/765
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/288 Entity relationship models
• CPC Class Codes: G06F 16/902 using more than one table in sequence, i.e. systems with three or more layers

Abstract

A multidimensional query results processing system for translating output of an execution of a multidimensional query into a data result set that reflects the semantics of an entity/relationship schema report specification is provided. The system comprises a result set description generation module for producing a result set description that reflects the semantics of the report specification, a tabular row generation module for converting the results of the single multi-dimensional query into a collection of rows of data, a tabular summary level calculation module for calculating a summarization level of a row a data, a tabular header row generation module for producing a header row to include in the row of data, and a cross-tabulated result generation module for converting the results of the single multi-dimensional query into a result set that matches the semantics of the report specification.

Claims

1. A system for processing a multidimensional (MD) data set produced from an execution of multidimensional query on a MD data source, the MD data set comprising data for a report described by a report specification based on an entity/relationship (ER) schema, the system comprising:

a result set description generation module producing a result set description matching the semantics of the report specification based on the-ER schema from a MD data set description describing the semantics of the MD data set using result processing information;

a tabular row generation module converting the MD data set into a collection of rows of data to generate a result set of the report output as a tabular result set when the ER report specification conforms to a tabular report, wherein the tabular row generation module generates each row of data by producing a respective full stack of members from the MD data set, wherein each full stack of members represents a row of data available for inclusion in the tabular result set, and wherein producing the full stack of members comprises;

pushing a highest-level member of a dimension onto a stack,

traversing parent/child relationships within a dimension along an edge to push each member at each level onto the stack,

popping a top member off the stack, and

pushing all siblings of the top member onto the stack;

a cross-tabulated result generation module converting the MD data set into a cross tabulated result set when the report specification conforms to a cross-tabulated report

a translation module converting the report specification based on the ER schema into the MD query; and

an execution module for executing the MD query.

8. The system as claimed in claim 1, wherein the tabular row generation module further comprises:

a tabular summary level calculation module producing a header row to include in the row of data.

9. The system as claimed in claim 8, wherein the result processing information provides a mapping between objects within the ER schema and MD data source.

2. A computer-implemented method of processing a multidimensional (MD) data set produced from an execution of a multidimensional query on a MD data source, the MD data set comprising data for a report described by the report specification based on an entity/relationship (ER) schema, the method comprising:

producing a result set description matching the semantics of the report specification based on the ER schema from a MD data set description describing the semantics of the MD data set using result processing information; and

converting MD data set into a collection of rows of data to generate a result set of the report output as a tabular result set when the ER report specification conforms to a tabular report, wherein converting the MD data set into a collection of rows of data includes, producing respective full stacks of members, each full stack representing a row of data available for inclusion in the collection of rows of data, wherein producing given full stack of members comprises;

pushing a highest-level member of a dimension onto a stack,

traversing parent/child relationships within a dimension along an edge to push each member at each level onto the stack,

popping a top member off the stack, and

pushing all siblings of the top member onto the stack; and

converting the MD data set into a cross tabulated result set when the report specification conforms to a cross-tabulated report.

3. The method as claimed in claim 2, further comprising creating a header row, including:

setting a state of header to header nested;

performing a check header nested;

performing a check header current;

performing a check header done;

performing a check children;

performing a check nested;

performing a check current;

performing a check sibling; and

performing a check ancestor.

4. The method as claimed in claim 2, further comprising creating a header row, including:

setting a state of an element to header nested;

checking for nested dimensions until there are no more;

setting the state of the element to check header current;

filling the dimension with nested fillers;

determining a summary level;

setting the state of the element to header done;

removing the nested dimension fillers;

checking all children of the element until there are no more children;

setting the state of the element to check nested;

determining if there are any nested elements;

filling a current dimension with nested dimension fillers;

setting the state of the element to check siblings;

moving the MDDS iterator to the next sibling;

keeping a row copy; and

setting the state to check ancestor.

5. The method as claimed in claim 4, further comprising:

indicating a row has been found if the summary level is >

=0;

deleting an element if there are other check header current states in the stack; and

setting the state to check header nested if there are more siblings in the state.

6. A non-transitory computer-readable storage medium having computer readable code embodied therein for use in the execution in a computer of a method of processing a multidimensional (MD) data set produced from an execution of a multidimensional query on a MD data source, the MD data set comprising data for a report described by a report specification based on an entity-relationship (ER) schema, the method comprising:

producing a result set description matching the semantics of the report specification based on the ER schema from a MD data set description describing the semantics of the MD data set using result processing information;

converting MD data set into a collection of rows of data to generate a result set of the report output as a tabular result set when the ER report specification conforms to a tabular report, wherein converting the MD data set into a collection of rows of data includes, producing respective full stacks of members, each full stack representing a row of data available for inclusion in the collection of rows of data, wherein producing given full stack of members comprises;

pushing a highest-level member of a dimension onto a stack,

traversing parent/child relationships within a dimension along an edge to push each member at each level onto the stack,

popping a top member off the stack, and

pushing all siblings of the top member onto the stack; and

converting the MD data set into a cross tabulated result set when the report specification conforms to a cross-tabulated report.

7. A non-transitory computer-readable storage medium for use in the execution in a computer a system for processing a multidimensional (MD) data set produced from an execution of multidimensional query on a MD data set comprising data for a report described by a report specification based on an entity/relationship (ER) schema, the computer program product comprising:

a result set description generation module producing a result set description matching the semantics of the report specification based on the-ER schema from a MD data set description describing the semantics of the MD data set using result processing information;

a tabular row generation module converting the MD data set into a collection of rows of data to generate a result set of the report output as a tabular result set when the ER report specification conforms to a tabular report, wherein the tabular row generation module generates each row of data by producing a respective full stack of members from the MD data set, wherein each full stack of members represents a row of data available for inclusion in the tabular result set, wherein producing given full stack of members comprises;

pushing a highest-level member of a dimension onto a stack,

traversing parent/child relationships within a dimension along an edge to push each member at each level onto the stack,

popping a top member off the stack, and

pushing all siblings of the top member onto the stack;

a cross-tabulated result generation module converting the MD data set into a cross tabulated result set when the report specification conforms to a cross-tabulated report

a translation module converting the report specification based on the ER schema into the MD query; and

an execution module executing the MD query.