Business operation data visual analysis method and system

Abstract

The present application belongs to the technical field of data analysis, and particularly relates to a business operation data visual analysis method and system, which comprises the following steps: collecting data, connecting multiple tables to construct a path graph with weight relations, synchronously setting an initial aggregation granularity and a base threshold, traversing the associated connection path to complete multi-dimensional evaluation, determining low-confidence paths according to the key column non-empty proportion and Gini coefficient, identifying shortcut replacement paths through the access cost ratio, calculating path quality points by fusing affinity, output base difference and confidence markers, removing invalid paths below the pruning threshold, and adjusting the aggregation granularity according to the remaining path to predict the base, with the larger the base, the coarser the granularity; combining the remaining path, shortcut markers and granularity to generate a query aggregation instruction, and executing the instruction to output a visual data set. The present application can intelligently prune redundant paths, utilize shortcuts and flexibly adjust the granularity, thereby improving the response speed and analysis accuracy.

Description

Technical Field

[0001] This invention relates to the field of data analysis technology. More specifically, this invention relates to methods and systems for visualizing and analyzing enterprise business data. Background Technology

[0002] As enterprises deepen their digital transformation, they accumulate a large amount of business data in their daily operations. In modern enterprise data warehouse architectures, to reduce data redundancy and maintain data consistency, enterprise operational data is typically stored and organized using the snowflake schema. In this schema, a core fact table is surrounded by multiple levels of dimension tables, forming a complex network of multi-table relationships. When business users initiate visualization analysis requests containing specific dimensions, measures, and presentation types, it is usually necessary to perform numerous multi-table join operations based on the snowflake schema's hierarchical structure to integrate the data scattered across the various dimension and fact tables.

[0003] Currently, facing the explosive growth of data volume and the increasing refinement of business dimensions, complex multi-table joins consume a large amount of computing and memory resources, resulting in excessively long front-end visualization response times and failing to meet the high demands of modern enterprises for real-time and interactive data analysis. Furthermore, not all table join relationships in the physical model are of practical value to the current visualization analysis request. Many join paths may result in a large number of low-confidence, invalid calculations due to a large number of null values ​​or extremely uneven key-value distribution. In addition, traditional query mechanisms often perform full table traversal, failing to identify and utilize faster access shortcuts, and lacking flexible granular control mechanisms when generating visualization data.

[0004] However, when the output data volume after correlation far exceeds the capacity of the front-end visualization charts, it not only causes network transmission congestion and front-end rendering lag, but also results in overly dense chart information and visually chaotic presentation, reducing the decision-making support value of data visualization. Therefore, how to intelligently evaluate and filter multi-table join relationships when faced with complex snowflake pattern data, remove low-value or high-cost redundant join paths, and adjust the aggregation granularity of data according to the output data scale, while ensuring the accuracy of analysis, reducing computational overhead, and improving visualization response speed and presentation quality, has become a pressing technical challenge in the field of enterprise data analysis. Summary of the Invention

[0005] To address the technical challenge of intelligently pruning snowflake connection paths and flexibly adjusting grain size to reduce visualization computational overhead, this invention proposes a method and system for visual analysis of enterprise business data. This system can intelligently prune redundant paths, utilize shortcuts, and flexibly adjust grain size, thereby improving response speed and analysis accuracy.

[0006] In a first aspect, the present invention provides a method for visual analysis of enterprise operating data, comprising: S1, acquiring enterprise operating data in snowflake pattern and a visualization analysis request including target dimensions, metrics, and presentation types; abstracting the multi-table join relationships in the enterprise operating data into a weighted relational path graph, wherein the path weights of the relational path graph are determined by the co-occurrence frequency and semantic similarity between tables; calculating the initial affinity of each join path; and determining the initial aggregation granularity and cardinality threshold based on the visualization analysis request; S2, traversing and evaluating the join paths associated with the visualization analysis request; and determining the method when the proportion of non-null values ​​in the join key column is lower than the lower bound of participation or the Gini coefficient of the key value distribution is higher than the upper bound of skewness. The corresponding connection path is marked as a low-confidence state. When the ratio of the original table traversal cost to the hierarchical shortcut access cost is greater than the preset cost coefficient, the corresponding connection path is marked as a shortcut replacement state. The path quality score of the connection path is generated by combining the initial affinity, the difference between the predicted output cardinality and the cardinality threshold, and the low-confidence state marking. S3, the connection path with a path quality score lower than the pruning threshold is removed, and the initial aggregation granularity is adjusted based on the predicted output cardinality of the remaining connection paths. The larger the predicted output cardinality, the coarser the adjusted aggregation granularity. The query aggregation instruction is generated based on the remaining connection paths, the shortcut replacement state marking, and the aggregation granularity, and the query aggregation instruction is executed to obtain the visualized dataset.

[0007] By adopting the above technical solutions, a path graph weighted by co-occurrence frequency and semantic similarity is constructed. The path quality is evaluated and pruned by comprehensively considering data quality, access cost, and cardinality differences. This can intelligently eliminate low-value connections, utilize shortcuts, and flexibly adjust the aggregation granularity, significantly reducing the computational overhead of multi-table associations in the snowflake mode, while ensuring the visualization response speed and the reliability of the analysis results.

[0008] Preferably, the step of acquiring enterprise operating data in snowflake pattern and visualization analysis requests containing target dimensions, metrics, and presentation types includes: loading the metadata of a snowflake-shaped data table containing fact tables and multi-level dimension tables into a memory buffer pool by establishing a secure connection with the enterprise data warehouse; parsing the front-end message of the visualization analysis request to extract query conditions, target analysis dimensions, metric fields, and expected presentation types; mapping the extracted fields to the physical tables and columns corresponding to the metadata, and generating a queue of visualization tasks to be processed.

[0009] By adopting the above technical solution, the steps for acquiring data are limited. By loading metadata, parsing front-end messages, and mapping fields to generate a task queue, the automated parsing and pipelined processing of visual analysis requests are realized, improving data preparation efficiency and reducing manual configuration errors.

[0010] Preferably, the calculation of the initial affinity of each connection path includes: constructing a relational path graph using the fact table and dimension table of enterprise operating data as nodes and foreign key constraints between tables as edges; counting the number of queries in the historical query logs where both ends of each connection edge appear simultaneously, dividing the number of simultaneous queries by the maximum value of the total number of occurrences of a single end table in both ends to obtain the co-occurrence frequency between dimension attributes; using a pre-trained natural language processing model to calculate the cosine similarity of word vectors of field names and annotation text in both ends of the table as semantic similarity; normalizing the semantic similarity to the [0,1] interval and then weighting and summing it with the co-occurrence frequency as the path weight of each connection path, and multiplying the weights of each edge on the path as the initial affinity of each connection path.

[0011] By adopting the above technical solution, the calculation method of initial affinity is defined. The edge weight is determined by weighting co-occurrence frequency and semantic similarity and the path weight is accumulated. This can accurately characterize the strength of the relationship between tables and provide an objective and multi-dimensional basis for subsequent path optimization.

[0012] Preferably, determining the initial aggregation granularity and cardinality threshold based on the visualization analysis request includes: parsing the time dimension or hierarchical dimension requirements in the visualization analysis request, extracting the smallest grouping unit as the initial aggregation granularity; counting the total number of non-duplicate records of the target dimension in the corresponding physical table, and combining the maximum rendering limit of the front-end presentation type, taking the minimum value of the two to determine the cardinality threshold for allowing the returned results.

[0013] By adopting the above technical solution, the method for determining the initial aggregation granularity and the base threshold is limited. By extracting the smallest grouping unit and taking the minimum value in combination with the front-end rendering limit, the data details and chart carrying capacity can be effectively balanced, avoiding front-end rendering lag due to excessively fine aggregation granularity or loss of analysis details due to excessively coarse aggregation granularity.

[0014] Preferably, marking the corresponding join path as low-confidence when the proportion of non-null values ​​in the join key column is lower than the lower bound of participation or the Gini coefficient of the key value distribution is higher than the upper bound of skewness includes: scanning the key columns of the data tables at both ends of the join path to be tested, and calculating the proportion of non-null values ​​by the ratio of the number of valid data rows to the total number of rows; calculating the Gini coefficient of the uneven distribution based on the frequency of occurrence of each key value in the key column; comparing the proportion of non-null values ​​with a preset lower bound of participation, and comparing the Gini coefficient with a preset upper bound of skewness; if either condition is met, updating the status flag of the corresponding join path in the data dictionary to low-confidence state.

[0015] By adopting the above technical solution, the marking process of low confidence states is limited. By using the non-null value ratio and Gini coefficient for dual verification, missing or skewed data connection paths can be identified. This can accurately detect low-quality associations in advance, avoid subsequent execution of invalid and costly join operations, and improve overall query efficiency and analysis accuracy.

[0016] Preferably, the step of generating a path quality score for a connection path by integrating the initial affinity, the difference between the predicted output cardinality and the cardinality threshold, and the low-confidence status marker includes: calling a cardinality estimation algorithm to estimate the size of the result set after performing a multi-table join on the connection path; if the corresponding connection path has a shortcut substitution status marker, estimating the size of the result set after shortcut substitution to obtain the predicted output cardinality; when the predicted output cardinality is greater than the cardinality threshold, calculating the difference between the predicted output cardinality and the cardinality threshold and normalizing the difference to obtain a difference penalty term; when the predicted output cardinality is not greater than the cardinality threshold, the difference penalty term is zero, and the initial affinity is subtracted from the difference penalty term to obtain the initial score; checking whether the corresponding connection path has a low-confidence status marker, and if so, multiplying the initial score by a preset attenuation coefficient to obtain the path quality score.

[0017] Preferably, removing connection paths with path quality scores lower than the pruning threshold includes: sorting all connection paths in descending order of path quality scores; comparing each path quality score with a preset pruning threshold; when a path quality score is detected to be lower than the pruning threshold, removing the corresponding connection path object from the candidate path list and logically deleting the isolated dimension nodes generated in the graph to generate a set of pruned available paths.

[0018] Preferably, the step of generating query aggregation instructions based on the remaining connection paths, shortcut replacement status markers, and aggregation granularity, and executing the query aggregation instructions to obtain a visual dataset includes: traversing the remaining connection paths; for connection paths with shortcut replacement status markers, replacing the original multi-level dimension table joins with pre-built materialized views or aggregation tables; concatenating structured query statements based on the target dimension, metric, and aggregation granularity, and adding data aggregation clauses; sending the structured query statements to the underlying database for execution, obtaining the query result dataset, and converting the query result dataset into a data structure format that matches the presentation type requirements for output.

[0019] Preferably, when the ratio of the original table traversal cost to the hierarchical shortcut access cost is greater than a preset cost coefficient, the corresponding connection path is marked as a shortcut replacement state, which includes: calculating the original table traversal cost as the total number of rows in the source table multiplied by the average number of bytes per row, and calculating the hierarchical shortcut access cost as the total number of rows in the corresponding summary materialized view multiplied by the average number of bytes per row; if the ratio of the original table traversal cost to the hierarchical shortcut access cost is greater than the preset cost coefficient, the corresponding connection path is marked as a shortcut replacement state.

[0020] Secondly, the present invention provides an enterprise business data visualization and analysis system, including a processor and a memory, wherein the memory stores computer program instructions, and when the computer program instructions are executed by the processor, the above-mentioned enterprise business data visualization and analysis method is implemented.

[0021] By adopting the above technical solution, a computer program is generated from the above-mentioned enterprise operation data visualization analysis method and stored in the memory so that it can be loaded and executed by the processor. In this way, a terminal device can be made based on the memory and the processor for convenient use.

[0022] The present invention has the following beneficial effects: This invention constructs a weighted relational path graph based on co-occurrence frequency and semantic similarity and calculates path quality scores, which can accurately identify and remove redundant connection paths with low confidence, thereby reducing the complexity and resource consumption of multi-table association calculations in snowflake mode.

[0023] Furthermore, this invention optimizes the data query access path by marking shortcut alternative states and flexibly adjusts the aggregation granularity based on the predicted cardinality of the remaining paths. This reduces the front-end rendering pressure while preventing excessive aggregation from causing loss of details, thereby improving the response speed, result accuracy, and display effect of visualization analysis. Attached Figure Description

[0024] Figure 1 This is a flowchart of a method for visualizing and analyzing enterprise operational data. Figure 2 This is a schematic diagram of high-frequency data bucket distribution and data skew; Figure 3 This is a schematic diagram of the connection path scoring and pruning process. Detailed Implementation

[0025] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are some embodiments of the present invention, but not all embodiments.

[0026] This invention discloses a method for visualizing and analyzing enterprise operating data, referring to... Figure 1 This includes steps S1-S3: S1: Obtain enterprise operating data in snowflake pattern and visualization analysis requests including target dimensions, metrics, and presentation types. Abstract the multi-table join relationships in the enterprise operating data into a weighted relational path graph. The path weights of the relational path graph are determined by the co-occurrence frequency and semantic similarity between tables. Calculate the initial affinity of each join path and determine the initial aggregation granularity and cardinality threshold based on the visualization analysis requests.

[0027] This process uses SQLAlchemy to connect to fact and dimension tables in a database, reads the primary and foreign key relationships of each table to obtain snowflake-patterned enterprise business data, and receives lightweight data exchange format visualization analysis requests from external sources via the FastAPI framework. It parses out the target dimension, metric fields, and scatter plot or line chart presentation type within the request body. A directed graph object is constructed using the NetworkX graph computation library, mapping each data table to graph nodes, primary and foreign key dependencies between tables to graph edges, and abstracting multi-table join relationships into a weighted relational path graph. For calculating path weights, an inner join operation is performed on each pair of tables with primary and foreign key relationships using the pandas library's merge function. The number of successfully joined rows is counted and divided by the total number of rows in the original tables to obtain the co-occurrence frequency between the tables. A pre-trained BERT model is loaded using the transformers library to extract feature vectors from dimension table names and fact table names, and their semantic similarity is calculated.

[0028] The co-occurrence frequency and semantic similarity are summed according to a preset weight coefficient to obtain the path weight, which is then assigned to the edge weight attribute of the directed graph object. All simple path algorithms in the NetworkX library are preferentially called, and a maximum search hop count threshold is set to obtain a set of all candidate connection paths between the starting fact table node and the target dimension table node. The set of candidate connection paths is traversed, and the product of the weights of each edge along the path is used as the initial affinity of the connection path. The initial aggregation granularity and cardinality threshold are determined based on the presentation type in the visualization analysis request using a dictionary key-value pair lookup method. When the presentation type is a line chart, the initial aggregation granularity is set to daily and the cardinality threshold is set to 1000; when the presentation type is a bar chart, the initial aggregation granularity is set to monthly and the cardinality threshold is set to 50.

[0029] In one possible embodiment, acquiring enterprise operating data in snowflake pattern and a visualization analysis request including target dimensions, metrics, and presentation types includes: By establishing a secure connection with the enterprise data warehouse, the metadata of the snowflake-structured data table, which includes fact tables and multi-level dimension tables, is loaded into the memory buffer pool. Parse the front-end message of the visualization analysis request to extract the query conditions, target analysis dimensions, metric fields and expected presentation type; The extracted fields are mapped to the physical tables and columns corresponding to the metadata, generating a visual task queue to be processed.

[0030] Establish an SSL-encrypted secure connection with an enterprise-level data warehouse, such as Snowflake or ClickHouse, using JDBC or ODBC drivers. Execute metadata query commands such as SHOWTABLES and DESCRIBE to extract the table structure, foreign key constraints, column data types, and statistical information of the order fact table and multi-level dimension tables involved in the snowflake structure, such as the product dimension table and related brand sub-dimensional tables. Serialize this information into JSON format and cache it in a 2GB to 8GB memory buffer pool, such as Redis or Caffeine, to meet metadata reading requirements.

[0031] The engine retrieves RESTful API messages sent by the front-end web application and extracts filtering conditions from the request body using a JSON parser. These conditions include parameters such as `time_range="2023-01 to 2023-12"`, target analysis dimensions such as `region_name` and `product_category`, metric fields such as `SUM(sales_amount)`, and the expected chart type, such as `BarChart` or `HeatMap`. The engine then iterates through the extracted analysis fields, performing full-text comparisons or alias associations using a data dictionary in an in-memory buffer. This converts the fields into specific database names, table names, and column names in the underlying database. For example, `product_category` is mapped to `db_retail.dim_product.category_name`. This mapping, along with the timestamp and session ID, is encapsulated into a standard Task object and pushed into a message queue, such as Kafka, to generate a queue of visualization tasks to be processed.

[0032] In one possible embodiment, the multi-table join relationships in enterprise operating data are abstracted into a weighted relational path graph. The path weights of the relational path graph are determined by the co-occurrence frequency and semantic similarity between tables, and the initial affinity of each join path is calculated, including: A relationship path graph is constructed using the fact table and dimension table of enterprise operating data as nodes and foreign key constraints between tables as edges. Count the number of queries that occur simultaneously in both tables of each connection edge in the historical query log, and divide the number of simultaneous queries by the maximum value of the total number of occurrences of the single-end table in both tables to obtain the co-occurrence frequency between dimension attributes; The cosine similarity of word vectors between the field names of the two tables and the annotation text is calculated using a pre-trained natural language processing model as the semantic similarity. After normalizing the semantic similarity to the [0,1] interval, the weighted sum of the similarity and the co-occurrence frequency is used as the path weight of each connection path, and the product of the weights of each edge on the path is used as the initial affinity of each connection path.

[0033] Using a graph computing library in memory, such as NetworkX, a directed weighted graph is initialized. The extracted fact table and the dimension tables at each level are instantiated as nodes in the graph, and the primary and foreign key relationships between the tables are instantiated as directed edges in the graph. The database historical query logs from the past 30 to 90 days are parsed to determine the frequency of co-occurrence of table A (e.g., the order table) and table B (e.g., the user table) in the same SQL JOIN statement, for example, 1500 times. The total independent query counts for table A and table B are also obtained, for example, 2000 and 1800 times respectively. Dividing these two values ​​yields a co-occurrence frequency of 0.75. A pre-trained natural language processing (NLP) model, such as BERT, is loaded. This pre-trained NLP model includes an input layer, a multi-layer bidirectional attention encoder layer, and an output layer. The input to the pre-trained NLP model is a sequence of lexical terms representing the field names and annotation text of both tables. The output is a high-dimensional word vector representing the semantics of the text. The input layer converts the lexical sequence of the text into word embedding vectors, paragraph embedding vectors, and position embedding vectors, and then sums them. The multi-layer bidirectional attention encoder layer identifies the dependencies between lexical terms and calculates attention weights through a multi-head attention mechanism. The output layer extracts global features as high-dimensional word vectors through pooling operations. This model is used to tokenize and encode the column names and DDL annotation text of the associated fields of Table A and Table B, calculate the cosine similarity of the generated high-dimensional vectors, and normalize this cosine similarity to the [0,1] interval using extreme value mapping or the Softmax function, for example, 0.82 as the semantic similarity. Set the weight parameter for co-occurrence frequency, preferably in the range of 0.6 to 0.8, with an example of 0.7. Also set the weight parameter for semantic similarity, with an example of 0.3. Using the weighted summation formula, the result is 0.771, and the weight of a single edge is calculated. For snowflake connection paths containing multiple hops, such as Table A->Table B->Table C, multiply the weights of all edges along the path, resulting in 0.501. The initial affinity strength value of the entire complete connection path is then calculated.

[0034] In one possible embodiment, determining the initial aggregation granularity and cardinality threshold based on a visualization analysis request includes: Analyze the time or hierarchical requirements in the visualization analysis request and extract the smallest grouping unit as the initial aggregation granularity; The minimum value between the total number of non-duplicate records of the target dimension in the corresponding physical table and the maximum rendering limit of the front-end presentation type is used to determine the cardinality threshold for the allowed return results.

[0035] Upon receiving a visualization analysis task, the abstract syntax tree parser analyzes the attribute definitions of dimension fields in the request payload, identifying preset time levels such as "year-quarter-month-day" or geographical levels such as "country-province-city". It extracts the lowest granularity level specified in the query request, for example, extracting the "Day" level from "by day statistics" or the "City" level from "by city statistics", and sets this level as the initial aggregation granularity in the GROUP BY clause of the SQL statement. An estimation algorithm, such as HyperLogLog, is used to estimate the approximate cardinality of the target dimension in the actual physical table, i.e., the total number of unique records. For example, the city dimension might contain 340 unique city nodes. The maximum rendering limit parameters set in the configuration file for different visualization chart types are read; for example, the maximum number of records that can be rendered in the front end of a bar chart is set to 50, and the maximum number of records for a scatter plot is 5000. The estimated cardinality of the dimension physical table (e.g., 340) is compared with the maximum rendering limit of the chart (e.g., 50). The minimum value of the two is taken, which is 50. The cardinality threshold for the current query to return the result set to the front end is calculated to be 50, which serves as a benchmark to prevent browser memory overflow.

[0036] S2 evaluates the connection paths associated with the visualization analysis request. When the proportion of non-null values ​​in the connection key column is lower than the lower bound of participation or the Gini coefficient of the key value distribution is higher than the upper bound of skewness, the corresponding connection path is marked as a low confidence state. When the ratio of the original table traversal cost to the hierarchical shortcut access cost is greater than the preset cost coefficient, the corresponding connection path is marked as a shortcut substitution state. The path quality score of the connection path is generated by combining the initial affinity, the difference between the predicted output cardinality and the cardinality threshold, and the low confidence state marking.

[0037] Traverse all simple paths between the starting fact table and the target dimension table in the NetworkX diagram object, extracting the join key column data for each level of foreign key joins on each path. Check for non-null states, use the SUM function to count the number of non-null values, divide by the total number of rows in the column to obtain the proportion of non-null values, and check if the proportion is lower than the set participation lower bound of 0.8. Count the frequency of each key value in the join key column, such as... Figure 2 As shown, Figure 2The frequency distribution of each key value in the join key column is analyzed. The high-frequency key value '1' dominates the data, while other key values ​​have extremely low proportions. This results in a high Gini coefficient for the key value distribution, triggering the low-confidence flag check rule. The Gini coefficient for the key value distribution is obtained by calculating the average of the absolute differences using the standard Gini coefficient formula from the sorted frequency array and dividing by twice the average. The coefficient is then checked to see if it exceeds the upper bound of skewness (0.6). If the proportion of non-null values ​​is below the lower bound of participation or the Gini coefficient for the key value distribution is above the upper bound of skewness, the low-confidence state of the Boolean variable is set to true by modifying the NetworkX graph edge attribute dictionary.

[0038] The cost of traversing the original table is calculated using database statistics. This cost equals the total number of rows in the source table multiplied by the average number of bytes per row. Then, the cost of accessing the hierarchical shortcut is calculated, which equals the total number of rows in the corresponding summary materialized view multiplied by the average number of bytes per row. The ratio of the cost of traversing the original table to the cost of accessing the hierarchical shortcut is compared. When the ratio is greater than the preset cost coefficient of 3, the Boolean variable "shortcut substitution state" is set to true in the NetworkX graph edge attribute dictionary. The HyperLogLog cardinality estimation algorithm provided by the Redis database is used to quickly evaluate the deduplication cardinality of each table involved in the connection by calling the pfadd and pfcount methods of the Redis client. The deduplication cardinality of each table on the path is estimated by probability multiplication according to the connection conditions to obtain the predicted output cardinality. The path quality score is calculated using mathematical formulas. The initial affinity is used as the base score. When the predicted output base is greater than the base threshold, the difference between the two is calculated and normalized to obtain the difference penalty score. When the predicted output base is not greater than the base threshold, the difference penalty score is zero. If the low confidence state is true, an additional fixed deduction of 0.2 is added. The path quality score is obtained by subtracting the difference penalty score and the fixed deduction from the base score and storing it in the edge attributes of the graph object.

[0039] In one possible implementation, when the proportion of non-null values ​​in the join key column is below the lower bound of participation or the Gini coefficient of the key value distribution is above the upper bound of skewness, the corresponding join path is marked as a low-confidence state, including: Scan the key columns of the data tables at both ends of the connection path to be tested, and calculate the ratio of valid data rows to total rows to obtain the proportion of non-null values; Calculate the Gini coefficient, representing the degree of uneven distribution, based on the frequency of occurrence of each key value in the keyword column; The proportion of non-null values ​​is compared with the preset lower bound of participation, and the Gini coefficient is compared with the preset upper bound of skewness. If either condition is met, the status flag of the corresponding connection path in the data dictionary is updated to the low confidence state.

[0040] During the join path evaluation phase, for each table to be joined, a probe SQL scan is performed on the foreign key column to obtain the number of valid rows with non-null values ​​(e.g., 90,000 rows) and the total number of rows (e.g., 100,000 rows). Dividing these two values ​​by the number of rows yields the proportion of non-null values ​​in the foreign key column, which is estimated to be 0.90. High-frequency data bucket distribution statistics for this foreign key column are extracted. The average of the absolute differences in the frequency of each unique key value is calculated using a formula, and then divided by twice the average to obtain the Gini coefficient of the current key value data distribution. For example, a value of 0.85 indicates that a minority of key values ​​occupy the vast majority of data rows, suggesting a data skew problem. Pre-configured data quality constraint parameters are read from the backend. The lower bound for participation is typically set between 0.65 and 0.80 (0.75 in this example), and the upper bound for skewness is typically set between 0.70 and 0.85 (0.80 in this example). The measured proportion of non-null values ​​(0.90) is compared with the lower bound of participation (0.75), and the Gini coefficient (0.85) is cross-compared with the upper bound of skewness (0.80). Since 0.85 is greater than the upper bound of skewness (0.80) in this example, any blocking trigger condition is met. The engine will update the Boolean status flag of this connection path from false to true in the data dictionary of the graph structure, marking the path as a low-confidence state so that it can be deweighted in subsequent stages.

[0041] In one possible embodiment, the path quality score of the connectivity path is generated by combining the initial affinity, the difference between the predicted output cardinality and the cardinality threshold, and the low-confidence state labeling, including: The cardinality estimation algorithm is called to estimate the size of the result set after performing a multi-table join on the join path. If the corresponding join path has a shortcut substitution status flag, the size of the result set after the shortcut substitution is estimated to obtain the predicted output cardinality. When the predicted output base is greater than the base threshold, the difference between the predicted output base and the base threshold is calculated and the difference is normalized to obtain the difference penalty term. When the predicted output base is not greater than the base threshold, the difference penalty term is zero. The initial score is obtained by subtracting the difference penalty from the initial affinity. Check if there is a low confidence status marker on the corresponding connection path. If so, multiply the initial score by a preset attenuation coefficient to obtain the path quality score.

[0042] The system integrates a query optimizer cost model, which, combined with the selectivity of foreign keys between tables, pre-estimates the number of intermediate result set rows after a multi-table JOIN operation. If the path has triggered a shortcut substitution state, the system queries the row count metadata of the pre-aggregated table, e.g., 150 rows, as the predicted output cardinality; otherwise, it estimates the cardinality according to the original multi-table JOIN logic, e.g., 250 rows. This predicted output cardinality, e.g., 250, is compared with a cardinality threshold, e.g., 195. Since 250 > 195, the excess difference is calculated to be 55. A scaling formula is used to calculate the penalty term, which is the difference divided by the cardinality threshold and the tolerance multiplier, preferably 1.5 to 2. In this example, the product of 2 is used, resulting in a penalty term of 0.141. The initial affinity value calculated in the pre-stage is extracted, e.g., 0.771, and subtracted to obtain 0.630. This is used as the initial score for the join path. Optionally, if the result is less than zero, zero or a very small positive number is taken to obtain the initial score. The system checks whether a low confidence status flag is set in the data dictionary of the current path object. If it exists, a preset attenuation coefficient is called, preferably in the range of 0.3 to 0.5, and set to 0.4 in the example. The initial score is then converted and calculated, and the result is 0.252. The generated and saved value of 0.252 is the path quality score of this connection path.

[0043] S3 removes connection paths with a path quality score lower than the pruning threshold, and adjusts the initial aggregation granularity based on the predicted output cardinality of the remaining connection paths. The larger the predicted output cardinality, the coarser the adjusted aggregation granularity. Generate query aggregation instructions based on the remaining connection paths, shortcut replacement status flags, and aggregation granularity, and execute the query aggregation instructions to obtain a visualized dataset.

[0044] The algorithm iterates through all candidate connection paths in the NetworkX graph object and extracts the path quality score from the edge attributes. When the path quality score is lower than a preset pruning threshold of 0.5, the corresponding connection edge and the resulting path are physically removed. The algorithm then extracts the maximum predicted output cardinality of the remaining connection paths in the graph object. The logarithm is calculated using the log10 function. Based on the step-range mapping rule of the cardinality, the initial aggregation granularity is adjusted. When the cardinality is greater than 3 and the initial aggregation granularity is daily, the granularity is adjusted to monthly; when the cardinality is greater than 4, the granularity is adjusted to yearly. This satisfies the logic that the larger the predicted output cardinality, the coarser the adjusted aggregation granularity, and thus the aggregation granularity is obtained. Figure 3 As shown, Figure 3 The path quality scores of the four candidate connection paths are calculated. The dashed line represents a fixed pruning threshold of 0.5. Paths 1, 2, and 3 meet the threshold and can be retained. Path 4 has a score below the threshold, which meets the criteria for triggering pruning and removal. This distinguishes between usable and non-removable multi-table association paths.

[0045] The SQL query statement is constructed using string concatenation techniques to serve as the query aggregation instruction. The node sequence in the remaining join path is parsed to construct FROM and JOIN clauses. While traversing join edges, the shortcut substitution status flag is checked; if the flag is true, the original dimension table name is replaced with a pre-built materialized view table name in the JOIN clause. When constructing the SELECT clause, the corresponding date truncation function is selected based on the aggregation granularity. The WHERE clause is constructed using the query conditions from the parsed visualization analysis request for data filtering. Grouping is implemented using the GROUP BY clause, and SUM or AVG aggregation functions are applied to the measure fields to generate the query aggregation instruction. A connection to the PostgreSQL data warehouse is established using the psycopg2 database driver package. The generated query aggregation instruction is issued by calling the cursor object's execute method. All returned data rows are extracted using the fetchall method and loaded into a pandas DataFrame two-dimensional table object, thus obtaining the visualization dataset supporting the front-end page rendering.

[0046] In one possible embodiment, removing connection paths with a path quality score below a pruning threshold includes: Sort all connected paths by their path quality scores in descending order; Each path quality score is compared with a preset pruning threshold. When a path quality score is found to be lower than the pruning threshold, the corresponding connected path object is removed from the candidate path list, and the isolated dimension nodes generated in the graph are logically deleted to generate a set of pruned usable paths.

[0047] After all feasible connection paths in the relational path graph have been scored, a priority queue is constructed. The array of connection path objects in memory is sorted in descending order according to the value of the "path quality score" field, forming an ordered list such as [path 1 (0.85), path 2 (0.72), path 3 (0.55), path 4 (0.25)]. The globally preset pruning threshold in the engine is configured within a reasonable range based on statistical distribution, with an optimal range between 0.40 and 0.60. Here, the hard-coded threshold of 0.50 is used in the example. The sorted array elements are traversed one by one using an iterator, and the quality score of each element is compared with 0.50. When the quality score of path 4 is 0.252, which is lower than 0.50, the graph pruning mechanism is triggered, and the connection path object is directly removed from the candidate path list in memory. At the same time, the background starts an isolated node detection mechanism to scan whether an isolated dimension table node with an in-degree or out-degree of 0 has been generated in the graph after the path is removed. If it exists, the activity status flag of the node object is set to false, and the deletion logic is executed. After pruning to filter out invalid or inefficient relationships, a concise "set of available paths" is output as the basis for generating SQL statements.

[0048] In one possible embodiment, a query aggregation instruction is generated based on the remaining connection path, shortcut replacement status flags, and aggregation granularity, and the query aggregation instruction is executed to obtain a visualized dataset, including: Traverse the remaining join paths, and for join paths with shortcut replacement status flags, replace the original multilevel dimension table joins with pre-built materialized views or aggregate tables. Construct a structured query statement based on the target dimension, metric, and aggregation granularity, and add data aggregation clauses; The structured query statement is sent to the underlying database for execution, the query result dataset is obtained, and the query result dataset is converted into a data structure format that matches the presentation type requirements for output.

[0049] The SQL generation engine parses the pruned set of available paths, sequentially checking the metadata attributes of each valid path object. When it finds a path to a fact table related to a dimension table with a shortcut substitution status flag, it automatically queries the dictionary routing table, replacing the original multi-level JOIN syntax with a FROM clause pointing to a pre-built materialized view name, such as `mv_sales_product_summary` or an OLAP wide table, reducing runtime Cartesian product calculation overhead. The engine uses a string template concatenation tool to assemble the target dimension, aggregation metric function (e.g., `SUM(sales_amount)`), and adjusted aggregation granularity (e.g., `DATETRUNC(month, order_date)`) passed from the front end into the SELECT and GROUPBY clauses. It also assembles the query conditions passed from the front end, such as `time_range="2023-01 to 2023-12"`, into the WHERE clause, forming an executable structured query SQL text. The SQL text is asynchronously sent to the compute node for execution via a database connection pool, such as HikariCP. After the result set is returned, the result set parser will reconstruct the tiled two-dimensional table data into a nested JSON array format required by the front-end framework, such as ECharts, according to the expected chart type requirements of the front-end. This array contains independent xAxis arrays and series data objects, and is then output to the visualization application via the HTTP protocol.

[0050] This invention also discloses an enterprise business data visualization and analysis system, including a processor and a memory. The memory stores computer program instructions, and when the computer program instructions are executed by the processor, an enterprise business data visualization and analysis method according to the present invention is implemented.

[0051] The system also includes other components well known to those skilled in the art, such as communication buses and communication interfaces, the settings and functions of which are known in the art and will not be described in detail here.

[0052] In the description of this specification, "multiple" or "several" means at least two, such as two, three or more, unless otherwise expressly and specifically defined.

Claims

1. A method for visualizing and analyzing enterprise operational data, characterized in that: include: S1. Obtain enterprise operating data in snowflake pattern and visualization analysis requests including target dimensions, metrics, and presentation types. Abstract the multi-table join relationships in the enterprise operating data into a weighted relational path graph. The path weights in the relational path graph are determined by the co-occurrence frequency and semantic similarity between tables. Calculate the initial affinity of each join path and determine the initial aggregation granularity and cardinality threshold based on the visualization analysis request. S2. Traverse and evaluate the join paths associated with the visualization analysis request. When the proportion of non-null values ​​in the join key column is lower than the lower bound of participation or the Gini coefficient of the key value distribution is higher than the upper bound of skewness, mark the corresponding join path as low confidence. In the first stage, when the ratio of the cost of traversing the original table to the cost of accessing the hierarchical shortcut is greater than a preset cost coefficient, the corresponding connection path is marked as a shortcut replacement state. The path quality score of the connection path is generated by combining the initial affinity, the difference between the predicted output cardinality and the cardinality threshold, and the low confidence state marking. In the second stage, connection paths with path quality scores lower than the pruning threshold are removed, and the initial aggregation granularity is adjusted based on the predicted output cardinality of the remaining connection paths. The larger the predicted output cardinality, the coarser the adjusted aggregation granularity. Query aggregation instructions are generated based on the remaining connection paths, the shortcut replacement state marking, and the aggregation granularity, and the query aggregation instructions are executed to obtain a visualized dataset.

2. The method according to claim 1, characterized in that, The process of acquiring enterprise operating data in snowflake mode and the visualization analysis request including target dimensions, metrics, and presentation types includes: By establishing a secure connection with the enterprise data warehouse, the metadata of the snowflake-structured data table, which includes fact tables and multi-level dimension tables, is loaded into the memory buffer pool. Parse the front-end message of the visualization analysis request to extract the query conditions, target analysis dimensions, metric fields and expected presentation type; The extracted fields are mapped to the physical tables and columns corresponding to the metadata, generating a visual task queue to be processed.

3. The method according to claim 1, characterized in that, The calculation of the initial affinity for each connection path includes: A relational path graph is constructed using the fact table and dimension table of enterprise operating data as nodes and foreign key constraints between tables as edges. The number of queries that appear simultaneously in both tables of each connection edge in the historical query log is counted. The co-occurrence frequency between dimension attributes is obtained by dividing the number of simultaneous queries by the maximum value of the total number of occurrences of each end table in both ends. The cosine similarity of word vectors between the field names of the two end tables and the annotation text is calculated using a pre-trained natural language processing model as the semantic similarity. The semantic similarity is normalized to the [0,1] interval and then weighted and summed with the co-occurrence frequency as the path weight of each connection path. The weight product of each edge on the path is used as the initial affinity of each connection path.

4. The method according to claim 1, characterized in that, The process of determining the initial aggregation granularity and cardinality threshold based on the visualization analysis request includes: Analyze the time or hierarchical requirements in the visualization analysis request and extract the smallest grouping unit as the initial aggregation granularity; The minimum value between the total number of non-duplicate records of the target dimension in the corresponding physical table and the maximum rendering limit of the front-end presentation type is used to determine the cardinality threshold for the allowed return results.

5. The method according to claim 1, characterized in that, When the proportion of non-null values ​​in the join key column is lower than the lower bound of participation or the Gini coefficient of the key value distribution is higher than the upper bound of skewness, the corresponding join path is marked as a low-confidence state, including: Scan the key columns of the data tables at both ends of the connection path to be tested, and calculate the ratio of valid data rows to total rows to obtain the proportion of non-null values; Calculate the Gini coefficient, representing the degree of uneven distribution, based on the frequency of occurrence of each key value in the keyword column; The proportion of non-null values ​​is compared with the preset lower bound of participation, and the Gini coefficient is compared with the preset upper bound of skewness. If either condition is met, the status flag of the corresponding connection path in the data dictionary is updated to the low confidence state.

6. The method according to claim 1, characterized in that, The process of generating a path quality score for a connection path by integrating the initial affinity, the difference between the predicted output cardinality and the cardinality threshold, and the low-confidence status markers includes: calling a cardinality estimation algorithm to estimate the size of the result set after performing a multi-table join on the connection path; if the corresponding connection path has a shortcut substitution status marker, estimating the size of the result set after shortcut substitution to obtain the predicted output cardinality; when the predicted output cardinality is greater than the cardinality threshold, calculating the difference between the predicted output cardinality and the cardinality threshold and normalizing the difference to obtain a difference penalty term; when the predicted output cardinality is not greater than the cardinality threshold, the difference penalty term is zero, and the initial affinity is subtracted from the difference penalty term to obtain the initial score; checking whether the corresponding connection path has a low-confidence status marker, and if so, multiplying the initial score by a preset attenuation coefficient to obtain the path quality score.

7. The method according to claim 1, characterized in that, The removal of connection paths with a path quality score lower than the pruning threshold includes: Sort all connected paths by their path quality scores in descending order; Each path quality score is compared with a preset pruning threshold. When a path quality score is found to be lower than the pruning threshold, the corresponding connected path object is removed from the candidate path list, and the isolated dimension nodes generated in the graph are logically deleted to generate a set of pruned usable paths.

8. The method according to claim 1, characterized in that, The process of generating query aggregation instructions based on remaining connection paths, shortcut replacement status flags, and aggregation granularity, and then executing these instructions to obtain a visualized dataset, includes: Traverse the remaining join paths, and for join paths with shortcut replacement status flags, replace the original multilevel dimension table joins with pre-built materialized views or aggregate tables. Construct a structured query statement based on the target dimension, metric, and aggregation granularity, and add data aggregation clauses; The structured query statement is sent to the underlying database for execution, the query result dataset is obtained, and the query result dataset is converted into a data structure format that matches the presentation type requirements for output.

9. The method according to claim 1, characterized in that, When the ratio of the original table traversal cost to the hierarchical shortcut access cost is greater than a preset cost coefficient, the corresponding connection path is marked as a shortcut replacement state, including: The cost of traversing the original table is calculated by multiplying the total number of rows in the source table by the average number of bytes per row. The cost of accessing the hierarchical shortcut is calculated by multiplying the total number of rows in the corresponding summary materialized view by the average number of bytes per row. If the ratio of the cost of traversing the original table to the cost of accessing the hierarchical shortcut is greater than the preset cost coefficient, then the corresponding connection path will be marked as a shortcut replacement state.

10. An enterprise operation data visualization and analysis system, characterized in that: include: A processor and a memory, wherein the memory stores computer program instructions that, when executed by the processor, implement the enterprise business data visualization and analysis method according to any one of claims 1-9.

Images