CN112559567A

CN112559567A - Query method and device suitable for OLAP query engine

Info

Publication number: CN112559567A
Application number: CN202011434001.9A
Authority: CN
Inventors: 刘文政; 李栋; 李扬; 韩卿
Original assignee: Yunyun Shanghai Information Technology Co ltd
Current assignee: Yunyun Shanghai Information Technology Co ltd
Priority date: 2020-12-10
Filing date: 2020-12-10
Publication date: 2021-03-26
Also published as: EP4083810A4; WO2022121098A1; EP4083810A1; US20230017300A1

Abstract

The invention provides an optimization scheme and a device suitable for an OLAP query engine. The query pattern matching module is used for acquiring an MDX query request received by an OLAP query engine, and generating at least one group of aggregation query sets for the MDX query request based on dimension processing, wherein the group of aggregation query sets comprises a plurality of aggregation query requests; and the query distributed execution module is used for processing the plurality of aggregated query requests in parallel based on an OLAP query engine to obtain a plurality of corresponding aggregated query results in a database, wherein the aggregated query requests are set corresponding to the aggregated query results. The method and the device provided by the invention can construct an efficient OLAP query execution engine, respond to complex OLAP queries of various report systems, remarkably improve the execution efficiency of MDX queries and accelerate the response of analysis requests of the report systems.

Description

Query method and device suitable for OLAP query engine

Technical Field

The present invention relates to data query technologies, and in particular, to a query method and apparatus suitable for an OLAP query engine.

Background

How to improve the execution efficiency of the OLAP query and quickly respond to the report analysis requirement of the business is always a hotspot problem concerned by the OLAP query system.

The query language commonly used by OLAP (Online analytical analysis) is MDX, and most reporting systems (including but not limited to Excel, Power BI, Tableau, MSTR, Smartbi, etc.) support the use of MDX interface.

Currently, the traditional OLAP engine supporting MDX in the industry is SSAS and Mondrian, etc. These conventional OLAP engines suffer from a severe degradation of response performance when the MDX queries sent by the reporting system are complex. The reason is three points, firstly, the data exchange format of the MDX interface is XMLA, which is inefficient in data transmission, and the query network transmission time is very long when some query result sets are large. Second, the conventional OLAP engine executes the MDX query step by step based on each MDX operator without optimizing in consideration of the MDX query characteristics of the reporting system. Third, the conventional OLAP engine executes MDX queries independently, and in some cases where the query request amount is large, slow execution and memory overflow may occur.

Aiming at the problem that the query request of the report is difficult to respond quickly due to the defects of the MDX engine technology in the prior art, an effective solution is not provided in the prior art.

Disclosure of Invention

The embodiment of the invention provides a query method and a query device suitable for an OLAP query engine, which can construct an efficient OLAP query execution engine, can deal with complex OLAP queries of various report systems, can obviously improve the execution efficiency of MDX queries, and can accelerate the response of analysis requests of the report systems.

In a first aspect of the embodiments of the present invention, a query method applicable to an OLAP query engine is provided, where the query method includes:

the method comprises the steps of obtaining an MDX query request received by an OLAP query engine, and generating at least one group of aggregation query sets for the MDX query request based on dimension processing, wherein the group of aggregation query sets comprises a plurality of aggregation query requests;

and processing the plurality of aggregated query requests in parallel based on an OLAP query engine to obtain a plurality of corresponding query results in a database, wherein the aggregated query requests are set corresponding to the query results.

Optionally, in a possible implementation manner of the first aspect, the obtaining the MDX query request received by the OLAP query engine includes:

and converting the format of the MDX query request from XMLA into any one or more of JSON format and Protobuf format.

Optionally, in a possible implementation manner of the first aspect, before the converting the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format, the method further includes:

judging whether the numerical value of the MDX inquiry request is larger than a first preset value or not;

and if the numerical value of the MDX query request is larger than a first preset value, converting the format of the MDX query request from XMLA into any one or more of JSON format and Protobuf format.

Optionally, in a possible implementation manner of the first aspect, after the concurrently processing, by the OLAP-based query engine, the multiple aggregated query requests to obtain corresponding multiple query results in a database, the method further includes:

and feeding the query results back to an OLAP query engine and a report system.

Optionally, in a possible implementation manner of the first aspect, service requirement data is obtained, where the service requirement data includes dimension information;

and constructing the original detail data included in the data warehouse into Cube corresponding to the dimension information according to the dimension information.

In a second aspect of the embodiments of the present invention, a query apparatus suitable for an OLAP query engine is provided, where the query apparatus includes:

the query pattern matching module is used for acquiring an MDX query request received by an OLAP query engine, and generating at least one group of aggregation query sets for the MDX query request based on dimension processing, wherein the group of aggregation query sets comprises a plurality of aggregation query requests;

and the query distributed execution module is used for processing the plurality of aggregated query requests in parallel based on an OLAP query engine to obtain a plurality of corresponding query results in a database, wherein the aggregated query requests are set corresponding to the query results.

Optionally, in a possible implementation manner of the second aspect, the system further includes a client agent module, configured to convert the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format.

Optionally, in a possible implementation manner of the second aspect, the client agent module is configured to determine whether a value of the MDX query request is greater than a first preset value;

Optionally, in a possible implementation manner of the second aspect, the system further includes a pre-aggregation module, configured to obtain service requirement data, where the service requirement data includes dimension information;

A third aspect of the embodiments of the present invention provides a readable storage medium, in which a computer program is stored, and the computer program is used for implementing the method according to the first aspect of the present invention and various possible designs of the first aspect of the present invention when the computer program is executed by a processor.

The invention provides a query method and a query device suitable for an OLAP query engine, which can generate at least one group of aggregation query sets by an MDX query request based on dimension processing, and process and acquire corresponding query results in the query process of the OLAP query engine.

In addition, the invention provides a performance optimization scheme of network transmission when the OLAP engine has a large query result set, and the network transmission data volume between the report system and the OLAP engine is greatly reduced.

In addition, the invention provides an execution flow optimization scheme of the OLAP engine in processing complex MDX query, more MDX operators are combined for operation, the operation flow is simplified, and the parallelism of MDX execution is enhanced.

In addition, the invention also provides a query optimization scheme for the OLAP engine when processing large data volume, and combines the advantages of distributed computation and pre-computation, thereby greatly improving the query performance.

Drawings

FIG. 1 is a flow diagram of a first embodiment of a query method suitable for use in an OLAP query engine;

FIG. 2 is a flow diagram of a second embodiment of a query method suitable for use in an OLAP query engine;

FIG. 3 is a flow diagram of a third embodiment of a query method suitable for use in an OLAP query engine;

FIG. 4 is a block diagram of a first embodiment of a query method suitable for use in an OLAP query engine;

FIG. 5 is a block diagram of a second embodiment of a query method suitable for use in an OLAP query engine.

Detailed Description

In order to make the objects, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

The terms "first," "second," "third," "fourth," and the like in the description and in the claims, as well as in the drawings, if any, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used is interchangeable under appropriate circumstances such that the embodiments of the invention described herein are capable of operation in sequences other than those illustrated or described herein.

It should be understood that, in various embodiments of the present invention, the sequence numbers of the processes do not mean the execution sequence, and the execution sequence of the processes should be determined by the functions and the internal logic of the processes, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

It should be understood that in the present application, "comprising" and "having" and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.

It should be understood that, in the present invention, "a plurality" means two or more. "and/or" is merely an association describing an associated object, meaning that three relationships may exist, for example, and/or B, may mean: a exists alone, A and B exist simultaneously, and B exists alone. The character "/" generally indicates that the former and latter associated objects are in an "or" relationship. "comprises A, B and C" and "comprises A, B, C" means that all three of A, B, C comprise, "comprises A, B or C" means that one of A, B, C comprises, "comprises A, B and/or C" means that any 1 or any 2 or 3 of A, B, C comprises.

It should be understood that in the present invention, "B corresponding to a", "a corresponds to B", or "B corresponds to a" means that B is associated with a, and B can be determined from a. Determining B from a does not mean determining B from a alone, but may be determined from a and/or other information. And the matching of A and B means that the similarity of A and B is greater than or equal to a preset threshold value.

As used herein, "if" may be interpreted as "at … …" or "when … …" or "in response to a determination" or "in response to a detection", depending on the context.

The technical solution of the present invention will be described in detail below with specific examples. The following several specific embodiments may be combined with each other, and details of the same or similar concepts or processes may not be repeated in some embodiments.

Definition of terms, OLAP: online data analysis, a technique that enables analysts to quickly gain insight into data from multiple dimensions; MDX, multi-dimensional expressions (multi-dimensional expressions), commonly used for OLAP queries; cube: a multi-dimensional dataset; XMLA: a data access protocol.

The invention provides a query method suitable for an OLAP query engine, as shown in FIG. 1, comprising:

step S20, obtaining an MDX query request received by the OLAP query engine, and generating at least one aggregated query set based on dimension processing for the MDX query request, where the aggregated query set includes multiple aggregated query requests.

In step S20, the MDX query received by the OLAP query engine is mainly analyzed, and an aggregate query plan adapted to the report layout is generated. Firstly, report operation and layout elements contained in the MDX query, such as selection, sorting and filtering of dimension measurement, use of a report filter, hierarchical drill-down, subtotal and total, are analyzed through pattern matching, then the layout elements of the report under each aggregation level are obtained through decomposition, and finally a group of plans of an aggregation query set is generated, wherein each plan contains dimension measurement information to be obtained, filtering and sorting information and the like.

The method can effectively optimize the execution process of the whole OLAP query engine, combine and execute more MDX operators, and enhance the parallelism of the MDX execution process.

Step S40, based on the OLAP query engine, processing the multiple aggregated query requests in parallel to obtain multiple corresponding query results in the database, where the aggregated query requests are set corresponding to the query results.

In step S40, the execution will be performed mainly in a distributed manner for a plurality of aggregated query requests, plans. Firstly, finding out data information of a corresponding data source, namely a query result (which may be pre-aggregated Cube information or raw table data in a data warehouse) according to dimension measurement information, and then submitting a query task to a distributed execution engine, wherein the task includes loading data in the data source into the distributed execution engine (the distributed execution engine includes but is not limited to Spark, MapReduce and the like), executing operations such as filtering and sorting, and performing function operation of some MDXs in the distributed execution engine. Where the query results may be pre-stored in a database.

Step S20, the obtaining of the MDX query request received by the OLAP query engine includes:

step S202, converting the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format.

Before step S202, as shown in fig. 2, the method further includes:

step S200, judging whether the numerical value of the MDX inquiry request is larger than a first preset value or not;

step S201, if the value of the MDX query request is greater than a first preset value, converting the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format.

In step S200, step S201, and step S202, the first preset value may be preset. The OLAP query method is mainly used for an agent reporting system, firstly, the data format of the OLAP query is converted from XMLA to a more lightweight and efficient data format (including but not limited to JSON, Protobuf and the like), the network data transmission quantity is further reduced by using a compression algorithm, then the data format interacts with an OLAP query engine, and finally, the data format of the result obtained from the OLAP query engine is converted into XMLA and returned to the reporting system. The steps can greatly reduce the network data transmission quantity of the original report system and the OLAP query engine. When the query result amount is small, the step is not needed.

After step S40, the method further includes:

and step S50, feeding the query results back to the OLAP query engine and the reporting system.

Through step S50, after the distributed execution engine finishes executing according to the aggregated query plan, the results are collected back to the OLAP query engine and returned. This step solves the performance problem that exists when the query request volume is large.

In one embodiment, as shown in fig. 3, the method further comprises the following steps:

step S60, acquiring service requirement data, wherein the service requirement data comprises dimension information;

and step S80, constructing the original detail data included in the data warehouse into Cube corresponding to the dimension information according to the dimension information.

In step S60 and step S80, the raw detailed data in the data warehouse is constructed into Cube according to the OLAP analysis requirement of the business (which dimension metrics need to be analyzed), and the aggregated data needed by the OLAP query is provided. The method adopts OLAP modeling tools (including but not limited to kylin and the like) which are commonly used in the industry, and mainly improves the query efficiency of the aggregation query. It should be noted that this step is not a main flow. When the query request amount is small, this step may not be used.

The applicant tests the query performance of the traditional OLAP query engine and the OLAP query engine constructed by the scheme under a common report system based on Apache Kylin as a pre-aggregation module, wherein the speed of the OLAP query engine is 2-5 times faster than that of the traditional OLAP query engine under common query, and the speed of the OLAP query engine under complex query is 10-100 times faster than that of the traditional OLAP query engine under the common query.

The present invention further provides a query apparatus suitable for the OLAP query engine, as shown in fig. 4, including:

Further, as shown in fig. 5, the MDX query request further includes a client agent module, configured to convert the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format.

Further, the client agent module is configured to determine whether a value of the MDX query request is greater than a first preset value;

The system further comprises a pre-polymerization module for acquiring service demand data, wherein the service demand data comprises dimension information;

The readable storage medium may be a computer storage medium or a communication medium. Communication media includes any medium that facilitates transfer of a computer program from one place to another. Computer storage media may be any available media that can be accessed by a general purpose or special purpose computer. For example, a readable storage medium is coupled to the processor such that the processor can read information from, and write information to, the readable storage medium. Of course, the readable storage medium may also be an integral part of the processor. The processor and the readable storage medium may reside in an Application Specific Integrated Circuits (ASIC). Additionally, the ASIC may reside in user equipment. Of course, the processor and the readable storage medium may also reside as discrete components in a communication device. The readable storage medium may be a read-only memory (ROM), a random-access memory (RAM), a CD-ROM, a magnetic tape, a floppy disk, an optical data storage device, and the like.

The present invention also provides a program product comprising execution instructions stored in a readable storage medium. The at least one processor of the device may read the execution instructions from the readable storage medium, and the execution of the execution instructions by the at least one processor causes the device to implement the methods provided by the various embodiments described above.

In the above embodiments of the terminal or the server, it should be understood that the Processor may be a Central Processing Unit (CPU), other general-purpose processors, a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), etc. A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like. The steps of a method disclosed in connection with the present invention may be embodied directly in a hardware processor, or in a combination of the hardware and software modules within the processor.

Finally, it should be noted that: the above embodiments are only used to illustrate the technical solution of the present invention, and not to limit the same; while the invention has been described in detail and with reference to the foregoing embodiments, it will be understood by those skilled in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; and the modifications or the substitutions do not make the essence of the corresponding technical solutions depart from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. a query method applicable to OLAP query engine, is characterized in that, comprises:

Obtain the MDX query request received by the OLAP query engine, and generate at least one group of aggregated query requests based on dimension processing for the MDX query request, and the group of aggregated query sets includes multiple aggregated query requests;

Based on the OLAP query engine, the multiple aggregated query requests are processed in parallel to obtain multiple corresponding aggregated query results in the database, and the aggregated query requests are set corresponding to the aggregated query results.

2. the query method of OLAP query engine according to claim 1, is characterized in that,

Obtaining the MDX query request received by the OLAP query engine includes:

Convert the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format.

3. the query method of OLAP query engine according to claim 2, is characterized in that,

Before converting the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format, it also includes:

Determine whether the size of the MDX query request packet is greater than a first preset value;

If the size of the MDX query request packet is larger than the first preset value, the format of the MDX query request is converted from XMLA to any one or more of JSON format and Protobuf format.

4. the query method of OLAP query engine according to claim 1, is characterized in that,

After the OLAP-based query engine processes the multiple aggregated query requests in parallel to obtain multiple corresponding query results in the database, the method further includes:

The multiple query results are fed back to the OLAP query engine and the report system.

5. the query method of OLAP query engine according to claim 1, is characterized in that,

Obtain business requirement data, where the business requirement data includes dimension information;

According to the dimension information, the original detailed data included in the data warehouse is constructed into a cube corresponding to the dimension information.

6. A query device applicable to an OLAP query engine is characterized in that, comprising:

a query pattern matching module, configured to obtain the MDX query request received by the OLAP query engine, and to generate at least one group of aggregated query sets based on dimension processing for the MDX query request, where the group of aggregated query sets includes multiple aggregated query requests;

The query distributed execution module is configured to obtain multiple corresponding query results in the database based on the parallel processing of the multiple aggregated query requests by the OLAP query engine, where the aggregated query requests are set corresponding to the query results.

7. the query device of OLAP query engine according to claim 1, is characterized in that,

It also includes a client proxy module for converting the format of the MDX query request from XMLA to any one or more of JSON format and Protobuf format.

8. the query device of OLAP query engine according to claim 1, is characterized in that,

The client proxy module is used to judge whether the numerical value of the MDX query request is greater than a first preset value;

If the value of the MDX query request is greater than the first preset value, the format of the MDX query request is converted from XMLA to any one or more of JSON format and Protobuf format.

9. the query method of OLAP query engine according to claim 1, is characterized in that,

Also includes a pre-aggregation module for acquiring business requirement data, where the business requirement data includes dimension information;

10 . A readable storage medium, wherein a computer program is stored in the readable storage medium, and when the computer program is executed by a processor, is used to implement the method according to any one of claims 1 to 5 .