OLAP query generation engine

Details OLAP query generation engine OLAP query generation engine

2001-03-01

2003-11-18

Rones, Charles (Department: 2175)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000

Reexamination Certificate

active

06651055

ABSTRACT:

FIELD OF THE INVENTION
The present invention relates to the field of multi-dimensional databases. In particular, this invention relates to an On-Line Analytical Processing (OLAP) query generation engine for generating OLAP queries for use with different OLAP servers.
BACKGROUND OF THE INVENTION
On-Line Analytical Processing (OLAP) database servers provide a platform for high-end analytics and decision support applications. OLAP technology allows organizations (i.e., enterprises) to extract data from multiple, disparate transactional and operational systems into homogenous, aggregated repositories of information for the purpose of high-performance analysis and reporting. The process of moving data out of core transactional and operational systems into an OLAP environment typically includes cleansing the data by enforcing consistency and data integrity, and aggregating the data using like keys. This process is known as data transformation. The result of the data transformation process is a multi-dimensional OLAP database, known as an “OLAP cube”, which is highly optimized for reporting and analysis.
OLAP cubes are typically subject-matter oriented, with the data in the cube being organized by a series of dimensions. The dimensions are hierarchical in nature (e.g., year, quarter, month, etc.) to allow for multiple levels of data aggregation. An end user can thus choose whether to analyze data in an OLAP cube at varying levels of data aggregation, such as at summary, intermediate and detailed analysis levels. OLAP data cubes with different subject matters typically have different numbers of dimensions with different hierarchical designs. OLAP cubes can also share one or more dimensions to provide a homogenous view of the structure of the cube data amongst different cubes. The concept of keying cube data by dimensions, known as dimensional modeling, allows for a simplified approach to reporting and analysis of data. In particular, users need only understand the dimensions of the OLAP cubes and their hierarchies, and are insulated from the need to understand the underlying physical implementation of the database schema that is used to store the data. Thus, users are presented with a logical view of the information stored in the cube, and can create reports and queries without having to perform complex joins or any other function which would require an understanding of the underlying database structure.
The leading OLAP database servers on the market today are the Hyperion Essbase™ OLAP server, available from Hyperion Solutions Corp. of CA, and the Microsoft Analysis Services OLAP server, available from Microsoft Corp. of WA. Both servers provide the ability to build subject matter cubes using dimensional modeling techniques which support hierarchies, calculated members, aggregations, and high-performance reporting and analysis. From a querying standpoint, both servers offer similar features, but with very different implementations. In particular, the structured query formats used for the two database servers are very different. The query language used for the Microsoft Analysis Services OLAP server is known as Multi-Dimensional Expression Query Language (“MDX”). The MDX query language leverages Microsoft's OLE database (“OLEDB”) for OLAP standard, and follows a Structured Query Language (SQL)-like syntax with special extensions that are used for OLAP cube querying. The special extensions are needed since SQL is only two-dimensional, while OLAP data cubes are n-dimensional. The query language that is used for the Hyperion Essbase™ OLAP server is known as Report Scripts (“RS”). Although the RS query language provides a full range of querying options within the Hyperion Essbase™ environment, the syntax of queries for RS is very different from the syntax of queries for MDX. For example, exemplary MDX and RS query statements
100
are shown in
FIG. 1
(note the data cubes used for the two examples contained different data). A comparison shows that the syntax used to create these query statements is very different. The differences between these formats makes it difficult for OLAP query and reporting applications to support both Microsoft Analysis Services and Hyperion Essbase™ OLAP servers from the same code base.
Along with the Microsoft Analysis Services and Hyperion Essbase™ OLAP database servers, there are other OLAP servers on the market today that use other structured query formats. Further, there are likely to be still other OLAP servers on the market in the future that will use still other structured query formats, including formats that are not yet known. The differences in structured query formats between these present and future OLAP servers makes it even more difficult for OLAP query and reporting applications to support any or all of these different servers. For example, it would be very difficult to design an OLAP query and reporting application that can be easily adapted to support a future OLAP server that will use a structured query format that is as yet unknown.
In the relational database world, the SQL and Open Database Connectivity (“ODBC”) standards are industry-standard methodologies for accessing relational databases. Thus, query and reporting applications in the relational database world can implement SQL and ODBC methodologies to access relational databases across relational database vendors. Unfortunately, industry-standard methodologies do not exist for use with OLAP database technology. In particular, there are currently no SQL or ODBC-type standards that simplify the generation of OLAP queries. Thus, it is difficult for an OLAP query and reporting application to support both Microsoft Analysis Services and Hyperion Essbase™ servers from one code base, or to support other combinations of OLAP servers that use, or will use, different structured query formats.
Thus, there is a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to efficiently and easily support multiple OLAP database servers that use different structured query formats. For example, there is a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to support both Microsoft Analysis Services and Hyperion Essbase™ database servers from one code base. There is also a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to support other OLAP servers on the market today that use different structured query formats, or that can be easily adapted to support OLAP servers that will be on the market in the future.
SUMMARY OF THE INVENTION
In accordance with one embodiment of the present invention, a method of generating an On-Line Analytical Processing (OLAP) query includes providing a query object capable of supporting a plurality of OLAP servers (each server 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 which corresponds to the OLAP server that was determined.
In one embodiment of this method, the query statement is generated using the Multi-Dimensional Expression (MDX) query format when the property of the query object indicates the OLAP server is a Microsoft Analysis Services OLAP server. In this embodiment, processing the query object may include generating a select clause and a from clause from the query object, and combining the select and the from clauses to generate the query statement. Processing the query object may also include generating a with clause, a where clause and/or an order by clause, and combining any of these other clauses with the select clause and the from clause.
In another embodiment of this method, the query statement is generated using the Report Scripts (RS) query format when the property of the query object indicates the server is an Hyper

Affiliated with

Also associated with

Rating

OLAP query generation engine does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with OLAP query generation engine, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and OLAP query generation engine will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3160049