An index calculation method and device based on a hierarchical index system

Abstract

The present application relates to the technical field of data warehouse index calculation, and proposes an index calculation method and device based on a hierarchical index system, comprising: acquiring external data sources collected to an ODS layer, connecting fact tables and dimension tables into wide tables and saving to a DWD layer; based on the DWD layer, constructing atomic indexes, derivative indexes and composite indexes, integrating to form index aggregation; performing index aggregation through a data acquisition task, loading the calculation result to a target table, constructing an index API to return a query result; estimating the performance quantitative value of the index aggregation, when exceeding an optimization decision threshold, tracing back to the upstream to obtain dependent indexes of each level; based on the dependent indexes, constructing corresponding materialized views, and using database transparent rewriting technology to redirect the query request to the materialized view. The present application improves the execution efficiency and system response performance of index calculation, and realizes intelligent, automatic and high-performance calculation processing of a large-scale index system.

Description

Technical Field

[0001] This invention relates to the field of data warehouse indicator calculation technology, and in particular to an indicator calculation method and apparatus based on a hierarchical indicator system. Background Technology

[0002] In modern industrial manufacturing and production management, enterprise data warehouse systems need to process massive amounts of industrial data from various data sources, such as production equipment, sensor networks, manufacturing execution systems, and enterprise resource planning systems, and construct a hierarchical indicator system covering business areas such as production efficiency, quality control, equipment operation, and cost analysis. A hierarchical indicator system is a data computing architecture that organizes business indicators into layers according to atomic, derived, and composite indicators. By constructing standardized indicator definitions, unified calculation logic, and reusable indicator combinations, it achieves efficient processing and multi-dimensional analysis of massive amounts of industrial data.

[0003] However, the existing indicator system lacks a standardized and reusable architecture design. When business departments build new indicators, they often repeatedly develop similar calculation logic, resulting in inconsistent indicator definitions and differences in calculation results. At the same time, the system is difficult to cope with the indicator expansion requirements when business needs change, and adding new business scenarios requires a lot of development work to build indicator calculation modules. Secondly, the existing indicator system faces serious computing performance bottlenecks: real-time monitoring of production lines requires key indicator calculations with a response time of seconds, but complex multi-table associations and large-scale data aggregation often result in query times reaching minutes; equipment fault warning indicators involve the composite calculation of tens of millions of historical sensor data, and excessive system resource consumption leads to frequent server crashes; the indicator aggregation query of quality analysis reports cannot be completed in a timely manner during peak production periods, affecting the real-time nature of production decisions and quality control.

[0004] In existing technologies, hierarchical indicator calculation mainly adopts traditional ETL data processing and basic SQL query optimization methods, improving query performance by pre-calculating some indicator results or establishing simple data caching. However, existing methods lack both a systematic and reusable indicator architecture design, which cannot effectively support the standardized management and flexible expansion of business indicators, and automated performance monitoring and intelligent optimization capabilities. They also cannot automatically identify bottlenecks and build targeted materialized view optimization schemes based on the actual execution performance of indicator aggregation queries in industrial production environments. Summary of the Invention

[0005] In view of this, the present invention proposes an indicator calculation method and device based on a hierarchical indicator system, which solves the problems of existing methods that lack a systematic and reusable indicator architecture design, cannot effectively support the standardized management and flexible expansion of business indicators, lack automated performance monitoring and intelligent optimization capabilities, and cannot automatically identify bottlenecks and build targeted materialized view optimization schemes based on the actual execution performance of indicator aggregation queries in industrial production environments.

[0006] The technical solution of this invention is implemented as follows: On the one hand, this invention provides an indicator calculation method based on a hierarchical indicator system, including the following steps: Obtain data tables or data files from external data sources, collect the data tables or data files into the data warehouse ODS layer, connect the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table and save it to the data warehouse DWD layer. Based on the data tables of the DWD layer of the data warehouse, atomic indicators, derived indicators, and composite indicators are constructed. Multiple atomic indicators, derived indicators, and composite indicators with the same dimension are integrated to obtain indicator aggregation. Construct a data retrieval task and a metrics API. Perform metrics aggregation through the data retrieval task to obtain calculation results. Load the calculation results into the target table. Perform metrics aggregation through the metrics API to return query results. Based on the execution plan of the target table and query results, the performance quantification value of the aggregated indicators is estimated. When the performance quantification value is greater than the preset optimization decision threshold, the dependent atomic indicators, derived indicators and composite indicators are obtained by tracing upstream according to the aggregated indicators. Materialized views are constructed based on atomic, derived, and composite metrics, and query requests are redirected to these materialized views using database transparent rewriting techniques.

[0007] In some embodiments, the estimation of the performance quantification value of the aggregated metrics based on the execution plan of the target table and the query results includes: Key performance parameters are extracted from the execution plan of the target table and query results, and a performance quantification value is calculated based on the key performance parameters. The key performance parameters include the number of rows scanned, access type, join method, and aggregation operation. The performance quantification value is compared with a preset optimization decision threshold. When the performance quantification value is greater than the optimization decision threshold, an upstream tracing process is triggered. The upstream tracing process includes: scanning the dependency table of the indicator system, obtaining the upstream and downstream dependencies of indicators at each level, tracing the composite indicators, derived indicators and atomic indicators that the indicator aggregation depends on layer by layer from the indicator aggregation, and obtaining a complete list of dependent indicators through recursive traversal.

[0008] In some embodiments, the dependency table of the scanning indicator system includes the dependency relationship between reading indicator aggregation and composite indicators, the dependency relationship between composite indicators and derived indicators, and the dependency relationship between derived indicators and atomic indicators. The recursive traversal method includes starting from the current indicator node and visiting the upstream indicator nodes that the current indicator node directly depends on, and repeating the same traversal process for each upstream indicator node until the atomic indicator layer is reached. An initial graph structure for dependencies is constructed using the adjacency list method. The initial graph structure is then topologically sorted to verify the dependency cycle. The in-degree of all nodes is calculated, and nodes with an in-degree of zero are added to a queue. Nodes in the queue are then retrieved sequentially, and the in-degree of downstream nodes is decremented by one for each outgoing edge. If there are any untraversed nodes, the dependency cycle is determined.

[0009] In some embodiments, obtaining a complete list of dependency metrics through recursive traversal includes: Starting from the indicator aggregation node, trace upwards layer by layer in the order of composite indicator layer, derived indicator layer, and atomic indicator layer, extract the upstream indicator nodes that the indicator nodes at each level directly depend on, record the dependency path and level depth of each level of indicator, and obtain the complete dependency chain from indicator aggregation to basic data source.

[0010] In some embodiments, the construction of corresponding materialized views based on dependent atomic metrics, derived metrics, and composite metrics includes: Using the data tables of the DWD layer of the data warehouse, the DWD layer data tables for atomic metric calculation, and the SQL for calculating atomic or derived metrics as the base tables for the materialized views of atomic metrics, derived metrics, and composite metrics, respectively, aggregate operations, aggregation operations, and arithmetic operations are performed on the metric fields to obtain the corresponding materialized views. Data acquisition tasks, data processing tasks, atomic indicator materialized view refresh tasks, derived indicator materialized view refresh tasks, composite indicator materialized view refresh tasks, and indicator polymerized view refresh tasks are used as task nodes. Directed edges are constructed based on upstream and downstream dependencies to build a directed acyclic graph workflow. The scheduling module executes each task node in the topological sorting order. After generating the materialized view, the calculation logic of atomic indicators, derived indicators, and composite indicators in the original query request is replaced with direct access to the corresponding materialized view.

[0011] In some embodiments, the workflow for constructing a directed acyclic graph includes: Scan the dependency table of the indicator system to obtain the upstream and downstream dependencies of indicators at each level, map the refresh requirements of indicators at different levels to the corresponding task nodes, and record the indicator identifier, the upstream node identifier it depends on, and the task execution priority for each task node. Using all task nodes as vertices of the graph, and upstream and downstream dependencies as directed edges of the graph, construct the initial graph structure, and label each directed edge with dependency type and triggering condition. The initial graph structure is topologically sorted for verification. The in-degree of all nodes is calculated, and nodes with an in-degree of zero are added to the queue. Nodes in the queue are taken out one by one and all outgoing edges of the nodes are traversed. The in-degree of downstream nodes is decremented by one. If the in-degree of a downstream node becomes zero, it is added to the queue. When all nodes have been traversed, the verification is successful, and a directed acyclic graph workflow is generated.

[0012] In some embodiments, the task execution priority is arranged in the order of data acquisition, data processing, atomic indicator refresh, derived indicator refresh, composite indicator refresh, and indicator aggregation refresh; When the indicator system changes, an incremental update mechanism is adopted. The change log of the indicator system dependency table is monitored to locate the changed indicator identifier and the corresponding task node. Only the original edges of the task node corresponding to the changed indicator identifier and the downstream direct dependent nodes of the corresponding task node are deleted. The dependency relationship of the changed node is re-collected to generate new edges. The topological sorting verification is re-executed on the local subgraph involved in the change. After the verification is passed, the local subgraph is integrated into the original directed acyclic graph workflow. The database transparent rewriting technology uses a database query optimizer to identify the indicator calculation logic involved in the query statement, determine whether there is a corresponding materialized view, and replace the indicator calculation part in the original query statement with a materialized view access statement.

[0013] In some embodiments, the step of acquiring data tables or data files from an external data source, collecting the data tables or data files into the data warehouse ODS layer, joining the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table, and saving it to the data warehouse DWD layer includes: Obtain data tables or data files from external data sources and collect them into the data warehouse ODS layer; Join operations are performed on the fact table and dimension table in the data warehouse ODS layer to form a wide table. Data cleaning and dimension redundancy processing are performed on the wide table. The processed wide table is then saved to the data warehouse DWD layer, and the table fields in the data warehouse DWD layer are marked as either measures or dimensions.

[0014] In some embodiments, the construction of atomic indicators, derived indicators, and composite indicators based on data tables in the data warehouse DWD layer, and the integration of multiple atomic indicators, derived indicators, and composite indicators of the same dimension to obtain indicator aggregation, includes: Based on the data tables in the DWD layer of the data warehouse, atomic indicator calculation logic, derived indicator calculation logic, and composite indicator calculation logic are constructed respectively, and the dependencies between each indicator are recorded. Multiple atomic, derived, and composite indicators of the same dimension are integrated through join operations to form indicator aggregation, and the atomic, derived, and composite indicators that the indicator aggregation depends on are recorded.

[0015] On the other hand, the present invention also provides an indicator calculation device based on a hierarchical indicator system, the device comprising: The data acquisition and processing module is used to acquire data tables or data files from external data sources, collect the data tables or data files into the data warehouse ODS layer, connect the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table and save it to the data warehouse DWD layer. The indicator system construction module is used to construct atomic indicators, derived indicators, and composite indicators based on the data tables of the data warehouse DWD layer, and to integrate multiple atomic indicators, derived indicators, and composite indicators with the same dimension to obtain indicator aggregation. The indicator calculation service module is used to construct data retrieval tasks and indicator APIs. The data retrieval tasks are used to perform indicator aggregation to obtain calculation results, load the calculation results into the target table, and the indicator API is used to perform indicator aggregation and return query results. The performance evaluation decision module is used to estimate the performance quantification value of the aggregated indicators based on the execution plan of the target table and query results. When the performance quantification value is greater than the preset optimization decision threshold, the module traces upstream according to the aggregated indicators to obtain the dependent atomic indicators, derived indicators and composite indicators. The materialized view optimization module is used to construct corresponding materialized views based on dependent atomic indicators, derived indicators, and composite indicators, and to redirect query requests to materialized views using database transparent rewriting technology.

[0016] The index calculation method and apparatus based on a hierarchical index system of the present invention have the following advantages over the prior art: (1) This application constructs a hierarchical index system architecture, realizes a hierarchical calculation process from atomic indexes to index aggregation, establishes a reusable index combination and an extensible business architecture, and effectively solves the problems of standardized management and efficient calculation of complex index systems; through a systematic dependency management mechanism, it ensures the logical consistency of inheritance and combination between indexes, and significantly improves the flexibility and extensibility of the index system; through an automated performance evaluation and intelligent optimization decision mechanism, when the performance quantification value of index aggregation exceeds the preset threshold, it automatically triggers dependency tracing, accurately locates the performance bottleneck and constructs the corresponding materialized view, and uses database transparent rewriting technology to realize query redirection, thereby significantly improving the execution efficiency of index calculation and system response performance, and realizing automated and high-performance calculation processing of large-scale index systems; (2) This application automatically extracts key performance parameters from the execution plan of the target table and query results and calculates the performance quantification value. Combined with the preset optimization decision threshold, it performs intelligent performance evaluation and judgment. When the performance quantification value exceeds the threshold, it automatically triggers the upstream tracing process. By scanning the dependency relationship table of the indicator system and recursively traversing, it traces upward from the indicator aggregation layer by layer to the composite indicator, derived indicator and atomic indicator. It uses the adjacency list method to construct the dependency relationship graph structure and uses topological sorting to verify that the cyclic dependency problem is avoided. Finally, it obtains the complete dependency chain from the indicator aggregation to the basic data source, thereby realizing the automated monitoring and evaluation of indicator calculation performance and intelligent dependency analysis. It provides an accurate list of dependency indicators for materialized view optimization and significantly improves the performance optimization efficiency and accuracy of the hierarchical indicator system. (3) This application constructs materialized views by hierarchically building the indicators based on dependencies. The data warehouse DWD layer data table, atomic indicator calculation results, and derived indicator calculation SQL are used as the base tables for materialized views at different levels. Aggregation and arithmetic operations are performed to generate corresponding materialized views. Then, various data processing tasks and materialized view refresh tasks are used as nodes, and upstream and downstream dependencies are used as directed edges to build a directed acyclic graph workflow. The intelligent execution of task nodes is achieved through topological sorting verification and priority scheduling mechanism. Combined with incremental update mechanism and database transparent rewriting technology, the indicator calculation logic in the original query request is automatically replaced with direct access to the materialized view. Thus, the complex dependencies in the hierarchical indicator system are captured through systematic materialized view construction and intelligent workflow scheduling, realizing full-link query performance optimization from the underlying data source to the upper-level indicator aggregation. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0018] Figure 1 This is a flowchart of an indicator calculation method based on a hierarchical indicator system according to the present invention; Figure 2 This is a flowchart of the sub-steps of step S1 in an embodiment of the present invention; Figure 3 This is a flowchart of the sub-steps of step S2 in an embodiment of the present invention; Figure 4 This is a flowchart of the sub-steps of step S3 in an embodiment of the present invention. Detailed Implementation

[0019] The technical solutions of the present invention will be clearly and completely described below with reference to the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention.

[0020] Currently, the calculation of hierarchical indicator systems in enterprise data warehouses often relies heavily on manual configuration and experience-based tuning to determine indicator calculation strategies. Significant performance differences exist in indicator aggregation across different business scenarios, and the optimization strategy for each indicator combination requires multiple tests and verifications, which is time-consuming and costly, resulting in long tuning cycles and poor system performance stability. Existing experience-based models suffer from a lack of automated optimization capabilities, insufficient accuracy, and difficulty in accurately modeling the mapping relationship between complex indicator dependencies and query performance for complex indicator aggregations.

[0021] In related technologies, rule-based performance optimization systems cannot effectively handle the nonlinear performance relationships of multi-dimensional index aggregation, leading to optimization results that deviate from actual needs. Furthermore, traditional algorithms have limited responsiveness to dynamic query load changes, making it difficult to meet the high-precision performance requirements of real-time data analysis for index calculation.

[0022] In some embodiments, please refer to Figure 1 This invention provides a method for calculating indicators based on a hierarchical indicator system, comprising the following steps: S1: Obtain data tables or data files from external data sources, collect the data tables or data files into the data warehouse ODS layer, connect the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table and save it to the data warehouse DWD layer.

[0023] It is understandable that step S1 establishes connections with external relational databases, non-relational databases, and data files to achieve standardized collection and hierarchical storage of raw data. The data collection unit scans and copies the content of external data sources to the data warehouse ODS layer, maintaining the original format. Subsequently, SQL join operations are performed to form wide tables from the fact tables and dimension tables in the data warehouse ODS layer. After data cleaning and dimension redundancy processing, these tables are stored in the data warehouse DWD layer, providing a standardized data foundation for indicator construction.

[0024] S2 constructs atomic indicators, derived indicators, and composite indicators based on the data tables of the DWD layer of the data warehouse, and integrates multiple atomic indicators, derived indicators, and composite indicators with the same dimension to obtain indicator aggregation.

[0025] Step S2 constructs a three-layer indicator system architecture based on the data tables of the Data Warehouse DWD layer. Atomic indicators form the basic calculation logic by selecting dimensions and measure fields from the base table and defining aggregation methods; derived indicators select some dimensions from atomic indicators as new dimensions and reuse the measure fields of the atomic indicators; composite indicators perform arithmetic operations based on atomic and derived indicators. Finally, various indicators with the same dimensions are integrated into indicator aggregates through join operations, and dependencies are recorded for optimization decisions.

[0026] S3, construct a data retrieval task and a metric API, perform metric aggregation through the data retrieval task to obtain the calculation results, load the calculation results into the target table, and perform metric aggregation through the metric API to return the query results.

[0027] Step S3 establishes a dual-mode metric calculation service architecture. The data retrieval task establishes a mapping relationship between metrics and target table fields, sets up a scheduled mechanism, executes metric aggregation SQL, and loads the calculation results into a predefined target table, achieving periodic updates of batch data. The metric API provides a real-time query interface based on a REST architecture. After receiving query requests, it executes metric aggregation calculations and returns results via HTTP responses, meeting real-time analysis needs.

[0028] S4. Based on the execution plan of the target table and query results, estimate the performance quantification value of the aggregated indicators. When the performance quantification value is greater than the preset optimization decision threshold, trace upstream according to the aggregated indicators to obtain the dependent atomic indicators, derived indicators and composite indicators.

[0029] Step S4 implements an intelligent performance evaluation and optimization decision-making mechanism. By analyzing the data characteristics of the target table and the execution plan of the query results, it calculates the performance quantification value of aggregated indicators, including key indicators such as query time and resource consumption. When the performance quantification value exceeds a preset threshold, it triggers an automatic optimization process, tracing upstream according to pre-recorded dependencies to identify the atomic indicators, derived indicators, and combinations of composite indicators that need optimization.

[0030] S5 constructs corresponding materialized views based on dependent atomic indicators, derived indicators, and composite indicators, and uses database transparent rewriting technology to redirect query requests to the materialized views.

[0031] Step S5 constructs materialized view optimization schemes for the identified performance bottleneck metrics. Based on the dependent atomic, derived, and composite metrics, corresponding materialized views are created, and intermediate results are pre-calculated and stored. Utilizing database transparent rewriting technology, the original query is automatically redirected to the corresponding materialized view during query execution, significantly reducing query response time and computational resource consumption.

[0032] This embodiment realizes a complete closed loop from data collection, indicator construction, computing services to performance optimization, and establishes an automated indicator performance evaluation and optimization decision-making mechanism. It effectively solves the performance bottleneck problem of traditional methods in complex indicator aggregation scenarios and significantly improves the computing efficiency and query response performance of the hierarchical indicator system.

[0033] In some embodiments, based on the above embodiments, such as Figure 2 As shown, step S1 includes: S11: Obtain data tables or data files from external data sources and collect them into the data warehouse ODS layer.

[0034] For example, a connection is established with an external data source, which includes a relational database, a non-relational database, and a data file; the table structure and data content of the data tables or data files in the external data source are scanned by the data acquisition unit; the data tables or data files in the external data source are copied to the data warehouse ODS layer in their original format, keeping the original table structure, field types, and data format of the data tables unchanged; a unique identifier is assigned to each acquired data table or data file, and metadata information such as data source, acquisition time, and data volume is recorded.

[0035] In this embodiment, multi-source data connection technology enables unified access to heterogeneous data sources, ensuring the integrity and traceability of the data acquisition process. The data acquisition unit employs a batch scanning mechanism to improve data discovery and replication efficiency, while metadata records ensure the accurate maintenance of data inheritance relationships.

[0036] S12: Perform join operations on the fact table and dimension table in the data warehouse ODS layer to form a wide table. Perform data cleaning and dimension redundancy processing on the wide table. Save the processed wide table to the data warehouse DWD layer and mark the table field type of the data warehouse DWD layer as either a measure or a dimension.

[0037] For example, the join logic between fact tables and dimension tables in the ODS layer of the data warehouse is defined using SQL statements. Join operations are performed to form a wide table, which includes fact data and dimension attributes. Data cleaning is then performed on the wide table to obtain standardized data. This data cleaning includes deduplication, null value handling, outlier filtering and correction, and format conversion. Dimension redundancy processing is then performed on the standardized data, redundanting dimension fields into the DWD layer tables of the data warehouse to reduce dimension table join operations during subsequent queries. Each field in the DWD layer tables is labeled with a type, classifying the fields into measure fields and dimension fields. The processed wide table is then saved to the DWD layer of the data warehouse.

[0038] This embodiment uses a star schema table join strategy to integrate scattered factual and dimensional data into a unified wide table structure. Dimension redundancy handling significantly reduces the complexity of table joins during indicator calculation, and field type marking provides a clear data semantic foundation for indicator system construction, effectively supporting the automated construction process of hierarchical indicators.

[0039] Through data collection and processing, this embodiment establishes a complete transformation link from raw heterogeneous data to standardized hierarchical data, laying a high-quality data foundation for the construction of the indicator system. At the same time, through optimization strategies such as dimensional redundancy and field labeling, the data access efficiency and indicator calculation performance are significantly improved.

[0040] In some embodiments, based on the above embodiments, such as Figure 3 As shown, step S2 includes: S21. Based on the data tables in the DWD layer of the data warehouse, construct atomic indicator calculation logic, derived indicator calculation logic, and composite indicator calculation logic respectively, and record the dependency relationships between each indicator.

[0041] For example, using a data table in the DWD layer of the data warehouse as the base table, the dimensions and measure fields of the base table are selected according to the business scope, the aggregation method of the measure fields is defined, and the atomic indicator calculation logic is constructed through SQL statements. The atomic indicator calculation logic includes joinless operation, aggregation operation, projection operation, and selection operation. Using atomic indicators as the data source, some dimensions are selected from the dimension range of atomic indicators as derived indicator dimensions, and the measure fields of atomic indicators are used as the measure fields of derived indicators. The derived indicator calculation logic is constructed through SQL statements. The derived indicator calculation logic includes joinless operation, grouping operation, aggregation operation, projection operation, and selection operation, and the atomic indicators that the derived indicators depend on are recorded. Using atomic indicators and derived indicators as the data source, arithmetic operation expressions based on atomic indicators and derived indicators are calculated according to the business scope. The composite indicator calculation logic is constructed through SQL statements. The composite indicator calculation logic includes join operation, grouping operation, no aggregation operation, projection operation, and no selection operation, and the atomic indicators and derived indicators that the composite indicators depend on are recorded.

[0042] In this embodiment, a hierarchical indicator construction strategy is adopted to achieve a progressive construction from basic atomic indicators to complex composite indicators. Clearly defined SQL operation characteristics ensure the standardization and maintainability of indicator calculation logic, and dependency records provide a precise traceability basis for performance optimization.

[0043] S22 integrates multiple atomic indicators, derived indicators, and composite indicators of the same dimension through a join operation to form an indicator aggregation, and records the atomic indicators, derived indicators, and composite indicators that the indicator aggregation depends on.

[0044] For example, multiple atomic, derived, and composite metrics with the same dimension are selected, and the SQL for calculating each metric is used as a subquery. Join operations are performed according to the metric dimension. The measure value and dimension of the subquery are used as projection columns, and the metric dimension is used as the calculation filter condition. The metric aggregation calculation logic is constructed through SQL statements. The metric aggregation calculation logic includes join operations, no grouping operations, no aggregation operations, projection operations, and selection operations. The atomic, derived, and composite metrics that the metric aggregation depends on are recorded, and a materialized view is constructed through the recorded dependencies.

[0045] In this embodiment, a higher-order indicator aggregation calculation logic is formed through a connection and integration mechanism of indicators of the same dimension. The subquery join strategy effectively solves the problem of unified calculation of indicators at different levels, and the dependency record ensures the complete correlation between indicator aggregation and underlying indicators, providing reliable data support for intelligent performance optimization decisions.

[0046] Through the hierarchical indicator system construction process, this embodiment establishes a complete indicator calculation architecture from atomic indicators to indicator aggregation, realizing the standardized definition and reusable combination of business indicators. At the same time, through systematic dependency management, it lays a technical foundation for automated performance evaluation and materialized view optimization, significantly improving the flexibility and scalability of indicator calculation.

[0047] In some embodiments, based on the above embodiments, such as Figure 4 As shown, step S3 includes: S31, construct a timed data retrieval task, aggregate the execution indicators of the data retrieval task to obtain the calculation results, and load the calculation results into the target table.

[0048] For example, a mapping relationship is established between the indicator measurement values ​​and dimensions in the indicator aggregation and the fields in the target table of the target data source. A scheduled start time is set according to year, week, month, day, hour, minute, and second to construct a scheduled data retrieval task. The data retrieval task reads the indicator aggregation SQL, executes the indicator aggregation calculation logic, and obtains the calculation result, which includes the indicator measurement value and dimension. The indicator measurement value and dimension in the calculation result are loaded into the corresponding fields of the target table according to the mapping relationship to complete the scheduled loading of indicator data.

[0049] It can be understood that the data retrieval task is a scheduled ETL task that loads the aggregated metric calculation results into the target table. The data retrieval task establishes a mapping relationship between the metric measures and dimensions in the aggregated metrics and the fields in the target table of the target data source. The scheduled start time is set according to year, week, month, day, hour, minute, and second. The DataX task is executed periodically. In DataX, the Reader is set to the data warehouse as the data source and reads the metric aggregation SQL. In DataX, the Writer is set to the target data source and the target data table, and sets the mapping fields of the target table in sequence.

[0050] In this embodiment, a timed scheduling mechanism is used to automate the calculation of indicators, and a precise mapping relationship ensures consistent matching between the calculation results and the target table structure. Fine-grained time settings provide a flexible scheduling control strategy, effectively supporting the differentiated timeliness requirements of indicator data in different business scenarios.

[0051] S32, Build an indicator API interface service, and perform indicator aggregation through the indicator API to return query results.

[0052] For example, a metrics API interface service based on the REST architecture style HTTP protocol is constructed. The metrics API interface service receives metrics query requests; the metrics API executes metrics aggregation SQL, reads the metrics aggregation calculation logic, and obtains query results, which include metrics metric values ​​and dimensions; the query results are returned to the calling program through an HTTP response to complete the real-time query of metrics data.

[0053] In this embodiment, a real-time query service for indicator data is achieved through a standardized REST interface design. The adoption of the HTTP protocol ensures the universality and interoperability of the interface, while the direct execution of the SQL calculation logic guarantees the real-time performance and accuracy of the query results, providing an efficient way for upper-layer application systems to obtain indicator data.

[0054] By constructing the indicator calculation application process, this embodiment realizes a dual-mode data service architecture of offline batch calculation and online real-time query. The timed scheduling mechanism ensures the continuous updating of indicator data, and the API interface service meets the business needs of real-time query. Together, they form a complete indicator data application system, providing a stable and reliable data foundation for performance evaluation and optimization decision-making.

[0055] In some embodiments, based on the above embodiments, step S4 includes: The execution plan based on the target table and query results estimates the performance quantification value of aggregated metrics, including: Key performance parameters are extracted from the execution plan of the target table and query results, and a performance quantification value is calculated based on the key performance parameters. The key performance parameters include the number of rows scanned, access type, join method, and aggregation operation.

[0056] For example, the database query optimizer generates an execution plan for the target table query. The number of rows scanned (full table scan and index scan) is read from the execution plan as the number of rows scanned. Database access methods, including sequential scan, index scan, and bitmap heap scan, are extracted as access type parameters. Table join strategies, such as nested loop join, hash join, and sort-merge join, are obtained as join method parameters. Data aggregation algorithms, such as group aggregation, hash aggregation, and sort aggregation, are identified as aggregation operation parameters. Based on a preset performance quantification formula, the number of rows scanned, access type weight, join method weight, and aggregation operation weight are weighted and calculated to obtain the performance quantification value of the aggregation metric.

[0057] In this embodiment, a precise quantitative evaluation of the aggregated performance of indicators is achieved through an execution plan parsing mechanism, and the extraction of multi-dimensional performance parameters ensures the comprehensiveness and accuracy of the evaluation results. A weighted calculation strategy effectively integrates the influence weights of different performance factors, providing a scientific quantitative basis for automated performance optimization decisions.

[0058] In one specific embodiment, the performance quantification formulas for single-table query and multi-table join aggregation query are as follows, for different application scenarios: ; ; ; in, This is a quantitative value for single-table query performance. This is a performance quantification value for multi-table join aggregation queries; This represents the number of rows scanned. It estimates the number of rows scanned in the driving table. It is the first Average number of matched rows per driven table; This represents the number of related tables. More rows result in higher I / O overhead; in the execution plan, Cardinality is the estimated number of rows to return. Weights are assigned to data access types to quantify the I / O efficiency of different access methods, where:

[0059] Index covering scan: ; Primary key / unique index scan: ; Normal index range scan: ; Full index scan: ; Full table scan: .

[0060] The connection method weights quantify the efficiency differences between different connections, where: Nested loop join: ; Hash join: ; Merge links: .

[0061] To scan the data block size, in bytes, supplement the records of physical I / O differences; This is a data volume correction factor; The number of rows to be aggregated is represented by Cardinality, which is the estimated number of rows to be returned in the execution plan. To query the total number of rows scanned, the number of rows scanned from the driving table and all driven tables is summed. For aggregation operation weights, . Weights for driving table access types; For the first Weights of the type of the driven table access.

[0062] The design basis for the performance quantification formulas for single-table query and multi-table join aggregation query includes: I. Core Theoretical Basis for Formula Design Theoretical basis: The query overhead is decomposed into "I / O overhead + CPU overhead + communication overhead" through the SystemR model. This embodiment combines the feature of metric aggregation that "completes multi-metric dimension alignment and splicing within a single node database and has no cross-node communication", eliminates communication overhead and focuses only on I / O overhead (number of rows read) and CPU overhead (access type, connection method, aggregation operation). Customized improvements: The traditional overhead model is designed for "single-table / multi-table computational queries", while indicator aggregation is a "multi-indicator integration query without computation". Therefore, this formula weakens the weight of "computational complexity", strengthens the weight of "dimensional alignment efficiency" (reflected by the join method and access type), and eliminates "grouping / aggregation operation complexity" (because indicator aggregation has no grouping and no aggregation operation), making the formula more suitable for the business scenario of indicator aggregation.

[0063] II. Principles for Parameter Selection The formula selects four parameters: number of rows scanned, access type, join method, and aggregation operation, rather than other parameters (such as execution time and memory usage). The core principle is: 1. Predictability: All parameters can be statically extracted from the database execution plan, and performance quantification can be completed without actually executing the query.

[0064] 2. Strongly correlated with the characteristics of indicator aggregation, atomic indicators, derived indicators, and composite indicators: Number of rows scanned: directly reflects I / O overhead; Access type: determines data reading efficiency; Connection method: directly related to the core logic of "multi-indicator dimension splicing"; Aggregation operation: directly related to CPU overhead.

[0065] 3. Quantifiable and calibrable: All parameters can be converted into numerical weights, making it easy to calculate a unique performance quantification value through formulas. Furthermore, the weights can be dynamically calibrated according to different business scenarios (such as government data and enterprise data), improving the universality of the formulas.

[0066] The performance quantification value is compared with a preset optimization decision threshold. When the performance quantification value is greater than the optimization decision threshold, an upstream tracing process is triggered.

[0067] For example, an optimization decision threshold based on historical performance statistical analysis is set. The calculated performance quantification value is compared with the optimization decision threshold. When the performance quantification value is greater than the optimization decision threshold, it is determined that there is a performance bottleneck in the current indicator aggregation, and the materialized view optimization strategy is automatically triggered by tracing upstream. When the performance quantification value is less than or equal to the optimization decision threshold, it is determined that the performance of the current indicator aggregation meets the requirements, and the existing calculation mode is maintained without optimization.

[0068] In this embodiment, a threshold comparison-based decision-making mechanism enables intelligent triggering of performance optimization, avoiding unnecessary optimization operations that waste system resources. The threshold setting strategy based on historical statistics ensures the rationality and practicality of the decision-making criteria, providing a reliable basis for accurate performance optimization.

[0069] The upstream tracing process includes: scanning the dependency table of the indicator system, obtaining the upstream and downstream dependencies of indicators at each level, tracing the composite indicators, derived indicators and atomic indicators that the indicator aggregation depends on layer by layer from the indicator aggregation, and obtaining a complete list of dependent indicators through recursive traversal.

[0070] In some embodiments, based on the above embodiments, the scanning indicator system dependency table includes the dependency relationship between reading indicator aggregation and composite indicators, the dependency relationship between composite indicators and derived indicators, and the dependency relationship between derived indicators and atomic indicators. The recursive traversal method includes starting from the current indicator node and visiting the upstream indicator nodes that the current indicator node directly depends on, and repeating the same traversal process for each upstream indicator node until the atomic indicator layer is reached. An initial graph structure for dependencies is constructed using the adjacency list method. The initial graph structure is then topologically sorted to verify the dependency cycle. The in-degree of all nodes is calculated, and nodes with an in-degree of zero are added to a queue. Nodes in the queue are then retrieved sequentially, and the in-degree of downstream nodes is decremented by one for each outgoing edge. If there are any untraversed nodes, the dependency cycle is determined.

[0071] In this embodiment, a systematic dependency scanning and recursive traversal algorithm is used to accurately trace the complete dependency links of the indicator system. The construction of the adjacency list graph structure and the topology sorting verification mechanism effectively ensure the correctness and completeness of the dependencies, providing a reliable definition of the indicator range for the accurate construction of the materialized view.

[0072] In some embodiments, based on the above embodiments, obtaining the complete list of dependency metrics through recursive traversal includes: Starting from the indicator aggregation node, trace upwards layer by layer in the order of composite indicator layer, derived indicator layer, and atomic indicator layer, extract the upstream indicator nodes that the indicator nodes at each level directly depend on, record the dependency path and level depth of each level of indicator, and obtain the complete dependency chain from indicator aggregation to basic data source.

[0073] For example, starting from the metric aggregation node, the list of composite metric nodes directly dependent on the metric aggregation is read, and the composite metric nodes are added to the dependent metric list with a level depth of one. For each composite metric node, the derived metric nodes that it directly depends on are traced upwards, and the derived metric nodes are added to the dependent metric list with a level depth of two. For each derived metric node, the atomic metric nodes that it directly depends on are traced upwards, and the atomic metric nodes are added to the dependent metric list with a level depth of three. The complete dependency path from the metric aggregation to each atomic metric is recorded to form a hierarchical dependent metric list, establishing a complete dependency chain mapping relationship from the metric aggregation to the basic data source.

[0074] In this embodiment, a hierarchical dependency tracing strategy is used to construct a complete dependency spectrum from indicator aggregation to basic data. Precise recording of hierarchical depth and dependency paths provides clear structural guidance for the hierarchical construction of materialized views, ensuring the relevance and effectiveness of the optimization strategy.

[0075] Through automated performance evaluation and dependency tracing processes, this embodiment achieves intelligent identification and precise location of performance bottlenecks in the indicator system. The decision-making mechanism based on quantitative evaluation ensures the accuracy of optimization triggers, and complete dependency tracing provides accurate definition of the indicator range for materialized view construction, significantly improving the adaptive optimization capability and overall operating efficiency of the indicator calculation system.

[0076] In some embodiments, based on the above embodiments, step S5 includes: The corresponding materialized views are constructed using the dependency-based atomic indices, derived indices, and composite indices, including: Using the data tables of the DWD layer of the data warehouse, the DWD layer data tables for atomic metric calculation, and the SQL for calculating atomic or derived metrics as the base tables for the materialized views of atomic metrics, derived metrics, and composite metrics, respectively, aggregation operations, arithmetic operations are performed on the metric fields to obtain the corresponding materialized views.

[0077] For example, the fact table and dimension table of the data warehouse DWD layer are used as the base tables for the atomic indicator materialized view. Aggregation operations such as summation, counting, and averaging are performed on the measure fields according to the atomic indicator calculation logic to generate the atomic indicator materialized view. The calculation results of the atomic indicators are used as the base tables for the derived indicator materialized view. Secondary aggregation operations are performed on the measure fields of the atomic indicators according to the derived indicator calculation logic to generate the derived indicator materialized view. The calculation SQL of the atomic indicators and derived indicators are used as the base tables for the composite indicator materialized view. Arithmetic operations such as addition, subtraction, multiplication, and division are performed on multiple indicators according to the composite indicator calculation logic to generate the composite indicator materialized view. The generated materialized views at each level are stored in the database to establish a mapping relationship between the materialized views and the original indicators.

[0078] In this embodiment, a hierarchical materialized view construction strategy optimizes the pre-computation and storage of indicator calculation results. Differentiated computation processing based on the characteristics of different levels of indicators ensures the accuracy of materialized view calculations, and the pre-computation mechanism effectively reduces the real-time computation overhead during queries, significantly improving the response performance of indicator queries.

[0079] For example, by iteratively tracing upstream from the atomic, derived, and composite metrics upon which the metric aggregation depends, tracing upstream from the atomic and derived metrics upon which the composite metrics depend, and tracing upstream from the atomic metrics upon which the derived metrics depend, all the composite, derived, and atomic metrics required for metric aggregation can be obtained. The materialized view is constructed as follows:

[0080] For example, constructing the materialized view of the atomic indices includes: The DWD layer data table is used as the base table; Connectionless operations; No grouping operations; Aggregation operations are supported. Aggregation operations are performed on the measure field. Aggregation methods include summation, count, maximum value, minimum value, average value, etc.

[0081] There is a projection operation. The projection column is formed by the aggregation operation column.

[0082] There are selection operations. The selection and filtering conditions are composed of dimension and measure fields.

[0083] For example, constructing the materialized view of the derived metric includes: The DWD layer data table calculated using atomic indices is used as the base table for calculating derived indices.

[0084] Connectionless operation.

[0085] There is a grouping operation. The dimensions of the derived indices are determined from the dimensional range of the atomic indices, and this is used as the grouping condition.

[0086] Aggregation operations are performed. Aggregation operations are performed on the measure fields in the atomic metrics.

[0087] Projection operations are performed. The dimensions of the derived metrics are determined from the dimension range of the atomic metrics and used as the projection columns. Aggregation operations are performed on the measure fields in the atomic metrics and used as the projection columns.

[0088] There are selection operations. The selection and filtering conditions are composed of dimension and measure fields.

[0089] For example, constructing the materialized view of the composite indicator includes: It includes join operations. The SQL is used as a subquery to calculate atomic or derived metrics, and joins are performed along the metric dimensions.

[0090] There is a grouping operation. The dimension of the composite indicator is determined from the dimension range of the atomic and derived indicators, and this is used as the grouping condition.

[0091] No aggregation operations.

[0092] Projection operations are included. The subquery measure is substituted into the index in the arithmetic element expression as the projection. Arithmetic operations include +, -, *, / , (,).

[0093] No selection operation.

[0094] For example, constructing the polymerized view of the metrics includes: It includes join operations. It uses SQL as a subquery to calculate atomic, derived, or composite metrics, and performs joins along the metric dimensions.

[0095] No grouping operation.

[0096] No aggregation operations.

[0097] There is a projection operation. The subquery measure and dimension are used as the projection.

[0098] Selective operations are performed. The indicator dimension is used as the calculation filter condition.

[0099] For example, the workflow for constructing the materialized view includes: The workflow for the data preparation phase is structured as follows: data acquisition tasks and data processing tasks are predecessor and successor nodes, respectively, and the dependencies between tasks (data processing tasks depend on data acquisition tasks from one or more source tables) are directed edges.

[0100] The workflow for building the indicator system is as follows: The task of refreshing the materialized view to generate atomic indicators is the successor node, and the data processing tasks in the data tables of the DWD layer that it depends on are the predecessor nodes, with directed edges forming the dependency relationship. The task of refreshing the materialized view to generate derived indicators is the successor node, and the task of refreshing the materialized view to generate atomic indicators that it depends on is the predecessor node, with directed edges forming the dependency relationship. The task of refreshing the materialized view to generate composite indicators is the successor node, and the tasks of refreshing the materialized view to generate atomic and derived indicators that it depends on are the predecessor nodes, with directed edges forming the dependency relationship. The task of refreshing the materialized view to generate composite indicators is the successor node, and the tasks of refreshing the materialized view to generate atomic and derived indicators that it depends on are the predecessor nodes, with directed edges forming the dependency relationship. The task of refreshing the materialized view to generate indicator aggregations is the successor node, and the tasks of refreshing the materialized view to generate atomic, derived, and composite indicators that it depends on are the predecessor nodes, with directed edges forming the dependency relationship.

[0101] In some embodiments, composite metrics depend on atomic and derived metrics, and the dimensions of the dependent atomic and derived metrics must be the same. The composite metric calculation uses the previously described atomic metrics as the data source. The system records the atomic and derived metrics that the composite metric depends on for use in constructing materialized views. Based on different business definitions, arithmetic expressions based on atomic and derived metrics are calculated. These expressions include atomic metrics, derived metrics, numbers, and symbols. Arithmetic operations include +, -, *, / , (,).

[0102] The logic for calculating composite indicators is accomplished by defining SQL. The characteristics of the SQL for calculating composite indicators include: Join operations are involved. This refers to SQL queries that include a JOIN clause to perform table joins, using the metric calculation query as a subquery. In SQL queries with join operations, two or more tables are merged based on the related fields using methods such as INNERJOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Join operations can integrate related data scattered across different tables, enabling horizontal data expansion and relational queries. It is a core technology for building wide tables and performing complex metric calculations.

[0103] Grouping operations are included. The dimensions of the composite indicator are determined from the dimensions of the atomic and derived indicators, and this is used as the grouping condition. This refers to the operation feature of SQL queries that include a GROUP BY clause to group data. In SQL queries with grouping operations, data records are divided into different groups based on the specified grouping field, usually in conjunction with aggregate functions to perform statistical calculations on the data within each group. Grouping operations achieve vertical data aggregation, classifying and statistically analyzing detailed data according to business dimensions. It is a key operation for achieving data dimensionality reduction and aggregation statistics in the calculation of derived and composite indicators.

[0104] No aggregation operations. This refers to SQL queries that do not contain a GROUP BY clause or aggregate functions (such as SUM, COUNT, AVG, MAX, MIN, etc.). In SQL queries without aggregation operations, the query results maintain the original data granularity, without grouping or summarizing the data; each row of input data corresponds to one or more rows of output data. Typical examples of queries without aggregation operations include simple SELECT queries, table join queries, and UNION queries. These queries are primarily used for data filtering, combination, and format conversion without altering the statistical hierarchy of the data.

[0105] Projection operations are involved. The projection is achieved by substituting the subquery measure into the arithmetic element expression. Projection refers to the operational characteristic of SQL queries where the SELECT clause selects specific fields or expressions. In SQL queries with projection operations, a subset of required fields is filtered from all fields in the source table, or new derived fields are generated through calculated expressions. Projection operations control the column structure of the query results, enabling field pruning and computational expansion of data, ensuring that the query results only contain the data columns required by the business logic, and improving data transmission efficiency and storage optimization.

[0106] No selection operation. This refers to an SQL query that does not include a WHERE clause for filtering. In SQL with no selection operation, the query processes all data records in the base table or view without setting any filtering conditions to limit the data range. This type of query is typically used in full-data processing scenarios, such as data migration, computational processing of complete datasets, or batch processing tasks that require performing the same operation on all data records. No selection operation ensures the integrity of data processing but may increase the consumption of computing resources.

[0107] Metric aggregation serves as a vehicle for metric applications. It integrates multiple atomic, derived, and composite metrics of the same dimension into a single metric aggregation. The system records the atomic, derived, and composite metrics that the metric aggregation depends on for use in constructing materialized views. The metric aggregation calculation logic is implemented through defined SQL. The metric aggregation calculation logic in this embodiment differs fundamentally from the composite metric calculation logic:

[0108] Positioning: Composite indicators are reusable calculation units within an indicator system. They are the products of atomic / derived indicator operations and are essentially indicators. Indicator aggregation, on the other hand, is not an indicator but a multi-indicator integration output unit for business applications, providing a standardized data outlet for data retrieval tasks and indicator APIs.

[0109] Design goals: Composite indicators achieve the fusion of single-dimensional indicators through arithmetic operations, producing new independent indicators. Indicator aggregation achieves the standardized integration and unified output of multiple types of indicators, producing a combined dataset of multiple indicators.

[0110] Data Output: Composite Indicators produce a dataset of a single composite indicator, containing only the dimensions of that composite indicator and a unique metric generated through arithmetic operations. Indicator Aggregation produces a combined dataset of multiple indicators, containing the dimensions of all integrated indicators and their original metric values. The dimensions are unified, but the metric values ​​of each indicator are independent.

[0111] Reusability: Composite metrics, as basic metrics, are integrated and reused by the metric aggregation layer, but cannot be directly provided to external application services. Metric aggregation cannot be reused by the metric layer; it only provides services directly to business applications and is the final carrier for the external output of the metric system. The metric aggregation calculation logic is completed by defining SQL. The characteristics of the SQL for metric aggregation calculation logic include:

[0112] It includes join operations. The SQL query for metric calculation is used as a subquery, and joins are performed according to metric dimensions. The SQL query for metric aggregation includes a JOIN clause for table join operations. In metric aggregation, the calculation results of multiple atomic, derived, and composite metrics with the same dimension are combined and merged using methods such as inner join (INNER JOIN) and left outer join (LEFT JOIN). The join operation uses the common dimension field of each metric as the join key to achieve horizontal integration of metric data at different levels, forming a unified metric aggregation dataset.

[0113] No grouping operations. The SQL query for metric aggregation does not include a GROUP BY clause for data grouping. Since metric aggregation integrates atomic, derived, and composite metrics that have already undergone aggregation calculations, and each input metric has already been grouped and statistically analyzed along the same dimension, no further grouping operations are needed at the metric aggregation level. The horizontal merging of metric data is achieved directly through join operations.

[0114] No aggregation operations. The SQL query for metric aggregation does not contain aggregate functions (such as SUM, COUNT, AVG, etc.) for data statistical operations. The goal of metric aggregation is to combine and display the results of multiple pre-calculated metrics, rather than to statistically summarize the raw data. The atomic, derived, and composite metrics of each input already contain the final measure value; metric aggregation only needs to concatenate and combine these measures according to their dimensions, without performing additional aggregation calculations.

[0115] Projection operations are included. The subquery's measure and dimension are used as the projection. The SQL query for metric aggregation selects specific fields for computational features via the SELECT clause. In metric aggregation, the required dimension and measure fields are selected from various input metrics, and the projection operation controls the final output field structure. The projection operation ensures that the metric aggregation result only includes the dimensions and metric measures required by the business application, achieving data structure standardization and optimization.

[0116] Selection operations are included. The metric dimensions serve as the filtering conditions for calculations. The SQL query statement for metric aggregation includes a WHERE clause for conditional filtering. In metric aggregation, filtering conditions such as time range and dimension conditions are set to limit the range of data participating in the aggregation, ensuring that the aggregation results meet specific business query requirements. Selection operations implement business logic control for metric aggregation, improving query efficiency and the relevance of results.

[0117] The SQL feature design for indicator aggregation revolves around the principles of "no computation, pure integration, and standardized output," fundamentally differentiating it from the computational SQL features of derived and composite indicators. This also serves the design intent of indicator aggregation as a unified carrier at the application layer. The technical considerations and effects are as follows:

[0118] The algorithm retains join operations, but excludes grouping and aggregation operations. Join operations are only used to achieve precise alignment of multiple indicators along the same dimension, ensuring dimensional consistency of the integrated dataset and providing a standardized data structure for subsequent data retrieval tasks and indicator APIs. The deliberate omission of grouping and aggregation operations is the core design difference between indicator aggregation and derived or composite indicators. Since indicator aggregation is positioned as a "multi-indicator integration carrier" rather than a "new indicator calculation unit," it does not need to generate new metrics through grouping or aggregation. It only needs to retain the original metrics and unified dimensions of each underlying indicator, fundamentally avoiding functional confusion with the calculation layer indicators, while reducing meaningless computational overhead and improving the efficiency of indicator integration and querying.

[0119] The projection operation only retains the subquery's measure and dimension: The design of the projection column strictly follows the principle of "no processing, pure mapping". It does not perform any arithmetic operations or field transformations, and directly uses the original measure and unified dimension of each underlying indicator as the projection result. This ensures the authenticity and consistency of the indicator data, while allowing data retrieval tasks and indicator APIs to directly parse and use the projection result without additional field adaptation processing, thus reducing the integration cost of the application layer.

[0120] Only the indicator dimension is retained as the selection operation condition: The selection operation is only based on the unified indicator dimension for filtering, without adding any new business filtering rules. This ensures the universality of indicator aggregation for different business applications, avoids the problem of data inconsistency between multiple versions caused by adding new filtering rules, and allows dimension filtering to be combined with database index optimization to further improve the query performance of indicator aggregation.

[0121] The workflow is constructed by treating data acquisition tasks, data processing tasks, atomic indicator materialized view refresh tasks, derived indicator materialized view refresh tasks, composite indicator materialized view refresh tasks, and indicator polymerized view refresh tasks as task nodes, and upstream and downstream dependencies as directed edges.

[0122] The scheduling module executes each task node in the topological sorting order. After generating the materialized view, the calculation logic of atomic indicators, derived indicators, and composite indicators in the original query request is replaced with direct access to the corresponding materialized view.

[0123] In this embodiment, an ordered scheduling and management of materialized view refresh tasks is achieved through a directed acyclic graph workflow construction mechanism. The topology sorting verification algorithm ensures the correctness of the task execution order and the integrity of dependencies, providing a reliable scheduling framework for automated materialized view maintenance.

[0124] In some embodiments, based on the above embodiments, the workflow for constructing a directed acyclic graph includes: Scan the dependency table of the indicator system to obtain the upstream and downstream dependencies of indicators at each level, map the refresh requirements of indicators at different levels to the corresponding task nodes, and record the indicator identifier, the upstream node identifier it depends on, and the task execution priority for each task node. Using all task nodes as vertices of the graph, and upstream and downstream dependencies as directed edges of the graph, construct the initial graph structure, and label each directed edge with dependency type and triggering condition. The initial graph structure is topologically sorted for verification. The in-degree of all nodes is calculated, and nodes with an in-degree of zero are added to the queue. Nodes in the queue are taken out one by one and all outgoing edges of the nodes are traversed. The in-degree of downstream nodes is decremented by one. If the in-degree of a downstream node becomes zero, it is added to the queue. When all nodes have been traversed, the verification is successful, and an executable directed acyclic graph workflow is generated.

[0125] In some embodiments, based on the above embodiments, the task execution priority is arranged in the order of data acquisition, data processing, atomic indicator refresh, derived indicator refresh, composite indicator refresh, and indicator aggregation refresh; When the indicator system changes, an incremental update mechanism is adopted. The change log of the indicator system dependency table is monitored to locate the changed indicator identifier and the corresponding task node. Only the original edges of the task node corresponding to the changed indicator identifier and the downstream direct dependent nodes of the corresponding task node are deleted. The dependency relationship of the changed node is re-collected to generate new edges. The topological sorting verification is re-executed on the local subgraph involved in the change. After the verification is passed, the local subgraph is integrated into the original directed acyclic graph workflow. The database transparent rewriting technology uses a database query optimizer to identify the indicator calculation logic involved in the query statement, determine whether there is a corresponding materialized view, and replace the indicator calculation part in the original query statement with a materialized view access statement.

[0126] In this embodiment, intelligent scheduling and transparent rewriting techniques are used to achieve automatic maintenance and query optimization of materialized views. The incremental update mechanism effectively reduces the reconstruction overhead when the indicator system changes, and the transparent database rewriting technology ensures automatic optimization of query performance, providing efficient operational support for indicator calculation.

[0127] By optimizing the process with intelligent materialized views, this embodiment achieves a comprehensive improvement in the performance of indicator calculation. The hierarchical materialized view construction strategy effectively reduces the computational complexity during queries, the directed acyclic graph workflow mechanism ensures the orderly maintenance of materialized views, and the transparent rewriting technology realizes automatic optimization of query performance. This significantly improves the overall operating efficiency of the indicator calculation system and the user query experience, providing a complete technical solution for high-performance calculation of large-scale indicator systems.

[0128] In one specific embodiment, the SQL for the hierarchical indicators differs significantly. Specifically, the indicator system construction stage includes multiple steps, among which the calculation logic for constructing derived indicators includes: The derived indicator calculation logic is completed by defining SQL. The atomic indicator calculation characteristics of the derived indicator calculation logic include: Joinless computation. Atomic metric calculations are based directly on a single data table in the Data Warehouse (DWD) layer, without involving multi-table joins. This design ensures the independence and efficiency of atomic metrics as the most basic computational unit, avoiding the performance overhead of complex table joins, while providing a stable and reliable data foundation for the calculation of subsequent derived and composite metrics. The joinless nature of atomic metrics allows for rapid response to query requests, reducing system resource consumption.

[0129] Atomic metrics have dimensions. Dimension fields are only used for defining the dimensions of their derived metrics. They do not participate in the calculation of atomic metric values. Atomic metrics contain one or more dimension fields, which are primarily used for defining the dimensions of subsequent derived metrics and grouping data; they do not directly participate in the calculation of atomic metric values. Dimension fields provide analytical perspectives and classification criteria for data, laying the foundation for the hierarchical construction of the metric system. Through dimension design, atomic metrics can support multi-faceted business analysis needs and achieve refined data management.

[0130] Atomic metrics have metrics. The metric field is used to aggregate the metric values ​​of its derived metrics. An atomic metric contains a unique metric field, which is the core data source for metric calculation and is used for various aggregation operations on subsequent derived metrics. The metric field carries the actual numerical content of the business metric and is the foundation of quantitative analysis. Through clear metric definitions, atomic metrics ensure data consistency and comparability, providing a reliable guarantee for the accurate calculation of higher-level metrics.

[0131] Atomic metrics perform aggregation operations on the measure field. Aggregation methods include summation, counting, maximum, minimum, and average. Atomic metrics perform standardized aggregation operations on the measure field, including summation, counting, maximum, minimum, and average. Aggregation operations transform raw detailed data into summary metrics that can be used for analysis, improving data processing efficiency. Through predefined aggregation rules, atomic metrics achieve data standardization, ensuring the consistency and accuracy of metric calculations.

[0132] Atomic indicators involve projection operations. The projection column is composed of the aggregation operation columns. The atomic indicators determine the final output data columns through projection operations, primarily consisting of the result columns of the aggregation operations. Projection operations precisely define the output format and content structure of the indicator, ensuring the standardization and normalization of the data results. Through reasonable projection design, atomic indicators can provide clear and concise data output, facilitating subsequent indicator referencing and calculation.

[0133] Atomic metrics employ selection operations. Selection filters are composed of dimension and measure fields. Atomic metrics use dimension and measure fields to construct selection filters, enabling precise control over the data range. Selection operations ensure the business accuracy of metric calculations, and flexible filter settings can meet the data needs of different business scenarios. This design gives atomic metrics excellent adaptability and configurability, supporting diverse business analysis requirements.

[0134] The logic for constructing derived indicator calculations includes: The derived metric calculation logic is implemented by defining SQL. The characteristics of the SQL for derived metric calculation logic include: Join-free computation. Derived metric calculations are based on the results of atomic metric calculations, directly using the DWD layer data table used in the atomic metric calculations as the base table, eliminating the need for multi-table joins. This design ensures the efficiency and stability of derived metric calculations, avoiding the performance overhead of complex table joins. By inheriting the data foundation of atomic metrics, derived metrics can achieve rapid calculations while maintaining data consistency, providing a reliable data source for upper-level composite metrics.

[0135] Grouping is involved. The dimensions of derived metrics are determined from the dimensional range of atomic metrics, serving as the grouping criteria. Derived metrics achieve dimensional aggregation through grouping operations, selecting a subset of dimensions from the dimensional range of atomic metrics as grouping conditions. Grouping operations are a key feature distinguishing derived metrics from atomic metrics; they categorize and summarize detailed data from atomic metrics according to business dimensions, achieving hierarchical data processing. Through flexible dimension selection and grouping strategies, derived metrics can meet business analysis needs at different granularities, providing multi-faceted data views.

[0136] Aggregation operations are performed. Aggregation operations are applied to the measure fields of atomic metrics. Derived metrics perform aggregation operations on the measure fields of atomic metrics, including summation, counting, maximum, minimum, and average, achieving statistical summarization of the underlying data. Aggregation operations are the core mechanism for derived metrics to generate new measure values, transforming the measure data of atomic metrics into higher-level business metrics through predefined aggregation rules. This aggregation processing not only enhances the analytical value of the data but also provides a standardized data foundation for the calculation of composite metrics.

[0137] Projection operations are performed. The dimensions of the derived metric are determined from the dimension range of the atomic metric, serving as the projection column. Aggregation operations are then performed on the measure fields of the atomic metric, also serving as the projection column. The projection operation of the derived metric comprises two core components: the selected dimension field as the projection column, and the result of the aggregation operation on the measure field as the projection column. The projection operation precisely defines the output structure of the derived metric, ensuring that the data results meet business needs and subsequent calculation requirements. Through a reasonable projection design, the derived metric can provide a clear and standardized data output format, facilitating the hierarchical construction and application integration of the metric system.

[0138] Selection operations are included. The selection operations defined by the atomic indicators and those defined by the derived indicators together constitute the filtering conditions for calculating the derived indicators. The selection operations of the derived indicators consist of two parts: the existing selection filtering conditions in the atomic indicators, and the selection filtering conditions added when defining the derived indicators. This dual filtering mechanism ensures the business accuracy and data integrity of the derived indicator calculations, and allows for precise control of the data range according to different business perspectives and analytical needs. Through the design of inherited and extended filtering conditions, the derived indicators maintain logical consistency with the atomic indicators while possessing unique business characteristics.

[0139] Furthermore, the application phase of metrics includes building data retrieval tasks, building metric APIs, evaluating metric aggregation performance, and building materialized views. In some embodiments, the present invention also provides an indicator calculation device based on a hierarchical indicator system, the device comprising: The data acquisition and processing module is used to acquire data tables or data files from external data sources, collect the data tables or data files into the data warehouse ODS layer, connect the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table and save it to the data warehouse DWD layer. The indicator system construction module is used to construct atomic indicators, derived indicators, and composite indicators based on the data tables of the data warehouse DWD layer, and to integrate multiple atomic indicators, derived indicators, and composite indicators with the same dimension to obtain indicator aggregation. The indicator calculation service module is used to construct data retrieval tasks and indicator APIs. The data retrieval tasks are used to perform indicator aggregation to obtain calculation results, load the calculation results into the target table, and the indicator API is used to perform indicator aggregation and return query results. The performance evaluation decision module is used to estimate the performance quantification value of the aggregated indicators based on the execution plan of the target table and query results. When the performance quantification value is greater than the preset optimization decision threshold, the module traces upstream according to the aggregated indicators to obtain the dependent atomic indicators, derived indicators and composite indicators. The materialized view optimization module is used to construct corresponding materialized views based on dependent atomic indicators, derived indicators, and composite indicators, and to redirect query requests to materialized views using database transparent rewriting technology.

[0140] Through modular device design, this embodiment constructs a complete index calculation system architecture. The modules achieve loosely coupled collaboration through standardized interfaces. The data acquisition and processing module lays the data foundation, the index system construction module establishes the calculation framework, the index calculation service module provides application support, the performance evaluation and decision-making module ensures operating efficiency, and the materialized view optimization module achieves performance improvement.

[0141] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for calculating indicators based on a hierarchical indicator system, characterized in that, Includes the following steps: Obtain data tables or data files from external data sources, collect the data tables or data files into the data warehouse ODS layer, connect the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table and save it to the data warehouse DWD layer. Based on the data tables of the DWD layer of the data warehouse, atomic indicators, derived indicators, and composite indicators are constructed. Multiple atomic indicators, derived indicators, and composite indicators with the same dimension are integrated to obtain indicator aggregation. Construct a data retrieval task and a metrics API. Perform metrics aggregation through the data retrieval task to obtain calculation results. Load the calculation results into the target table. Perform metrics aggregation through the metrics API to return query results. Based on the execution plan of the target table and query results, the performance quantification value of the aggregated indicators is estimated. When the performance quantification value is greater than the preset optimization decision threshold, the dependent atomic indicators, derived indicators and composite indicators are obtained by tracing upstream according to the aggregated indicators. Materialized views are constructed based on atomic, derived, and composite metrics, and query requests are redirected to these materialized views using database transparent rewriting techniques.

2. The indicator calculation method based on a hierarchical indicator system as described in claim 1, characterized in that, The execution plan based on the target table and query results estimates the performance quantification value of aggregated metrics, including: Key performance parameters are extracted from the execution plan of the target table and query results, and a performance quantification value is calculated based on the key performance parameters. The key performance parameters include the number of rows scanned, access type, join method, and aggregation operation. The performance quantification value is compared with a preset optimization decision threshold. When the performance quantification value is greater than the optimization decision threshold, an upstream tracing process is triggered. The upstream tracing process includes: scanning the dependency table of the indicator system, obtaining the upstream and downstream dependencies of indicators at each level, tracing the composite indicators, derived indicators and atomic indicators that the indicator aggregation depends on layer by layer from the indicator aggregation, and obtaining a complete list of dependent indicators through recursive traversal.

3. The indicator calculation method based on a hierarchical indicator system as described in claim 2, characterized in that, The dependency table of the scanning indicator system includes the dependency relationship between aggregated and composite indicators, the dependency relationship between composite indicators and derived indicators, and the dependency relationship between derived indicators and atomic indicators. The recursive traversal method includes starting from the current indicator node and visiting the upstream indicator nodes that the current indicator node directly depends on, and repeating the same traversal process for each upstream indicator node until the atomic indicator layer is reached. An initial graph structure for dependencies is constructed using the adjacency list method. The initial graph structure is then topologically sorted to verify the dependency cycle. The in-degree of all nodes is calculated, and nodes with an in-degree of zero are added to a queue. Nodes in the queue are then retrieved sequentially, and the in-degree of downstream nodes is decremented by one for each outgoing edge. If there are any untraversed nodes, the dependency cycle is determined.

4. The indicator calculation method based on a hierarchical indicator system as described in claim 3, characterized in that, The complete list of dependency metrics obtained through recursive traversal includes: Starting from the indicator aggregation node, trace upwards layer by layer in the order of composite indicator layer, derived indicator layer, and atomic indicator layer, extract the upstream indicator nodes that the indicator nodes at each level directly depend on, record the dependency path and level depth of each level of indicator, and obtain the complete dependency chain from indicator aggregation to basic data source.

5. The indicator calculation method based on a hierarchical indicator system as described in claim 1, characterized in that, The corresponding materialized views are constructed using the dependency-based atomic indices, derived indices, and composite indices, including: Using the data tables of the DWD layer of the data warehouse, the DWD layer data tables for atomic metric calculation, and the SQL for calculating atomic or derived metrics as the base tables for the materialized views of atomic metrics, derived metrics, and composite metrics, respectively, aggregate operations, aggregation operations, and arithmetic operations are performed on the metric fields to obtain the corresponding materialized views. Data acquisition tasks, data processing tasks, atomic indicator materialized view refresh tasks, derived indicator materialized view refresh tasks, composite indicator materialized view refresh tasks, and indicator polymerized view refresh tasks are used as task nodes. Directed edges are constructed based on upstream and downstream dependencies to build a directed acyclic graph workflow. The scheduling module executes each task node in the topological sorting order. After generating the materialized view, the calculation logic of atomic indicators, derived indicators, and composite indicators in the original query request is replaced with direct access to the corresponding materialized view.

6. The index calculation method based on a hierarchical index system as described in claim 5, characterized in that, The workflow for constructing a directed acyclic graph includes: Scan the dependency table of the indicator system to obtain the upstream and downstream dependencies of indicators at each level, map the refresh requirements of indicators at different levels to the corresponding task nodes, and record the indicator identifier, the upstream node identifier it depends on, and the task execution priority for each task node. Using all task nodes as vertices of the graph, and upstream and downstream dependencies as directed edges of the graph, construct the initial graph structure, and label each directed edge with dependency type and triggering condition. The initial graph structure is topologically sorted for verification. The in-degree of all nodes is calculated, and nodes with an in-degree of zero are added to the queue. Nodes in the queue are taken out one by one and all outgoing edges of the nodes are traversed. The in-degree of downstream nodes is decremented by one. If the in-degree of a downstream node becomes zero, it is added to the queue. When all nodes have been traversed, the verification is successful, and a directed acyclic graph workflow is generated.

7. The index calculation method based on a hierarchical index system as described in claim 6, characterized in that, The task execution priority is arranged in the following order: data acquisition, data processing, atomic indicator refresh, derived indicator refresh, composite indicator refresh, and indicator aggregation refresh. When the indicator system changes, an incremental update mechanism is adopted. The change log of the indicator system dependency table is monitored to locate the changed indicator identifier and the corresponding task node. Only the original edges of the task node corresponding to the changed indicator identifier and the downstream direct dependent nodes of the corresponding task node are deleted. The dependency relationship of the changed node is re-collected to generate new edges. The topological sorting verification is re-executed on the local subgraph involved in the change. After the verification is passed, the local subgraph is integrated into the original directed acyclic graph workflow. The database transparent rewriting technology uses a database query optimizer to identify the indicator calculation logic involved in the query statement, determine whether there is a corresponding materialized view, and replace the indicator calculation part in the original query statement with a materialized view access statement.

8. The indicator calculation method based on a hierarchical indicator system as described in claim 1, characterized in that, The process of acquiring data tables or data files from external data sources, collecting these data tables or data files into the data warehouse ODS layer, joining the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table, and saving it to the data warehouse DWD layer includes: Obtain data tables or data files from external data sources and collect them into the data warehouse ODS layer; Join operations are performed on the fact table and dimension table in the data warehouse ODS layer to form a wide table. Data cleaning and dimension redundancy processing are performed on the wide table. The processed wide table is then saved to the data warehouse DWD layer, and the table fields in the data warehouse DWD layer are marked as either measures or dimensions.

9. The indicator calculation method based on a hierarchical indicator system as described in claim 1, characterized in that, The data tables based on the data warehouse DWD layer are used to construct atomic indicators, derived indicators, and composite indicators. Multiple atomic, derived, and composite indicators with the same dimension are integrated to obtain indicator aggregation, including: Based on the data tables in the DWD layer of the data warehouse, atomic indicator calculation logic, derived indicator calculation logic, and composite indicator calculation logic are constructed respectively, and the dependencies between each indicator are recorded. Multiple atomic, derived, and composite indicators of the same dimension are integrated through join operations to form indicator aggregation, and the atomic, derived, and composite indicators that the indicator aggregation depends on are recorded.

10. An indicator calculation device based on a hierarchical indicator system, used to execute an indicator calculation method based on a hierarchical indicator system as described in any one of claims 1-9, characterized in that, The device includes: The data acquisition and processing module is used to acquire data tables or data files from external data sources, collect the data tables or data files into the data warehouse ODS layer, connect the fact tables and dimension tables in the data warehouse ODS layer to obtain a wide table and save it to the data warehouse DWD layer. The indicator system construction module is used to construct atomic indicators, derived indicators, and composite indicators based on the data tables of the data warehouse DWD layer, and to integrate multiple atomic indicators, derived indicators, and composite indicators with the same dimension to obtain indicator aggregation. The indicator calculation service module is used to construct data retrieval tasks and indicator APIs. The data retrieval tasks are used to perform indicator aggregation to obtain calculation results, load the calculation results into the target table, and the indicator API is used to perform indicator aggregation and return query results. The performance evaluation decision module is used to estimate the performance quantification value of the aggregated indicators based on the execution plan of the target table and query results. When the performance quantification value is greater than the preset optimization decision threshold, the module traces upstream according to the aggregated indicators to obtain the dependent atomic indicators, derived indicators and composite indicators. The materialized view optimization module is used to construct corresponding materialized views based on dependent atomic indicators, derived indicators, and composite indicators, and to redirect query requests to materialized views using database transparent rewriting technology.

Images