A multi-table analysis method and device

Abstract

This invention discloses a multi-table analysis method and apparatus, relating to the field of data analysis technology. The multi-table analysis method includes: constructing a semantic model for multiple data tables; upon receiving a user analysis request, parsing the user analysis request into a multi-indicator model based on the semantic model; splitting the multi-indicator model into multiple single-indicator models according to basic indicators, obtaining single-indicator models corresponding to each basic indicator; querying based on the single-indicator models corresponding to each basic indicator, obtaining query result sets corresponding to each basic indicator; and generating analysis results corresponding to the user analysis request based on the query result sets corresponding to each basic indicator. This invention eliminates the need to merge physical wide tables, instead performing multi-table join calculations on demand based on the semantic model. It boasts low maintenance costs and high flexibility, and for users, it eliminates the need for specialized technical knowledge, significantly lowering the barrier to entry.

Description

Technical Field

[0001] This invention relates to the field of data analysis technology, and in particular to a multi-table analysis method and apparatus. Background Technology

[0002] In the process of enterprise digital transformation, business personnel's demand for data analysis is no longer limited to the observation of single indicators, but is gradually evolving towards in-depth mining across business domains. As a result, multi-table joint analysis has become a key scenario to support business decision-making. However, existing multi-table analysis solutions still face a series of technical bottlenecks.

[0003] Traditional methods rely on physical wide tables, merging multiple tables into a single wide table via ETL processes. While this approach offers good query performance, it suffers from significant data redundancy and model rigidity. If business requirements change, the wide table often needs to be rebuilt, resulting in high maintenance costs and insufficient flexibility. Furthermore, forced merging loses rich relationships between the original tables, making it difficult to support temporary, multi-path exploratory analysis.

[0004] Current multi-table analysis solutions require a high level of user expertise. For example, some solutions require users to master professional formula expression languages ​​(such as DAX), making them more suitable for professional data analysts. Other solutions, when dealing with complex N:N relationship scenarios, rely heavily on the user's understanding of the data model for the correct configuration of the relationship logic. The threshold for self-service analysis remains high, making it difficult for business personnel to complete data analysis independently and efficiently, thus hindering the improvement of enterprises' data-driven decision-making capabilities. Summary of the Invention

[0005] In view of this, the present invention provides a multi-table analysis method and apparatus to solve the problems of serious data redundancy, rigid models, and high maintenance costs caused by existing multi-table analysis schemes based on physical wide tables, as well as the problem that existing multi-table analysis schemes require too much professional skill from users, making it difficult for users to independently complete in-depth analysis across business domains. The technical solution is as follows:

[0006] The first aspect of this invention provides a multi-table analysis method, comprising:

[0007] When a user analysis request is received, if the user analysis request includes a target analysis dimension and a target derived indicator, then the user analysis request is parsed into a multi-indicator model based on a semantic model pre-built for multiple related data tables. The semantic model includes the relationship between the multiple data tables and the calculation method of the derived indicators defined for the multiple data tables. The multi-indicator model includes the target analysis dimension, multiple basic indicators and the relationship between the tables involved in the multiple basic indicators. The multiple basic indicators are the basic indicators on which the target derived indicators depend.

[0008] The multi-indicator model is split into multiple single-indicator models according to the basic indicators, and the single-indicator models corresponding to the multiple basic indicators are obtained. The single-indicator model includes the target analysis dimension, the corresponding basic indicator, the data table on which the corresponding basic indicator depends, and the inter-table relationship involved in the corresponding basic indicator.

[0009] Based on the single indicator models corresponding to the multiple basic indicators, queries are performed to obtain the query result sets corresponding to the multiple basic indicators.

[0010] Based on the query result sets corresponding to the multiple basic indicators and the calculation method of the target derived indicators, the analysis results corresponding to the user analysis request are generated.

[0011] In one possible implementation, the multi-table analysis method further includes:

[0012] If the user analysis request includes a target analysis dimension and multiple basic indicators, then according to the semantic model, the user analysis request is parsed into a multi-indicator model that includes the target analysis dimension, the multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators.

[0013] In one possible implementation, a semantic model is constructed for multiple related data tables, including:

[0014] Obtain metadata from multiple related data tables from the data source and present the metadata of the multiple data tables in the UI interface;

[0015] In response to a user's association operation on the metadata of the multiple data tables in the UI interface, an association relationship is created between the multiple data tables, the association relationship including the association field and the association type;

[0016] In response to the user's configuration operation on the formula panel, a derived indicator calculation method is generated based on the formula expression entered by the user, wherein the formula expression references fields in the multiple data tables;

[0017] By integrating the relationships between the multiple data tables with the formula for calculating the derived indicators, a semantic model is obtained.

[0018] In one possible implementation, parsing the user analysis request into a multi-metric model based on the semantic model includes:

[0019] Extract the target analysis dimension and the target derived indicators from the user analysis request;

[0020] Based on the semantic model, determine the multiple basic indicators on which the target derived indicator depends, as well as the inter-table relationships involved in the multiple basic indicators;

[0021] By integrating the target analysis dimensions, the multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators, a multi-indicator model is obtained.

[0022] In one possible implementation, the step of querying based on the single-indicator models corresponding to the multiple basic indicators to obtain query result sets corresponding to the multiple basic indicators includes:

[0023] For each of the basic indicators, a query statement is generated based on the single indicator model corresponding to the basic indicator to obtain the query statement corresponding to the basic indicator.

[0024] Execute the query statements corresponding to the various basic indicators to obtain the query result sets corresponding to the various basic indicators.

[0025] In one possible implementation, after obtaining the query statements corresponding to the multiple basic indicators, and before executing the query statements corresponding to the multiple basic indicators, the method further includes:

[0026] For each of the basic indicators, detect whether there is a many-to-many relationship between the tables involved in the basic indicator;

[0027] If the basic indicators involve many-to-many relationships between tables, then rewrite the query statement corresponding to the basic indicators so that the basic indicators are first aggregated according to the target analysis dimension during the query, and then associated with other tables.

[0028] If the table relationships involved in the basic indicators do not have a many-to-many relationship, then the query statement corresponding to the basic indicators remains unchanged.

[0029] In one possible implementation, after constructing the semantic model and before executing the query statements corresponding to the multiple basic indicators, the method further includes:

[0030] Dictionary data is generated based on the associations in the semantic model. The dictionary data contains the mapping relationship between the primary key field values ​​and text field values ​​of the referenced data table. The primary key field value is used to uniquely identify the record, and the text field value is used to record the text information corresponding to the primary key field value.

[0031] Based on the dictionary data, the query statements corresponding to the various basic indicators are rewritten.

[0032] In one possible implementation, generating the analysis result corresponding to the user analysis request based on the query result sets corresponding to the multiple basic indicators and the calculation method of the target derived indicators includes:

[0033] The query result sets corresponding to the multiple basic indicators are merged according to the target analysis dimension to obtain a merged result set;

[0034] The merged result set is processed according to the calculation method of the target derived index to obtain the calculation result of the target derived index;

[0035] An analysis result containing the calculation results of the target derived indicators is generated, thus obtaining the analysis result corresponding to the user analysis request.

[0036] In one possible implementation, the multi-table analysis method further includes:

[0037] Based on the associations in the semantic model, establish the dependencies between the multiple data tables;

[0038] When any of the multiple data tables is updated, the affected dictionary data is determined based on the dependencies between the multiple data tables;

[0039] The affected dictionary data is updated based on the updated content of the updated data table.

[0040] A second aspect of the present invention provides a multi-table analysis device, comprising: a semantic model construction module, a user analysis request parsing module, a multi-indicator model splitting module, a data query module, and an analysis result generation module;

[0041] The semantic model construction module is used to construct a semantic model for multiple related data tables. The semantic model includes the relationship between the multiple data tables and the calculation method of the derived indicators defined for the multiple data tables.

[0042] The user analysis request parsing module is used to, when receiving a user analysis request, if the user analysis request contains target derived indicators and target analysis dimensions, parse the user analysis request into a multi-indicator model containing the target analysis dimension, multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators, according to the semantic model. The multiple basic indicators are the basic indicators on which the target derived indicators depend.

[0043] The multi-indicator model splitting module is used to split the multi-indicator model into multiple single-indicator models according to the basic indicators, so as to obtain the single-indicator models corresponding to the multiple basic indicators respectively. The single-indicator model includes the target analysis dimension, the corresponding basic indicator, the data table on which the corresponding basic indicator depends, and the inter-table relationship involved in the corresponding basic indicator.

[0044] The data query module is used to perform queries based on the single indicator models corresponding to the multiple basic indicators, and obtain the query result sets corresponding to the multiple basic indicators respectively.

[0045] The analysis result generation module is used to generate analysis results corresponding to the user analysis request based on the query result sets corresponding to the multiple basic indicators and the calculation method of the target derived indicators.

[0046] By means of the above technical solution, the multi-table analysis method provided by the present invention, when receiving a user analysis request containing target derived indicators and target analysis dimensions, firstly parses the user analysis request into a multi-indicator model based on a semantic model pre-constructed for multiple related data tables, then splits the multi-indicator model into multiple single-indicator models according to basic indicators, obtaining single-indicator models corresponding to each of the multiple basic indicators, then queries are performed based on the single-indicator models corresponding to each of the multiple basic indicators, obtaining query result sets corresponding to each of the multiple basic indicators, and finally, based on the query result sets corresponding to each of the multiple basic indicators and the calculation method of the target derived indicators, generates the analysis result corresponding to the user analysis request. The multi-table analysis method provided by this invention does not require merging multiple data tables into a physical wide table. Instead, it performs multi-table join calculations on demand during queries based on predefined relationships in the semantic model. When the data table structure changes, only the semantic model needs to be adjusted, without reconstructing the physical wide table, thus significantly reducing maintenance costs and improving model flexibility and business response speed. Furthermore, users do not need to master professional languages ​​such as DAX; they can trigger the data query and calculation process simply by initiating an analysis request containing derived indicators and analysis dimensions, greatly lowering the barrier to entry for multi-table analysis and enabling business personnel to independently complete in-depth analysis across business domains. In addition, the multi-table analysis method provided by this invention can flexibly respond to diverse user choices of analysis dimensions and indicators without disrupting the original data relationship structure, supporting ad-hoc, multi-path exploratory analysis, and effectively meeting the complex and ever-changing in-depth analysis needs in business decision-making scenarios. Attached Figure Description

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

[0048] Figure 1 This is a flowchart illustrating the multi-table analysis method provided in an embodiment of the present invention.

[0049] Figure 2This is a flowchart illustrating the process of constructing a semantic model for multiple related data tables, as provided in an embodiment of the present invention.

[0050] Figure 3 A flowchart illustrating the process of parsing a user analysis request into a multi-indicator model based on a semantic model, provided for embodiments of the present invention;

[0051] Figure 4 This is a schematic diagram illustrating the splitting of a multi-index model into a single-index model, as provided in an embodiment of the present invention.

[0052] Figure 5 This is a schematic diagram of the structure of the multi-table analysis device provided in an embodiment of the present invention. Detailed Implementation

[0053] The embodiments of the present invention will now be described with reference to the accompanying drawings. The terminology used in the embodiments section is for illustrative purposes only and is not intended to limit the scope of the invention.

[0054] The embodiments of the present invention will now be described with reference to the accompanying drawings. Those skilled in the art will recognize that, with technological advancements and the emergence of new scenarios, the technical solutions provided by the embodiments of the present invention are equally applicable to similar technical problems.

[0055] The terms "first," "second," etc., used in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such terms are interchangeable where appropriate; this is merely a way of distinguishing objects with the same attributes in the description of embodiments of the invention. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion, so that a process, method, system, product, or apparatus that comprises a series of units is not necessarily limited to those units, but may include other units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0056] Please see Figure 1 The diagram illustrates a flowchart of a multi-table analysis method provided in an embodiment of the present invention. This multi-table analysis method may include:

[0057] Step S101: When a user analysis request is received, if the user analysis request contains target derived indicators and target analysis dimensions, then the user analysis request is parsed into a multi-indicator model based on the semantic model pre-built for multiple related data tables.

[0058] The semantic model pre-built for multiple related data tables includes, but is not limited to: the relationships between multiple data tables and the calculation methods of derived indicators defined for multiple data tables.

[0059] Users can initiate analysis requests through the UI interface. These requests can include target analysis dimensions and target derived metrics, and optionally, filtering conditions. When an analysis request containing target derived metrics and target analysis dimensions is received, the system parses the request into a multi-metric model based on the semantic model.

[0060] Among them, the multi-indicator model may include, but is not limited to: target analysis dimensions, multiple basic indicators on which target-derived indicators depend, and inter-table relationships involved in multiple basic indicators.

[0061] It should be noted that, in addition to initiating analysis requests that include target analysis dimensions and target derived indicators, users can also initiate analysis requests that include target analysis dimensions and multiple basic indicators. If a user initiates an analysis request that includes target analysis dimensions and multiple basic indicators, the analysis request will also be parsed into a multi-indicator model according to the semantic model. This multi-indicator model includes the target analysis dimension, multiple basic indicators (multiple basic indicators in the analysis request), and the inter-table relationships involved in the multiple basic indicators.

[0062] Step S102: The multi-indicator model is split into multiple single-indicator models according to the basic indicators, resulting in single-indicator models corresponding to the multiple basic indicators.

[0063] Each single-indicator model may include, but is not limited to, the target analysis dimension, the corresponding basic indicator, the data table on which the corresponding basic indicator depends, and the inter-table relationships involved in the corresponding basic indicator.

[0064] Step S103: Query based on the single indicator models corresponding to the multiple basic indicators to obtain the query result sets corresponding to the multiple basic indicators.

[0065] For each basic indicator, a query statement is generated based on the single indicator model corresponding to that basic indicator to obtain query statements corresponding to multiple basic indicators. Then, the query statements corresponding to multiple basic indicators are executed (preferably, to improve query efficiency, the query statements corresponding to multiple basic indicators can be executed in parallel) to obtain the query result set corresponding to multiple basic indicators.

[0066] Step S104: Generate the analysis results corresponding to the user analysis request based on the query result sets corresponding to the multiple basic indicators and the calculation method of the target derived indicators.

[0067] After obtaining the query result sets corresponding to multiple basic indicators, the query result sets corresponding to multiple basic indicators can be merged according to the target analysis dimension to obtain a merged dataset. Then, according to the calculation method of the target derived indicators, the data in the merged dataset is calculated to obtain the calculation results of the target derived indicators. In turn, the analysis results containing the calculation results of the target derived indicators are generated to obtain the analysis results corresponding to the user's analysis request.

[0068] As mentioned above, in addition to initiating analysis requests that include target analysis dimensions and target derived indicators, users can also initiate analysis requests that include target analysis dimensions and multiple basic indicators. If a user initiates an analysis request that includes target analysis dimensions and multiple basic indicators, after obtaining the query result sets corresponding to the multiple basic indicators respectively, the query result sets corresponding to the multiple basic indicators can be merged according to the target analysis dimensions to obtain a merged dataset. Then, analysis results containing the merged dataset are generated, thus obtaining the analysis results corresponding to the user's analysis request.

[0069] The multi-table analysis method provided in this invention constructs a semantic model containing the relationships between data tables and the calculation methods of derived indicators. It parses user-initiated analysis requests into a multi-indicator model, further breaks it down into multiple single-indicator models for separate query execution, and finally generates the analysis results corresponding to the user's analysis request based on the query results. This method eliminates the need to merge multiple data tables into a single physical wide table. Instead, it performs multi-table join calculations as needed during queries based on predefined relationships within the semantic model. When the data table structure changes, only the semantic model needs adjustment, without reconstructing the physical wide table, significantly reducing maintenance costs and improving model flexibility and business response speed. Furthermore, users do not need to master professional languages ​​such as DAX; they can trigger data query and calculation processes simply by initiating an analysis request containing indicators and analysis dimensions, greatly lowering the barrier to entry for multi-table analysis and enabling business personnel to independently complete in-depth analysis across business domains. In addition, the multi-table analysis method provided in this invention, without disrupting the original data relationship structure, can flexibly respond to diverse user selections of analysis dimensions and indicators, supporting ad-hoc, multi-path exploratory analysis, and effectively meeting the complex and ever-changing in-depth analysis needs in business decision-making scenarios.

[0070] The above embodiments mention that when a user analysis request is received, the request is parsed into a multi-indicator model based on a semantic model pre-built for multiple related data tables. Some embodiments of the present invention describe the implementation process of building a semantic model for multiple related data tables.

[0071] In one possible implementation, such as Figure 2 As shown, the process of building a semantic model for multiple related data tables may include:

[0072] Step S201: Obtain metadata of multiple related data tables from the data source and present the metadata of multiple data tables in the UI interface.

[0073] The system retrieves metadata from multiple user-selected tables from a specified data source (such as a database). This metadata includes, but is not limited to, structural descriptions such as table name, field names, and field types. After retrieving the metadata from multiple tables, the system presents this metadata in a structured or graphical manner in the UI, allowing users to intuitively understand the field composition of each table and providing a visual basis for subsequent relationship configuration and indicator definition.

[0074] For example, the user selects three tables: "Salesperson Information Table", "Sales Target Table", and "Sales Fact Table". The metadata of the "Salesperson Information Table", "Sales Target Table", and "Sales Fact Table" is retrieved from the specified database and displayed in the UI interface. For example, "Salesperson ID" and "Salesperson Name" in "Salesperson Information Table", "Sales Amount" in "Sales Fact Table", and "Target Amount" in "Sales Target Table".

[0075] Step S202: In response to the user's association operation on the metadata of multiple data tables on the UI interface, create the association relationship between the multiple data tables.

[0076] After the metadata of multiple data tables is presented in the UI, users can create relationships between the multiple data tables (including related fields, related types, etc.) by dragging, dropping, and other methods in the UI.

[0077] In response to user operations on metadata related to multiple data tables in the UI, relationships are created between the multiple data tables. These relationships are the core basis for the subsequent dynamic generation of query execution plans.

[0078] For example, the UI displays metadata for the "Salesperson Information Table," "Sales Target Table," and "Sales Fact Table." In response to user interactions with these three tables' metadata in the visual interface, the following relationships are created:

[0079] The "Salesperson Information Table" is linked to the "Sales Target Table" in a one-to-many (1:N) manner through the "Salesperson ID" field (a salesperson has multiple targets).

[0080] The "Salesperson Information Table" is linked to the "Sales Fact Table" (a salesperson has multiple orders) in a one-to-many (1:N) manner through the "Salesperson ID" field.

[0081] Step S203: In response to the user's configuration operation on the formula panel, generate a derived indicator calculation method based on the formula expression entered by the user. The formula expression references fields from multiple data tables.

[0082] A formula panel can be provided for users to enter custom formula expressions. Users can use this panel to reference fields from imported data tables and define formula expressions.

[0083] For example, users can define the following formulas in the formula panel for the "Salesperson Information Table," "Sales Target Table," and "Sales Fact Table":

[0084] "Sales Amount" = SUM_AGG([Sales Facts Table].Sales Amount);

[0085] "Target Amount" = SUM_AGG([Sales Target Table].Target Amount);

[0086] "Percentage of sales target achieved" = (Sales amount - Target amount) / Target amount.

[0087] In response to the user's configuration operations in the formula panel, a derived indicator calculation method is generated based on the formula expression entered by the user.

[0088] Step S204: Integrate the relationships between multiple data tables and the calculation methods of derived indicators to obtain a semantic model.

[0089] The relationships created in the preceding steps and the calculation methods of the generated derivative indicators are integrated to form a complete semantic model. This semantic model serves as an intermediary layer between the data source and the upper-level analysis application, encapsulating the relationship logic between data tables and the calculation rules for business indicators.

[0090] Semantic models can be stored in a configuration center and managed in atomic units called "analysis topics". Each analysis topic corresponds to an independent semantic model configuration, including semantic information such as the data table relationships defined under that topic and the calculation methods of derived indicators. The configurations of different topics are isolated from each other and do not interfere with each other, thereby supporting parallel management and independent iteration of multiple business scenarios and multiple analysis perspectives.

[0091] In some embodiments of the present invention, the process of "parsing user analysis requests into multi-index models based on a semantic model pre-constructed for multiple related data tables" in the above embodiments is described.

[0092] like Figure 3 As shown, the process of parsing a user analysis request into a multi-metric model based on a semantic model pre-built for multiple relevant data tables may include:

[0093] Step S301: Extract target derived metrics and target analysis dimensions from user analysis requests.

[0094] The user analysis request in this step includes target derived metrics and target analysis dimensions. This step aims to parse the user's analysis intent and extract the core elements in the analysis request, namely target derived metrics and target analysis dimensions. If the user analysis request also includes filtering conditions, then the filtering conditions also need to be extracted.

[0095] It should be noted that the target derived metrics are the core business metrics that users want to analyze, such as "the percentage of sales target achievement". The target analysis dimension indicates which angle the user wants to analyze from, such as "salesperson". The target derived metrics and target analysis dimensions (and may also have filtering conditions) constitute the core of the analysis request and are the basis for subsequent parsing and querying.

[0096] Step S302: Based on the semantic model pre-built for multiple related data tables, determine the multiple basic indicators on which the target derived indicator depends, as well as the inter-table relationships involved in the multiple basic indicators.

[0097] As mentioned in the above embodiments, the semantic model includes the relationship between multiple data tables, as well as the calculation method of derived indicators. The multiple basic indicators on which the target derived indicator depends can be determined according to the calculation method of derived indicators in the semantic model, and the relationship between multiple basic indicators can be determined according to the relationship between tables in the semantic model.

[0098] Step S303: Integrate the target analysis dimensions, the multiple basic indicators on which the target derived indicators depend, and the inter-table relationships involved in the multiple basic indicators to obtain a multi-indicator model.

[0099] After obtaining the target analysis dimensions, the multiple basic indicators on which the target derived indicators depend, and the inter-table relationships involved in the multiple basic indicators, this information can be integrated to obtain a multi-indicator model.

[0100] The following example illustrates the process of parsing a user analysis request into a multi-metric model based on a semantic model.

[0101] The user-initiated analysis request was for "analyzing the percentage of each salesperson's sales target achievement":

[0102] First, extract the target analysis dimension "salesperson" and the target derived indicator "sales target achievement percentage" from the analysis request "analyze the percentage of each salesperson's sales target achievement".

[0103] Then, based on the calculation method of the derived indicators in the semantic model, the basic indicator on which the target derived indicator "percentage of sales target achievement" depends is determined. The calculation method of the derived indicator "percentage of sales target achievement" in the semantic model is: Percentage of sales target achievement = (Sales amount - Target amount). The target amount, based on this calculation method, determines that the basic indicators upon which the target-derived indicator "percentage of sales target achievement" depends are "sales amount" and "target amount". Simultaneously, based on the inter-table relationships in the semantic model, the inter-table relationships involved in the basic indicators "sales amount" and "target amount" are determined. The inter-table relationships in the semantic model are as follows: the "salesperson information table" is linked to the "sales target table" in a one-to-many (1:N) manner through the "salesperson ID" field; and the "salesperson information table" is linked to the "sales fact table" in a one-to-many (1:N) manner through the "salesperson ID" field. Based on the inter-table relationships in the semantic model, the inter-table relationships involved in the basic indicators "sales amount" and "target amount" are as follows: the "salesperson information table" is linked to the "sales target table" in a one-to-many (1:N) manner through the "salesperson ID" field; and the "salesperson information table" is linked to the "sales fact table" in a one-to-many (1:N) manner through the "salesperson ID" field.

[0104] Finally, the basic indicators "sales amount" and "target amount" on which the target analysis dimension "salesperson" and the target derivative indicator "percentage of sales target achievement" depend, as well as the inter-table relationships involved in the two basic indicators, are integrated together (if there are filtering conditions, the filtering conditions also need to be integrated) to obtain a multi-indicator model.

[0105] Additionally, it should be noted that if the target analysis request includes target analysis dimensions and multiple basic indicators, then the target analysis dimensions and multiple basic indicators should be extracted from the target analysis request. If the user analysis request also includes filtering conditions, then the filtering conditions should also be extracted. Based on the inter-table relationships in the semantic model, the inter-table relationships involved in the multiple basic indicators should be determined. Then, the target analysis dimensions, multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators should be integrated (if there are filtering conditions, then the filtering conditions should also be integrated) to obtain a multi-indicator model.

[0106] After obtaining the multi-indicator model, the multi-indicator model can be split into multiple single-indicator models according to the basic indicators, resulting in single-indicator models corresponding to the multiple basic indicators. This process is described in some embodiments of the present invention.

[0107] The process of breaking down a multi-indicator model into multiple single-indicator models based on fundamental indicators, and obtaining the single-indicator models corresponding to each fundamental indicator, may include:

[0108] Traverse the basic indicators in the multi-indicator model: Integrate the target analysis dimension, the currently traversed basic indicator, the data table on which the currently traversed basic indicator depends, and the inter-table relationships involved in the currently traversed basic indicator (if there are filtering conditions, the filtering conditions also need to be integrated) to obtain the single indicator model corresponding to the currently traversed basic indicator.

[0109] For the multi-index model example above, such as Figure 4 As shown, by breaking it down according to the basic indicators, we can obtain two single-indicator models:

[0110] Single indicator model-1: It can include the target analysis dimension "salesperson", the basic indicator "sales amount", the data table "sales fact table" on which the basic indicator "sales amount" depends, and the inter-table relationships involved in the basic indicator "sales amount" (i.e. the relationship between "salesperson information table" and "sales fact table": "salesperson information table" is linked to "sales fact table" in a one-to-many (1:N) manner through the "salesperson ID" field).

[0111] Single indicator model-2: It can include the target analysis dimension "salesperson", the basic indicator "target amount", the data table "sales target table" on which the basic indicator "target amount" depends, and the inter-table relationship involved in the basic indicator "target amount" (i.e. the relationship between "salesperson information table" and "sales target table": "salesperson information table" is associated with "sales target table" in a one-to-many (1:N) manner through the "salesperson ID" field).

[0112] After obtaining the single-indicator models corresponding to multiple basic indicators, query statements corresponding to multiple basic indicators are generated based on the single-indicator models corresponding to multiple basic indicators, and then the query statements corresponding to multiple basic indicators are executed to obtain the query result sets corresponding to multiple basic indicators.

[0113] For the example above, query information-1 is generated based on single indicator model-1, and query information-2 is generated based on single indicator model-2:

[0114] Query Information-1: [Salesperson Information Table].Salesperson, SUM_AGG(

Sales Facts Table

[0115] Query Information-2: [Salesperson Information Table].Salesperson, SUM_AGG([Sales Target Table].Target Amount);

[0116] Then, query information-1 and query information-2 are converted into query statements to obtain the query statements corresponding to the basic indicators "sales amount" and "target amount" respectively. These query statements are then executed to obtain the query result sets corresponding to "sales amount" and "target amount" respectively. Examples of the query result sets corresponding to "sales amount" and "target amount" are shown below.

[0117] Table 1. Example of query result set corresponding to "Sales Amount" Salesperson Sales amount (total amount) Zhang San 125000 Li Si 82000 Wang Wu 70000

[0118] Table 2 Example of query result set for "Sales Target" Salesperson Sales target (total amount) Zhang San 100000 Li Si 90000 Wang Wu 80000

[0119] Optionally, after constructing the semantic model, dictionary data can be generated based on the inter-table relationships in the semantic model. The dictionary data contains the mapping relationship between the primary key field values ​​and text field values ​​of the referenced data tables. The primary key field value is used to uniquely identify the record, and the text field value is used to record the text information corresponding to the primary key value. For example, the inter-table relationships in the semantic model are as follows: the "Salesperson Information Table" is associated with the "Sales Target Table" in a one-to-many (1:N) manner through the "Salesperson ID" field, and the "Salesperson Information Table" is associated with the "Sales Fact Table" in a one-to-many (1:N) manner through the "Salesperson ID" field. In this case, the referenced data table is the "Salesperson Information Table," the primary key field of the "Salesperson Information Table" is "Salesperson ID," and the text information corresponding to the primary key field "Salesperson ID" is "Salesperson Name."

[0120] It should be noted that dictionary data is a key-value structure used to store the mapping relationship between the primary key field value and the text field value of the referenced data table. The primary key field value (key) is used to uniquely identify the record (e.g., salesperson ID: 1001, 1002, 1003), and the text field value (value) is used to record the text information corresponding to the primary key field value (e.g., salesperson name: Zhang San, Li Si, Wang Wu).

[0121] After generating multiple query statements corresponding to each basic indicator based on the single indicator model corresponding to each basic indicator, the query statements corresponding to each basic indicator can be rewritten based on dictionary data, and the rewritten query statements become the final query statements.

[0122] After generating query statements corresponding to multiple basic indicators, rewriting these query statements using dictionary data can improve query performance.

[0123] Considering that any of the data tables in multiple datasets may be updated, the dependencies between the multiple data tables can be established based on the association relationships in the semantic model. When a data table is updated, the affected dictionary data is determined based on the dependencies between the multiple data tables, and then the affected dictionary data is updated.

[0124] Optionally, for each basic indicator, it is possible to detect whether there is a many-to-many relationship among the related relationships involved in that basic indicator. If there is a many-to-many relationship, the query statement corresponding to that basic indicator is rewritten so that when querying, the data tables with many-to-many relationships are first aggregated according to the target analysis dimension, and then the aggregation results are associated with other data tables. Then, the final query statements corresponding to each of the multiple basic indicators are executed to obtain the query result set corresponding to each basic indicator.

[0125] If a user analysis request includes target analysis dimensions and target derived indicators, then after obtaining the query result sets corresponding to each basic indicator, the analysis results corresponding to the user analysis request can be generated based on the query result sets corresponding to multiple basic indicators and the calculation method of the target derived indicators.

[0126] Specifically, the query result sets corresponding to multiple basic indicators can first be merged (FULL JION) according to the target analysis dimension to obtain a merged result set. Then, based on the merged result set, calculations are performed according to the calculation method of the target derived indicators to obtain the calculation results of the target derived indicators. Finally, an analysis result containing the calculation results of the target derived indicators is generated, thus obtaining the analysis result corresponding to the user's analysis request. It should be noted that the query result sets corresponding to multiple basic indicators can be cached in memory, and therefore, the merging and calculation of the query result sets corresponding to multiple basic indicators can be performed in memory.

[0127] For the example above, obtain the query result set corresponding to the basic indicator "sales amount" (e.g., including the total sales amount for each salesperson) and the query result set corresponding to the basic indicator "target amount" (including the total target amount for each salesperson). Merge the two query result sets according to the target analysis dimension "salesperson" to obtain the following merged dataset:

[0128] Table 3 Examples of merged datasets Salesperson Sales amount Sales Target Zhang San 125000 100000 Li Si 82000 90000 Wang Wu 70000 80000

[0129] Each data point in the above merged dataset corresponds to a salesperson. Each data point includes the salesperson's total sales amount and total target amount. Next, based on the calculation method of the target-derived indicator "percentage of sales target achievement" (percentage of sales target achievement = (sales amount - target amount) / target amount), the percentage of sales target achievement for each salesperson can be calculated, thus obtaining the analysis results.

[0130] The multi-table analysis method provided in this invention has the following advantages:

[0131] First, it lowers the barrier to entry for users, enabling self-service business analysis. This invention abstracts and encapsulates the complex underlying table structures, relationships, and computational logic through a semantic model. Users only need to express their analytical needs; they do not need to master technical jargon to independently complete complex multi-table relationship analyses, truly realizing self-service business analysis and greatly reducing technical barriers.

[0132] Secondly, it improves query performance and optimizes resource utilization. By splitting a multi-indicator model containing multiple indicators into multiple independent single-indicator models, each single-indicator model corresponds to a query for a basic indicator. This split makes each query structure simple and less complex, allowing the database to generate execution plans more efficiently. At the same time, multiple basic indicator queries can be executed in parallel, making full use of system resources and significantly improving the overall query response speed. Compared to executing complex multi-table join queries all at once, this invention can effectively avoid performance bottlenecks caused by multi-table joins.

[0133] Third, ensure data accuracy and avoid calculation errors. In multi-table join analysis, directly performing join queries on multiple fact tables can easily result in Cartesian products, leading to duplicate calculations and incorrect analysis results. This invention avoids data errors caused by inconsistent join granularity across multiple tables by splitting the multi-indicator model into a single-indicator model. Each basic indicator is independently aggregated and calculated from its respective fact table at the correct granularity.

[0134] Fourth, it enhances model reusability and reduces development costs. The semantic model exists as an independent resource and is not bound to specific query or display components. The same semantic model can be reused by multiple user requests. This design avoids redundant modeling and development.

[0135] Fifth, it supports incremental calculation and cache reuse. Since the basic indicator query is executed independently, its query results can be cached. When multiple derived indicators need to use the same basic indicator, only one query needs to be executed, and subsequent calculations can be performed directly using the cached results, avoiding repeated database queries. This mechanism is particularly suitable for high-frequency analysis scenarios or interactive analysis scenarios that require multiple drill-downs and linkages, which can significantly reduce database load and improve overall throughput and response speed.

[0136] This invention also provides a multi-table analysis device, such as... Figure 5 As shown, the multi-table analysis device may include: a semantic model construction module 501, a user analysis request parsing module 502, a multi-indicator model splitting module 503, a data query module 504, and an analysis result generation module 505.

[0137] The semantic model building module 501 is used to build a semantic model for multiple related data tables. The semantic model includes the relationship between multiple data tables and the calculation method of the derived indicators defined for multiple data tables.

[0138] The user analysis request parsing module 502 is used to parse the user analysis request into a multi-indicator model that includes the target analysis dimension, multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators when a user analysis request is received, if the user analysis request includes target derived indicators and target analysis dimensions, according to the semantic model. The multiple basic indicators are the basic indicators on which the target derived indicators depend.

[0139] The multi-indicator model splitting module 503 is used to split the multi-indicator model into multiple single-indicator models according to the basic indicators, and obtain the single-indicator models corresponding to the multiple basic indicators. The single-indicator model includes the target analysis dimension, the corresponding basic indicator, the data table on which the corresponding basic indicator depends, and the inter-table relationship involved by the corresponding basic indicator.

[0140] The data query module 504 is used to query based on the single indicator models corresponding to multiple basic indicators, and obtain the query result sets corresponding to the multiple basic indicators.

[0141] The analysis result generation module 505 is used to generate analysis results corresponding to the user's analysis request based on the query result sets corresponding to multiple basic indicators and the calculation method of the target derived indicators.

[0142] In one possible implementation, the user analysis request parsing module 502 is further configured to, when the user analysis request contains target analysis dimensions and multiple basic indicators, parse the user analysis request into a multi-indicator model that includes target analysis dimensions, multiple basic indicators, and inter-table relationships involved in the multiple basic indicators, based on the semantic model.

[0143] In one possible implementation, the semantic model building module 501, when building a semantic model for multiple related data tables, is specifically used for:

[0144] Obtain metadata from multiple related data tables from the data source and present the metadata of multiple data tables in the UI interface;

[0145] In response to user operations on metadata related to multiple data tables in the UI, create relationships between the multiple data tables, including related fields and relationship types;

[0146] In response to the user's configuration operation on the formula panel, the system generates a derived indicator calculation method based on the formula expression entered by the user. The formula expression references fields from multiple data tables.

[0147] By integrating the relationships between multiple data tables with the formulas for calculating derived indicators, a semantic model is obtained.

[0148] In one possible implementation, when the user analysis request parsing module 502 parses the user analysis request into a multi-metric model based on the semantic model, it specifically performs the following:

[0149] Extract target analysis dimensions and target derived metrics from user analysis requests; determine multiple basic metrics on which the target derived metrics depend and the inter-table relationships involved in these basic metrics based on the semantic model; integrate the target analysis dimensions, multiple basic metrics, and the inter-table relationships involved in these basic metrics to obtain a multi-metric model.

[0150] In one possible implementation, when the data query module 504 performs queries based on the single-indicator models corresponding to multiple basic indicators to obtain query result sets corresponding to multiple basic indicators, it is specifically used for:

[0151] For each basic indicator, a query statement is generated based on the single indicator model corresponding to the basic indicator, and the query statement corresponding to the basic indicator is obtained; the query statements corresponding to multiple basic indicators are executed respectively, and the query result sets corresponding to multiple basic indicators are obtained respectively.

[0152] In one possible implementation, the multi-table analysis device may further include: a relationship detection module and a query statement rewriting module.

[0153] The relationship detection module is used to detect whether there are many-to-many relationships between the tables involved in each basic indicator.

[0154] The query rewriting module is used to rewrite the query statements corresponding to the basic indicators when there are many-to-many relationships between the tables involved in the basic indicators. This allows the basic indicators to be aggregated first according to the target analysis dimension during the query, and then associated with other tables.

[0155] In one possible implementation, the multi-table analysis apparatus may also include a dictionary data generation module.

[0156] The dictionary data generation module generates dictionary data based on the relationships in the semantic model. The dictionary data contains the mapping between primary key field values ​​and text field values ​​of the referenced data tables. The primary key field value uniquely identifies a record, and the text field value records the text information corresponding to the primary key field value.

[0157] The query rewriting module is also used to rewrite the query statements corresponding to multiple basic indicators based on dictionary data.

[0158] In one possible implementation, when the analysis result generation module 505 generates the analysis results corresponding to the user's analysis request based on the query result sets corresponding to multiple basic indicators and the calculation method of the target derived indicators, it is specifically used for:

[0159] The query result sets corresponding to multiple basic indicators are merged according to the target analysis dimension to obtain the merged result set;

[0160] The merged result set is processed according to the calculation method of the target derived indicators to obtain the calculation results of the target derived indicators;

[0161] Generate analysis results that include the calculation results of the target derived indicators, and obtain the analysis results corresponding to the user's analysis request.

[0162] In one possible implementation, the multi-table analysis apparatus may further include: a module for building inter-table dependencies and a module for updating dictionary data.

[0163] The inter-table dependency building module is used to establish dependencies between multiple data tables based on the associations in the semantic model.

[0164] The dictionary data update module is used to determine the affected dictionary data based on the dependencies between multiple data tables when data tables are updated, and to update the affected dictionary data according to the updated content of the updated data tables.

[0165] The multi-table analysis device provided in this invention does not require merging multiple data tables into a single physical wide table. Instead, it performs multi-table join calculations on demand during queries based on predefined relationships in the semantic model. When the data table structure changes, only the semantic model needs adjustment, without reconstructing the physical wide table, thus significantly reducing maintenance costs and improving model flexibility and business response speed. Furthermore, users do not need to master professional languages ​​such as DAX; they can trigger data query and calculation processes simply by initiating an analysis request containing derived indicators and analysis dimensions, greatly lowering the barrier to entry for multi-table analysis and enabling business personnel to independently complete in-depth analysis across business domains. In addition, the multi-table analysis device provided by this invention can flexibly respond to diverse user choices of analysis dimensions and indicators without disrupting the original data relationship structure, supporting ad-hoc, multi-path exploratory analysis, and effectively meeting the complex and ever-changing in-depth analysis needs in business decision-making scenarios.

[0166] This invention also provides an electronic device, which may include at least one processor and a memory connected to the processor.

[0167] The processor may be a central processing unit (CPU), an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement embodiments of the present invention; the memory may include high-speed RAM, and may also include non-volatile memory, such as at least one disk storage.

[0168] The memory is used to store computer programs, and the processor is used to execute the computer programs so that the electronic device can implement the multi-table analysis method provided in the above embodiments.

[0169] This invention also provides a computer storage medium carrying one or more computer programs. When the one or more computer programs are executed by an electronic device, the electronic device is able to implement the multi-table analysis method provided in the above embodiments.

[0170] This invention also provides a computer program product, including computer-readable instructions, which, when executed on an electronic device, enable the electronic device to implement the multi-table analysis method provided in the above embodiments.

[0171] It should also be noted that the device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. In addition, in the accompanying drawings of the device embodiments provided by this invention, the connection relationship between modules indicates that they have a communication connection, which can be implemented as one or more communication buses or signal lines.

[0172] Through the above description of the embodiments, those skilled in the art can clearly understand that the present invention can be implemented by means of software plus necessary general-purpose hardware, and of course, it can also be implemented by special-purpose hardware including application-specific integrated circuits, special-purpose CPUs, special-purpose memory, special-purpose components, etc. Generally, any function performed by a computer program can be easily implemented by corresponding hardware, and the specific hardware structure used to implement the same function can also be diverse, such as analog circuits, digital circuits, or special-purpose circuits. However, for the present invention, software program implementation is more often the preferred implementation method. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a readable storage medium, such as a computer floppy disk, USB flash drive, mobile hard disk, ROM, RAM, magnetic disk, or optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, training equipment, or network device, etc.) to execute the methods described in the various embodiments of the present invention.

[0173] In the above embodiments, the implementation can be achieved, in whole or in part, through software, hardware, firmware, or any combination thereof. When implemented in software, it can be implemented, in whole or in part, in the form of a computer program product.

[0174] The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of the present invention are generated. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions may be transmitted from one website, computer, training device, or data center to another website, computer, training device, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can store or a data storage device such as a training device or data center that integrates one or more available media. The available media may be magnetic media (e.g., floppy disks, hard disks, magnetic tapes), optical media (e.g., DVDs), or semiconductor media (e.g., solid-state drives (SSDs)).

Claims

1. A multi-table analysis method, characterized in that, include: When a user analysis request is received, if the user analysis request includes a target analysis dimension and a target derived indicator, then the user analysis request is parsed into a multi-indicator model based on a semantic model pre-built for multiple related data tables. The semantic model includes the relationship between the multiple data tables and the calculation method of the derived indicators defined for the multiple data tables. The multi-indicator model includes the target analysis dimension, multiple basic indicators and the relationship between the tables involved in the multiple basic indicators. The multiple basic indicators are the basic indicators on which the target derived indicators depend. The multi-indicator model is split into multiple single-indicator models according to the basic indicators, and the single-indicator models corresponding to the multiple basic indicators are obtained. The single-indicator model includes the target analysis dimension, the corresponding basic indicator, the data table on which the corresponding basic indicator depends, and the inter-table relationship involved in the corresponding basic indicator. Based on the single indicator models corresponding to the multiple basic indicators, queries are performed to obtain the query result sets corresponding to the multiple basic indicators. Based on the query result sets corresponding to the multiple basic indicators and the calculation method of the target derived indicators, the analysis results corresponding to the user analysis request are generated.

2. The multi-table analysis method according to claim 1, characterized in that, Also includes: If the user analysis request includes a target analysis dimension and multiple basic indicators, then according to the semantic model, the user analysis request is parsed into a multi-indicator model that includes the target analysis dimension, the multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators.

3. The multi-table analysis method according to claim 1 or 2, characterized in that, A semantic model is constructed for multiple related data tables, including: Obtain metadata from multiple related data tables from the data source and present the metadata of the multiple data tables in the UI interface; In response to a user's association operation on the metadata of the multiple data tables in the UI interface, an association relationship is created between the multiple data tables, the association relationship including the association field and the association type; In response to the user's configuration operation on the formula panel, a derived indicator calculation method is generated based on the formula expression entered by the user, wherein the formula expression references fields in the multiple data tables; The semantic model is obtained by integrating the relationships between the multiple data tables with the calculation formula of the derived indicators.

4. The multi-table analysis method according to claim 1, characterized in that, The step of parsing the user analysis request into a multi-metric model based on the semantic model includes: Extract the target analysis dimension and the target derived indicators from the user analysis request; Based on the semantic model, determine the multiple basic indicators on which the target derived indicator depends, as well as the inter-table relationships involved in the multiple basic indicators; By integrating the target analysis dimensions, the multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators, a multi-indicator model is obtained.

5. The multi-table analysis method according to claim 1 or 2, characterized in that, The step of querying based on the single-indicator models corresponding to the multiple basic indicators to obtain query result sets corresponding to the multiple basic indicators includes: For each of the basic indicators, a query statement is generated based on the single indicator model corresponding to the basic indicator to obtain the query statement corresponding to the basic indicator. Execute the query statements corresponding to the various basic indicators to obtain the query result sets corresponding to the various basic indicators.

6. The multi-table analysis method according to claim 5, characterized in that, After obtaining the query statements corresponding to the multiple basic indicators, and before executing the query statements corresponding to the multiple basic indicators, the process further includes: For each of the basic indicators, detect whether there is a many-to-many relationship between the tables involved in the basic indicator; If the basic indicators involve many-to-many relationships between tables, then rewrite the query statement corresponding to the basic indicators so that the basic indicators are first aggregated according to the target analysis dimension during the query, and then associated with other tables. If the table relationships involved in the basic indicators do not have a many-to-many relationship, then the query statement corresponding to the basic indicators remains unchanged.

7. The multi-table analysis method according to claim 5, characterized in that, After constructing the semantic model, before executing the query statements corresponding to the various basic indicators, the process further includes: Dictionary data is generated based on the associations in the semantic model. The dictionary data contains the mapping relationship between the primary key field values ​​and text field values ​​of the referenced data table. The primary key field value is used to uniquely identify the record, and the text field value is used to record the text information corresponding to the primary key field value. Based on the dictionary data, the query statements corresponding to the various basic indicators are rewritten.

8. The multi-table analysis method according to claim 1, characterized in that, The step of generating the analysis results corresponding to the user analysis request based on the query result sets corresponding to the multiple basic indicators and the calculation method of the target derived indicators includes: The query result sets corresponding to the multiple basic indicators are merged according to the target analysis dimension to obtain a merged result set; The merged result set is processed according to the calculation method of the target derived index to obtain the calculation result of the target derived index; An analysis result containing the calculation results of the target derived indicators is generated, thus obtaining the analysis result corresponding to the user analysis request.

9. The multi-table analysis method according to claim 7, characterized in that, Also includes: Based on the associations in the semantic model, establish the dependencies between the multiple data tables; When any of the multiple data tables is updated, the affected dictionary data is determined based on the dependencies between the multiple data tables; The affected dictionary data is updated based on the updated content of the updated data table.

10. A multi-table analysis device, characterized in that, include: The module includes a semantic model building module, a user analysis request parsing module, a multi-indicator model splitting module, a data query module, and an analysis result generation module. The semantic model construction module is used to construct a semantic model for multiple related data tables. The semantic model includes the relationship between the multiple data tables and the calculation method of the derived indicators defined for the multiple data tables. The user analysis request parsing module is used to, when receiving a user analysis request, if the user analysis request contains target derived indicators and target analysis dimensions, parse the user analysis request into a multi-indicator model containing the target analysis dimension, multiple basic indicators, and the inter-table relationships involved in the multiple basic indicators, according to the semantic model. The multiple basic indicators are the basic indicators on which the target derived indicators depend. The multi-indicator model splitting module is used to split the multi-indicator model into multiple single-indicator models according to the basic indicators, so as to obtain the single-indicator models corresponding to the multiple basic indicators respectively. The single-indicator model includes the target analysis dimension, the corresponding basic indicator, the data table on which the corresponding basic indicator depends, and the inter-table relationship involved in the corresponding basic indicator. The data query module is used to perform queries based on the single indicator models corresponding to the multiple basic indicators, and obtain the query result sets corresponding to the multiple basic indicators respectively. The analysis result generation module is used to generate analysis results corresponding to the user analysis request based on the query result sets corresponding to the multiple basic indicators and the calculation method of the target derived indicators.

Images