Method of transforming queries based upon E/R schema into multi-dimensional expression queries

• US 7,945,575 B2
• Filed: 05/27/2004
• Issued: 05/17/2011
• 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: Cosma, Marius, Cushing, David, Beffert, Helmut, Warner, Neil
• Primary Examiner(s): Vo; Tim T.
• Assistant Examiner(s): Ahn; Sangwoo
• Application Number:
  US10/855,255
• Publication Number:
  US 20050010565A1
• Time in Patent Office: 2,546 Days
• Field of Search: 707 1- 10, 707100-1041, 707/760, 707/761, 707/765
• US Class Current: 707/760
• 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 method of transforming queries based upon an entity/relationship schema into multi-dimensional expression queries is provided. The method comprises matching an object referenced in an entity/relationship schema based report specification to a corresponding object in the entity/relationship schema, the corresponding object adorned with associated multidimensional metadata; adding the associated multi-dimensional metadata to the object referenced in the entity/relationship schema based report specification; translating the entity/relationship schema based report specification into a multi-dimensional expression query; and retrieving directly all data required to populate a report output of the entity/relationship schema report specification.

Claims

1. A computer-implemented method of transforming queries based upon an entity/relationship schema into multi-dimensional expression queries, the method comprising:

matching, by operation of one or more computer processors, an object referenced in a report specification based on an entity/relationship schema to a corresponding object in the entity/relationship schema, the corresponding object adorned with associated multi-dimensional metadata;

adding the associated multi-dimensional metadata to the object referenced in the report specification;

translating the report specification into a multi-dimensional expression query;

directly retrieving all data required to populate a report output of the report specification; and

converting, by operation of the one or more computer processors, results of the multi-dimensional expression query into a result set matching semantics of the report specification, wherein converting results of the multi-dimensional expression query comprises;

converting the results of the multi-dimensional expression query into rows of data; and

producing headers for inclusion into the rows of data.

2. The computer-implemented method of claim 1, wherein converting results of the multi-dimensional query further comprises:

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

3. The computer-implemented method of claim 1, wherein the object referenced in the report specification is generated from an entity/relationship client application.

4. The computer-implemented method of claim 1, wherein data in the report specification is stored in an OLAP format.

5. The computer-implemented method of claim 1, further comprising post-processing the retrieved data.

6. The computer-implemented method of claim 1, further comprising presenting the retrieved data in a format of an original query.

7. The computer-implemented method of claim 1, wherein translating is processed on an OLAP engine.

8. The computer-implemented method of claim 1, further comprising matching each object in the report specification to a corresponding object in the entity/relationship schema.

9. The computer-implemented method of claim 1, further comprising determining interactions of report constructs and multi-dimensional metadata precluding generation of a query.

10. A non-transitory computer-readable medium containing program code, which when executed on a processor performs an operation for transforming queries based upon an entity/relationship schema into multi-dimensional expression queries, the operation comprising:

matching an object referenced in a report specification based on an entity/relationship schema to a corresponding object in the entity/relationship schema, the corresponding object adorned with associated multi-dimensional metadata;

adding the associated multi-dimensional metadata to the object referenced in the report specification;

translating the report specification into a multi-dimensional expression query;

directly retrieving all data required to populate a report output of the report specification; and

converting results of the multi-dimensional expression query into a result set matching semantics of the report specification, wherein converting results of the multi-dimensional expression query comprises;

converting the results of the multi-dimensional expression query into rows of data; and

producing headers for inclusion into the rows of data.

11. The computer-readable medium of claim 10, wherein converting results of the multi-dimensional query further comprises:

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

12. A system, comprising:

a processor; and

a memory storing program code, which when executed on the processor performs an operation for operation for transforming queries based upon an entity/relationship schema into multi-dimensional expression queries, the operation comprising;

matching an object referenced in a report specification based on an entity/relationship schema to a corresponding object in the entity/relationship schema, the corresponding object adorned with associated multi-dimensional metadata;

adding the associated multi-dimensional metadata to the object referenced in the report specification;

translating the report specification into a multi-dimensional expression query;

directly retrieving all data required to populate a report output of the report specification; and

converting results of the multi-dimensional expression query into a result set matching semantics of the report specification, wherein converting results of the multi-dimensional expression query comprises;

converting the results of the multi-dimensional expression query into rows of data; and

producing headers for inclusion into the rows of data.

13. The system of claim 12, wherein converting results of the multi-dimensional query further comprises:

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