A method for data query analysis by dynamically constructing a hypercube

Abstract

The application discloses a method for data query analysis by dynamically constructing a hypercube, relates to the technical field of data processing, and can construct a query by combining a conversion SQL mode or a SQL mode with a hypercube mode, complete calculation on a custom field, realize basic data processing, and also can realize row-column conversion and aggregate sorting results through multidimensional analysis by dragging dimensions and metrics, so that the method is simple, convenient and flexible to use; the multidimensional calculation process is carried out in the memory, so that the syntax difference between databases can be shielded; and the method of the application can avoid assembling multiple dimensions by using a Cartesian product mode when assembling an MDX statement, so that the query efficiency is improved.

Description

[0001] The present application is a divisional application of the original application (application number: 201911412349.5) based on the original application (application date: December 31, 2019, application number: 201911412349.5, and invention name: A method for data processing or query by dynamically constructing a cube). TECHNICAL FIELD

[0002] The present application relates to the technical field of data processing, and in particular to a method for data query analysis by dynamically constructing a cube. BACKGROUND

[0003] With the rapid development of society, it becomes increasingly important to analyze the rules and trends implied by data and develop a complete solution to effectively integrate existing data in an enterprise to help the enterprise make wise business decisions. As a basic link of data analysis, constructing a cube poses great challenges to data analysis due to its complexity and flexibility.

[0004] In traditional data analysis systems, the following methods are mainly used to meet the complex, flexible, and personalized data analysis requirements: writing SQL scripts (SQL statements and stored procedures), pre-configuring cubes according to business, and performing multi-dimensional analysis.

[0005] In traditional data analysis systems, the method of writing SQL scripts (stored procedures) requires high skills of the programmers and has syntax differences between databases, which makes it inconvenient to transplant SQL between databases.

[0006] The main disadvantage of pre-configuring cubes according to business is that it is not flexible enough compared to the relational model, and it is very difficult to make relevant changes once the model is constructed. SUMMARY

[0007] The purpose of the present application is to provide a method for data processing or query by dynamically constructing a cube, thereby solving the aforementioned problems in the prior art.

[0008] To achieve the above purpose, the technical solution adopted by the present application is as follows:

[0009] A method for data processing by dynamically constructing a cube, comprising the following steps:

[0010] S1, constructing a model, creating a custom field, and performing data processing on the custom field through a local query syntax, and confirming whether the data processing can be performed through SQL;

[0011] S2, when the new custom field can be processed by SQL, the data is directly processed by the database, otherwise, step S3 is entered;

[0012] S3, a new custom field is used to build a data processing cube;

[0013] S4, data processing is performed on the built data processing cube.

[0014] Preferably, the model built in step S1 specifically includes: building a model through various files, relational databases, other data, and application interfaces;

[0015] Confirming whether the data can be processed by SQL specifically includes: judging whether the custom field has a nested relationship, if there is no nested custom field, only need to judge whether the custom field supports the SQL syntax of the corresponding database; if there is a nested use of other custom fields, it is necessary to judge whether all custom fields can be processed by SQL.

[0016] Preferably, step S2 specifically includes:

[0017] S21, when there is no nested custom field supporting the SQL syntax of the corresponding database, if yes, the calculation is directly performed by the database, otherwise, step S3 is entered;

[0018] S22, when all custom fields in the nested custom field can be processed by SQL, the calculation order of all custom fields is sorted and processed, the nested custom field is preferentially processed, and finally all custom fields are calculated by SQL; otherwise, step S3 is entered.

[0019] Preferably, the dynamic cube built in step S3 specifically includes:

[0020] S31, all the basic fields in the custom field are obtained, and the basic fields are processed to obtain a basic field list;

[0021] S32, the physical table of the model is obtained, and a cube table is built; if there is a filtering condition, the filtering condition is converted into a syntax recognized by the local query language to build a query table of the cube;

[0022] The basic field list is queried for the corresponding basic field data type in the model to build a cube dimension, and a cube measure is built for a numerical type;

[0023] S33, a calculation member is built;

[0024] S34, the entity object built above is used to operate an XML to build a cube.

[0025] Preferably, step S4 specifically comprises:

[0026] S41, constructing a data set by the primary key of the model for a row data set of the MDX statement, and constructing a calculation expression according to the custom field;

[0027] S42, constructing a multi-dimensional query statement according to the dynamic calculation expression and the constructed row data set, and performing multi-dimensional calculation through the cube and the MDX statement;

[0028] S43, obtaining the result of the multi-dimensional calculation and updating the calculation result to the model.

[0029] Another object of the present application is to provide a method for data query analysis by dynamically constructing a cube, comprising the following steps:

[0030] A1, constructing a data query analysis model, newly creating a picture chart, and binding the picture chart with an X-axis, a Y-axis or a Z-axis;

[0031] A2, judging the number of bound axes of the chart, if only 1-2 axes are bound and no dynamic expression is used, directly performing query analysis through a database mode, otherwise entering step A3;

[0032] A3, when a dynamic expression is used or a three-dimensional mode is used for display, dynamically constructing a cube;

[0033] A4, performing multi-dimensional data query analysis according to the constructed cube file.

[0034] Preferably, step A3 specifically comprises:

[0035] A31, obtaining all the basic fields in the bound basic fields and the dynamic expression by binding the basic fields;

[0036] A32, removing the duplicates of all the basic fields obtained in step A31 to obtain a basic field list;

[0037] A33, obtaining model information to construct a cube table, converting the filtering conditions, sorting conditions and display row numbers into a syntax recognizable by a local query language, so as to construct a query view of the cube;

[0038] A34, processing the basic field list in step A32 according to the data type to construct cube dimensions and measures, and obtaining a data query cube.

[0039] Preferably, the binding of the X-axis, the Y-axis and the Z-axis in step A1 specifically comprises: newly creating a dynamic expression or binding through dragging the picture chart;

[0040] Between step A1 and A2, if necessary, the bound picture chart can be filtered or sorted by dragging the model field.

[0041] Preferably, step A4 specifically comprises:

[0042] A41, obtaining the filtering and sorting information of the bound chart, constructing a SQL statement for query, obtaining the query result and further constructing a multidimensional row data set;

[0043] A42, converting and constructing a multidimensional column data set according to the bound data or dynamic expression;

[0044] A43, based on the constructed multidimensional row data set, multidimensional column data set and data query cube,

[0045] querying through the multidimensional engine to obtain the query result;

[0046] A44, converting the query result into a format recognizable by the chart for display, realizing the data analysis result.

[0047] It is worth noting that the innovation point in the application is:

[0048] 1, the constructed custom field, by calculating the nested relationship of the internal fields of the custom field, the calculation priority is determined, and the calculation order is determined.

[0049] 2, the data processing of the model is preferentially calculated through the SQL mode, which is helpful for processing complex and personalized requirements, and when the SQL mode cannot realize the data processing process, the data cube is further established in a dynamic manner, and the calculation is performed through the multidimensional engine.

[0050] 3, when multidimensional analysis is performed through the constructed data query cube, the dimension is sorted through the SQL mode, and the axis of the MDX statement is constructed, and the sorting of this mode supports: the sorting of the aggregation mode, including sorting the query result according to the sum, average value, maximum value, minimum value and original value.

[0051] 4, the constructed data cube can realize the processing of data, including single row processing or cross row processing.

[0052] 5, the MDX tuple and data set are constructed in the combination mode of SQL statement and MDX, so that the data query and processing speed are faster.

[0053] The application has the following beneficial effects:

[0054] The application realizes basic data processing by constructing a query through a user-added custom field in a conversion SQL mode or a combination of a SQL mode and a cube mode to complete calculation of the custom field, and also realizes row-column conversion and aggregation and sorting of results through multi-dimensional analysis by dragging dimensions and metrics, which is simple, convenient and flexible to use; the multi-dimensional calculation process is performed in memory, which can shield syntax differences between databases, and the application avoids assembling MDX statements in a Cartesian product mode by pre-processing data in a SQL mode, thereby improving query efficiency. BRIEF DESCRIPTION OF DRAWINGS

[0055] Figure 1 is a basic cube structure in the prior art;

[0056] Figure 2 is a method flowchart for data processing by dynamically constructing a cube in embodiment 1;

[0057] Figure 3 is a method flowchart for data query analysis by dynamically constructing a cube in embodiment 2. DETAILED DESCRIPTION

[0058] In order to make the objectives, technical solutions and advantages of the application clearer, the application is further described in detail below with reference to the drawings. It should be understood that the specific embodiments described herein are only used to explain the application and do not limit the application.

[0059] Embodiment 1

[0060] The embodiment provides a method for data processing by dynamically constructing a cube, which comprises the following steps:

[0061] S1, constructing a data processing model through a file (Excel / CSV / JSON / XML), a relational database, other data (ES / REDIS / INFLUX), an application interface (WebService, Restful) and the like, newly creating a custom field, and performing data processing on the custom field through a local query syntax and confirming whether the custom field can be processed through a SQL mode.

[0062] If there is no nested custom field, it is only necessary to determine whether the custom field supports the SQL syntax of the corresponding database; if there is a nested custom field, it is necessary to determine whether all the custom fields can be processed through the SQL mode.

[0063] S2, when the new custom field can be processed by SQL, the data is directly processed by the database, otherwise, step S3 is entered;

[0064] S3, a cube is constructed using the new custom field, and the process of constructing the cube specifically includes:

[0065] S31, all basic fields in the custom field are obtained, and the basic fields are processed to obtain a basic field list;

[0066] S32, the physical table of the model is obtained, and the cube table is constructed; if there is a filter condition, the filter condition is converted into a syntax recognized by the local query language, and the query table of the cube is constructed;

[0067] The basic field list is queried for the corresponding basic field data type in the model to construct a cube dimension, and a numerical type is constructed to measure the cube;

[0068] S33, a calculation member is constructed;

[0069] S34, the entity object constructed above is used to operate the XML cube.

[0070] S4, data processing is performed on the constructed cube, specifically including:

[0071] S41, a data set is constructed using the primary key of the model for the row data set of the MDX statement, and a calculation expression is constructed according to the custom field;

[0072] S42, a multidimensional query statement is constructed according to the dynamic calculation expression and the constructed row data set, and multidimensional calculation is performed through the constructed cube and the MDX statement;

[0073] S43, the result of the multidimensional calculation is obtained and the calculation result is updated to the model.

[0074] It is worth noting that whether the data processing in step S1 of the embodiment can be performed by SQL specifically includes: judging whether the custom field has a nested relationship, if there is no nested custom field, only the custom field needs to be judged whether it supports the SQL syntax of the corresponding database; if there is a nested custom field, it needs to be judged whether all custom fields can be processed by SQL.

[0075] Step S2 in the embodiment specifically includes:

[0076] S21, when the nested custom field does not support the SQL syntax of the corresponding database, if it is, the data is directly calculated by the database, otherwise, step S3 is entered;

[0077] S22, when all the custom fields in the nested custom fields can be processed by SQL, the order of all the custom fields is sorted, the nested custom fields are processed preferentially, and finally all the custom fields are calculated by SQL; otherwise, step S3 is entered.

[0078] In the embodiment, two ways are included when constructing the calculation member. Firstly, for the custom field which has been calculated by SQL, if the custom field which has been calculated by SQL is used to construct the cube, according to the type of the custom field, the corresponding data type of the basic field in the query model is directly inquired, the custom field is regarded as a normal field, the string, date, cube dimension, and cube measure of the number type are constructed, so that the calculation member of the cube is directly constructed.

[0079] If the custom field cannot be calculated by SQL, the expression in the custom field is converted into an expression which can be recognized by the cube, and is used as a measure.

[0080] Embodiment 2

[0081] The embodiment provides a method for data query analysis by dynamically constructing a cube, as shown in Figure 3 The method comprises the following steps:

[0082] A1, constructing a data query analysis model, a new picture chart, a new dynamic expression, or binding the X axis, Y axis, and Z axis of the picture chart by dragging, if filtering or sorting is needed, the bound picture chart can be filtered or sorted by dragging the model field;

[0083] A2, judging the number of axes bound by the chart, if only 1-2 axes are bound and no dynamic expression is used, the query analysis is directly performed by local query or corresponding database, otherwise step A3 is entered;

[0084] A3, when a dynamic expression is used or a three-dimensional way is used for display, a data query cube is dynamically constructed, which specifically comprises:

[0085] A31, binding the basic field, acquiring all the basic fields in the bound basic field and the dynamic expression;

[0086] A32, removing the duplicates of all the basic fields acquired in step A31 to acquire a basic field list;

[0087] A33, acquiring model information cube table, converting the filtering condition, sorting condition, and display row number into a syntax which can be recognized by the local query language, so as to construct the query view of the cube.

[0088] A34, using the base field list in step A32, processing by data type, constructing a cube dimension and measure, obtaining a data query cube.

[0089] A4, according to the established data query cube, performing data multi-dimensional query analysis.

[0090] The data multi-dimensional query analysis in step A4 in the embodiment specifically includes:

[0091] A41, obtaining filtering and sorting information of the bound chart, constructing a SQL statement for query, using the obtained query result to construct a multi-dimensional row data set;

[0092] A42, converting according to the bound data or dynamic expression, thereby constructing a multi-dimensional column data set;

[0093] A43, based on the constructed multi-dimensional row data set, multi-dimensional column data set, and data query cube,

[0094] querying through a multi-dimensional engine, thereby obtaining a query result;

[0095] A44, converting the query result into a format recognizable by the chart for display, realizing data analysis results.

[0096] By using the above technical solutions disclosed in the present application, the following beneficial effects are obtained:

[0097] The present application can realize the calculation of the custom field by constructing the query through the conversion of the SQL mode or the combination of the SQL mode and the cube mode, thereby realizing the basic data processing, and the multi-dimensional analysis can be realized by the drag of the dimension and the measure, the row and column conversion and the aggregation sorting result can be realized, the use is simple, convenient and flexible; the multi-dimensional calculation process is performed in the memory, the syntax difference between the databases can be shielded, the data is pre-processed through the SQL mode, the Cartesian product is directly used for assembly in the assembly of the MDX statement, the query efficiency is improved.

[0098] The above only describes the preferred embodiments of the present application, and it should be noted that for those skilled in the art, without departing from the principles of the present application, a number of improvements and refinements can be made, which should be considered as the protection scope of the present application.

Claims

1. A method for data query and analysis by dynamically constructing cubes, characterized in that, Includes the following steps: A1, Build a data query and analysis model, create a new screen chart, and bind the screen chart to the X-axis, Y-axis, and Z-axis; A2. Determine the number of axes bound to the chart. If only 1-2 axes are bound and no dynamic expression is used, perform a query and analysis directly through the database. Otherwise, proceed to step A3. A3, dynamically constructs the cube when using dynamic expressions or displaying it in a three-dimensional manner; A4. Perform multidimensional data query analysis based on the established cube file; Step A3 specifically includes: A31 allows you to bind basic fields, retrieve bound basic fields, and access all basic fields in dynamic expressions. A32, Remove duplicates from all basic fields obtained in step A31 to obtain a list of basic fields; A33: Obtain model information to construct a cube table, and convert the filtering conditions, sorting conditions, and number of rows to be displayed into syntax that the local query language can recognize, thereby constructing a query view of the cube; A34. Using the basic field list from step A32, process the data types to construct the cube dimensions and measures, thus obtaining the data query cube; Step A1, which involves binding the X, Y, and Z axes, specifically includes creating a new dynamic expression or binding them by dragging and dropping the chart on the screen. Between steps A1 and A2, there is also the option to filter or sort the bound screen charts by dragging and dropping model fields if necessary. Step A4 specifically includes: A41: Obtain the filtering and sorting information of the bound chart, construct an SQL statement to perform a query, obtain the query results, and then construct a multidimensional row dataset; A42, transform the calculated members and construct a multidimensional statement column dataset based on the bound data or dynamic expressions; A43, based on the constructed multidimensional row dataset, multidimensional column dataset, and data query cube, uses a multidimensional engine to perform queries and obtain query results; A44 converts query results into a format that charts can recognize for display, enabling data analysis.

Images