Data analysis and query methods based on the MDX engine

Abstract

This application relates to a data analysis and query method based on an MDX engine. The MDX engine can accurately generate data model description information and MDX statements based on the target fields queried by the user. By combining the data model description information and MDX statements, the query results of the target fields can be efficiently obtained from the database. The method of this application is particularly good at handling multidimensional data queries, has higher query efficiency, and provides users with a better query experience.

Description

Technical Field

[0001] The present application relates to the technical field of data warehouse, and in particular to a data analysis and query method based on an MDX engine. Background Art

[0002] With the advent of the big data era, big data analysis technology is becoming more and more popular. By quickly acquiring, processing, extracting and analyzing rich, multi-dimensional business data, valuable information can be obtained to assist the production and operation decisions of enterprises, thereby promoting the development of enterprises. Currently, the technologies involved in the field of big data include data warehouses, data security, data analysis, data mining, etc. These technologies are gradually attracting the attention of people in various industries in various business fields.

[0003] In existing big data application scenarios, the scale of data stored and managed by the database is huge, and the data has many dimensions. When users perform data analysis, data queries increasingly involve multidimensional data queries, which puts higher and higher requirements on data query technology, especially in terms of query efficiency. However, the existing technology is still not efficient and timely when processing users' multidimensional data queries, and the user's query efficiency is low. A method for improving the query efficiency of multidimensional data queries is needed. Summary of the invention

[0004] Based on this, the purpose of this application is to provide a data analysis query method based on the MDX engine, which has higher efficiency in processing multidimensional data queries.

[0005] The first aspect of the embodiment of the present application provides a data analysis query method based on the MDX engine, comprising the following steps:

[0006] Get the user's report query request;

[0007] Parsing the report query request to obtain a target field for query; determining a basic field according to the target field; wherein the target field includes a first MDX operation field, and the first MDX operation field is bound to at least one basic field;

[0008] Determine a plurality of star data models containing the basic fields; wherein each of the star data models respectively records a fact table, a dimension table, and the basic fields respectively contained in each data table;

[0009] The MDX engine obtains corresponding operation definition information according to the first MDX operation field; generates data model description information according to each of the star data models and the operation definition information; generates an MDX statement according to the report query request and the operation definition information; and obtains the query result of the target field from a preset database according to the MDX statement and the data model description information.

[0010] The data query method based on the MDX query engine described in the embodiment of the present application parses the user's report query request to obtain the target field of the query, and the target field includes a first MDX operation field; determines the basic field according to the target field, and determines a number of star data models containing the basic field; obtains the corresponding operation definition information according to the first MDX operation field through the MDX engine; generates data model description information according to each of the star data models and the operation definition information; generates an MDX statement according to the report query request and the operation definition information; and obtains the query result of the target field from a preset database according to the MDX statement and the data model description information. Through the method of the present application, the MDX engine can accurately generate data model description information and MDX statements, so that combined with the data model description information and the MDX statement, it can efficiently query the query result of the target field from the database. Through the method of the present application, it is particularly good at processing multidimensional data queries, has more efficient query efficiency, and gives users a better query experience.

[0011] For better understanding and implementation, the present application is described in detail below with reference to the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0012] Figure 1 A flowchart of a data analysis and query method based on an MDX engine according to an embodiment of the present application;

[0013] Figure 2 A schematic diagram of determining a star data model from a constellation data model in an example of this application;

[0014] Figure 3 This is a schematic diagram of the star data model after the dimension table in this application example has been trimmed. DETAILED DESCRIPTION

[0015] In order to make the purpose, technical solution and advantages of the present application clearer, the embodiments of the present application will be further described in detail below in conjunction with the accompanying drawings. Wherein, when the following description refers to the accompanying drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements.

[0016] It should be clear that the implementation methods described in the embodiments described below do not represent all implementation methods consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the attached claims. Based on the embodiments of the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present application.

[0017] The terms used in this application are only for the purpose of describing specific embodiments and are not intended to limit this application. The singular forms of "a", "said" and "the" used in this application are also intended to include plural forms, unless the context clearly indicates other meanings. In addition, in the description of this application, unless otherwise specified, "multiple" refers to two or more. It should also be understood that the term "and / or" used in this article refers to and includes any or all possible combinations of one or more associated listed items, for example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone; the character " / " generally indicates that the objects associated before and after are in an "or" relationship.

[0018] It should be understood that, although the terms first, second, third, etc. may be used in the present application to describe various information, such information should not be limited to these terms, and these terms are only used to distinguish similar objects, and do not have to be used to describe a specific order or sequence, nor can they be understood as indicating or implying relative importance. For those of ordinary skill in the art, the specific meanings of the above terms in the present application can be understood according to the specific circumstances. Depending on the context, the words "if" / "if" used in the present application can be interpreted as "at the time of" or "when" or "in response to determination".

[0019] The present application relates to the field of data warehouse technology. In existing big data application scenarios, the scale of data stored and managed by the database is huge, and the data has many dimensions. When users perform data analysis, data queries increasingly involve multidimensional data queries, which puts forward higher and higher requirements for data query technology, especially in terms of query efficiency. However, the existing technology is still not efficient and timely enough when processing users' multidimensional data queries, and the user's query efficiency is low. A method for improving the query efficiency of multidimensional data queries is needed.

[0020] Please refer to Figure 1 The present application embodiment provides a data analysis query method based on the MDX engine, comprising the following steps:

[0021] S101: Obtaining a report query request from a user;

[0022] S102: parsing the report query request to obtain a target field for query; determining a basic field according to the target field; wherein the target field includes a first MDX operation field, and the first MDX operation field is bound to at least one basic field;

[0023] S103: Determine a plurality of star data models including the basic fields; wherein each of the star data models respectively records a fact table, a dimension table, and basic fields respectively included in each data table;

[0024] S104: Obtain corresponding operation definition information according to the first MDX operation field through the MDX engine; generate data model description information according to each of the star data models and the operation definition information; generate an MDX statement according to the report query request and the operation definition information; and obtain the query result of the target field from a preset database according to the MDX statement and the data model description information.

[0025] The data query method based on the MDX query engine described in the embodiment of the present application parses the user's report query request to obtain the target field of the query, and the target field includes a first MDX operation field; determines the basic field according to the target field, and determines a number of star data models containing the basic field; obtains the corresponding operation definition information according to the first MDX operation field through the MDX engine; generates data model description information according to each of the star data models and the operation definition information; generates an MDX statement according to the report query request and the operation definition information; and obtains the query result of the target field from a preset database according to the MDX statement and the data model description information. Through the method of the present application, the MDX engine can accurately generate data model description information and MDX statements, so that combined with the data model description information and the MDX statement, it can efficiently query the query result of the target field from the database. Through the method of the present application, it is particularly good at processing multidimensional data queries, has more efficient query efficiency, and gives users a better query experience.

[0026] The executor of the data analysis and query method based on the MDX engine described in the embodiment of the present application is a computer. Specifically, it can be applied to a business intelligence system (BI system) to process user data query requests.

[0027] First, the relevant concepts in the embodiments of the present application are explained.

[0028] In the embodiment of the present application, the query request of the user may include multiple types, specifically, report query request and detailed data query request, wherein the report query request usually involves not only querying the native data of the native field (basic field) in the database, but also may involve aggregate calculation, and may also involve multi-dimensional analysis, etc.; the detailed data query request is only a data query for the native field. Specifically, the distinction between report query request and detailed query request can be made by carrying different query identifiers, or carrying a specific query identifier in one query request while not carrying a specific query identifier in the other, so as to distinguish according to whether there is a specific query identifier.

[0029] Of course, without considering detailed data query, the report query request should be interpreted more broadly - its essence is a data query request.

[0030] In this embodiment, the query request is parsed to obtain the target field that the user wants to query. Specifically, in different types of query requests (report query or detailed data query), the target fields obtained by parsing are generally different. In this embodiment, the target field obtained by parsing the report query request includes a basic field and / or a first MDX operation field. Generally speaking, the target field queried by the report query request includes at least a basic field, and may also include a first MDX operation field, wherein, if the first MDX operation field is included, the first MDX operation field will also be bound to at least one basic field-that is, the first MDX operation field specifies the operation of the bound basic field.

[0031] The basic fields are generally fields in the database that record the original business data. More specifically, the basic fields are generally divided into measurement fields and dimension fields.

[0032] The metric fields generally refer to fields in a fact table, and the dimension fields generally refer to fields in a dimension table.

[0033] The fact table and the dimension table are concepts in data modeling in the field of data warehouse. Currently, the main data models involved in the field of data warehouse include star data model, snowflake data model generated on the basis of star data model (which can also be considered as a kind of star data model) and constellation data model. In the star data model, a data table located in the center and other data tables connected to the center data table are included, wherein the center data table is the fact table, and the other data tables connected thereto are dimension tables. Generally speaking, in a complex constellation data model, if different data tables are centered, different star data models can be determined. A data table can be used as a fact table in a certain star data model and as a dimension table in other star data models. In one embodiment, the fields that can be queried by the user can be classified and stored in the database according to "metric field category" and "dimensional field category" respectively in advance, so that when the user's query request is received, the target field of the user's query is obtained and the basic field is determined according to the target field, it can be judged whether the basic field is a metric field or a dimension field according to the preset classification. In another embodiment, different identifiers can be added for different basic fields in the front-end system where the user performs query operations, so that the background determines that the basic field is a metric field based on the metric field identifier, and determines that the basic field is a dimension field based on the dimension field identifier (similarly, for various fields appearing in the embodiments of the present application, including basic fields and MDX operation fields, different field type identifiers can be added to determine the type of field).

[0034] Among them, the MDX operation field is not a native field recorded in the database, but a field obtained by definition. The data queried is obtained by performing complex operations (such as complex calculations) based on the data of the basic fields in the database. Specifically, the MDX operation fields for various operations can be defined according to the needs of data analysis.

[0035] Specifically in one embodiment, the MDX operation fields may be "same period", "previous period", "year-on-year", "month-on-month", "global proportion", "group proportion", "global ranking" and "group ranking", etc. Obviously, the query of the MDX operation field involves calculation or comparison of the data of the basic field in multiple dimensions. Unlike querying the basic field, which only requires obtaining the data corresponding to the basic field from the database, more complex analysis and processing involving multiple dimensions and / or multiple basic data is required.

[0036] The following is a detailed description of each step of the data analysis query method based on the MDX engine described in the embodiment of the present application.

[0037] In step S101, a report query request from a user is obtained.

[0038] In this embodiment, the report query request is a query request sent by the user after operating the report query interface of the front-end system.

[0039] In step S102, the report query request is parsed to obtain a target field for query; and a basic field is determined according to the target field.

[0040] This step parses the report query request to obtain the target field of the query, where the target field may include only the first MDX operation field, or may include the basic field and the first MDX operation field. If the target field of the query includes the first MDX operation field, the first MDX operation field will also be bound to the basic field. It should be noted that in this case, the first MDX operation field is the target field of the query, but the basic field bound to it is not the target field of the query. For example, the query request queries the "sales volume" "same period growth rate", where "sales volume" is the target field of the query, and "sales volume" is only the basic field referenced by the target field. The final query result should be the query result of "sales volume" and does not include the data of the "sales volume" field.

[0041] This step determines the basic fields related to the query based on the target field (same as above, not necessarily the target field of the query), so that in step S103, the star data model related to this query can be determined based on the basic fields. Specifically, if the target field of the query is a basic field, then it is the basic field related to the query; if the target field of the query is an MDX operation field, then the basic field bound to it is the basic field related to the query. In some special cases, there is also a type of aggregate field. This type of field is associated with the basic field, and the associated basic field determined by it is the basic field related to the query. For example, for the aggregate field "average sales volume", the "sales volume" field is the basic field related to the query.

[0042] In one embodiment, the first MDX operation field includes a user-defined operation field and a predefined operation field.

[0043] The predefined operation field is a predefined MDX operation field, and its corresponding definition formula is pre-stored in the background. Its operation definition information can be directly generated according to the definition formula and the basic fields bound during query.

[0044] Among them, the user-defined operation field is the MDX operation field constructed by the user in the front-end system of the operation query (and the MDX operation definition formula of the MDX operation field is constructed). When the background receives a query request containing a user-defined operation field, it will also parse the basic fields bound to the user-defined operation field and the user-defined MDX operation definition formula. Similarly, its operation definition information is generated according to the bound basic fields and MDX operation definition formula.

[0045] The operation definition information is used to define the operation processing method corresponding to the MDX operation field, which can also be said to define what operation to perform on the data of the basic field bound to the MDX operation field.

[0046] In one example, the predefined operation field is "same period growth rate". When querying, the user selects the metric field "sales volume", selects the parameter of its "time" dimension field as 2024, and selects the MDX operation field "same period growth rate". When submitting the query, the backend obtains the corresponding definition formula according to the "same period growth rate", such as "same period growth rate = (current data - previous data) / previous data", and substitutes the specific basic fields (metric fields and / or dimension fields) and related parameters into the definition formula to obtain the operation definition information of the "same period growth rate" of the query, such as "same period growth rate = (2024 sales volume - 2023 sales volume) / 2023 sales volume". Of course, this example is only for illustrative purposes to facilitate understanding of the MDX operation field and its operation definition information. The specific implementation details can be set according to the situation.

[0047] In this embodiment, the report query request not only includes the target field of the query, but also generally includes other parameters related to the query. In one embodiment, the query restriction information is also obtained by parsing the report query request. The query restriction information is used to limit the user's query, including limiting the scope of the data query or limiting the conditions for filtering the queried data. The specific limitation form can be set according to the situation.

[0048] In the process of parsing the report query request to obtain the target field of the query in this step, if an MDX operation field is involved, the relevant processing of the MDX operation field is also involved. Specifically, in this embodiment, the first MDX operation field is divided into a first MDX operation field including a static attribute and a first MDX operation field of a dynamic attribute. The first MDX operation fields of the two attributes will be processed differently in subsequent embodiments, and the details are described in the subsequent description.

[0049] In one embodiment, after the step of parsing the report query request to obtain the target field of the query in step S102, the following step is further included:

[0050] Step S1021, if the parsing of the report query request further obtains a user-defined operation field, a basic field bound to the user-defined operation field, and an MDX operation definition formula, the operation definition information corresponding to the user-defined operation field is determined according to the basic field bound to the user-defined operation field and the MDX operation definition formula; the user-defined operation field, the operation definition information corresponding to the user-defined operation field, and the attribute information of the user-defined operation field are recorded in the operation definition data set as dynamic attributes;

[0051] Step S1022: if the report query request is parsed to obtain a predefined operation field, a basic field bound to the predefined operation field, and a quick query identifier, then, according to the predefined operation field, a corresponding first operation definition formula is obtained from a preset operation definition formula library, and the operation definition information corresponding to the predefined operation field is determined according to the basic field bound to the predefined operation field and the first operation definition formula; the predefined operation field, the operation definition information corresponding to the predefined operation field, and the attribute information of the predefined operation field are recorded in the operation definition data set as static attributes; wherein the preset operation definition formula library stores a plurality of predefined operation fields and corresponding operation definition formulas;

[0052] Step S1023, if parsing the report query request also obtains a predefined operation field and a basic field bound to the predefined operation field, and the quick query identifier bound to the predefined operation field is not parsed, then according to the predefined operation field, the corresponding second operation definition formula is obtained from the operation definition formula library, and the operation definition information corresponding to the predefined operation field is determined according to the basic field bound to the predefined operation field and the second operation definition formula; the predefined operation field, the operation definition information corresponding to the predefined operation field, and the attribute information of the predefined operation field are recorded in the operation definition data set as dynamic attributes.

[0053] Among them, the operation definition data set can be created in advance to store the MDX operation field and its operation definition information and attribute information each time the MDX operation field is obtained by parsing the query request; it can also be created temporarily, and the MDX operation field and its operation definition information and attribute information are created and stored each time the MDX operation field is obtained by parsing the query request.

[0054] In this embodiment, three methods for processing report query requests are provided, namely, step S1021, step S1022 and step S1023, which respectively correspond to three query methods of users in the front-end query interface: 1. Users customize MDX operation fields for queries; 2. Users use predefined operation fields preset on the interface to query (this query method is a quick query method); 3. Users build query components based on preset predefined operation fields (the component contains user-specified basic fields and predefined operation fields. After creating the query component, the query component can be directly added to various queries, thereby reusing the settings in the query component), and use the query component to query.

[0055] For these three situations, after parsing to obtain the user-defined operation field or the predefined operation field and determining the user-defined operation field or the predefined operation field as the target field for the query, the user-defined operation field or the predefined operation field, and its corresponding operation definition information, and its attribute information are recorded in the operation definition data set. Regarding the determination of attribute information, in this embodiment, for the MDX operation field bound to the quick query identifier in the second query, its attribute information is recorded as a static attribute, and for the MDX operation field in other query scenarios, the attribute information is recorded as a dynamic attribute. The difference between the MDX operation fields of the two attributes and the corresponding processing methods will be explained in the subsequent corresponding steps.

[0056] For step S103, several star data models including the basic fields are determined; wherein each of the star data models respectively records the fact table, the dimension table and the basic fields respectively included in each data table.

[0057] This step determines several star data models including the basic fields according to the basic fields determined in step S102. The several star data models may be one or more. If there is one, it includes all the basic fields. If it is not possible to include all the basic fields in one star data model, only a part of the basic fields can be included in multiple star data models.

[0058] The star data model may be pre-set, with several star data models existing in the model database; or it may be determined from a larger constellation data model.

[0059] In this embodiment, the star data model is determined from a preset constellation data model, wherein the constellation data model is a data logic layer established in advance based on various data tables stored in a database, which records a number of data tables, basic fields contained in each data table, and graph relationships of each data table. In other words, the star data model does not record the specific business data of the fields of each data table, but only records the relevant information used to generate query statements, which mainly includes the table name of the data table and the field information of the data table. In addition, the graph relationship between the various data tables is established in the constellation data model. Among them, the graph relationship at least includes a directed graph relationship.

[0060] Among them, the main goal of determining the star data model from the constellation data model is to obtain the minimum available star data model related to the query, and the minimum available star data model should only contain data tables related to the query, and the data tables should only contain basic fields determined in step S102, wherein the data tables related to the query are divided into several cases: for dimension tables, they are considered to be data tables related to the query only when they contain the basic fields or are bound to query-related restriction information; there is no limitation for fact tables. Generally speaking, a fact table is a data table that must exist in a star data model, even if it does not contain basic fields.

[0061] There are many ways to determine the minimum available star data model, which are described in the following embodiments.

[0062] In one embodiment, the step S103 of determining a plurality of star data models including the basic fields comprises the following steps:

[0063] Step S1031, determining at least one data table in a preset constellation data model as a target data table according to the basic field;

[0064] Step S1032, taking any of the target data tables as the root node, determining other data tables in the constellation data model that can reach the target data table based on the graph relationship, and taking each of the target data tables as a fact table and the other data tables that can be reached by the fact table as dimension tables, to obtain several star data models.

[0065] Step S1033, removing the edge dimension tables that do not include the dimension fields in any of the target star data models, removing the basic fields other than the metric fields in the fact tables of any of the target star data models, and removing the basic fields other than the dimension fields in the dimension tables of any of the star data models.

[0066] The ability to reach the target data table in step S1032 means that the target data table can be reached along the path in the graph relationship.

[0067] Please refer to Figure 2 , Figure 2 An example of determining a star data model from a constellation data model is shown, where: Figure 2 a represents the original constellation data model, and each node of AF represents a data table. In this example, data table A is the root node (target data table), so the determined star data model is Figure 2 The star data model shown in b, in which data table A is a fact table, and data tables B, C, F, and E are dimension tables (data tables C and E are generally also referred to as snowflake dimensions). Among them, data tables E and F originally had a bidirectional relationship in the constellation data model, and after the star data model is determined, only the directional relationship centered on data table A will be retained, that is, data table E points to data table F (data table F points to data table A). In other words, in the star data model determined by the embodiment of the present application, the fact table is the center, and even if there is a bidirectional relationship between the fact table and other dimension tables, only the direction pointing to the central fact table (directly or indirectly) will be retained. Based on this, in one embodiment, it is possible to distinguish whether each data table is a fact table or a dimension table based on the directional relationship of the data tables in the star data model.

[0068] The edge dimension table in step S1033 refers to the edge dimension table in the star data model, for example, Figure 2 In the star data model of b, dimension tables C and E are edge dimension tables. Furthermore, if dimension table C is removed, dimension table B will become an edge dimension table, and so on.

[0069] That is to say, this embodiment determines the target data table in the constellation data model according to the basic fields, and determines several star data models with the fact table as the center and other data tables that can reach the fact table as dimension tables; removes the dimension tables that are not related to the query in each star data model, and removes the basic fields that are not related to the query, so that the final star data model only includes the fact table, dimension tables and basic fields that are related to the query, that is, to obtain the minimum available star data model.

[0070] It should be noted that the basic fields may include both metric fields and dimension fields, or may only include metric fields, or may only include dimension fields. In different cases, the specific execution of the step of determining the target data table of the constellation data model according to the basic fields in step S1031 will be different.

[0071] Specifically in one embodiment, if the basic field only includes a metric field, the step of determining at least one data table in a preset constellation data model as a target data table according to the basic field in step S1031 includes:

[0072] Step S10311, determining at least one data table containing any one or more of the metric fields in a preset constellation data model as a target data table;

[0073] If the basic fields only include dimension fields, the step of determining at least one data table in the preset constellation data model as a target data table according to the basic fields in step S102 includes:

[0074] Step S10312: determine each data table in the constellation data model as a target data table.

[0075] That is to say, in an embodiment of the present application, if the basic fields include metric fields, a data table containing any one or more of the metric fields is found from the constellation data model, and is used as the target data table to determine a star data model, and then the star data model is trimmed to remove data tables and basic fields that are not related to the query.

[0076] If the basic fields do not include metric fields but only dimension fields, this query is mainly for the data in the dimension table. Therefore, each data table of the constellation data model is used as the target data table to determine the corresponding star data models. For example, if the constellation data model has 10 data tables, 10 star data models can be determined by using each data table as the target data table. Of course, in this case, it is necessary to further determine the star data model containing any of the dimension fields from all the star data models. Specifically, after step S10312, it is necessary to determine whether there is a star data model whose dimension table contains the dimension field. In other words, if a star data model does not have a dimension table containing the dimension field, the star data model is discarded; only then are irrelevant dimension tables and irrelevant basic fields in the finalized star data model removed.

[0077] In one embodiment, after the step of obtaining a plurality of star data models by using each of the target data tables as a fact table and the other data tables that can be reached by the fact tables as dimension tables in step S1032, the following steps are included:

[0078] Determine the query restriction parameters of the user bound to each dimension table of the star data model;

[0079] The step of removing the edge dimension table that does not include the dimension field in any of the star data models in step S1033 includes:

[0080] The edge dimension tables that do not contain any of the dimension fields and are not bound to the query restriction parameters in any of the target star data models are removed.

[0081] Please refer to Figure 2 and Figure 3 In one case, assuming Figure 2 In the star data model of b, dimension tables B and F contain the dimension fields queried, while dimension tables C and E do not contain any dimension fields queried this time and are not bound to the user's query restriction parameters. Then dimension tables C and E will be removed from the star data model, and the result is Figure 3 In another case, assuming that dimension tables B, C, and E contain the dimension fields to be queried, and dimension table F does not contain the dimension fields to be queried and is not bound to the user's query restriction parameters, the star data model is still Figure 2 As shown in b, dimension tables F and E will not be removed.

[0082] That is to say, in this embodiment, when determining dimension tables irrelevant to the current query, not only whether the dimension table includes dimension fields and whether it is located at the edge is considered, but also whether the query restriction parameter is bound. If the query restriction parameter is bound, even if it is located at the edge and does not include dimension fields, it should be retained. This avoids pruning dimension tables related to the query.

[0083] In one embodiment, step S102 parses the report query request and further obtains query restriction information, where the query restriction information includes a metric field and a dimension field and / or a second MDX operation field bound to the metric field.

[0084] Among them, the query restriction information is information set by the user to limit the scope of query data, including a metric field and a dimension field and / or a second MDX operation field bound to the metric field. The metric field is the target data restricted by the query restriction information, and the dimension field and the second MDX operation field define what specific restrictions are to be made. For example, if the dimension field is bound to the parameter "Region = Guangdong", the data of the metric field will be restricted to the "Guangdong" region. For example, if the second MDX operation field defines "the average value of the past three months" (this is only an illustrative explanation, and the specific implementation will be specific parameters and relationship expressions), the data of the metric field will be restricted to the time range of the past three months and the average value of these data will be calculated.

[0085] In this embodiment, if the report query request also parses to obtain query restriction information, after determining several star data models, it is determined whether the query restriction information is valid by determining whether the star data model contains the metric field of the query restriction information. This is because the star data model records the data table and basic fields related to the query request, and the metric field of the query restriction information is not in any star data model. Obviously, the object of this restriction is irrelevant to the data of this query and has no restrictive effect. The query restriction information can be directly discarded and not processed. If the metric field of the query restriction information is in any star data model, the query restriction information is a valid restriction, and it is necessary to consider whether the query restriction information involves the MDX operation field.

[0086] Among them, for the situation of step S402, after determining that the query restriction information is valid and includes the second MDX operation field, it is necessary to record the second MDX operation field in the query restriction information and the operation definition information corresponding to the second MDX operation field into the operation definition data set, and record its attribute information. In this embodiment, the attribute information of the second MDX operation field in the query restriction information is uniformly recorded as dynamic attributes. In other embodiments, it can also be recorded as dynamic attributes or static attributes according to the situation. Thereby, the corresponding query definition information can be determined when the MDX statement is generated. Among them, since the target field does not include the first MDX operation field, when the data is queried through the MDX engine, there is obviously no step of obtaining the corresponding operation definition information according to the first MDX operation field, but it should be to obtain the corresponding operation definition information according to the second MDX operation field. In addition, the subsequent steps are the same and will not be repeated.

[0087] For step S104, the corresponding operation definition information is obtained according to the first MDX operation field through the MDX engine; data model description information is generated according to each of the star data models and the operation definition information; an MDX statement is generated according to the report query request and the operation definition information; and the query result of the target field is obtained from the preset database according to the MDX statement and the data model description information.

[0088] In this embodiment, the MDX statement is a query statement with a grammatical structure similar to that of the SQL statement. In the SQL statement or the MDX statement, the general format is: select query field from query data source; when there are fields that need to be defined, the query statement also includes "with field operation definition" before the select field; when there are query restrictions that need to be limited, the query statement also includes "where query restrictions" after the from field.

[0089] That is to say, when generating a query statement, at least the query field and the query data source need to be determined, wherein the query field is used to specify the target field that the user wants to query, and the query data source generally specifies the target data table of the target database that the user wants to query, so it is generally filled in with the table name of the data table. In some cases, it can also be filled in with another query statement (the query statement essentially determines a set of data, and this set of data can also be considered to constitute a data table).

[0090] The preset database in this embodiment includes data of each data table corresponding to the constellation data model. That is, the preset database is actually the basis for modeling the constellation data model. In one embodiment, the preset database is preferably an OLAP (On-Line Analytical Processing) database.

[0091] Regarding this step, you first need to understand the MDX engine. In this embodiment, the MDX engine refers to a multidimensional query engine. For details, please refer to the working principle of the Mondrian engine (an open source online analytical processing engine developed based on Java). In the MDX engine, it is necessary to combine the data model description information and the MDX statement for querying. Specifically, the data model description information is generally a Schema XML (Extensible Markup Language) file, which describes the database table description information, measurement description information, dimension description information, and MDX operation field description information (including calculated member description information and / or named set description information) of a data model cube (data cube); the query data source of the MDX statement is determined to be the model name defined in the data model description information (generally speaking, the Schema XML defines the model name as cube, and the corresponding name in the MDX statement is from cube).

[0092] In the MDX engine, for an MDX operation field, the corresponding operation definition information (such as a calculation formula) is required to perform the corresponding operation. Specifically, the definition of the MDX operation field can be defined in the with part of the MDX statement or in the data model description information (Schema XML file).

[0093] In this embodiment, the MDX engine is a multidimensional query engine obtained by secondary development based on a general multidimensional analysis engine such as the Mondrian engine. That is, it not only executes queries based on MDX statements and data model description information like a general multidimensional analysis engine, but also generates MDX statements and data model description information before that.

[0094] In one embodiment, the step of acquiring corresponding operation definition information according to the first MDX operation field by the MDX engine in step S104 includes:

[0095] Step S1041: determining, according to preset attribute information of the first MDX operation field, whether the first MDX operation field is a first MDX operation field of static attributes or a first MDX operation field of dynamic attributes;

[0096] Step S1042, obtaining operation definition information of the first MDX operation field of the static attribute;

[0097] Step S1043, obtaining operation definition information of the first MDX operation field of the dynamic attribute;

[0098] The step of generating data model description information according to each of the star data models and the operation definition information comprises:

[0099] Step S1044, generating data model description information according to each of the star data models and the operation definition information of the first MDX operation field of the static attribute;

[0100] The step of generating an MDX statement according to the report query request and the operation definition information includes:

[0101] Step S1045 : generating an MDX statement according to the report query request and the operation definition information of the first MDX operation field of the dynamic attribute.

[0102] In this embodiment, different processing is performed on the first MDX operation fields of different attributes. For the first MDX operation field of a static attribute, its operation definition information is described in the data model description information, and for the first MDX operation field of a dynamic attribute, its operation definition information is described in the MDX statement (i.e., it is defined in the with part of the MDX statement).

[0103] In one embodiment, the step of determining, according to the preset attribute information of the first MDX operation field, in step S1041, whether the first MDX operation field is a first MDX operation field of a static attribute or a first MDX operation field of a dynamic attribute includes:

[0104] Determining, according to the attribute information of the first MDX operation field recorded in the operation definition data set, whether the first MDX operation field is a first MDX operation field of a static attribute or a first MDX operation field of a dynamic attribute;

[0105] The step of obtaining the operation definition information of the first MDX operation field of the static attribute in step S1042 includes:

[0106] According to the operation definition data set, obtaining operation definition information of a first MDX operation field of a static attribute;

[0107] The step of obtaining the operation definition information of the first MDX operation field of the dynamic attribute in step S1043 includes:

[0108] According to the operation definition data set, operation definition information of a first MDX operation field of a dynamic attribute is obtained.

[0109] In this embodiment, the step of generating an MDX statement according to the report query request and the operation definition information of the first MDX operation field of the dynamic attribute in step S1045 specifically includes:

[0110] Determine the query field of the MDX statement according to the target field;

[0111] The query definition information of the MDX statement is determined according to the operation definition information of the first MDX operation field of the dynamic attribute.

[0112] Among them, if the report query request also parses the query restriction information, and the query restriction information is valid for this query (that is, several metric fields in the star data model contain query restriction information), then it is also necessary to add corresponding restrictions in the MDX statement. Specifically, the query restriction condition (that is, the where part) of the MDX statement is determined according to the query restriction information.

[0113] In this embodiment, the step of generating data model description information according to each of the star data models and the operation definition information of the first MDX operation field of the static attribute in step S1044 includes:

[0114] Step S501, determining database table description information according to the fact table and dimension table of each of the star data models;

[0115] Step S502, determining metric description information according to the metric fields of each of the star data models;

[0116] Step S503, determining dimension description information according to the dimension fields of each of the star data models;

[0117] Step S504, determining first MDX operation field description information according to the operation definition information of the first MDX operation field of the static attribute;

[0118] Step S505: Generate data model description information according to the database table description information, the metric description information, the dimension description information and the first MDX operation field description information.

[0119] In an embodiment of the present application, the MDX statement is determined based on the target field of the user query, and the data tables and basic fields in the several star data models are also determined based on the target fields of the user query. The data model description information is generated according to the data tables and basic fields recorded in the several star data models, so that the data model description information actually describes the information related to the MDX statement, so that the MDX engine can execute the MDX statement according to the data model description information and query the query result of the target field.

[0120] The step of determining database table description information according to each fact table and dimension table of the star data model in step S501 includes:

[0121] Determine a corresponding first query data table according to any fact table in the star data model;

[0122] Determining a corresponding second query data table according to any dimension table in the star data model that is not bound to a query restriction parameter; wherein the query restriction parameter is used to limit the query scope of the user;

[0123] Determine a corresponding third query data table according to the dimension table bound to the query restriction parameter in any of the star data models; wherein the third query data table is determined according to the dimension table bound to the query restriction parameter and the query restriction parameter bound thereto;

[0124] Left-joining the first query data table corresponding to any of the star data models with the corresponding second query data tables in sequence, and inner-joining with the corresponding third query data tables in sequence, to obtain a corresponding fourth query data table;

[0125] Merge and concatenate the fourth query data tables corresponding to the star data models to obtain a fifth query data table;

[0126] According to the fifth query data table, database table description information is determined.

[0127] The merge connection in this embodiment adopts the connection mode of union all (an operator in SQL statement).

[0128] In the embodiment of the present application, the main data tables, basic fields and query restriction parameters related to this query are recorded in the star data model. According to the basic fields contained in the star data model, the basic data related to the target field can be determined, and according to the data tables contained in the star data model, the data tables related to this query can be determined. Among them, the data table may include three types: one is a fact table, one is a dimension table that is not bound to the query restriction parameter, and one is a dimension table bound to the query restriction parameter. For the first and second types, it can be determined that the data tables are the first query data table and the second query data table, respectively. For the third type, the third query data table is determined according to the dimension table bound to the query restriction parameter and the query restriction parameter bound to it. It can be understood that based on the query restriction parameter, it is used to limit the query scope of the query statement, so the third query data table is a data table used to limit the query scope of the query statement-specifically, by connecting the third query data table to the first query data table and the second query data table, the field data in the final data table of the query is restricted or filtered, so that the fourth query data table obtained after the connection limits the scope of the field or does not contain the field that limits the query. Of course, if there is no dimension table bound to the query restriction parameter, it is not necessary to determine the third query data table for limiting the query scope of the query statement, and only the first query data table and the second query data table need to be connected to obtain the fourth query data table.

[0129] In this embodiment, when the first query data table, the second query data table and the third query data table corresponding to each of the target star data models are respectively connected, the connection conditions can be set according to the situation. Specifically, the common fields between the two connected data tables can be used as the connection conditions through field matching, or the connection can be made according to the connection conditions set by the user, and there is no restriction on this.

[0130] It should be noted that, in this embodiment, if there is only one star data model, there is no step of merging and connecting the fourth query data tables corresponding to the star data models to obtain the fifth query data table, but the database table description information is directly obtained according to the fourth query data table.

[0131] In one embodiment, the step of generating data model description information according to each of the star data models and the operation definition information of the first MDX operation field of the static attribute in step S1044 includes the following steps:

[0132] Step S10441, if it is determined that the plurality of star data models determined by the current query do not have corresponding cached star data models, data model description information of the current query is generated according to the operation definition information of each of the star data models and the first MDX operation field of the static attribute;

[0133] Step S10442, if it is determined that there are corresponding cached star data models for the several star data models determined by the current query, determine whether there is a first MDX operation field for the static attribute of the current query in the corresponding cached data model description information; if it is determined that there is no first MDX operation field for the static attribute of the current query in the cached data model description information, generate the data model description information of the current query according to the operation definition information of each star data model and the first MDX operation field of the static attribute; if it is determined that there are corresponding cached star data models for the several star data models determined by the current query, and there is a first MDX operation field for the static attribute of the current query in the cached data model description information, use the cached data model description information as the data model description information of the current query.

[0134] In this embodiment, the MDX engine caches the data of the star data model determined during the historical query and the corresponding data model description information, so that when processing a new query request, if it is determined that there are corresponding cached star data models for the several star data models determined by the current query, and the first MDX operation field of the static attribute of the current query is also in the cached data model description information, there is no need to regenerate the data model description information, but directly use the cached data model description information as the data model description information of the current query, thereby speeding up the query efficiency. Specifically, this embodiment is actually aimed at the query situation of step S1022. When the user chooses to perform a quick query, its MDX operation field is recorded as a static attribute in step S1022. In the case of static attributes, it may meet the conditions and directly use the cached data model description information for quick query.

[0135] Based on the description of the above embodiment, in one embodiment, if the target field obtained by parsing the report query request only includes the first MDX operation field of the static attribute, but does not include the first MDX operation field of the dynamic attribute, then when performing data query through the MDX engine, the operation definition information corresponding to the first MDX operation field of the static attribute is obtained; data model description information is generated according to the operation definition information corresponding to each of the star data models and the first MDX operation field of the static attribute; and an MDX statement is directly generated according to the report query request (without the operation definition information of the first MDX operation field of the dynamic attribute); according to the MDX statement and the data model description information, the query result of the target field is queried from the preset database. In this case, when the MDX engine processes the query request, it refers to the description of the above embodiment when judging whether the cached data model description information (which records the description information of the same star data model) can be used.

[0136] Based on the description of the above embodiment, in one embodiment, if the target field obtained by parsing the report query request only includes the first MDX operation field of the dynamic attribute, but does not include the first MDX operation field of the static attribute, then when performing data query through the MDX engine, the operation definition information corresponding to the first MDX operation field of the dynamic attribute is obtained; data model description information is generated directly according to each of the star data models (but there is no operation definition information corresponding to the first MDX operation field of the static attribute); an MDX statement is generated according to the report query request and the operation definition information of the first MDX operation field of the dynamic attribute; and the query result of the target field is obtained from the preset database according to the MDX statement and the data model description information. In this case, when the MDX engine processes the query request, if it is determined that there are corresponding cached star data models for the several star data models determined by the current query, it is not necessary to regenerate the data model description information, but directly use the cached data model description information as the data model description information of the current query (obviously, it is not necessary to determine whether the cached data model description information contains the operation definition information of the first MDX operation field of the static attribute), thereby speeding up the query efficiency. That is to say, when the target field of the query does not involve the first MDX operation field of the static attribute, it is only necessary to define the first MDX operation field of the dynamic attribute in the MDX statement at most, without defining it in the data model description information. Therefore, the cached data model description information corresponding to the same star data model (recording the description information of the same star data model) can be directly used in this query, thereby achieving fast query.

[0137] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for ordinary technicians in this field, several modifications and improvements can be made without departing from the concept of the present application, and the present application is also intended to include such modifications and modifications.

Claims

1. A data analysis and query method based on an MDX engine, characterized in that: The following steps are involved: Get the user's report query request; Parsing the report query request to obtain a target field for query; determining a basic field according to the target field; wherein the target field includes a first MDX operation field, and the first MDX operation field is bound to at least one basic field; Determine a plurality of star data models containing the basic fields; wherein each of the star data models respectively records a fact table, a dimension table, and the basic fields respectively contained in each data table; Determine, by means of the MDX engine, whether the first MDX operation field is a first MDX operation field of a static attribute or a first MDX operation field of a dynamic attribute according to preset attribute information of the first MDX operation field; obtain operation definition information of the first MDX operation field of a static attribute; obtain operation definition information of the first MDX operation field of a dynamic attribute; generate data model description information according to each of the star data models and the operation definition information of the first MDX operation field of a static attribute; generate an MDX statement according to the report query request and the operation definition information of the first MDX operation field of a dynamic attribute; and obtain a query result of the target field from a preset database according to the MDX statement and the data model description information; The step of generating data model description information according to each of the star data models and the operation definition information of the first MDX operation field of the static attribute comprises the following steps: If it is determined that the plurality of star data models determined by the current query do not have corresponding cached star data models, generating data model description information of the current query according to the operation definition information of each of the star data models and the first MDX operation field of the static attribute; If it is determined that there are corresponding cached star data models for the several star data models determined by the current query, determine whether there is a first MDX operation field for the static attribute of the current query in the corresponding cached data model description information; if it is determined that there is no first MDX operation field for the static attribute of the current query in the cached data model description information, generate the data model description information of the current query according to the operation definition information of each star data model and the first MDX operation field of the static attribute; if it is determined that there are corresponding cached star data models for the several star data models determined by the current query, and the first MDX operation field for the static attribute of the current query exists in the cached data model description information, use the cached data model description information as the data model description information of the current query.

2. The data analysis and query method based on the MDX engine according to claim 1, characterized in that: The first MDX operation field includes a user-defined operation field and a predefined operation field; after the step of parsing the report query request to obtain the query target field, the method further includes: If the analysis of the report query request further obtains a user-defined operation field, a basic field bound to the user-defined operation field, and an MDX operation definition formula, the operation definition information corresponding to the user-defined operation field is determined according to the basic field bound to the user-defined operation field and the MDX operation definition formula; the user-defined operation field, the operation definition information corresponding to the user-defined operation field, and the attribute information of the user-defined operation field are recorded in the operation definition data set as dynamic attributes; If parsing the report query request also obtains a predefined operation field, a basic field bound to the predefined operation field, and a quick query identifier, then according to the predefined operation field, obtain the corresponding first operation definition formula from a preset operation definition formula library, and determine the operation definition information corresponding to the predefined operation field according to the basic field bound to the predefined operation field and the first operation definition formula; record the predefined operation field, the operation definition information corresponding to the predefined operation field, and record the attribute information of the predefined operation field as static attributes in the operation definition data set; wherein, the preset operation definition formula library stores a number of predefined operation fields and corresponding operation definition formulas.

3. The data analysis and query method based on the MDX engine according to claim 2, characterized in that: After the step of parsing the report query request to obtain the target field for query, the method further includes: If parsing the report query request also obtains a predefined operation field and a basic field bound to the predefined operation field, and the quick query identifier bound to the predefined operation field is not parsed, then according to the predefined operation field, the corresponding second operation definition formula is obtained from the operation definition formula library, and the operation definition information corresponding to the predefined operation field is determined according to the basic field bound to the predefined operation field and the second operation definition formula; the predefined operation field, the operation definition information corresponding to the predefined operation field, and the attribute information of the predefined operation field are recorded in the operation definition data set as dynamic attributes.

4. The data analysis and query method based on the MDX engine according to claim 2, characterized in that: The step of determining, according to preset attribute information of the first MDX operation field, whether the first MDX operation field is a first MDX operation field of static attributes or a first MDX operation field of dynamic attributes comprises: Determining, according to the attribute information of the first MDX operation field recorded in the operation definition data set, whether the first MDX operation field is a first MDX operation field of a static attribute or a first MDX operation field of a dynamic attribute; The step of obtaining the operation definition information of the first MDX operation field of the static attribute includes: According to the operation definition data set, obtaining operation definition information of a first MDX operation field of a static attribute; The step of obtaining the operation definition information of the first MDX operation field of the dynamic attribute includes: According to the operation definition data set, operation definition information of a first MDX operation field of a dynamic attribute is obtained.

5. The data analysis and query method based on the MDX engine according to claim 1, characterized in that: The step of generating an MDX statement according to the report query request and the operation definition information of the first MDX operation field of the dynamic attribute includes: Determine the query field of the MDX statement according to the target field; The query definition information of the MDX statement is determined according to the operation definition information of the first MDX operation field of the dynamic attribute.

6. The data analysis and query method based on the MDX engine according to claim 1, characterized in that: Any data table of the star data model does not contain any fields other than the basic fields of the target field; The step of generating data model description information according to each of the star data models and the operation definition information of the first MDX operation field of the static attribute includes: Determine database table description information according to the fact table and dimension table of each of the star data models; Determining metric description information according to the metric fields of each of the star data models; Determining dimension description information according to the dimension fields of each of the star data models; Determine first MDX operation field description information according to the operation definition information of the first MDX operation field of the static attribute; Data model description information is generated according to the database table description information, the metric description information, the dimension description information, and the first MDX operation field description information.

7. The data analysis and query method based on the MDX engine according to claim 6, characterized in that: The step of determining database table description information according to each of the fact tables and dimension tables of the star data model comprises: Determine a corresponding first query data table according to any fact table in the star data model; Determining a corresponding second query data table according to any dimension table in the star data model that is not bound to a query restriction parameter; wherein the query restriction parameter is used to limit the query scope of the user; Determine a corresponding third query data table according to the dimension table bound to the query restriction parameter in any of the star data models; wherein the third query data table is determined according to the dimension table bound to the query restriction parameter and the query restriction parameter bound thereto; Left-joining the first query data table corresponding to any of the star data models with the corresponding second query data tables in sequence, and inner-joining with the corresponding third query data tables in sequence, to obtain a corresponding fourth query data table; Merge and concatenate the fourth query data tables corresponding to the star data models to obtain a fifth query data table; According to the fifth query data table, database table description information is determined.

Images