Query optimization

• US 9,418,101 B2
• Filed: 09/12/2012
• Issued: 08/16/2016
• Est. Priority Date: 09/12/2012
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: He, Xiaowen, Luo, Lin, Petitclerc, Martin
• Primary Examiner(s): Reyes, Mariela
• Assistant Examiner(s): HARMON, COURTNEY N
• Application Number:
  US13/612,611
• Publication Number:
  US 20140074771A1
• Time in Patent Office: 1,434 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Provided are techniques for generating a relational query. Information is collected from a query specification and a model for an On-Line Analytical Processing (OLAP) query having at least a first expression and a second expression. The collected information is used to generate a relational query to retrieve report data to be used to satisfy the first expression and the second expression.

Claims

1. A method, comprising:

generating an On-Line Analytical Processing (OLAP) query having a first expression for retrieving report data and other report data from an OLAP cache and a second expression for retrieving additional report data from the OLAP cache;

executing the first expression to retrieve the report data that is stored in the OLAP cache to respond to the first expression;

for the other report data that is not stored in the OLAP cache to respond to the first expression,

identifying the other report data to be retrieved for the first expression and the additional report data to be retrieved for the second expression that is not stored in the OLAP cache by;

identifying OLAP objects referenced in the first expression and the second expression;

mapping the OLAP objects to relational database tables in a relational database using a mapping stored in an OLAP database; and

grouping OLAP objects from the first expression and the second expression that are mapped to a same relational database table from the relational database tables;

generating a relational query to retrieve the other report data to be used to respond to the first expression and the additional report data to be used to respond to the second expression from the relational database;

executing the relational query to retrieve the other report data and the additional report data from the relational database;

storing the other report data in the OLAP cache;

using the other report data stored in the OLAP cache to respond to the first expression; and

pre-loading the additional report data in the OLAP cache in anticipation of execution of the second expression; and

subsequently executing the second expression by retrieving the additional report data to be used to respond to the second expression from the OLAP cache instead of from the relational database.

2. The method of claim 1, wherein a model includes an OLAP model definition that defines the OLAP model, a relational model definition that defines the relational model, and the mapping between the OLAP model and the relational model.

3. The method of claim 1, wherein the second expression is executed without accessing the relational database.

4. The method of claim 1, wherein the report data is used to respond to another OLAP query.

5. The method of claim 1, further comprising:

using the report data to generate a report having a layout defined by a layout definition of a report specification.

6. The method of claim 1, wherein a Software as a Service (SaaS) is provided to perform the method.

7. A computer program product, the computer program product comprising:

a non-transitory computer readable storage medium having computer readable program code embodied therewith, wherein, the computer readable program code, when executed by at least one processor of a computer, is configured to perform;

generating an On-Line Analytical Processing (OLAP) query having a first expression for retrieving report data and other report data from an OLAP cache and a second expression for retrieving additional report data from the OLAP cache;

executing the first expression to retrieve the report data that is stored in the OLAP cache to respond to the first expression;

for the other report data that is not stored in the OLAP cache to respond to the first expression,

identifying the other report data to be retrieved for the first expression and the additional report data to be retrieved for the second expression that is not stored in the OLAP cache by;

identifying OLAP objects referenced in the first expression and the second expression;

mapping the OLAP objects to relational database tables in a relational database using a mapping stored in an OLAP database; and

grouping OLAP objects from the first expression and the second expression that are mapped to a same relational database table from the relational database tables;

generating a relational query to retrieve the other report data to be used to respond to the first expression and the additional report data to be used to respond to the second expression from the relational database;

executing the relational query to retrieve the other report data and the additional report data from the relational database;

storing the other report data in the OLAP cache;

using the other report data stored in the OLAP cache to respond to the first expression; and

pre-loading the additional report data in the OLAP cache in anticipation of execution of the second expression; and

subsequently executing the second expression by retrieving the additional report data to be used to respond to the second expression from the OLAP cache instead of from the relational database.

8. The computer program product of claim 7, wherein a model includes an OLAP model definition that defines the OLAP model, a relational model definition that defines the relational model, and the mapping between the OLAP model and the relational model.

9. The computer program product of claim 7, wherein the second expression is executed without accessing the relational database.

10. The computer program product of claim 7, wherein the report data is used to respond to another OLAP query.

11. The computer program product of claim 7, wherein, the computer readable program code, when executed by the at least one processor of the computer, is configured to perform:

using the report data to generate a report having a layout defined by a layout definition of a report specification.

12. The computer program product of claim 7, wherein a Software as a Service (SaaS) is configured to perform the computer program product operations.

13. A computer system for augmenting a relational query, comprising:

a processor; and

a storage device connected to the processor, wherein the storage device has stored thereon a program, and wherein the processor is configured to execute instructions of the program to perform operations, wherein the operations comprise;

generating an On-Line Analytical Processing (OLAP) query having a first expression for retrieving report data and other report data from an OLAP cache and a second expression for retrieving additional report data from the OLAP cache;

executing the first expression to retrieve the report data that is stored in the OLAP cache to respond to the first expression;

for the other report data that is not stored in the OLAP cache to respond to the first expression,

identifying the other report data to be retrieved for the first expression and the additional report data to be retrieved for the second expression that is not stored in the OLAP cache by;

identifying OLAP objects referenced in the first expression and the second expression;

mapping the OLAP objects to relational database tables in a relational database using a mapping stored in an OLAP database; and

grouping OLAP objects from the first expression and the second expression that are mapped to a same relational database table from the relational database tables;

generating a relational query to retrieve the other report data to be used to respond to the first expression and the additional report data to be used to respond to the second expression;

executing the relational query to retrieve the other report data and the additional report data from the relational database;

storing the other report data in the OLAP cache;

using the other report data stored in the OLAP cache to respond to the first expression; and

pre-loading the additional report data in the OLAP cache in anticipation of execution of the second expression; and

subsequently executing the second expression by retrieving the additional report data to be used to respond to the second expression from the OLAP cache instead of from the relational database.

14. The computer system of claim 13, wherein a model includes an OLAP model definition that defines the OLAP model, a relational model definition that defines the relational model, and the mapping between the OLAP model and the relational model.

15. The computer system of claim 13, wherein the second expression is executed without accessing the relational database.

16. The computer system of claim 13, wherein the report data is used to respond to another OLAP query.

17. The computer system of claim 13, wherein the operations further comprise:

using the report data to generate a report having a layout defined by a layout definition of a report specification.

18. The computer system of claim 13, wherein a Software as a Service (SaaS) is provided to perform the system operations.