System and method of transforming queries based upon E/R schema into multi-dimensional expression queries

• US 20050010565A1
• Filed: 05/27/2004
• Published: 01/13/2005
• Est. Priority Date: 05/27/2003
• Status: Active Grant

• Current Assignee: Congos Incorported, International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cosma, Marius, Cushing, David, Beffert, Helmut, Warner, Neil
• Granted Patent: US 7,945,575 B2
• Time in Patent Office: Days
• 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 system for transforming queries based upon entity/relationship schema into multi-dimensional expression queries is provided. The system comprises a binding module for matching an object reference in a report specification to a corresponding entity/relationship schema object and adding associated multi-dimensional metadata information from the entity/relationship schema to a corresponding report specification object, and a query translation module for translating the report specification into a single multi-dimensional query.

Claims

1. A system for transforming queries based upon entity/relationship schema into multi-dimensional expression queries, the system comprising:

a binding module for matching an object reference in a report specification to a corresponding entity/relationship schema object, and adding associated multi-dimensional metadata information from the entity/relationship schema to a corresponding report specification object; and

a query translation module for translating the report specification into a single multi-dimensional query.

2. The system as claimed in claim 1, further comprising an error detection module to determine if there are any interactions of report constructs and multi-dimensional metadata that preclude the generation of a single multi-dimensional query.

3. The system as claimed in claim 2, further comprising a result processing information generation module for translating output of an execution of the single multi-dimensional query into a data result set that reflects the semantics of the report specification.

4. The system as claimed in claim 1, further comprising:

an execution module for executing the single multi-dimensional query; and

a result processing module for converting the results from the single multi-dimensional query into a result set that reflect the semantics of the report specification.

5. The system as claimed in claim 4, wherein the result processing module has:

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.

6. A method of transforming queries based upon entity/relationship schema into multi-dimensional expression queries, the method comprising the steps of:

analyzing a query based upon an entity/relationship schema;

generating translations of the entity/relationship schema to dimensional expressions; and

generating a multi-dimensional query based upon the translations.

7. The method as claimed in claim 6, wherein:

the step of generating translations comprises the step of binding a report specification to a corresponding object in an entity/relationship schema; and

the step of generating a multi-dimensional query comprises the step of transforming the bound report into a multi-dimensional query.

8. The method as claimed in claim 6, wherein the step of generating a multi-dimensional query includes the steps of:

calling a set of members that result from an application of a filter to members at a dimension level;

for each descendent level of the dimension level, generating a set of descendants;

for each ancestor level, generating a set of ancestors; and

performing a union of the set of member, set of descendants, and set of ancestors.

9. The method as claimed in claim 6, wherein the step of generating a multi-dimensional query includes the steps of:

generating an ordered set of members at a dimension level;

for each member, sorting descendants of a member at the lowest level in the dimension; and

for each member, sorting ancestors of a member at all levels.

10. The method as claimed in claim 6, wherein the step of generating a multi-dimensional query includes the steps of:

defining a set of members at a lowest dimension level at which a filter is applied and upon which higher-level aggregations are computed;

for each member;

generating ancestors of a member each dimension level;

generating descendants of the member; and

defining a calculated measure to roll up values associated with the member; and

in the case of pre-aggregation filters applied to multiple dimensions, defining a calculated measure for each dimension for each basic measure in a report.

11. The method as claimed in claim 6, further comprising the step of converting a multi-dimensional dataset into a result set that reflects the semantics of an entity/relationship report specification, the step including:

combining a result set description of a multi-dimensional query and rules of the entity/relationship schema to produce a final result set description;

converting results of the multi-dimensional query result set into a collection of rows of data;

producing headers for inclusion into the rows of data; and

converting results of the multi-dimensional query into a result set that matches the semantics of the entity/relationship report specification.

12. The method as claimed in claim 11, further comprising the step of producing a full stack of members representing a row of data, the step including:

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.

13. The method as claimed in claim 11, further comprising the step of creating a header row, the step including:

setting a state of a 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.

14. A computer data signal embodied in a carrier wave and representing sequences of instructions which, when executed by a processor, cause the processor to perform a method of transforming queries based upon entity/relationship schema into multi-dimensional expression queries, the method comprising steps of:

analyzing a query based upon an entity/relationship schema;

generating translations of the entity/relationship schema to dimensional expressions; and

generating a multi-dimensional query based upon the translations.

15. A computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of transforming queries based upon entity/relationship schema into multi-dimensional expression queries, the method comprising steps of:

analyzing a query based upon an entity/relationship schema;

generating translations of the entity/relationship schema to dimensional expressions; and

generating a multi-dimensional query based upon the translations.

16. A computer program product for use in the execution in a computer of a system for transforming queries based upon entity/relationship schema into multi-dimensional expression queries, the computer program product comprising:

a binding module for matching an object reference in a report specification to a corresponding entity/relationship schema object, and adding associated multi-dimensional metadata information from the entity/relationship schema to a corresponding report specification object; and

a query translation module for translating the report specification into a single multi-dimensional query.