OLAP query generation engine

• US 20040039736A1
• Filed: 08/20/2003
• Published: 02/26/2004
• Est. Priority Date: 03/01/2001
• Status: Active Grant

• Current Assignee: Infor Incorporated
• Original Assignee: Lawson Software Incorporated (Infor Incorporated)
• Inventors: Bendickson, Richard Scott, Kilmer, Lee Eric, Jaquier, Gary Lynn, Pavelka, Thomas Joseph
• Granted Patent: US 7,363,287 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A system and method for generating an On-Line Analytical Processing (OLAP) query. A query object is provided that supports a plurality of OLAP servers which use different structured query formats. An OLAP server is determined from among the OLAP servers for which the query will be executed based on a property of the query object. The query object is processed to generate a query statement using the structured query format that corresponds to the determined OLAP server.

Claims

1. A method of generating an On-Line Analytical Processing (OLAP) query, comprising:

providing a query object capable of supporting a plurality of OLAP servers, each of the OLAP servers using a different structured query format;

determining an OLAP server from among the plurality of OLAP servers for which the query will be executed based upon a property of the query object; and

processing the query object to generate a query statement using the structured query format corresponding to the OLAP server determined.

2. The method of claim 1, wherein the OLAP server is determined from a group of OLAP servers including Microsoft Analysis Services OLAP server and Hyperion Essbase™

OLAP server.

3. The method of claim 2, wherein the query statement is generated using the Multi-Dimensional Expression (MDX) query format when the property of the query object indicates Microsoft Analysis Services OLAP server.

4. The method of claim 3, wherein processing the query object includes generating a select clause and a from clause from the query object, and combining the select clause and the from clause to generate the query statement.

5. The method of claim 3, wherein processing the query object includes generating a select clause, a from clause, and a with clause from the query object, and combining the with, select and from clauses to generate the query statement.

6. The method of claim 3, wherein processing the query object includes generating a select clause, a from clause, and a where clause from the query object, and combining the select, from and where clauses to generate the query statement.

7. The method of claim 3, wherein processing the query object includes generating a select clause, a from clause, and an order by clause from the query object, and combining the select, from and order by clauses to generate the query statement.

8. The method of claim 2, wherein the query statement is generated using the Report Scripts (RS) query format when the property of the query object indicates Hyperion Essbase™

OLAP server.

9. The method of claim 8, wherein processing the query object includes generating a header specification, an axis specification and an execute specification from the query object, and combining the header, axis and execute specifications.

10. The method of claim 8, wherein processing the query object includes generating a header specification, an axis specification, a member specification, and an execute specification from the query object, and combining the header, axis, member and execute specifications to generate the query statement.

11. The method of claim 8, wherein processing the query object includes generating a header specification, an axis specification, an expression specification, and an execute specification from the query object, and combining the header, axis, expression and execute specifications to generate the query statement.

12. The method of claim 1, wherein the query object property supports at least three OLAP servers, each of the OLAP servers using a different structured query format.

13. An apparatus for generating an OLAP query using a query object capable of supporting a plurality of OLAP servers, each of the OLAP servers using a different structured query format, the apparatus comprising:

means for determining, based upon a property of the query object, an OLAP server from among the plurality of OLAP servers; and

means for processing the query object to generate a query statement using the structured query format corresponding to the OLAP server determined.

14. The apparatus of claim 13, wherein the query object is capable of supporting the Microsoft Analysis Services OLAP server and Hyperion Essbase™

OLAP server, and the means for processing generates an MDX query statement and an RS query statement when the property indicates that the OLAP server is a Microsoft Analysis Services OLAP server and an Hyperion Essbase™

OLAP server, respectively.

15. The apparatus of claim 13, wherein the query object is capable of supporting at least three OLAP servers which each uses a different structured query format, and the means for processing generates the query statement using one of the at least three formats.

16. An OLAP query generation engine, comprising:

a query object model, the query object model including a data structure which models an OLAP query; and

a programming interface for generating an OLAP query statement from the query object model according to a structured query format specified by the query object model.

17. The engine of claim 16, wherein the data structure models the OLAP query in an abstract form that is compatible with a plurality of different OLAP structured query formats.

18. The engine of claim 16, wherein the data structure models the OLAP query in a form different from the implementation of underlying OLAP servers.

19. The engine of claim 16, wherein the programming interface is also for maintaining the OLAP query statement.

20. The engine of claim 16, wherein the programming interface is also for executing the OLAP query statement.

21. The engine of claim 16, wherein the query object model is capable of specifying the MDX and the RS query formats, and the programming interface generates an MDX query statement and an RS query statement when the query object model specifies the MDX query format and the RS query format, respectively.

22. The engine of claim 16, wherein the query object model is capable of specifying first, second and third structured query formats, and the programming interface generates the query statement using the first, second and third formats when the query object model specifies the first, second and third structured query formats, respectively.

23. An OLAP query object model, comprising:

a query object which defines an OLAP query in abstract form; and

a root object including methods for transforming the query object into an OLAP query statement according to a structured query format specified by the query object.

24. The model of claim 23, wherein the query object is capable of specifying the MDX query format and the RS query format, and the root object includes methods for generating an MDX query statement and an RS query statement when the query object specifies the MDX query format and the RS query format, respectively.

25. The model of claim 23, wherein the query object is capable of specifying first, second and third structured query formats, and the root object includes methods for generating the query statement using the first, second and third structured query formats when the query object specifies the first, second and third structured query formats, respectively.

26. An OLAP query generation engine for use with an OLAP query and reporting application that supports a first OLAP server using a first structured query format and supports a second OLAP server using a second structured query format, the engine comprising:

an object model including a data structure that models an OLAP query; and

a programming interface for generating an OLAP query statement according to the first structured query format when the first structured query format is specified by the object model and according to the second structured query format when the second structured query format is specified.

27. The engine of claim 26, wherein the query and reporting application supports a Microsoft Analysis Services OLAP server using the MDX query format and an Hyperion Essbase™

OLAP server using the RS query format, and the programming interface generates an MDX query statement and an RS query statement when the object model specifies the MDX query format and the RS query format, respectively.

28. The engine of claim 26, wherein the query and reporting application supports first, second and third OLAP servers using first, second and third structured query formats, and the programming interface generates the query statement using the first, second and third structured query formats when the first, second and third structured query formats are specified.

29. A method of generating an OLAP query using a query object capable of supporting a plurality of OLAP servers, each of the OLAP servers using a different structured query format, the method comprising:

determining, based upon a property of the query object, an OLAP server from among the plurality of OLAP servers; and

processing the query object to generate a query statement using the structured query format corresponding to the OLAP server determined.

30. The method of claim 29, wherein the query object supports the Microsoft Analysis Services OLAP server and Hyperion Essbase™

OLAP server, and the processing generates an MDX query statement and an RS query statement when the property indicates that the OLAP server is a Microsoft Analysis Services OLAP server and an Hyperion Essbase™

OLAP server, respectively.

31. The method of claim 29, wherein the query object supports at least three OLAP servers which each uses a different structured query format, and the processing generates the query statement using one of the at least three formats.