Front-end multi-dimensional data dynamic query and optimization method and device and medium

Abstract

The invention discloses a front-end multi-dimensional data dynamic query and optimization method and device and a medium, and relates to the technical field of front-end data processing. The method comprises the following steps: constructing a dimension rule base, configuring an analytic function, and organizing an association mapping table and index mapping configuration; receiving back-end full data, generating a unique fragmentation identifier based on the combination of time, organization and personnel characteristic values, and establishing a fragmentation index table for pre-fragmentation storage; initializing a double-cache query pool based on an LRU strategy; analyzing a user query condition, and generating target time and an organization range by utilizing the rule base; and directly positioning target data fragments through the fragment index table, and executing data screening, interactive abnormal value processing and index aggregation calculation. According to the method, dimension association development is simplified by utilizing bidirectional mapping logic, and multi-dimensional data query with low resource consumption, high expansibility and high accuracy is realized in combination with configurable index mapping and a double-cache mechanism.

Description

Technical Field

[0001] This invention belongs to the field of front-end data processing technology, specifically relating to a method, device, and medium for dynamic querying and optimization of multi-dimensional front-end data. Background Technology

[0002] In the process of enterprise digital transformation, multi-dimensional structured data is an important basis for core business decisions. This type of data is usually stored in multiple dimensions, such as time (year, quarter, month), organization (including departments and personnel, and the two are bound together), and business (data type, product category). Users need to flexibly select different combinations of dimensions to query the corresponding data according to their actual needs.

[0003] When implementing multi-dimensional data query functions in the current front-end, the following technical pain points are commonly encountered:

[0004] Low data processing efficiency: The backend often returns full, multi-dimensional data that includes multiple years, departments, and personnel relationships. When the frontend queries, if iterates through all the data and filters it, the query response latency often exceeds 500ms when the data volume reaches tens of thousands or more, and may even cause the page to freeze.

[0005] Poor dimensional adaptability and scalability: The logic for linking "department-personnel" (such as filtering corresponding userId by deptId) usually needs to be implemented manually. Adding a new dimension requires modifying the core query code, resulting in high development and maintenance costs, and is prone to causing query result deviations due to errors in the linking logic.

[0006] Waste of resources due to repeated calculations: Under the same query conditions (such as "2025 Q1 + deptId=dept001 + userId=user012 + data type= sales"), the front end needs to repeatedly perform related filtering and indicator aggregation. If an effective caching mechanism is not established, the peak CPU utilization rate can reach more than 60%.

[0007] Abnormal data processing is complex: Existing technologies have cumbersome logic for handling outliers, often relying on complex configurations or hard coding, and are prone to misjudging normal data as abnormal, lacking a flexible human-computer interaction confirmation mechanism.

[0008] Insufficient flexibility in metric mapping: The mapping relationship between business metrics and data fields is mostly hard-coded in the code, which cannot meet the differentiated configuration requirements under different time dimensions (such as monthly and annual), resulting in high business expansion costs. Summary of the Invention

[0009] Purpose of the invention: The purpose of this invention is to address the shortcomings of existing technologies by providing a method, apparatus, and medium for dynamic querying and optimization of multi-dimensional data at the front end, aiming to solve the problems of low front-end query efficiency, complex dimension association logic, high resource consumption, and poor scalability in existing technologies.

[0010] Technical solution: The front-end multi-dimensional data dynamic query and optimization method of the present invention includes the following steps:

[0011] Step S1: Construct a dimension rule base: Configure a parsing rule base on the front end that includes dimension types, parsing functions, dimension mapping tables, aggregation methods, and indicator mapping configurations. The dimension mapping table at least defines the relationship between dimensions at different organizational levels.

[0012] Step S2, Front-end data pre-sharding: Receive the full multi-dimensional structured data returned by the back-end, extract multiple dimensional feature values ​​for each data item, generate a unique sharding identifier based on the combination of the multiple dimensional feature values, store the data in the corresponding sharding set, and establish a sharding index table indexed by the sharding identifier.

[0013] Step S3: Initialize the query cache pool: Create a query cache pool based on the least recently used strategy to store the mapping relationship between query conditions and query results;

[0014] Step S4, Multi-dimensional query parsing: Receive the query conditions input by the user, perform rule matching and dimension transformation according to the dimension rule base, and generate an intermediate query object containing the target time range and the target organizational range;

[0015] Step S5, Data Sharding Query and Aggregation: Generate the corresponding target shard identifier based on the target organizational range in the intermediate query object, directly locate the target data shard through the shard index table, perform filtering, outlier handling and indicator aggregation calculation on the data within the shard, and generate query results.

[0016] To further improve the above technical solution, in step S1, the dimension mapping table includes a first mapping relationship from department identifier to personnel identifier list, a second mapping relationship from personnel identifier to department identifier, and a third mapping relationship from personnel identifier to personnel name;

[0017] In step S4, the dimension transformation includes: if the query condition only contains the department dimension, then all personnel identifiers under the department are obtained through the first mapping relationship to generate the target organization scope; if the query condition only contains the personnel dimension, then the department identifier to which the personnel belongs is obtained through the second mapping relationship to generate the associated value of the target shard identifier.

[0018] Furthermore, in step S2, the dimensional feature values ​​include a time field, a department identifier field, and a personnel identifier field;

[0019] The segment identifier is a string generated by concatenating the time field, organization association field, and personnel identifier field in a preset order;

[0020] The establishment of the shard index table specifically includes: traversing the full data, generating shard identifiers according to the preset order, and storing the data in the shard index table with a hash table structure, where the key is the shard identifier and the value is the corresponding data set and the last update time.

[0021] Furthermore, in step S1, the indicator mapping configuration supports differentiated configuration based on the time dimension;

[0022] The differentiated configuration includes: defining a mapping relationship between the first set of business indicator names and data field names for the first type of time granularity nodes, and defining a mapping relationship between the second set of business indicator names and data field names for the second type of time granularity nodes;

[0023] In step S4, the rule matching step further includes: matching the corresponding mapping relationship from the indicator mapping configuration according to the time dimension type in the query conditions.

[0024] Furthermore, the dimensional rule base is also associated with an outlier threshold configuration file; in step S5, the outlier processing includes the following steps:

[0025] Read the outlier threshold configuration file associated with the dimension rule base;

[0026] Traverse the filtered valid data and mark data whose indicator field values ​​exceed the preset range of the threshold configuration file as suspected abnormal data;

[0027] Generate an interactive interface containing the suspected abnormal data prompt information, and listen for user input processing instructions;

[0028] In response to receiving the first processing instruction, the suspected abnormal data is excluded in subsequent indicator aggregation calculations;

[0029] In response to receiving the second processing instruction, the suspected abnormal data is retained in subsequent indicator aggregation calculations.

[0030] Furthermore, in step S3, the query cache pool adopts a dual-caching storage mechanism:

[0031] Store the complete cache data for frequently queried data in memory, and store a simplified version of the cache data containing the cache key and the result hash value in the local persistent storage space;

[0032] When the page is refreshed or initialized, it is loaded from the memory first; if there is no data in the memory, it is restored from the local persistent storage space.

[0033] Furthermore, the method also includes a dynamic adaptation and update step: when an update of multi-dimensional structured data in the backend is detected, an incremental update mechanism is triggered through the frontend state management module to regenerate only the shard set corresponding to the shard identifier containing the updated data, update the shard index table, and actively invalidate the query cache associated with the shard identifier.

[0034] This invention also provides a front-end multi-dimensional data dynamic query and optimization device, including:

[0035] The rule configuration module is used to build a dimensional rule library, which includes dimensional types, parsing functions, dimensional mapping tables, aggregation methods, and indicator mapping configurations.

[0036] The data sharding module is used to receive the full amount of multi-dimensional structured data, extract multiple dimensional feature values ​​of each data, generate a unique sharding identifier based on the combination of the multiple dimensional feature values, and establish a sharding index table with the sharding identifier as the index.

[0037] The cache management module is used to initialize a query cache pool based on the least recently used strategy;

[0038] The query parsing module is used to receive query conditions, perform rule matching and dimension transformation, and generate intermediate query objects.

[0039] The data aggregation module is used to generate target shard identifiers based on intermediate query objects, locate data shards through the shard index table, and perform data filtering, outlier handling, and indicator aggregation calculations.

[0040] The present invention also provides a computer-readable storage medium having a computer program stored thereon that, when executed by a processor, implements the method described above.

[0041] Beneficial effects: Compared with the prior art, the advantages of the present invention are as follows:

[0042] In existing technologies, the front-end typically serves only as a data presentation layer or performs simple array traversal (Array.filter) filtering, making it difficult to handle complex queries of multi-dimensional dynamic business data. This invention innovatively constructs a complete lightweight query engine within the limited computing resources of a browser. Unlike simple "data grouping (Group By)" or "common knowledge piecing together," this invention uses a "data pre-sharding + shard indexing" mechanism to discretize the entire dataset into small sets (buckets) indexed by unique shard identifiers (Keys). It utilizes a "parsing rule base" as the control center to drive the execution of query logic, achieving deep decoupling between query logic and data storage. This allows the front-end to perform "execution plan parsing" and "precise index positioning" like a database. Queries do not require traversing the entire dataset; only the target shard needs to be located. In actual tests with tens of thousands of data points, the query response time was reduced from over 500ms in traditional solutions to less than 40ms, significantly improving query efficiency and achieving millisecond-level response times independent of the back-end.

[0043] In multi-dimensional data queries, there is a common technical pain point: the inconsistency between the "query granularity (e.g., department)" and the "storage granularity (e.g., personnel)". Traditional methods often require traversing the entire dataset to determine the attribution relationship for each record. This invention, based on the dimension mapping table (especially the bidirectional mapping table) in the dimension rule base, achieves automated conversion of "department-personnel" associations. It can automatically "expand" a user's fuzzy range query (e.g., querying a specific department) into a set of precise target shard identifiers (e.g., the shard key set of all personnel under that department) through the rule base, thereby directly hitting a specific shard in the hash table. This synergistic effect of "parsing the rule base" and "shard index" transforms the original O(N) complexity hierarchical association query into an O(1) or O(k) hash lookup (where k is the number of shards). Front-end development does not require hardcoding complex filtering logic; whether it is "searching for a department to find a person" or "searching for a person to find a department", it can be automatically adapted, greatly reducing development and maintenance costs and CPU computation overhead.

[0044] To address the resource waste caused by redundant calculations in multi-dimensional queries, a combination of the LRU (Least Recently Used) strategy and a dual-caching storage mechanism (memory + LocalStorage) is used. This not only avoids repeated aggregation calculations for the same query conditions but also solves the cold start problem after page refreshes through local persistent storage. The system prioritizes loading cached results from memory or local storage, effectively reducing peak CPU usage (from 60% to below 15%) and ensuring a smooth user experience in scenarios with frequent interactions and page refreshes.

[0045] Adopt an exception handling solution of "preset threshold + interactive confirmation" and an index mapping structure that supports differentiated configuration by time dimension. Correct algorithm misjudgments through human-computer interaction, which not only ensures the accuracy of data cleaning but also simplifies the code logic for exception judgment; in terms of system scalability, adding new business types or metrics only requires expanding the configuration file without intruding on and modifying the core query and sharding logic, greatly reducing the system adaptation cost and regression test risk. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] Figure 1 is a flowchart of the front-end multi-dimensional data dynamic query and optimization method provided by the present invention;

[0047] Figure 2 is a schematic diagram of the specific path obtained by querying in Embodiment 1 of the present invention.

[0048] Figure 3 is a principle block diagram of the front-end multi-dimensional data dynamic query and optimization device provided by the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0049] The technical solution of the present invention will be described in detail below with reference to the accompanying drawings, but the protection scope of the present invention is not limited to the described embodiments.

[0050] Embodiment 1: As Figure 1 shown, the present invention provides a front-end multi-dimensional data dynamic query and optimization method, which mainly includes the following steps:

[0051] Step S1, Configuration of the dimension rule library

[0052] Construct a multi-dimensional parsing rule library, which is the basis of the entire query logic. The rule library includes five core fields: dimension type, parsing function, dimension mapping table, aggregation method, and index mapping. The preset dimension types and corresponding configurations are as follows:

[0053] 1. Time dimension:

[0054] It includes three sub-types: year (year), quarter (quarter), and month (month);

[0055] Parsing function: Implement the conversion from dimension value to time range based on a date processing tool (such as moment.js);

[0056] Dimension mapping table: Store the corresponding relationship between time dimension values and Chinese names (such as month:1 → "January", quarter:1 → "First Quarter");

[0057] Aggregation methods: Preset "sum" (year / quarter dimension, summarizing data for subordinate time units) and "direct" (month dimension, directly extracting data for a single month);

[0058] 2. Organizational Relationship Dimension:

[0059] It includes two subcategories: dept (department, identified by deptId) and user (personnel, identified by userId).

[0060] Establish a dimension mapping table, specifically including three sub-mapping tables: establish a two-way association between "department-personnel" and store the mapping between personnel name and ID;

[0061] deptUserMap: A mapping from deptId to a list of userIds;

[0062] userDeptMap: A mapping from userId to deptId;

[0063] userNameMap: A mapping from userId to user name.

[0064] The mapping table above supports bidirectional association queries between "departments" and "personnel".

[0065] 3. Business Dimension:

[0066] Includes dataType (data type), supporting business scenarios such as kpi, opportunity, efficiency, and budget;

[0067] Parsing functions: enable precise matching of data types (e.g., dataType=sales → filter sales data);

[0068] Dimension mapping table: Stores the correspondence between data types and business scenarios (e.g., kpi: "KPI target management", opportunity: "business opportunity management").

[0069] Aggregation method: Preset "direct" (precisely matches data types and extracts corresponding business data).

[0070] 4. Indicator mapping configuration:

[0071] It adopts a configurable structure to define the mapping relationship between "business indicator name → data field name" under different business types, and supports differentiated configuration by time dimension (monthly / annual).

[0072] Configuration features: When adding new business types or metrics, only the configuration file needs to be extended, without modifying the core query logic;

[0073] 5. Outlier handling configuration:

[0074] Associate the threshold configuration file path for outlier detection in the rule base (e.g., " / config / abnormalThreshold.json") and define the normal value range of the basic indicators for each data type.

[0075] Step S2: Front-end data pre-sharding and storage

[0076] Receive the full, multi-dimensional structured data returned by the backend (usually stored in the frontend state management module, such as Pinia's targetList). The data must contain at least the following fields:

[0077] Time fields: targetYear (year), targetMonth (month);

[0078] Organization-related fields: deptId (department identifier), userId (user identifier);

[0079] Business field: targetSource (data type, corresponding to dataType);

[0080] Basic indicator fields: oneT1~oneT8 (the original fields corresponding to different business indicators).

[0081] Generate a sharding key using the combination "targetYear+deptId+userId" (example format: "2025_dept001_user012"), and then perform the following operations:

[0082] Generate sharding keys: Iterate through all the data in targetList, read the targetYear, deptId, and userId fields of each data entry, and generate sharding keys in the format of "targetYear_deptId_userId" to ensure that each "year-department-person" combination corresponds to a unique sharding key;

[0083] Storing sharded data: Create a sharded collection with an array structure, and store each piece of data into the collection corresponding to the shard key, forming a mapping relationship of "shard key - data collection". For example, the data collection corresponding to "2025_dept001_user012" contains all the kpi and opportunity type data records of this user in 2025.

[0084] Create a sharded index table: Store the sharded index in the form of a hash table (Map), with the key being the shard key and the value being the corresponding data set and the last update time (lastUpdated). When querying, the target data set can be located directly by the shard key without traversing the entire targetList data.

[0085] Incremental update mechanism: When backend data is updated, the targetList is changed through the Pinia state management tool. The frontend only reprocesses the collection corresponding to the shard key containing the updated data (identified by the isUpdated=true flag pushed by the backend), avoiding full shard reconstruction.

[0086] Step S3: Query cache pool initialization

[0087] Create a query cache pool based on the LRU (Least Recently Used) strategy to store the mapping relationship between query conditions and results, avoiding duplicate calculations.

[0088] Cache key generation: The JSON.stringify() method is used to filter out unnecessary fields from the query conditions (including dimension type, dimension value, targetYear, deptId, userId, targetSource) and convert them into a unique string as the cache key. Example: '{"dimType":"dept-user","dimValue":{"deptId":"dept001","userId":"user012"},"targetYear":2025,"targetSource":"kpi"}';

[0089] Cache capacity setting: The default maximum cache capacity is 50 entries (to adapt to Chrome browser memory limits) to avoid excessive caching leading to memory overflow;

[0090] Cache invalidation policy:

[0091] Active invalidation: When shard data is updated, delete the cached values ​​corresponding to all cache keys associated with that shard key;

[0092] Passive invalidation: Add a cache timestamp (cacheTimestamp) and an expiration period (30 minutes for departmental dimensions and 15 minutes for personnel dimensions) to the cached value. If the cache expires during a query, the query will be re-executed.

[0093] Dual-caching storage: Cache data from the most frequently queried queries in the past hour is stored in memory, while a simplified version of all cached data (including cache key, cache timestamp, and result hash) is stored in localStorage. When the page is refreshed, data is loaded from memory first, and if there is no data in memory, it is restored from localStorage.

[0094] Step S4: Multi-dimensional query analysis

[0095] Receive user-input query conditions (generated by selecting time type, time range, department / personnel, and business type through the front-end interface), and perform parsing using the dimension rule base from step S1. Specific steps:

[0096] Validity check: Check whether the query conditions include dimension type (dimType), dimension value (dimValue), targetYear (year), targetSource (data type), and at least one organization-related field (deptId or userId). If missing, a pop-up window will prompt the user to complete the query.

[0097] Rule matching: Based on the dimension type of the query, match the corresponding parsing function, dimension mapping table, aggregation method and indicator mapping configuration from the dimension rule library. Example: When querying the "Department-Month-KPI" dimension, match the parsing function of the month dimension, deptUserMap of orgMapping, sum aggregation method and indicatorMapping.monthly.kpi indicator mapping.

[0098] Dimensional transformation:

[0099] Time dimension conversion: Call the getCurrentTimeRange function to convert the user-input dateString into a specific time range (startTime, endTime) and the corresponding time unit (e.g., month:5, quarter:2).

[0100] Organizational Relationship Dimension Transformation:

[0101] If the query condition is "department dimension" (only deptId is entered): all userIds corresponding to the deptId are obtained through orgMapping.deptUserMap, and a "userId range" is generated as the target range (targetRange). At the same time, the corresponding list of personnel names is obtained through userNameMap.

[0102] If the query condition is "personnel dimension" (only userId is entered): use orgMapping.userDeptMap to obtain the deptId to which the userId belongs, generate "deptId associated value" as the target range, and at the same time obtain the personnel name;

[0103] If the query condition is "department-personnel dimension" (enter both deptId and userId): verify the validity of the association between the two (e.g., whether user012 belongs to dept001). If valid, directly use "deptId+userId" as the target range.

[0104] Business dimension conversion: Match the corresponding metric mapping configuration based on targetSource (e.g., targetSource=kpi → match monthly.kpi);

[0105] Output intermediate results: Generate intermediate objects containing targetYear (target year), timeRange (time range), targetRange (organizational target range), aggregate (aggregation method), indicatorMap (indicator mapping configuration), and abnormalConfigPath (abnormal value configuration file path) for subsequent data querying.

[0106] The above steps embody the core "parsing-transformation-fragmentation positioning" engine logic of this invention, and its technological advancement lies in solving the problem of front-end inefficient processing of hierarchical related data. The specific mechanism is as follows:

[0107] In actual business scenarios, the query conditions entered by users (such as "query department A") are often coarser than the storage dimensions of the underlying data (such as "data of employee B and employee C"). Traditional front-end processing must traverse all employee data and check whether each employee belongs to department A.

[0108] The parsing rule base (especially mapping tables such as deptUserMap) built in step S1 acts as a bridge between "query intent" and "physical storage". In step S4, the engine does not simply record the query conditions, but uses the mapping table to perform "dimensional drill-down" logic - that is, it actively converts the coarse-grained condition of "department ID" into a list of all valid "personnel IDs" under that department in real time through the mapping relationship in memory.

[0109] Step S5: Data Query and Aggregation by Shard

[0110] Based on the intermediate results output in step S4, and combined with the deptId and userId in the query conditions, data filtering, outlier handling, and metric aggregation are performed. Specific steps are as follows:

[0111] Sharding location: Generate a sharding key using "targetYear+deptId+userId" (or a combination thereof), and retrieve the target data shard from the sharding index table in step S2. Example: When querying the department dimension, locate all shards under the prefix "targetYear+deptId+*".

[0112] Data filtering: Traverse the target data shards and filter out valid data that "matches the query conditions", "is within the timeRange", and "has dimension values ​​within the targetRange". Exclude invalid data that is empty for targetYear, invalid for deptId / userId, or mismatched for targetSource. Record the number of invalid data entries and the reason (e.g., "userId=user999 is not in the user list of dept001").

[0113] Outlier handling:

[0114] Read the configuration file: Load the abnormal value threshold configuration of the corresponding targetSource through abnormalConfigPath in the intermediate result (such as the normal range of "basic collection indicator" under the kpi type is 5000-200000).

[0115] Detecting suspected outliers: Iterate through the filtered valid data and obtain the original field corresponding to the indicator based on the indicatorMap mapping relationship (e.g., "Basic Indicator of Payment Collection" → oneT2). If the field value exceeds the preset range (e.g., oneT2=250000), it is marked as a suspected outlier.

[0116] Manual confirmation: The front-end pop-up displays information on suspected outliers (including indicator name, original field name, indicator value, normal range, user / department, and time range), providing two options: "Confirm Outlier (Exclude Calculation)" and "Confirm Normal (Retain Calculation)," which users can manually judge.

[0117] Log reporting: After user confirmation, the abnormal value information (including the processing result) is reported to the backend log system to facilitate business review;

[0118] Indicator aggregation:

[0119] If the aggregation method is "direct" (personnel dimension / monthly dimension): extract the indicator field value corresponding to the userId based on the indicatorMap (e.g., oneT1 → "order target indicator");

[0120] If the aggregation method is "sum" (department dimension / quarterly / annual dimension): accumulate the indicator field values ​​of all valid data within the target range (excluding user-confirmed outliers), and treat null values ​​as 0 during the accumulation process;

[0121] Metric name mapping: Based on indicatorMap, the original field names are converted into unified business metric names (such as oneT1 → "Order Target Metric", oneT2 → "Basic Receivables Metric") to adapt to front-end rendering requirements.

[0122] In step S5, the system uses the fine-grained "personnel ID" obtained after transformation, combined with the time dimension, to pre-generate a corresponding set of "target shard identifiers". This means that the query engine already knows precisely which specific hash buckets the data is stored in before accessing the data storage layer (shard index table).

[0123] This mechanism enables close collaboration between the "rule base" and the "index table": the rule base handles logical calculations (converting fuzzy intentions into precise coordinates), while the index table handles physical access. Through this collaboration, the system successfully avoids linear scanning of the entire dataset, achieving precise "point-and-shoot" positioning—an effect that simple data sharding or simple rule configuration cannot achieve independently.

[0124] Step S6: Query result caching and formatting

[0125] Result formatting: Generate a JSON object containing the business metric name, metric value, time range, department / person, and result description (e.g., "One manually confirmed outlier has been excluded").

[0126] Cache storage: Call the cacheResult function to store the cache key corresponding to the query condition and the formatted result (cache value) into the query cache pool of step S3, and update the cache timestamp at the same time;

[0127] Result Return: Returns the formatted JSON result to the front-end component, which can be directly used for table display, line chart / bar chart rendering, and other scenarios without additional format conversion.

[0128] Step S7, Dynamic Adaptation and Update

[0129] Query condition change: When a user switches the query dimension (e.g., from department to personnel) or modifies the dimension value, targetYear, deptId, userId, or targetSource, repeat steps S4 to S6 to retrieve and return the latest query results.

[0130] Data Update: When the backend multi-dimensional structured data is updated, the targetList is changed through the Pinia state management tool, the incremental update logic in step S2 is re-executed, the old shard data and shard index table are overwritten, and the associated cache is actively invalidated at the same time.

[0131] Dimension Expansion: When adding new query dimensions (such as the weekly timeWeek dimension, the region dimension) or new business types (such as adding the "Budget Management" business), you only need to:

[0132] Add a new dimension: Add the type definition, parsing function, dimension mapping table, and aggregation method to the dimension rule base in step S1;

[0133] Add new business types: Expand the corresponding business's indicator mapping relationship in the indicator mapping configuration (e.g., add a monthly indicator mapping of type "budget");

[0134] Without modifying core logic such as sharded storage, cache pool, and aggregation calculation, it can support query functions for new dimensions / business types.

[0135] The effects and advantages of this embodiment:

[0136] 1. Significantly improved query efficiency: Through the "data pre-sharding + sharded indexing" mechanism, queries do not need to traverse the entire dataset. With tens of thousands of data points, the query response time is reduced from more than 500ms in the traditional solution to less than 40ms, improving efficiency by 92%.

[0137] 2. Simplified Dimension Association Logic: Based on a two-way mapping table between deptId and userId, the system automatically converts the "department-personnel" association, while also supporting the association query between personnel name and ID. No manual coding of the association logic is required, reducing development and maintenance costs by 75%.

[0138] 3. Resource utilization optimization: The LRU cache pool reduces redundant calculations, and the dual-caching storage mechanism improves the efficiency of the first query after page refresh, reducing the peak CPU utilization from 60% to below 15%;

[0139] 4. Low technical implementation threshold: Outlier handling adopts a simplified solution of "preset threshold + manual confirmation", which is simple and easy to operate and can be implemented without professional knowledge;

[0140] 5. Strong business scalability: The metric mapping adopts a configurable design. When adding new business types or metrics, only the configuration file needs to be extended, without modifying the core code, reducing adaptation costs by 80%.

[0141] 6. High accuracy of query results: Outliers are manually confirmed to avoid result deviations caused by misjudgment, and the accuracy of query results reaches over 98%. It also supports the tracing and review of abnormal data.

[0142] Example 2: Figure 2 As shown, this embodiment provides a query scenario of "department dimension - quarter - business opportunity (Opportunity)".

[0143] 1. Input search criteria

[0144] Users select the following conditions on the front-end interface:

[0145] Organizational dimension: deptId = dept002 (East China Sales Department), userId is empty (indicating a query for the entire department's data);

[0146] Timeframe: 2025-Q1 (First Quarter of 2025);

[0147] Business type: targetSource = opportunity (opportunity management).

[0148] 2. Detailed Explanation of Execution Steps

[0149] S4, Multi-dimensional Query Analysis

[0150] Rule matching: Based on the input conditions, the system automatically matches the parsing function of quarter, the indicator mapping configuration of opportunity in the rule base, and the aggregation method of sum (because the query is at the department level, it is necessary to summarize the data of subordinate personnel).

[0151] Time frame conversion: The `quarter` parsing function is called to convert 2025-Q1 into a specific time range.

[0152] startTime: "2025-01-01"

[0153] endTime: "2025-03-31"

[0154] Organizational affiliation conversion (key steps):

[0155] If the system detects that the query only contains deptId, it calls the deptUserMap (first mapping relationship) in the dimension rule base.

[0156] Retrieve a list of all valid user IDs under dept002: [user088, user089, user090];

[0157] Generate target organization range: dept002 + [user088, user089, user090].

[0158] S5, Sharded Data Query and Aggregation

[0159] Segmentation and positioning:

[0160] Based on the list of personnel within the target organization, the system automatically generates a set of target segment identifiers:

[0161] 2025_dept002_user088

[0162] 2025_dept002_user089

[0163] 2025_dept002_user090

[0164] The three shard collections are located in parallel using a shard index table (Map), eliminating the need to traverse the entire dataset.

[0165] Data filtering: In the above three shards, filter data records where targetSource = opportunity and the time is between 2025-01-01 and 2025-03-31.

[0166] Interactive outlier handling (excluding scenarios):

[0167] Read the exception threshold configuration of type opportunity (e.g., "Expected opportunity amount" oneT1 threshold upper limit is 1,000,000).

[0168] Detection: user089 was found to have a record in February with oneT1 = 5,000,000 (far exceeding the threshold).

[0169] Interaction: The front end pops up an error message box to display the abnormal data.

[0170] Instruction: After verification, the user clicks the "Confirm Anomaly (Exclude Calculation)" button (i.e., sends the first processing instruction).

[0171] Handling: During subsequent aggregation, the system will mark the record with 5,000,000 as invalid and exclude it from the summary.

[0172] Indicator aggregation (SUM mode):

[0173] Perform cumulative calculation on all valid data for user088 and user090, and valid data for user089 excluding abnormal data:

[0174] oneT1 (estimated business opportunity amount) cumulative result: 850,000;

[0175] The cumulative result of oneT3 (weighted win probability) is 620,000.

[0176] In this scenario, the user queries "East China Sales Department (dept002)," while the underlying data is stored in shards based on "personnel (user088, etc.)." Using existing conventional techniques, the front-end would need to traverse the entire targetList (assuming 10,000 records) and check if the deptId of each record equals dept002, resulting in a massive computational burden.

[0177] By employing the technical solution of this invention, through the aforementioned "dimensional transformation logic," the system directly identifies three specific sharding keys (e.g., 2025_dept002_user088). The query process is transformed from "finding a needle in a haystack" to "directly retrieving these three shard sets." This strategy of "finding the key by dimension and retrieving the value by key" based on a mapping table is the key technical means that distinguishes this invention from commonly known data processing methods.

[0178] S6. Result Processing and Caching

[0179] Name mapping: Based on the mapping configuration of opportunity, oneT1 is converted to "total estimated business opportunities for the department", and oneT3 is converted to "weighted forecast amount".

[0180] Formatting: Generate the final JSON object and mark the resultNote field with: "1 confirmed exception data excluded".

[0181] Cache storage: Generate cache keys (including features such as dept002, 2025-Q1, etc.) and store the results in an LRU cache pool to facilitate quick rollback when switching between department / personnel views.

[0182] Example 3: As Figure 3 As shown, this embodiment provides a front-end multi-dimensional data dynamic query and optimization device for implementing the method of Embodiment 1, including:

[0183] Rule configuration module: Used to build the dimensional rule library described in S1.

[0184] Data sharding module: used to perform data pre-sharding and index creation as described in S2.

[0185] Cache management module: Used to perform cache initialization and management as described in S3 and S6.

[0186] Query parsing module: Used to perform query condition parsing and transformation as described in S4.

[0187] Data aggregation module: used to perform the segmentation location, filtering, interactive exception handling and aggregation calculation described in S5.

[0188] The modules mentioned above can be computer program instructions stored in memory and executed by a processor.

[0189] Example 4: This example provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the method described in Example 1.

[0190] As described above, although the invention has been shown and described with reference to specific preferred embodiments, it should not be construed as limiting the invention itself. Various changes in form and detail may be made without departing from the spirit and scope of the invention as defined in the appended claims.

Claims

1. A method for dynamic querying and optimization of multi-dimensional front-end data, characterized in that, Includes the following steps: Step S1: Construct a dimension rule base: Configure a parsing rule base on the front end that includes dimension types, parsing functions, dimension mapping tables, aggregation methods, and indicator mapping configurations. The dimension mapping table at least defines the relationship between dimensions at different organizational levels. Step S2, Front-end data pre-sharding: Receive the full multi-dimensional structured data returned by the back-end, extract multiple dimensional feature values ​​for each data item, generate a unique sharding identifier based on the combination of the multiple dimensional feature values, store the data in the corresponding sharding set, and establish a sharding index table indexed by the sharding identifier. Step S3: Initialize the query cache pool: Create a query cache pool based on the least recently used strategy to store the mapping relationship between query conditions and query results; Step S4, Multi-dimensional query parsing: Receive the query conditions input by the user, perform rule matching and dimension transformation according to the dimension rule base, and generate an intermediate query object containing the target time range and the target organizational range; Step S5, Data Sharding Query and Aggregation: Generate the corresponding target shard identifier based on the target organizational range in the intermediate query object, directly locate the target data shard through the shard index table, perform filtering, outlier handling and indicator aggregation calculation on the data within the shard, and generate query results.

2. The front-end multi-dimensional data dynamic query and optimization method according to claim 1, characterized in that, In step S1, the dimension mapping table includes a first mapping relationship from department identifiers to a list of personnel identifiers, a second mapping relationship from personnel identifiers to department identifiers, and a third mapping relationship from personnel identifiers to personnel names; In step S4, the dimension transformation includes: if the query condition only contains the department dimension, then obtain all personnel identifiers under the department through the first mapping relationship to generate the target organization scope; If the query conditions only include the personnel dimension, the department identifier to which the personnel belongs is obtained through the second mapping relationship to generate the associated value of the target shard identifier.

3. The front-end multi-dimensional data dynamic query and optimization method according to claim 1, characterized in that, In step S2, the dimensional feature values ​​include a time field, a department identifier field, and a personnel identifier field; The segment identifier is a string generated by concatenating the time field, organization association field, and personnel identifier field in a preset order; The establishment of the shard index table specifically includes: traversing the full data, generating shard identifiers according to the preset order, and storing the data in the shard index table with a hash table structure, where the key is the shard identifier and the value is the corresponding data set and the last update time.

4. The front-end multi-dimensional data dynamic query and optimization method according to claim 1, characterized in that, In step S1, the indicator mapping configuration supports differentiated configuration based on the time dimension; The differentiated configuration includes: defining a mapping relationship between the first set of business indicator names and data field names for the first type of time granularity nodes, and defining a mapping relationship between the second set of business indicator names and data field names for the second type of time granularity nodes; In step S4, the rule matching step further includes: matching the corresponding mapping relationship from the indicator mapping configuration according to the time dimension type in the query conditions.

5. The front-end multi-dimensional data dynamic query and optimization method according to claim 1, characterized in that, The dimensional rule base is also associated with an outlier threshold configuration file; in step S5, the outlier processing includes the following steps: Read the outlier threshold configuration file associated with the dimension rule base; Traverse the filtered valid data and mark data whose indicator field values ​​exceed the preset range of the threshold configuration file as suspected abnormal data; Generate an interactive interface containing the suspected abnormal data prompt information, and listen for user input processing instructions; In response to receiving the first processing instruction, the suspected abnormal data is excluded in subsequent indicator aggregation calculations; In response to receiving the second processing instruction, the suspected abnormal data is retained in subsequent indicator aggregation calculations.

6. The front-end multi-dimensional data dynamic query and optimization method according to claim 1, characterized in that, In step S3, the query cache pool adopts a dual-caching storage mechanism: Store the complete cache data for frequently queried data in memory, and store a simplified version of the cache data containing the cache key and the result hash value in the local persistent storage space; When the page is refreshed or initialized, it is loaded from the memory first; if there is no data in the memory, it is restored from the local persistent storage space.

7. The front-end multi-dimensional data dynamic query and optimization method according to claim 1, characterized in that, The method also includes dynamic adaptation and update steps: When an update to multi-dimensional structured data in the backend is detected, an incremental update mechanism is triggered through the frontend state management module. Only the shard set corresponding to the shard identifier containing the updated data is regenerated, the shard index table is updated, and the query cache associated with the shard identifier is actively invalidated.

8. A front-end multi-dimensional data dynamic query and optimization device, characterized in that, include: The rule configuration module is used to build a dimensional rule library, which includes dimensional types, parsing functions, dimensional mapping tables, aggregation methods, and indicator mapping configurations. The data sharding module is used to receive the full amount of multi-dimensional structured data, extract multiple dimensional feature values ​​of each data, generate a unique sharding identifier based on the combination of the multiple dimensional feature values, and establish a sharding index table with the sharding identifier as the index. The cache management module is used to initialize a query cache pool based on the least recently used strategy; The query parsing module is used to receive query conditions, perform rule matching and dimension transformation, and generate intermediate query objects. The data aggregation module is used to generate target shard identifiers based on intermediate query objects, locate data shards through the shard index table, and perform data filtering, outlier handling, and indicator aggregation calculations.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the front-end multi-dimensional data dynamic query and optimization method as described in any one of claims 1 to 7.

Images