Optimization method for index management and index data query

Abstract

The invention discloses an index management and index data query optimization method, and relates to the technical field of index management. The index management and index data query optimization method comprises the following specific steps: acquiring data from a business system to an ODS layer table of a data warehouse through an acquisition system, creating a dimension logic table and a fact logic table, inputting dimension table metadata in an index management system, storing the dimension table metadata in an index dimension table, a dimension field table and a dimension attribute enumeration value table, and performing index management and index data query. The method comprises the following steps: creating a derived index after inputting atomic index metadata in an index management system, processing derived index data from the derived index through coding and configuration scheduling according to the definition of the input derived index, storing the derived index data into a data warehouse DWS layer, querying the index data, merging the derived indexes, and storing the merged derived index data into a database; and then pre-calculation of statistical granularity grouping calculation is carried out. According to the method, the number of the derived indexes is reduced by adjusting the definition of the derived indexes, and the time cost and the maintenance cost for creating the derived indexes are reduced.

Description

Technical Field

[0001] The present invention relates to the technical field of index management, and in particular to an optimization method for index management and index data query. Background Art

[0002] An indicator is a quantitative standard used to measure a certain phenomenon, characteristic or performance. It can reflect information such as the quantity, proportion, trend, rate of change, etc. of a certain phenomenon or characteristic. It is a very important tool in management and decision-making.

[0003] Existing indicator management systems with similar technologies are generally only used to manage data indicators, and are mainly used to solve various data indicator management problems in the process of building the data center, such as data indicators with the same name but different meanings, synonyms but different names, inconsistent indicator calculation logic, etc. The usage query of indicator data is more based on the query of data-derived indicators, and the definition of derived indicators tends to use the complete four parts: atomic indicator + business limitation + statistical period + dimension or combination of dimensions (statistical granularity).

[0004] However, the current indicator management and indicator data query use a complete four-part definition of derived indicators, resulting in a large number of derived indicators and time-consuming creation and entry; the derived indicator data is generated through scheduling in the pre-calculation stage, resulting in a large amount of calculation in the pre-calculation stage; and it cannot meet the upstream system's flexible assembly query requirements for indicator data. Summary of the invention

[0005] In view of the deficiencies of the prior art, the present invention provides an optimization method for indicator management and indicator data query, which solves the problems raised in the above background technology.

[0006] To achieve the above objectives, the present invention is implemented through the following technical solutions: an optimization method for index management and index data query, comprising the following specific steps:

[0007] S1: Collect data from the business system through the data collection system, and after data verification and data cleaning, store it in the ODS layer table of the data warehouse;

[0008] S2: Create dimension logic tables (dim layer of data warehouse) and fact logic tables (dwd layer of data warehouse);

[0009] S3: Based on the dimension table information of the DIM layer of the data warehouse, dimension table metadata is entered in the indicator management system and stored in the indicator dimension table; dimension table field metadata is entered and stored in the dimension field table; dimension attribute enumeration value metadata is entered and stored in the dimension attribute enumeration value table;

[0010] S4: Create derived indicators after entering the metadata of atomic indicators in the indicator management system based on the fact logic table information of the DWD layer of the data warehouse. Only two parts are used to create and enter derived indicators, where derived indicators = statistical period + atomic indicators. The created derived indicator information is stored in the derived indicator table of the indicator management system;

[0011] In S4, the existing derived indicator = statistical period + statistical granularity + atomic indicator, while in this embodiment, the group calculation of the statistical granularity is put into the pre-calculation, making the creation and entry of the derived indicator faster and more flexible;

[0012] S5: According to the definition of the entered derived indicator, the derived indicator is encoded, and the derived indicator data is processed and stored in the data warehouse DWS layer;

[0013] S6: When querying the indicator data, the derived indicators are merged, and then the statistical granularity grouping calculation is pre-calculated, specifically by merging at the sub-business granularity, merging at the indicator granularity, merging at the sub-business and indicator granularity, and setting the filtering logic of the business-limited calculation. The indicator data is dynamically assembled according to the assembled business limitations to obtain SQL, and the secondary processing data of the derived indicators is dynamically queried based on the SQL.

[0014] A further improvement of the technical solution of the present invention is that the data verification and data cleaning in S1 complete the data cleaning, format conversion and calibration of the data collected by the data acquisition system, so that this type of data can be used as the core feature analysis business of the business system.

[0015] A further improvement of the technical solution of the present invention is that: the indicator data query docking target is the upstream system, the upstream system queries the derived indicator table to obtain all derived indicators, the upstream system queries the indicator dimension table to query all dimensions of the indicator, and the upstream system queries the dimension enumeration indicator to query the enumeration value of the dimension.

[0016] A further improvement of the technical solution of the present invention is that the upstream system completes the query of business-defined derived indicator data corresponding to various dimensional attribute enumeration values ​​by customizing the selection of any indicator, the dimension corresponding to any indicator, and the enumeration value corresponding to any dimension.

[0017] A further improvement of the technical solution of the present invention is that the upstream system transmits parameters to dynamically generate business limitations, and obtains indicator data based on dynamic SQL assembly and execution of SQL.

[0018] A further improvement of the technical solution of the present invention is that the pre-calculation of merging derived indicators and grouping calculation of statistical granularity in the query indicator data in S6 is based on the ad hoc query stage, and the pre-calculation includes constructing an indicator calculation request, initiating indicator calculation, obtaining indicator calculation results, and saving data to the DWS layer.

[0019] A further improvement of the technical solution of the present invention is that: the sub-business granularity merging combines the sub-businesses in the indicator data query from one by one to a simultaneous query of all sub-businesses.

[0020] A further improvement of the technical solution of the present invention lies in that: the merging by indicator granularity merges the queries one by one for multiple indicators in the DWS table in the indicator data query into a simultaneous query for all indicators, and the merging by sub-business and indicator granularity merges the queries one by one for multiple indicators of multiple sub-businesses at two angles in the DWS table in the indicator data query into a simultaneous query for multiple indicators of multiple sub-businesses at multiple angles.

[0021] Compared with the prior art, the beneficial effects of the present invention are as follows: by adjusting the definition of derived indicators, the number of derived indicators is reduced, and the time cost and maintenance cost of creating derived indicators are reduced; business restrictions are flexibly assembled according to the parameters transmitted by the upstream system, SQL for obtaining indicator data is dynamically assembled according to the assembled business restrictions, and secondary processing data of derived indicators is dynamically queried according to SQL, and the definition of derived indicators is adjusted, the business restriction calculation and filtering logic in the derived indicators is put into the ad hoc query stage, the business restrictions are dynamically generated by the parameters transmitted by the upstream system, and then dynamic SQL assembly and SQL execution are performed to obtain indicator data to ensure the flexibility of indicator data acquisition;

[0022] It can be applied to integrate the existing indicator management system of the market to improve the flexibility of upstream systems in obtaining indicator data, reduce the number of creation and entry of derived indicators, and reduce the amount of calculation in the pre-calculation stage of derived indicator data generation. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 A flowchart of the optimization method in the optimization method for indicator management and indicator data query;

[0024] Figure 2 It is a schematic diagram of the overall process in the optimization method of indicator management and indicator data query;

[0025] Figure 3 It is a schematic diagram of interaction for obtaining indicator data in an optimization method for indicator management and indicator data query;

[0026] Figure 4 A schematic diagram of derived indicator merging in an optimization method for indicator management and indicator data query;

[0027] Figure 5The invention provides a secondary processing execution diagram for derived indicators in the optimization method of indicator management and indicator data query. DETAILED DESCRIPTION

[0028] Various exemplary embodiments, features and aspects of the present application will be described in detail below with reference to the accompanying drawings. The same reference numerals in the accompanying drawings represent elements with the same or similar functions. Although various aspects of the embodiments are shown in the accompanying drawings, the drawings are not necessarily drawn to scale unless otherwise specified.

[0029] The word “exemplary” is used exclusively herein to mean “serving as an example, example, or illustration.” Any embodiment described herein as “exemplary” is not necessarily to be construed as preferred or advantageous over other embodiments.

[0030] In addition, in order to better illustrate the present application, numerous specific details are provided in the specific embodiments below. It should be understood by those skilled in the art that the present application can also be implemented without certain specific details. In some examples, methods, means, and elements well known to those skilled in the art are not described in detail in order to highlight the subject matter of the present application.

[0031] Reference Figures 1 - 5 :The present invention provides an optimization method for index management and index data query, comprising the following specific steps:

[0032] S1: Collect data from the business system through the data collection system, and after data verification and data cleaning, store it in the ODS layer table of the data warehouse;

[0033] S2: Create dimension logic tables (dim layer of data warehouse) and fact logic tables (dwd layer of data warehouse);

[0034] S3: Based on the dimension table information of the DIM layer of the data warehouse, dimension table metadata is entered in the indicator management system and stored in the indicator dimension table; dimension table field metadata is entered and stored in the dimension field table; dimension attribute enumeration value metadata is entered and stored in the dimension attribute enumeration value table;

[0035] S4: Create derived indicators after entering the metadata of atomic indicators in the indicator management system based on the fact logic table information of the DWD layer of the data warehouse. Only two parts are used to create and enter derived indicators, where derived indicators = statistical period + atomic indicators. The created derived indicator information is stored in the derived indicator table of the indicator management system;

[0036] In S4, the existing derived indicator = statistical period + statistical granularity + atomic indicator, while in this embodiment, the group calculation of the statistical granularity is put into the pre-calculation, so that the creation and entry of the derived indicator is faster and more flexible.

[0037] S5: According to the definition of the entered derived indicator, the derived indicator is encoded, and the derived indicator data is processed and stored in the data warehouse DWS layer;

[0038] S6: When querying the indicator data, the derived indicators are merged, and then the statistical granularity grouping calculation is pre-calculated, specifically by merging at the sub-business granularity, merging at the indicator granularity, merging at the sub-business and indicator granularity, and setting the filtering logic of the business-limited calculation. The indicator data is dynamically assembled according to the assembled business limitations to obtain SQL, and the secondary processing data of the derived indicators is dynamically queried based on the SQL.

[0039] The data verification and data cleaning in S1 complete the data cleaning, format conversion and calibration of the data collected by the data acquisition system, so that this type of data can be used as the core feature analysis business of the business system.

[0040] The indicator data query docking target is the upstream system. The upstream system can query the derived indicator table to obtain all derived indicators. The upstream system can query the indicator dimension table to query all dimensions of the indicator. The upstream system can query the dimension enumeration indicator to query the enumeration value of the dimension.

[0041] The upstream system completes the query of business-defined derived indicator data corresponding to various dimensional attribute enumeration values ​​by customizing the selection of any indicator, the dimension corresponding to any indicator, and the enumeration value corresponding to any dimension.

[0042] The upstream system passes parameters to dynamically generate business limits, assembles and executes SQL based on dynamic SQL to obtain indicator data.

[0043] The pre-calculation of merging derived indicators and grouping calculation of statistical granularity in the query indicator data in S6 is based on the ad hoc query stage. The pre-calculation includes constructing an indicator calculation request, initiating indicator calculation, obtaining indicator calculation results, and saving data to the DWS layer.

[0044] The sub-business granularity merging combines the sub-businesses in the index data query from querying them one by one to querying all the sub-businesses simultaneously.

[0045] The merging by indicator granularity is to merge the queries one by one for multiple indicators in the DWS table in the indicator data query into simultaneous queries for all indicators. The merging by sub-business and indicator granularity is to merge the queries one by one for multiple indicators of multiple sub-businesses at two angles in the DWS table in the indicator data query into simultaneous queries for multiple indicators of multiple sub-businesses at multiple angles.

[0046] On the basis of the above, it is assumed that the derived indicators to be counted and the definitions according to the original general scheme are as follows:

[0047] Indicator name definition

[0048]

[0049]

[0050] According to the commonly used solution, the decomposition of derived indicators requires the decomposition of business limitations. However, according to the method provided by this solution, the above table only needs to define one derived indicator, which is the daily inbound volume of each site in the past 30 days. The decomposition definition is:

[0051] Statistical period: nearly 30 days;

[0052] Statistical granularity: daily, site;

[0053] Atomic indicators: station entry volume;

[0054] Then, the business restrictions are placed in the query interface. In the interface, all business restrictions can be adapted to the derived indicators of the site = site parameters by querying according to the site parameters passed in the page, which greatly reduces the number of derived indicators. At the same time, the business restrictions are dynamically passed into the interface as parameters to perform SQL where splicing for filtering and calculation, which reduces the amount of tasks and calculations in the derived indicator pre-calculation stage;

[0055] The configuration file code required for the SQL where splicing mentioned above is as follows:

[0056] <select id＝"searchnumber"resultmap＝"numbermap">

[0057] select member_id,member_nick,member_gender,member_age,member_Number

[0058] from members

[0059]

[0060] <if test="gender!=null">

[0061] and member_gender=#{gender}

[0062]

[0063] <if test="minAge!=null">

[0064] and member_age>;=#{minAge}

[0065]

[0066] <if test="maxAge!=null">

[0067] and member_age<;=#{minAge}

[0068]

[0069] <if test="number[A-Z]!=null">

[0070]

[0071]

[0072] It should be noted that there seems to be an error in the original text where "and member_age<;=#{minAge}" in line 67 should probably be "and member_age<=#{maxAge}". This might affect the correct understanding and translation of the SQL - like code.< / select>

[0073] In the above, the upstream system passes parameters to dynamically generate business restrictions. Before obtaining indicator data based on dynamic SQL assembly and SQL execution, it is necessary to merge the derived indicators and pre-calculate the group calculation of statistical granularity, merge by sub-business granularity, merge by indicator granularity, merge by sub-business and indicator granularity, and set the filtering logic of business restriction calculation;

[0074] Assume that in this embodiment, there are 100 sub-services, and the pre-calculation of the service granularity in the traditional indicator pre-calculation is as follows:

[0075] Select indicator from DWS where sub-business = "1";

[0076] Select indicator from DWS where sub-business = "2";

[0077] Select indicator from DWS where sub-business = "3"; ...

[0079] Select indicator from DWS where sub-business = "100";

[0080] After the derived indicators are merged, the pre-calculation of the indicator values ​​is merged according to the business granularity as follows:

[0081] Select indicator, sub-business from DWS where group by sub-business;

[0082] Assume that in this embodiment, there are 100 sub-services, and in the traditional indicator pre-calculation, the indicator granularity pre-calculation is as follows:

[0083] Select count(column1)as measure1 from DWS where sub-business='A';

[0084] Select sum(column2)as measure2 from DWS where sub-business='A';

[0085] Select avg(column3)as measure3 from DWS where sub-business='A'; ...

[0087] Select avg(column100)as measure100 from DWS where sub-business='A';

[0088] After the derived indicators are merged, the precalculation of the indicator values ​​is combined and precalculated according to the indicator granularity as follows:

[0089] Select count as measure1, sum(column2) as measure2, avg(column3) as measure3, sub-business from DWS where sub-business = 'A' group by sub-business;

[0090] Assume that in this embodiment, there are 100 sub-services, and in the traditional indicator pre-calculation, the sub-services and indicator granularity are pre-calculated as follows:

[0091] Select count(column1)as measure1 from DWS where sub-business='A';

[0092] Select sum(column2)as measure2 from DWS where sub-business='B';

[0093] Select avg(column3)as measure3 from DWS where sub-business='C';

[0094] After the derived indicators are merged, the indicator value is pre-calculated based on the sub-business and indicator granularity as follows:

[0095] Select count(column1) as measure1, sum(column2) as measure2, avg(column3) as measure 3, sub-business from DWS group by sub-business.

[0096] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device.

[0097] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. An optimization method for index management and index data query, characterized in that: The specific steps include: S1: Collect data from the business system through the data collection system, and after data verification and data cleaning, store it in the ODS layer table of the data warehouse; S2: Create dimension logic table and fact logic table; S3: Based on the dimension table information of the DIM layer of the data warehouse, the dimension table metadata is entered in the indicator management system and stored in the indicator dimension table. The dimension table field metadata is entered and stored in the dimension field table. The dimension attribute enumeration value metadata is entered and stored in the dimension attribute enumeration value table. S4: Create derived indicators after entering the metadata of atomic indicators in the indicator management system based on the fact logic table information of the DWD layer of the data warehouse. Only two parts are used to create and enter derived indicators, where derived indicators = statistical period + atomic indicators. The created derived indicator information is stored in the derived indicator table of the indicator management system; S5: According to the definition of the entered derived indicator, the derived indicator is encoded, and the derived indicator data is processed and stored in the data warehouse DWS layer; S6: When querying the indicator data, the derived indicators are merged, and then the statistical granularity grouping calculation is pre-calculated, specifically by merging at the sub-business granularity, merging at the indicator granularity, merging at the sub-business and indicator granularity, and setting the filtering logic of the business-limited calculation. The indicator data is dynamically assembled according to the assembled business limitations to obtain SQL, and the secondary processing data of the derived indicators is dynamically queried based on the SQL.

2. The method for optimizing index management and index data query according to claim 1, characterized in that: The data verification and data cleaning in S1 complete the data cleaning, format conversion and calibration of the data collected by the data acquisition system, so that this type of data can be used as the core feature analysis business of the business system.

3. The method for optimizing index management and index data query according to claim 1, characterized in that: The indicator data query docking target is the upstream system. The upstream system can query the derived indicator table to obtain all derived indicators. The upstream system can query the indicator dimension table to query all dimensions of the indicator. The upstream system can query the dimension enumeration indicator to query the enumeration value of the dimension.

4. The method for optimizing index management and index data query according to claim 2, characterized in that: The upstream system completes the query of business-defined derived indicator data corresponding to various dimensional attribute enumeration values ​​by customizing the selection of any indicator, the dimension corresponding to any indicator, and the enumeration value corresponding to any dimension.

5. The method for optimizing index management and index data query according to claim 2, characterized in that: The upstream system passes parameters to dynamically generate business limits, assembles and executes SQL based on dynamic SQL to obtain indicator data.

6. The method for optimizing index management and index data query according to claim 1, characterized in that: The pre-calculation of merging derived indicators and grouping calculation of statistical granularity in the query indicator data in S6 is based on the ad hoc query stage. The pre-calculation includes constructing an indicator calculation request, initiating indicator calculation, obtaining indicator calculation results, and saving data to the DWS layer.

7. The method for optimizing index management and index data query according to claim 1, characterized in that: The sub-business granularity merging combines the sub-businesses in the index data query from querying them one by one to querying all the sub-businesses simultaneously.

8. The method for optimizing index management and index data query according to claim 7, characterized in that: The merging by indicator granularity is to merge the queries one by one for multiple indicators in the DWS table in the indicator data query into simultaneous queries for all indicators. The merging by sub-business and indicator granularity is to merge the queries one by one for multiple indicators of multiple sub-businesses at two angles in the DWS table in the indicator data query into simultaneous queries for multiple indicators of multiple sub-businesses at multiple angles.

Images