BI analysis method and system based on large model and storage medium

Abstract

The invention discloses a BI analysis method and system based on a large model, and a storage medium, relates to the technical field of BI analysis, and solves the problems that an existing BI analysis system seriously limits the universality and user interaction efficiency of the system, and the BI analysis system is poor in data processing capability and algorithm limitation, and the user interaction efficiency is poor. And the accuracy and reliability of an analysis conclusion are reduced. The method comprises the following steps of: retrieving a query problem of effective content by utilizing an intention large model, matching a data table structure, and optimizing fields in the matched data table structure to obtain a new data table structure; based on the new data table structure, converting the query problem into a corresponding sql query statement by the intention large model; obtaining a final sql query statement based on a mapping relation between the new data table structure and the original data table structure; based on the final sql query statement, outputting an answer corresponding to the final sql query statement; according to the method, the interactivity, flexibility and query efficiency of BI analysis are improved.

Description

Technical Field

[0001] The present invention belongs to the field of BI analysis, and specifically relates to a BI analysis method, system and storage medium based on a large model. Background Art

[0002] BI analysis is a process of using data warehouse, data mining and data display technologies for data analysis to achieve business value. Business data is extracted, transformed and then loaded into the data warehouse. With the development of self-service analysis platforms, enterprises hope that users can independently explore and analyze data through the platform to shorten the time cycle from data to business insights. However, existing BI analysis systems rely on structured query language (SQL) or preset scripts to achieve data interaction, requiring users to have professional programming and database operation knowledge, resulting in non-technical users being unable to independently complete complex analysis tasks, seriously limiting the universality of the system and the user interaction efficiency; and due to insufficient data processing capabilities and algorithm limitations of BI analysis systems, when the quantity and dimension of data surge, result deviations are likely to occur, reducing the accuracy and reliability of analysis conclusions.

[0003] Therefore, the present invention provides a BI analysis method, system and storage medium based on a large model. Summary of the Invention

[0004] The present invention aims to solve at least one of the technical problems existing in the prior art; for this purpose, the present invention proposes a BI analysis method, system and storage medium based on a large model, which is used to solve the technical problems that the existing BI analysis system seriously limits the universality of the system and the user interaction efficiency, and the BI analysis system reduces the accuracy and reliability of analysis conclusions due to insufficient data processing capabilities and algorithm limitations.

[0005] To achieve the above object, the first aspect of the present invention provides a BI analysis method based on a large model, including the following steps:

[0006] Obtain the user's query problem, and the intent large model determines whether the query problem is valid content; if yes, rewrite the query problem; if no, input the query problem into the FAQ to search for the answer corresponding to the query problem;

[0007] The intent large model retrieves the data table structure matching the query problem, and optimizes the fields in the matching data table structure to obtain a new data table structure;

[0008] Use the rewritten query problem and the new data table structure as a prompt to input into the intent large model, and the intent large model converts the query problem into a corresponding SQL query statement;

[0009] Based on the mapping relationship between the new data table structure and the original data table structure, map the new fields in the SQL query statement to the fields in the original data table structure to obtain the final SQL query statement; where the original data table structure is the unoptimized data table structure.

[0010] The intent large model outputs the answer corresponding to the final SQL query statement based on the final SQL query statement.

[0011] Preferably, the construction process of the intent large model includes the following steps:

[0012] Obtain a number of query statements and corresponding answers from historical data;

[0013] Integrate the query statements and the corresponding answer tags into several groups of training data and test data; use the training data to train the artificial intelligence model; use the test data to test the trained artificial intelligence model, and adjust the artificial intelligence model according to the test results; finally obtain an intent large model with the query statement as the input and the answer tag as the output; where the artificial intelligence model is a BP neural network model or an RBF neural network model; and the answer tag corresponds and matches the preset answer.

[0014] Preferably, collect the user's query questions, the original data table structure and the new data table structure, mark them as training corpus, and input the training corpus into the intent large model for training at the same time interval to optimize the intent large model.

[0015] Preferably, the intent large model determines whether the query question is valid content, including:

[0016] Take the query content and the data table structure as a prompt and input it into the intent large model. The intent large model calculates the association strength value between the query statement and the data table structure, and determines whether the association strength value is greater than the preset strength threshold; if so, mark the query question as valid content; if not, mark the query question as invalid content.

[0017] Preferably, the rewriting of the query question includes:

[0018] The user expresses the query intent in natural language, and the intent large model converts the user's query question into natural language in the database.

[0019] Preferably, the acquisition method of the new data table structure is: rewrite the non-English fields in the original data table structure into English fields.

[0020] Preferably, the intent large model calculates the association strength value between the query statement and the data table structure, including:

[0021] Perform word segmentation and stop word removal on the query statement, such as Chinese word segmentation tools like Jieba and THULAC, and English word segmentation tools like NLTK and spaCy; extract the keywords of the query question, and use a pre-trained NLP model (such as SBERT) to convert the keywords of the query statement and the field names in the data table structure into vectors, calculate the cosine similarity between the keyword vectors and the field name vectors, and obtain the association strength value between the query statement and the data table structure.

[0022] Preferably, the answers corresponding to the search query questions include:

[0023] When the query question is invalid content, input the query question into the FAQ to search for answers, extract the intersection between the BM25 similarity recall of Elasticsearch and the vector library recall as the answer corresponding to the query question searched by the FAQ; determine whether the FAQ matching score exceeds the preset score; if so, return the answer; if not, input the query question into the RAG to search for answers and return the answer corresponding to the query question.

[0024] The second aspect of the present invention provides a BI analysis system based on a large model, including a data processing module and a retrieval module;

[0025] Data processing module: used to obtain the user's query question and determine whether the query question is valid content; if so, rewrite the query question; if not, input the query question into the FAQ to search for the answer corresponding to the query question;

[0026] Retrieve the data table structure that matches the query question by the intent large model, and optimize the fields in the matched data table structure to obtain a new data table structure;

[0027] Retrieval module: input the rewritten query question and the new data table structure as a prompt into the intent large model, and the intent large model converts the query question into a corresponding sql query statement;

[0028] Based on the mapping relationship of fields between the new data table structure and the original data table structure, map the new fields in the sql query statement to the fields in the original data table structure to obtain the final sql query statement;

[0029] The intent large model outputs the answer corresponding to the final sql query statement based on the final sql query statement.

[0030] The third aspect of the present invention provides a computer-readable storage medium, on which a computer program is stored, and the steps of the method are implemented when the program is executed by a processor.

[0031] Compared with the prior art, the beneficial effects of the present invention are:

[0032] The present invention automatically determines whether the user's query is valid content through an intent large model, and decides whether to rewrite or search the FAQ according to the query content, which greatly reduces the need for manual intervention; by rewriting the query and optimizing the data table structure, the intent large model can better understand the user's intent, thereby generating more accurate SQL query statements; for invalid content, the FAQ search mechanism is used. For invalid or ambiguous queries, the system can provide preset answers, increasing the accuracy of the query results; this process can handle various types of user queries, including valid and invalid queries, showing a high degree of flexibility; and the optimization of the data table structure and the mapping mechanism of new fields facilitate the expansion of the system, enabling the system to easily adapt to new data types and query requirements; in summary, the present invention uses a large model to parse the user's natural language query and combines the context semantics to confirm the required data tables and fields. While the user query is more flexible, the query efficiency is increased. Users without a technical background can also independently build a data analysis model according to their needs, conduct in-depth data mining and analysis. It is a technical solution with strong interactivity and high query accuracy, improving the interactivity, flexibility and query efficiency of BI analysis. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0034] Figure 1 It is a schematic flowchart of the method of the present invention;

[0035] Figure 2 It is a schematic flowchart of the FAQ retrieval method of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0036] The following will clearly and completely describe the technical solutions of the present invention in combination with the embodiments. Obviously, the described embodiments are only some embodiments of the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope of protection of the present invention.

[0037] Please refer to Figure 1 , the first aspect embodiment of the present invention provides a BI analysis method based on a large model, including the following steps:

[0038] Obtain the user's query question, and the intent large model determines whether the query question is valid content; if yes, rewrite the query question; if no, input the query question into the FAQ to search for the corresponding answer to the query question;

[0039] The specific query question rewriting is as follows: The user expresses the query intent in natural language, and the intent large model converts the user's query question into natural language in the database.

[0040] For example: Rewrite the user's query "Query the total photovoltaic installation volume in Anhui Province in November 2023" as "Query the total photovoltaic installation volume where province = Anhui Province, year = 2023, month = November".

[0041] The construction process of the intent large model is as follows:

[0042] Obtain several query statements and corresponding answers from historical data;

[0043] Integrate the query statements and corresponding answer tags into several groups of training data and test data; use the training data to train the artificial intelligence model; use the test data to test the trained artificial intelligence model, and adjust the artificial intelligence model according to the test results; finally obtain an intent large model with query statements as input and answer tags as output; among them, the artificial intelligence model is a BP neural network model or an RBF neural network model; and the answer tags correspond and match with the preset answers.

[0044] In addition, collect the user's query questions, the original data table structure, and the new data table structure, mark them as training corpus, and input the training corpus into the intent large model for training at the same time interval to optimize the intent large model.

[0045] The intent large model retrieves the data table structure that matches the query question, and optimizes the fields in the matched data table structure to obtain a new data table structure;

[0046] Since there may be problems with non-standard field naming when building the original data table structure, to ensure the inference effect of the subsequent coder large model, after obtaining the table structure, rewrite the fields through the rewrite large model, and uniformly rewrite Chinese fields and pinyin fields as English fields;

[0047] For example, the table structure is: "CREATE TABLE tb_256_guangfumoxingxinxin(id VARCHAR(255) COMMENT 'Serial number', householdNumber VARCHAR(255) COMMENT 'Field description of household number: household number', householdName VARCHAR(255) COMMENT 'Field description of household name: household name', capacity varchar(255) DEFAULT '0' COMMENT 'Field description of photovoltaic capacity: photovoltaic capacity', shengfen VARCHAR(255) COMMENT 'Field description of province: province, example: Anhui Province', dishi VARCHAR(255) COMMENT 'Field description of city: city, example: Hefei City', quxian TEXT COMMENT 'Field description of district: district, example: Shushan District, Feixi County', nianfen INT DEFAULT '0' COMMENT 'Year', yuefen INT DEFAULT '0' COMMENT 'Month', taskId INT NOT NULL COMMENT 'Task id'. Rewrite the Chinese and pinyin fields among them into English;

[0048] "CREATE TABLE tb_256_guangfumoxingxinxin(id VARCHAR(255) COMMENT 'Serial number', accountNumber VARCHAR(255) COMMENT 'Field description of household number: household number', accountName VARCHAR(255) COMMENT 'Field description of household name: household name', province VARCHAR(255) COMMENT 'Field description of province: province, example: Anhui Province', city VARCHAR(255) COMMENT 'Field description of city: city, example: Hefei City', district TEXT COMMENT 'Field description of district: district, example: Shushan District, Feixi County', year INT DEFAULT '0' COMMENT 'Year', month INT DEFAULT '0' COMMENT 'Month', taskId INT NOT NULL COMMENT 'Task id'".

[0049] Among them, the intention large model judges whether the query question is valid content, including the following analysis process:

[0050] Input the query content and the data table structure as prompts into the large language model; among them, there may be multiple data tables in the data platform. The most relevant table is determined by the semantic large language model in combination with the entities and attributes mentioned in the user's query.

[0051] For example, the user's query is "The growth of Hefei's photovoltaic installed capacity over the years, presented in a pie chart", and there are multiple data tables in the data platform. At this time, combine the user's query statement and the data tables into a prompt.

[0052] "Query statement: The growth of Hefei's photovoltaic installed capacity over the years, presented in a pie chart, table structure:

[0053] CREATE TABLE light_volts(id VARCHAR(255) COMMENT 'Serial number', household_number VAR CHAR(255) COMMENT 'Household number field description: Household number', household_name VARCHAR(255) COMMENT 'Household name field description: Household name', capacity varchar(255) DEFAULT '0' COMMENT 'Photovoltaic capacity field description: Photovoltaic capacity', shengfen VARCHAR(255) COMMENT 'Province field description: Province, example: Anhui Province', dishi VARCHAR(255) COMMENT 'City field description: City, example: Hefei City', quxian TEXT COMMENT 'District field description: District, example: Shushan District, Feixi County', nianfen INT DEFAULT '0' COMMENT 'Year', yuefen INT DEFAULT '0' COMMENT 'Month', taskId INT NOT NULL COMMENT 'Task id';

[0054] CREATE TABLE power_grid(id VARCHAR(255) COMMENT 'Serial number', household_number VARC HAR(255) COMMENT 'Household number field description: Household number', household_name VARCHAR(255) COMMENT 'Household name field description: Household name', voltagelevel varchar(255) DEFAULT '0' COMMENT 'Voltage level field description: Voltage level', taskId INT NOT NULL COMMENT 'Task id';". The large language model identifies the most suitable single table or multiple tables as the SQL query tables based on the query problem and the data table structure.

[0055] The intention is to perform word segmentation and stop word removal on the query statement using large language models, such as Chinese word segmentation tools like Jieba and THULAC, and English word segmentation tools like NLTK and spaCy; extract the keywords of the query problem, and use a pre-trained NLP model (such as SBERT) to convert the keywords of the query statement and the field names in the data table structure into vectors, calculate the cosine similarity between the keyword vectors and the field name vectors, and obtain the association strength value between the query statement and the data table structure;

[0056] For example: "Query statement: Query the household with the smallest installed photovoltaic equipment in Hefei City in 2023. Table fields: year int DEFAULT '0' COMMENT 'Year, example: 2023', state varchar(255) COMMENT 'Province field description: Province, example: Anhui Province', city varchar(255) COMMENT 'City field description: City, example: Hefei City', capacity varchar(255) DEFAULT '0' COMMENT 'Photovoltaic capacity field description: The household with the smallest installed photovoltaic equipment';

[0057] Among them, the matching between the query problem and the data table structure is shown in Table 1 below;

[0058] Table 1 Matching Field Table

[0059]

[0060] Convert the keyword phrases of the query conditions and the keywords of the table fields into vector representations through a vector conversion model respectively, and calculate the cosine similarity between the two vectors.

[0061] Judge whether the association strength value is greater than the preset strength threshold; if yes, mark the query problem as valid content; if no, mark the query problem as invalid content.

[0062] Use the rewritten query problem and the new data table structure as prompts to input into the intention large language model, and the intention large language model will convert the query problem into the corresponding SQL query statement;

[0063] For example: "create table task_id_information(id varchar(255) COMMENT 'Serial number', GC_ID varchar(255) COMMENT 'GC_ID', customer_account varchar(255) COMMENT 'Account number field description: Account number', customer_name varchar(255) COMMENT 'Customer name field description: Customer name', capacity varchar(255) DEFAULT '0' COMMENT 'Photovoltaic capacity field description: Photovoltaic capacity', installed_date varchar(255) COMMENT 'Installation date field description: Installation date', state varchar(255) COMMENT 'Province field description: Province, example: Anhui Province', city varchar(255) COMMENT 'City field description: City, example: Hefei City', district text COMMENT 'District field description: District, example: Shushan District, Feixi County', year int DEFAULT '0' COMMENT 'Year', month int DEFAULT '0' COMMENT 'Month', task_id int NOT NULL COMMENT 'Task id')\nPlease answer the following question based on the above data table\nQuestion: Which user has the smallest installed photovoltaic capacity in Feixi County, Hefei City in November 2023\nPlease output the final SQL query statement, starting with SELECT, and do not output other content".

[0064] Based on the mapping relationship between the new data table structure and the original data table structure, map the new fields in the sql query statement to the fields in the original data table structure to obtain the final sql query statement; among them, the original data table structure is the unoptimized data table structure.

[0065] For example: After obtaining the mapping table of new and old fields and the sql query statement output by the model, replace the new fields in the query statement with the fields of the original table structure.

[0066] For example: The sql query statement output by the model is:

[0067] After performing field mapping on "SELECT customer_name,capacity FROM task_id_information WHERE city='Hefei City' AND district='Feixi County' AND year=2023 AND month=11 ORDER BY capacity ASC LIMIT 1;", the final SQL query statement obtained is

[0068] "SELECT household_name,guangfurongliang FROM task_id_information WHERE dishi='Hefei City' AND quxian='Feixi County' AND nianfen=2023 AND yuefen=11 ORDER BY guangfurongliang ASC LIMIT 1";

[0069] The intention large model outputs the answer corresponding to the final SQL query statement based on the final SQL query statement.

[0070] Please refer to Figure 2 , when the query question is invalid content, input the query question into the FAQ to search for the answer, extract the intersection between the BM25 similarity recall of Elasticsearch and the vector library recall as the answer corresponding to the query question of the FAQ search, and obtain the FAQ matching score between the query question and the answer; determine whether the FAQ matching score exceeds the preset score; if yes, return the answer; if not, input the query question into RAG to search for the answer and return the answer corresponding to the query question.

[0071] The second aspect of the present invention provides a BI analysis system based on a large model, including a data processing module and a retrieval module;

[0072] Data processing module: used to obtain the query question of the user and determine whether the query question is valid content; if yes, rewrite the query question; if not, input the query question into the FAQ to search for the answer corresponding to the query question;

[0073] The intention large model retrieves the data table structure matching the query question and optimizes the fields in the matching data table structure to obtain a new data table structure;

[0074] Retrieval module: used to input the rewritten query question and the new data table structure as a prompt into the intention large model, and the intention large model converts the query question into a corresponding SQL query statement;

[0075] Based on the mapping relationship of fields between the new data table structure and the original data table structure, map the new fields in the SQL query statement to the fields in the original data table structure to obtain the final SQL query statement;

[0076] The intention large model outputs the answer corresponding to the final SQL query statement based on the final SQL query statement.

[0077] The third aspect of the present invention provides a computer-readable storage medium, on which a computer program is stored, and the program is executed by a processor to implement the steps of the method.

[0078] Some of the data in the above formula is calculated by removing the dimension and taking its numerical value. The formula is obtained by software simulation of a large amount of collected data to get a formula closest to the actual situation; the preset parameters and preset thresholds in the formula are set by those skilled in the art according to the actual situation or obtained through simulation of a large amount of data.

[0079] The above embodiments are only used to illustrate the technical method of the present invention and not to limit it. Although the present invention has been described in detail with reference to the preferred embodiments, those of ordinary skill in the art should understand that the technical method of the present invention can be modified or equivalently replaced without departing from the spirit and scope of the technical method of the present invention.

Claims

1. A BI analysis method based on a large model, characterized in that: The following steps are involved: Obtain the user's query question and input it into the intent big model, which determines whether the query question is valid content; If yes, rewrite the query; If no, enter the query question into the FAQ and search for the answer to the query question; The intent big model retrieves the data table structure that matches the query question, and optimizes the fields in the matching data table structure to obtain a new data table structure; The rewritten query question and the new data table structure are input into the intention big model as prompts. The intention big model converts the query question into the corresponding SQL query statement. Based on the mapping relationship between the new data table structure and the original data table structure, the new fields in the SQL query statement are mapped to the fields in the original data table structure to obtain the final SQL query statement; wherein the original data table structure is an unoptimized data table structure; The intention model outputs the answer corresponding to the final SQL query statement based on the final SQL query statement.

2. The BI analysis method based on a large model according to claim 1, characterized in that: The process of constructing the intention big model includes the following steps: Get several query statements and corresponding answers from historical data; Integrate query statements and corresponding answer labels into several groups of training data and test data; use the training data to train the artificial intelligence model; use the test data to test the trained artificial intelligence model, and adjust the artificial intelligence model according to the test results; finally obtain an intention model with query statements as input and answer labels as output; wherein the artificial intelligence model is a BP neural network model or a RBF neural network model; and the answer labels are matched with preset answers.

3. The BI analysis method based on a large model according to claim 2 is characterized in that: Also includes: The user's query questions, the original data table structure and the new data table structure are collected and marked as training corpus. The training corpus is input into the intent big model for training at equal intervals to optimize the intent big model.

4. The BI analysis method based on a large model according to claim 3 is characterized in that: The intention model determines whether the query question is valid content, including: The query content and data table structure are input as prompts into the intention big model. The intention big model calculates the association strength value between the query statement and the data table structure, and determines whether the association strength value is greater than the preset strength threshold; if yes, the query question is marked as valid content; if not, the query question is marked as invalid content.

5. The BI analysis method based on a large model according to claim 4 is characterized in that: The query question is rewritten to include: Users express their query intentions in natural language, and the intention model is used to convert their query questions into natural language in the database.

6. The BI analysis method based on a large model according to claim 5 is characterized in that: The new data table structure is obtained by rewriting the non-English fields in the original data table structure into English fields.

7. The BI analysis method based on a large model according to claim 4 is characterized in that: The intention big model calculates the association strength value between the query statement and the data table structure, including: Preprocess the query statement, extract the keywords of the query question, convert the keywords of the query statement and the field names in the data table structure into vectors, calculate the cosine similarity between the keyword vector and the field name vector, and obtain the association strength value between the query statement and the data table structure.

8. The BI analysis method based on a large model according to claim 4 is characterized in that: The answer to the search query question includes: When the query question is invalid content, the query question is input into the FAQ to search for answers, and the intersection between Elasticsearch's BM25 similarity recall and vector library recall is extracted and marked as the answer corresponding to the query question of the FAQ search; determine whether the FAQ matching score exceeds the preset score; if yes, return the answer; if not, input the query question into RAG to search for answers, and return the answer corresponding to the query question.

9. A BI analysis system based on a big model, operating based on a BI analysis method based on a big model according to any one of claims 1 to 8, characterized in that: It includes a data processing module and a retrieval module; Data processing module: used to obtain the user's query question and determine whether the query question is valid content; if so, rewrite the query question; If no, enter the query question into the FAQ and search for the answer to the query question; The intent big model retrieves the data table structure that matches the query question, and optimizes the fields in the matching data table structure to obtain a new data table structure; Retrieval module: used to input the rewritten query question and the new data table structure as prompts into the intention model, and the intention model converts the query question into the corresponding SQL query statement; Based on the mapping relationship between the fields of the new data table structure and the original data table structure, the new fields in the SQL query statement are mapped to the fields in the original data table structure to obtain the final SQL query statement; The intention model outputs the answer corresponding to the final SQL query statement based on the final SQL query statement.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: The program is executed by a processor to implement the steps of the method according to any one of claims 1 to 9.

Images