Method for managing and querying same index of multi-fact table based on index center

Abstract

The application relates to a management and query method for the same index of multi-fact tables based on an index center, which performs index definition on the same indexes of various source tables, and arranges the various source tables according to the data volume scale from small to large; according to the index query condition input by a user, the query dimension is acquired, and the source table matched with the query dimension is found and taken as a target source table; then, index query is carried out based on the target source table, and the corresponding index calculation result is returned. According to the screening dimension range of the index query condition, the available analysis dimensions on the various source tables are dynamically matched once, the current source table is selected to carry out data query when the matching succeeds, the best source table meeting the business requirement is found, and the index query efficiency is improved. The index platform does not need to create multiple indexes to respectively map different fact tables, one index is used for mapping, the query table is positioned according to the query condition and a preset table acceleration sequence, and the index management is simpler, and the index analysis is more intelligent.

Description

Technical Field

[0001] This disclosure relates to the field of table lookup technology, and in particular to a method for managing and querying the same indicator in multiple fact tables based on an indicator center, a device for managing and querying the same indicator in multiple fact tables based on an indicator center, and an electronic device. Background Technology

[0002] As attached Figure 1 As shown, during the data warehouse construction process, there will generally be multiple fact tables based on the processed detail tables (DWD or DWS). The only difference between these multiple fact tables is the range of dimensions, but the list of indicators is the same.

[0003] Although subsequent table lookup management can be performed on various fact tables based on different indicators, the following technical shortcomings still exist in practical applications:

[0004] First, in the process of generating multiple fact tables based on the detail table, the indicator platform needs to create multiple indicators and map them to different fact tables. This will make the indicator data too cumbersome, resulting in a slow subsequent table lookup process and low table lookup efficiency.

[0005] Secondly, the metrics design of multiple fact tables lacks unified correlation, so queries can only be performed according to the specified table, thus failing to achieve orderly and accelerated query services. If a fact table with a large data volume is encountered, and the required metric data is not found, it will delay the subsequent table query progress. Alternatively, prioritizing the query of the fact table with the same metric and a smaller data volume is the best path, but in reality, users cannot accurately find the best path. Summary of the Invention

[0006] To address the aforementioned issues, this application proposes a method for managing and querying the same indicator across multiple fact tables based on an indicator center, a device for managing and querying the same indicator across multiple fact tables based on an indicator center, and an electronic device.

[0007] This application proposes a method for managing and querying the same indicator across multiple fact tables based on an indicator center, comprising the following steps:

[0008] S1. Define the same metrics for each source table and arrange the source tables in ascending order of data volume;

[0009] S2. Obtain the query dimension of the indicator query conditions input by the user, traverse and find the source table that matches the query dimension, and use it as the target source table;

[0010] S3. Query the indicators based on the target source table and return the corresponding indicator calculation results.

[0011] As an optional implementation of this application, optionally, S1, the same indicators in each source table are defined, and the source tables are arranged in ascending order of data volume, including:

[0012] Define the indicator name and business definition;

[0013] Retrieve the corresponding source tables from the data warehouse, which contain the specified indicator names and business definitions.

[0014] The source tables are grouped according to the analysis dimensions;

[0015] The source tables after grouping are arranged in ascending order of data volume.

[0016] As an optional implementation of this application, S1, defining the same indicators for each source table and arranging the source tables in ascending order of data volume, may also include:

[0017] After grouping the source tables and arranging them in ascending order of data volume, each source table in the ascending order is marked with a serial number.

[0018] As an optional implementation of this application, S1, defining the same indicators for each source table and arranging the source tables in ascending order of data volume, may also include:

[0019] Bind the marked sequence number to the corresponding source table;

[0020] Generate and save the corresponding source table forms according to the source tables marked by serial numbers from smallest to largest.

[0021] As an optional implementation of this application, S2, obtaining the query dimension of the indicator query conditions input by the user, traversing and finding the source table that matches the query dimension, and using it as the target source table, includes:

[0022] Obtain the metric query conditions input by the user;

[0023] Perform query dimension parsing to obtain the query dimensions in the indicator query conditions;

[0024] Traverse the source table form, sequentially identify the analysis dimensions of each source table in the source table form, and determine whether the analysis dimensions include the query dimension:

[0025] If so, the traversal ends, and the source table that contains the query dimension in the analysis dimension is marked as the target source table;

[0026] Conversely, continue the traversal.

[0027] As an optional implementation of this application, when setting the indicator name and business scope, the following may also be included:

[0028] Based on the query dimension, construct the corresponding indicator name and business scope under the query dimension.

[0029] As an optional implementation of this application, optionally, S3, querying indicators based on the target source table and returning the corresponding indicator calculation results, includes:

[0030] Construct a query request based on the query dimensions;

[0031] The query request is sent to the data warehouse;

[0032] The data warehouse responds to the query request, performs indicator queries and calculations based on the target source table, and returns the corresponding indicator calculation results to the user.

[0033] In another aspect, this application proposes a management and query apparatus for the same indicator across multiple fact tables based on an indicator center, used to implement the aforementioned management and query method for the same indicator across multiple fact tables based on an indicator center, comprising:

[0034] The indicator definition module is used to define the scope of the same indicators from various source tables and to arrange the source tables in ascending order of data volume.

[0035] The execution plan matching module is used to obtain the query dimensions of the indicator query conditions input by the user, traverse and find the source table that matches the query dimensions, and use it as the target source table;

[0036] The indicator query module is used to query indicators based on the target source table and return the corresponding indicator calculation results.

[0037] In another aspect, this application also proposes an electronic device comprising:

[0038] processor;

[0039] Memory used to store processor-executable instructions;

[0040] The processor is configured to implement, when executing the executable instructions, a method for managing and querying the same indicator across multiple fact tables based on an indicator center.

[0041] Technical effects of the present invention:

[0042] This invention first defines the scope of identical indicators across various source tables and arranges these source tables in ascending order of data volume. Second, based on user-inputted indicator query conditions, it obtains the query dimensions and iterates through the source tables arranged in ascending order of data volume to find the source table that matches the query dimensions, designating it as the target source table. Finally, it performs indicator queries based on the target source table and returns the corresponding indicator calculation results. Therefore, it can dynamically match available analysis dimensions on each source table in one step according to the filter dimension range of the indicator query conditions. Upon successful matching, it selects the current source table for data querying, finding the optimal source table that meets business requirements and improving indicator query efficiency. The indicator platform does not need to create multiple indicators mapping to different fact tables; instead, it uses a single indicator for mapping and locates the query table based on query conditions and a preset table acceleration order, simplifying indicator management and making indicator analysis more intelligent.

[0043] Other features and aspects of this disclosure will become clear from the following detailed description of exemplary embodiments with reference to the accompanying drawings. Attached Figure Description

[0044] The accompanying drawings, which are included in and form part of this specification, illustrate exemplary embodiments, features, and aspects of this disclosure together with the specification and serve to explain the principles of this disclosure.

[0045] Figure 1 This diagram illustrates the mapping relationship between the existing detail table and the fact table.

[0046] Figure 2 The diagram shown is a schematic representation of the implementation process of the present invention;

[0047] Figure 3 The diagram illustrates how the source table is arranged in ascending order of data volume according to the present invention.

[0048] Figure 4 The diagram illustrates the process of querying indicators according to the present invention.

[0049] Figure 5 The diagram shown is a schematic representation of the device structure of the present invention.

[0050] Figure 6 The diagram shows an application schematic of the electronic device of the present invention. Detailed Implementation

[0051] Various exemplary embodiments, features, and aspects of this disclosure will now be described in detail with reference to the accompanying drawings. The same reference numerals in the drawings denote elements that have the same or similar functions. Although various aspects of the embodiments are shown in the drawings, they are not necessarily drawn to scale unless specifically indicated otherwise.

[0052] The term “exemplary” as used herein means “serving as an example, embodiment, or illustration.” Any embodiment illustrated herein as “exemplary” is not necessarily to be construed as superior to or better than other embodiments.

[0053] Furthermore, to better illustrate this disclosure, numerous specific details are set forth in the following detailed description. Those skilled in the art will understand that this disclosure can be practiced without certain specific details. In some instances, means, components, and circuits well known to those skilled in the art have not been described in detail in order to highlight the main points of this disclosure.

[0054] Related terminology and principle description:

[0055] 1. In a data warehouse (or simply data warehouse) or big data processing workflow, DWD (Data Warehouse Detail) and DWS (Data Warehouse Summary) are crucial stages in data processing. The DWD layer typically refers to the detailed data layer, storing the most original and detailed data; while the DWS layer summarizes and lightly aggregates the data from the DWD layer to enable more efficient analysis and querying.

[0056] The process of obtaining multiple fact tables from the DWD or DWS layer typically involves the following steps:

[0057] Data preparation:

[0058] Load data from data sources (such as databases, files, APIs, etc.) into the DWD layer.

[0059] Ensuring the integrity, accuracy, and consistency of data may require data cleaning, deduplication, and format conversion.

[0060] Building the DWD layer:

[0061] In the DWD layer, data is typically organized according to business processes, with each business process corresponding to one or more detailed tables.

[0062] These detailed tables contain all the detailed data generated during the business process and form the basis for subsequent analysis.

[0063] Building the DWS layer:

[0064] In the DWS layer, data from the DWD layer is summarized and aggregated according to business needs.

[0065] The granularity of aggregation may vary depending on business needs, such as by day, by month, or by department.

[0066] Data in the DWS layer is typically used to generate reports, perform data analysis, or serve as input for other data processing workflows.

[0067] Generate a fact table:

[0068] Based on the data from the DWD or DWS layer, multiple fact tables are generated according to the analysis requirements.

[0069] Fact tables typically contain measures (such as sales, quantity, cost, etc.) and keys associated with dimension tables (such as time dimension, product dimension, customer dimension, etc.).

[0070] The design of fact tables should meet the classic dimensional modeling principles such as star schemas or snowflake schemas to enable efficient analysis and querying.

[0071] Optimization and storage:

[0072] Optimize the generated fact table, such as by creating indexes and partitions, to improve query performance.

[0073] The optimized fact table is stored in a data warehouse or big data platform for subsequent analysis and querying.

[0074] Data management and maintenance:

[0075] Regularly update the data in the DWD and DWS layers to ensure the timeliness and accuracy of the data.

[0076] 2. The steps for querying metrics from a data warehouse can be divided into:

[0077] Choose your search method:

[0078] Choose to use SQL, BI tools, or predefined data modeling for queries, depending on your needs.

[0079] Write SQL queries (e.g., using SQL):

[0080] Use the SELECT statement to specify the table name, field names, and conditions to be queried.

[0081] Use the JOIN operation to join multiple tables and obtain a comprehensive view of the data.

[0082] Group the data using the GROUP BY clause and apply aggregate functions.

[0083] Execute query:

[0084] Run pre-written SQL queries or trigger queries through BI tools in the data warehouse system.

[0085] Optimize query performance:

[0086] Use variable binding techniques as much as possible to reduce physical I / O and redundant SQL parsing.

[0087] Reduce the number of logical reads or executions for a single statement to improve query efficiency.

[0088] Obtaining and processing results:

[0089] View the query results and perform further data processing or analysis as needed.

[0090] In this embodiment, the steps of storing multiple fact tables in the DWD or DWS after the data warehouse is processed based on the detail table, and querying indicators from the data warehouse based on the input query conditions, will not be described in detail. Example 1

[0091] like Figure 2 As shown, this application proposes a method for managing and querying the same indicator across multiple fact tables based on an indicator center, comprising the following steps:

[0092] S1. Define the same metrics for each source table and arrange the source tables in ascending order of data volume;

[0093] S2. Obtain the query dimension of the indicator query conditions input by the user, traverse and find the source table that matches the query dimension, and use it as the target source table;

[0094] S3. Query the indicators based on the target source table and return the corresponding indicator calculation results.

[0095] This invention first manages the multi-table mapping of indicator definitions. When defining the calculation criteria of indicators, they can be grouped and defined according to multiple source tables, each with its own analysis dimension, and arranged in ascending order of table data volume.

[0096] Subsequently, when performing multi-fact table queries under the same metric, the system can accelerate the order of queries based on the tables themselves, enabling the management and querying of the same metric across multiple fact tables. This improves query efficiency and avoids the drawbacks of query chaos caused by disordered table lookups. This invention can perform table queries according to the dataset size, sequentially querying each fact table from smallest to largest until a fact table matching the query dimension is found, thus improving query efficiency and strengthening the query logic.

[0097] The embodiments of the present invention will be further described below.

[0098] like Figure 3 As shown, as an optional implementation of this application, optionally, S1, defining the same indicators for each source table and arranging the source tables in ascending order of data volume includes:

[0099] Define the indicator name and business definition;

[0100] Retrieve the corresponding source tables from the data warehouse, which contain the specified indicator names and business definitions.

[0101] The source tables are grouped according to the analysis dimensions;

[0102] The source tables after grouping are arranged in ascending order of data volume.

[0103] In the indicator center, the first step is to define the indicator name and business scope of the source table to facilitate group definition.

[0104] It is necessary to combine the indicator name and business definition to find the source table in the data warehouse that meets the dimensions of this indicator and satisfies the query and calculation of indicator data under that dimension.

[0105] The indicator name and business definition can be defined based on the user's required indicator query dimensions. Here, we can construct the corresponding indicator name and business definition based on the query dimensions. For example:

[0106] Indicator Name: Trading Volume;

[0107] Business definition: Calculated based on the sum of transaction amounts from transaction records.

[0108] In this embodiment, the query dimensions are set according to the design requirements of the indicator system.

[0109] Since there are many source tables retrieved from the data warehouse, selection is necessary. Here, the source tables are grouped according to the required analysis dimensions. The source tables are divided into tables for different analysis dimensions. Different analysis dimensions correspond to different data volumes and subsequent metric calculations. Therefore, after grouping, the tables are sorted according to their data volume (which is also the scale of metric calculations or the size of metric data involved in the analysis dimension), arranged from smallest to largest data volume. The metric data volume in each table can also be found by reading the table (the data volume of the metric data recorded in the table can be found by reading the table).

[0110] This section also utilizes the LLM (Large Language Model) to retrieve the corresponding source tables from the data warehouse based on the defined indicator names and business definitions. This enables intelligent data warehouse table retrieval, automatic and accelerated source table construction, and improved efficiency. Specifically:

[0111] The prompt words for the large language model can be set based on the indicator name and business scope. The prompt words for the large language model can be set according to different indicator query dimensions, corresponding to different indicator names and business scopes, and the preset LLM large language model can be input. The LLM large language model can be called through API calls (such as Baidu Wenxin Yiyan), or deployed and referenced based on the LLM large language model used for custom construction.

[0112] The prompts can include information about the data volume, allowing the LLM large language model to read the data volume from each source table retrieved, which facilitates the construction of subsequent table sequence numbers.

[0113] The LLM (Large Language Model) retrieves the corresponding source tables from the data warehouse based on prompt words, specifying the metric name and business scope. It then groups these source tables according to the analysis dimensions. After automatically retrieving the relevant source tables and their corresponding data volumes from the data warehouse using the LLM model, it automatically sorts the retrieved source tables by data volume and outputs the sorting results.

[0114] The system will then query the metrics from the source table with the smallest data volume. If the source table with the smallest data volume contains the dimension for this metric calculation, the system will directly select the current source table for data querying, eliminating the need to query other source tables. This significantly reduces the query volume and improves query efficiency.

[0115] As an optional implementation of this application, S1, defining the same indicators for each source table and arranging the source tables in ascending order of data volume, may also include:

[0116] After grouping the source tables and arranging them in ascending order of data volume, each source table in the ascending order is marked with a serial number.

[0117] Sort each source table according to the data volume and assign it a corresponding serial number. For example, "Serial Number: 1" represents source table 1, which has been regrouped and rearranged.

[0118] Adding serial numbers makes it easier to access forms in the serial number table later, facilitating table lookup management and data binding management.

[0119] As an optional implementation of this application, S1, defining the same indicators for each source table and arranging the source tables in ascending order of data volume, may also include:

[0120] Bind the marked sequence number to the corresponding source table;

[0121] Generate and save the corresponding source table forms according to the source tables marked by serial numbers from smallest to largest.

[0122] This section saves the sorted source tables in a pre-defined blank form according to their serial numbers, generating a source table form. This allows for the integration of various sequentially arranged source tables, making it convenient for users. Alternatively, the indicator data from each source table can be written sequentially into the various tables in the form, and the content can be read sequentially later, saving time.

[0123] like Figure 4 As shown, as an optional implementation of this application, S2, obtaining the query dimension of the indicator query conditions input by the user, traversing and finding the source table that matches the query dimension, and using it as the target source table, includes:

[0124] Obtain the metric query conditions input by the user;

[0125] Perform query dimension parsing to obtain the query dimensions in the indicator query conditions;

[0126] Iterate through the source tables, systematically identifying the analysis dimensions of each source table (these analysis dimensions are stored in the database and can be read), and determine whether the analysis dimensions include the query dimension:

[0127] If so, the traversal ends, and the source table that contains the query dimension in the analysis dimension is marked as the target source table;

[0128] Conversely, continue the traversal.

[0129] The source table form, similar to an Excel spreadsheet, uses horizontal tables to store the metric data from various source tables, and vertical tables to represent each metric (and analysis dimensions). The source table form will fill in the metric data from the source tables, numbered from smallest to largest, into each row of horizontal tables, following the table acceleration direction.

[0130] Subsequently, when traversing the source tables and systematically identifying the analysis dimensions of each source table, the LLM large language model can be used to read the table indicator data from smallest to largest, obtaining the information from each table item on the horizontal axis. Therefore, the LLM large language model can be used to quickly traverse and find the source table with the smallest data size matching the analysis dimension according to the table acceleration direction.

[0131] This section uses the example of querying the indicator: Transaction outlet = "Ningbo Financial District Branch" & Transaction channel = "Self-service machine" to describe the indicator query process.

[0132] First, the query conditions for the above indicators were clarified;

[0133] Next, the query dimensions are analyzed to determine the specific query dimensions required for this indicator query. For example, the query dimensions might be "transaction outlets" and "transaction channels."

[0134] After identifying the query dimension, a matching search needs to be performed on each sorted source table to find the source table that matches the query dimension. This source table will then be used as the target table for this data query, from which the final indicator data will be queried and calculated.

[0135] The system iterates through the query dimension information of each table in the form, identifies and determines whether the analysis dimension contains the query dimension. If a source table containing the query dimension is found, the source table is considered to match the current query dimension and can be used as the indicator database pointed to by the query dimension, and the final indicator data is retrieved.

[0136] If no source is found, proceed to the next source table for identification.

[0137] For example, if the source table marked "Sequence Number 1" fails to match, the system will proceed to the source table marked "Sequence Number 2" for matching. If, after matching, the analysis dimension of the source table marked "Sequence Number 2" is found to include the query dimension for this metric query, then the source table marked "Sequence Number 2" is identified as the target source table for this query dimension, containing the required metric data. At this point, the match is successful, a query request is constructed, and the target metric data is queried and calculated based on the target source table.

[0138] The specific conditions for calculating the indicators can be pre-configured in the target source table, and are not limited here.

[0139] As an optional implementation of this application, optionally, S3, querying indicators based on the target source table and returning the corresponding indicator calculation results, includes:

[0140] Construct a query request based on the query dimensions;

[0141] The query request is sent to the data warehouse;

[0142] The data warehouse responds to the query request, performs indicator queries and calculations based on the target source table, and returns the corresponding indicator calculation results to the user.

[0143] When querying an indicator, an SQL query request can be generated and sent to the data warehouse, requesting the data warehouse to execute the request and return the corresponding indicator calculation results. Users can then view the indicator calculation results for the queried indicator.

[0144] Specifically: This can call an LLM (Large Language Model) to automatically construct a query request based on the query dimensions and request the data warehouse (data warehouse) to retrieve data for the corresponding dimensions. This can include the following steps:

[0145] 1. Understanding query dimensions

[0146] Input parsing: LLM first receives the query dimensions input by the user, which may include transaction outlets and transaction channels, etc.

[0147] Dimension Recognition: Through natural language processing technology, LLM identifies and extracts key dimension information from the query.

[0148] 2. Construct the query request

[0149] Query Templates: Based on predefined query templates or rules, LLM embeds the extracted dimension information into the query statement. These templates may be based on SQL, NoSQL, or other query languages. For example, using an SQL query template can generate SQL query requests. SQL query templates can be defined by the user or invoke components.

[0150] Dynamic assembly: Depending on the query dimensions, LLM can dynamically assemble query statements to ensure the accuracy and completeness of the query.

[0151] 3. Request data warehouse

[0152] Connecting to the data warehouse: LLM establishes connections to the data warehouse through APIs, database connection pools, or other means.

[0153] Execute query: Send the constructed query request to the data warehouse and wait for the data warehouse's response.

[0154] 4. Handling data warehouse responses

[0155] Result parsing: Receive data results returned by the data warehouse and parse them for subsequent processing or display.

[0156] Exception handling: If an exception occurs during the query process (such as connection failure, query timeout, etc.), the LLM needs to be able to capture these exceptions and handle them accordingly, such as retrying, logging, or notifying the user.

[0157] 5. Display or process the results

[0158] Data visualization: Displaying the parsed data results in a user-friendly way, such as tables, charts, etc.

[0159] Subsequent processing: Based on business needs, further processing or analysis of the data is performed, such as data aggregation and trend prediction.

[0160] The LLM big oracle model can automatically send the query results to the user's front end.

[0161] 6. Optimization and Iteration

[0162] Performance optimization: Based on query execution and user feedback, optimize query statements, connection pool configurations, etc., to improve query accuracy.

[0163] Therefore, this application dynamically matches the available analysis dimensions on each source table in one go according to the filtering dimension range of the indicator query conditions. If a match is successful, the current source table is selected for data query. The data table with the smallest data size can be selected for accelerated query.

[0164] This approach is compatible with different query scenarios and avoids the drawback of incompatibility between multiple fact tables caused by inconsistent query dimensions.

[0165] Obviously, those skilled in the art should understand that implementing all or part of the processes in the above embodiments can be accomplished by a computer program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the control embodiments described above. Those skilled in the art will understand that implementing all or part of the processes in the above embodiments can be accomplished by a computer program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the control embodiments described above. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium can also include combinations of the above types of memory. Example 2

[0166] like Figure 5 As shown, based on the implementation principle of Embodiment 1, this application, in another aspect, proposes a device for managing and querying the same indicator across multiple fact tables based on an indicator center, used to implement the management and querying method for managing and querying the same indicator across multiple fact tables based on an indicator center as described in Embodiment 1, including:

[0167] The indicator definition module is used to define the scope of the same indicators from various source tables and to arrange the source tables in ascending order of data volume.

[0168] The execution plan matching module is used to obtain the query dimensions of the indicator query conditions input by the user, traverse and find the source table that matches the query dimensions, and use it as the target source table;

[0169] The indicator query module is used to query indicators based on the target source table and return the corresponding indicator calculation results;

[0170] The indicator definition module is communicatively connected to the execution plan matching module, and the execution plan matching module is communicatively connected to the indicator query module.

[0171] The module functions and interactive application principles of this device can be understood by referring to Embodiment 1, and will not be repeated in this embodiment.

[0172] The modules or steps of the present invention described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. Optionally, they can be implemented using computer-executable program code, thereby storing them in a storage device for execution by a computing device, or fabricating them separately as individual integrated circuit modules, or fabricating multiple modules or steps into a single integrated circuit module. Thus, the present invention is not limited to any particular hardware and software combination. Example 3

[0173] like Figure 6 As shown, further, in another aspect, this application also proposes an electronic device, comprising:

[0174] processor;

[0175] Memory used to store processor-executable instructions;

[0176] The processor is configured to implement, when executing the executable instructions, a method for managing and querying the same indicator across multiple fact tables based on an indicator center, as described in Embodiment 1.

[0177] This disclosure discloses an electronic device including a processor and a memory for storing processor-executable instructions. The processor is configured to implement, when executing the executable instructions, any of the methods described above for managing and querying the same indicator across multiple fact tables based on an indicator center.

[0178] It should be noted here that the number of processors can be one or more. Furthermore, the electronic device in this embodiment may also include input devices and output devices. The processor, memory, input devices, and output devices can be connected via a bus or other means, without specific limitations herein.

[0179] As a computer-readable storage medium, memory can be used to store software programs, computer-executable programs, and various modules, such as the program or module corresponding to the method for managing and querying the same indicator in multiple fact tables based on an indicator center according to an embodiment of this disclosure. The processor executes various functional applications and data processing of the electronic device by running the software program or module stored in the memory.

[0180] Input devices can be used to receive input digital numbers or signals. These signals can be key signals related to user settings and function control of the device / terminal / server. Output devices can include display devices such as screens.

[0181] The various embodiments of this disclosure have been described above. These descriptions are exemplary and not exhaustive, and are not limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terminology used herein is chosen to best explain the principles, practical applications, or technical improvements to the technology in the market, or to enable others skilled in the art to understand the embodiments disclosed herein.

Claims

1. A method for managing and querying the same indicator across multiple fact tables based on an indicator center, characterized in that, Includes the following steps: S1. Define the definitions of the same metrics across the various source tables, and arrange the source tables in ascending order of data volume, including: Define the indicator name and business scope; Retrieve the corresponding source tables from the data warehouse, which contain the specified indicator names and business definitions. The source tables are grouped according to the analysis dimensions: the source tables are divided into tables for different analysis dimensions. After grouping, the tables are sorted according to the data volume of each table. The calculation scale of the indicators or the size of the indicator data involved in the analysis dimension are arranged from smallest to largest according to the data volume. Specifically: By leveraging an LLM (Large Language Model) based on defined metric names and business definitions, the system retrieves corresponding source tables from the data warehouse, enabling intelligent data warehouse table retrieval and automated, accelerated source table construction. The system sets prompts based on metric names and business definitions. For different metric query dimensions, prompts are set for different metric names and business definitions and input into the preset LLM system. The prompts include data volume information, allowing the LLM system to read the data volume from each retrieved source table. To facilitate the construction of sequence numbers in subsequent tables; the LLM large language model retrieves the corresponding source tables with the specified indicator name and business scope from the data warehouse based on prompt words; and groups the source tables according to the analysis dimensions; after automatically retrieving the corresponding source tables and reading their corresponding data volume from the data warehouse through the LLM large language model, the retrieved source tables are automatically sorted according to the data volume and the sorting results are output; subsequently, the indicator query will be performed from the source table with the smallest data volume. If the source table with the smallest data volume contains the dimension for this indicator calculation, the current source table will be directly selected for data query; The grouped source tables are arranged in ascending order of data volume, and each source table in the ascending order is marked with a serial number. S2. Obtain the query dimension of the indicator query conditions input by the user, traverse and find the source table that matches the query dimension, and use it as the target source table; S3. Based on the target source table, perform indicator query and return the corresponding indicator calculation results; First, we manage the multi-table mapping for indicator definitions. When defining the indicator calculation criteria, we group the indicators according to multiple source tables, select their respective analysis dimensions, and arrange the tables in ascending order of data volume. Subsequently, when performing multi-fact table queries for the same indicator, we implement accelerated table ordering based on each table. This enables the management and querying of the same indicator across multiple fact tables. We query the tables according to the dataset size, sequentially querying each fact table from smallest to largest until a fact table matching the query dimension is found.

2. The method for managing and querying the same indicator across multiple fact tables based on an indicator center, as described in claim 1, is characterized in that... S1. Define the definitions of the same metrics across the various source tables, and arrange the source tables in ascending order of data volume. This also includes: Bind the marked sequence number to the corresponding source table; Generate and save the corresponding source table forms according to the source tables marked by serial numbers from smallest to largest.

3. The method for managing and querying the same indicator across multiple fact tables based on an indicator center, as described in claim 1, is characterized in that... S2. Obtain the query dimensions of the indicator query conditions input by the user, traverse and find the source tables that match the query dimensions, and use them as the target source tables, including: Obtain the metric query conditions input by the user; Perform query dimension parsing to obtain the query dimensions in the indicator query conditions; Traverse the source table form, sequentially identify the analysis dimensions of each source table in the source table form, and determine whether the analysis dimensions include the query dimension: If so, the traversal ends, and the source table that contains the query dimension in the analysis dimension is marked as the target source table; Conversely, continue the traversal.

4. The method for managing and querying the same indicator across multiple fact tables based on an indicator center, as described in claim 3, is characterized in that... When setting indicator names and business definitions, the following should also be included: Based on the query dimension, construct the corresponding indicator name and business scope under the query dimension.

5. The method for managing and querying the same indicator across multiple fact tables based on an indicator center, as described in claim 3, is characterized in that... S3. Based on the target source table, perform indicator queries and return the corresponding indicator calculation results, including: Construct a query request based on the query dimensions; The query request is sent to the data warehouse; The data warehouse responds to the query request, performs indicator queries and calculations based on the target source table, and returns the corresponding indicator calculation results to the user.

6. A management and query apparatus for the same indicator across multiple fact tables based on an indicator center, used to implement the management and query method for the same indicator across multiple fact tables based on an indicator center as described in any one of claims 1-5, characterized in that, include: The indicator definition module is used to define the scope of the same indicators from various source tables and to arrange the source tables in ascending order of data volume. The execution plan matching module is used to obtain the query dimensions of the indicator query conditions input by the user, traverse and find the source table that matches the query dimensions, and use it as the target source table; The indicator query module is used to query indicators based on the target source table and return the corresponding indicator calculation results.

7. An electronic device, characterized in that, include: processor; Memory used to store processor-executable instructions; The processor is configured to implement, when executing the executable instructions, a method for managing and querying the same indicator across multiple fact tables based on an indicator center, as described in any one of claims 1-5.

Images