ChatBI-based data question and answer analysis system and method

Abstract

The invention discloses a ChatBI-based data question and answer analysis system and a ChatBI-based data question and answer analysis method. The system comprises a data source management module, a cue word management module, an SQL (Structured Query Language) set management module, a model management module and an application configuration module, the application configuration module is used for responding to a user configuration instruction, performing process arrangement on each selected module, and generating an executable data question and answer application; wherein the process arrangement comprises the steps of responding to a user query instruction, filling a database corresponding to the query instruction and an SQL question and answer pair into a placeholder corresponding to a cue word template, calling a large language model to generate an executable SQL statement, and executing in the corresponding database according to the executable SQL statement to obtain a result data set. The method has the advantages of flexible configuration, application decoupling, high chart matching degree, low production cost and the like.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data intelligent analysis, and more particularly to a data question and answer analysis system and method based on ChatBI. BACKGROUND

[0002] At present, BI (Business Intelligence) systems play an important role in enterprise decision-making, but the architecture and interaction mode of traditional BI have significant defects: 1. High threshold, relying on technical experts, requiring business personnel to master SQL (a structured query language) writing; 2. Insufficient real-time and flexibility, data analysis relies on already written SQL, and if the data query needs to be changed, the SQL needs to be rewritten; 3. Inadequate data analysis, analysis experience depends on individuals, and cannot perform more comprehensive data analysis.

[0003] Therefore, how to solve the above problems is a problem that needs to be solved by those skilled in the art. SUMMARY

[0004] In view of the above problems, the present application is proposed in order to provide a data question and answer analysis system and method based on ChatBI which overcomes the above problems or at least partially solves the above problems.

[0005] In order to achieve the above purpose, the present application adopts the following technical solutions: In a first aspect, the present application provides a data question and answer analysis system based on ChatBI, comprising: A data source management module for configuring a connection database; A prompt word management module for managing a prompt word template of a pre-set placeholder; A SQL set management module for managing a SQL question and answer pair set, each of the SQL question and answer pairs being associated with at least one standard question text and one verified executable SQL statement; A model management module for creating and managing multiple large language models and providing a unified calling interface; An application configuration module for responding to user configuration instructions, flowcharting the selected modules, and generating an executable data question and answer application; The flowcharting includes responding to user query instructions, filling the corresponding database and SQL question and answer pairs into the placeholder corresponding to the prompt word template, calling the large language model to generate an executable SQL statement, and executing the corresponding database according to the executable SQL statement to obtain a result data set.

[0006] In an optional embodiment, the data modeling module is further configured to select a table structure based on the database, and to annotate the table name and table field to enhance the database semantics.

[0007] In an optional embodiment, the executable SQL statement is subjected to syntax checking and / or security checking before execution.

[0008] In an optional embodiment, the prompt word management module further comprises a SQL result interpretation template, a SQL structure analysis template, and / or a question recommendation template, The SQL result interpretation template is configured to call a large language model to output an interpretation of the result data set; The SQL structure analysis template is configured to call a large language model to perform structure analysis on the result data set, automatically determine and call a corresponding chart component; The question recommendation template is configured to generate at least one relevant follow-up recommendation question based on the result data set.

[0009] In an optional embodiment, automatically determining and calling a corresponding chart component comprises: Parsing the SELECT clause and the GROUP BY clause in the executed SQL statement, identifying the fields appearing in the GROUP BY as dimensions, and identifying the fields in the SELECT clause that are different from the dimensions as indicators; According to the number and type of the dimensions and indicators, automatically selecting a corresponding chart type according to a preset chart matching rule set, wherein the rule set at least includes: When the number of indicator fields exceeds a preset threshold or a specified identifier appears, a list table is preferentially selected for display.

[0010] In an optional embodiment, a visualization module is included for managing the style configuration of the chart component.

[0011] In an optional embodiment, a dialogue configuration module is further included for configuring and managing the interaction parameters of the dialogue application.

[0012] In an optional embodiment, an application plug-in construction module is further included for constructing the data question and answer application as a browser plug-in package, which is awakened and executed in the webpage context accessed by the user in the running stage, and the query object is a database embedded or associated in the current webpage.

[0013] Based on the same inventive concept, the embodiments of the present application provide a data question and answer analysis method based on ChatBI, which is implemented based on the ChatBI-based data question and answer analysis system according to any one of the above embodiments, and the steps include: S1, configuring a database, a prompt word template, a SQL question and answer pair, and a large language model; S2, in response to the user query instruction, fill the database and SQL question and answer pair corresponding to the query instruction into the prompt word template corresponding placeholder, call the large language model to generate an executable SQL statement; S3, and execute according to the executable SQL statement in the corresponding database, obtain a result data set.

[0014] The embodiment of the application provides a data question and answer analysis system and method based on ChatBI, aiming to solve the problem of lack of intelligence in traditional BI systems; Compared with the prior art, the beneficial effects at least include: Flexible configuration, high reusability; the system provided by the application is a ChatBI production platform, which can configure multiple ChatBI applications at the same time, and when configuring the application, as long as an application scenario is configured, the remaining application scenarios can quickly reuse the existing configuration, and by replacing variables, the reuse can be achieved; Application decoupling; the application can realize plug-in interaction with the original system, compared with other mixed embedding methods with the system, the application of the ChatBI plug-in of the application and the current system can be decoupled, and then the plug-in introduction is realized; High chart matching degree; the application selects a chart by comprehensively analyzing the result data set and the structure of the sql executed by the sql, and visualizes the result. Compared with the chart type obtained by analyzing the result of the sql executed by the large model, the chart type is more accurate; Low production cost; the application proposes to combine a type of complex problems into a sql question and answer pair for the large model, which greatly improves the accuracy of the answer without fine tuning, and does not introduce training cost; Safe and reliable; the sql output by the large model will be subjected to safety check to ensure that the sql is a query statement and does not exist UPDATE INSERT DELETE type, so as to ensure that the data is not tampered with and is executed safely. BRIEF DESCRIPTION OF DRAWINGS

[0015] In order to more clearly illustrate the technical solutions in the embodiments of the application or the prior art, the following will briefly introduce the drawings needed to be used in the embodiments or prior art description. Obviously, the drawings in the following description are only embodiments of the application, and for those skilled in the art, other drawings can be obtained without creative labor on the basis of the provided drawings.

[0016] Figure 1 The structure diagram of the data question and answer analysis system based on ChatBI provided in the embodiment of the application. DETAILED DESCRIPTION

[0017] With reference to the accompanying drawings: clear and complete description of the technical solutions in the embodiments of the present application will be described below. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all. Based on the embodiments in the present application, all other embodiments obtained by a person of ordinary skill in the art without creative labor fall within the scope of the present application.

[0018] Embodiment one The embodiments of the present application disclose a ChatBI-based data question and answer analysis system, comprising: A data source management module is configured to connect a database. A prompt word management module is configured to manage a prompt word template of a preset placeholder. An SQL set management module is configured to manage a set of SQL question and answer pairs, each of which is associated with at least one standard question text and one verified executable SQL statement. A model management module is configured to create and manage a plurality of large language models and provide a unified calling interface. An application configuration module is configured to respond to a user configuration instruction, arrange selected modules in a process, and generate an executable data question and answer application. The process arrangement includes responding to a user query instruction, filling a database and an SQL question and answer pair corresponding to the query instruction into a placeholder corresponding to a prompt word template, calling a large language model to generate an executable SQL statement, and executing the executable SQL statement in a corresponding database to obtain a result dataset.

[0019] Preferably, the executable SQL statement is subjected to syntax checking and / or security checking before execution.

[0020] In some embodiments, The data source management module is configured to configure and connect each data source, implement structure query and SQL execution of a corresponding database, and manage database types including mysql, PostgreSQL, Sqlite, etc. Optionally, a new data source window is included in a data source management page. After filling in the ip, port, account password, etc. of a specified database, clicking the test button, displaying a successful connection, and clicking save, all database table structure information under the connection address is acquired during the saving process and saved according to different database names. After successful creation, a data source connection information is formed. The data source view details can show the database name, each database table name, and table field information, etc. under the data source.

[0021] As a preferred embodiment, the system further comprises a data modeling module for forming a data model based on the data source management module, selecting specific tables from the successfully connected data sources, and adding annotations to the table structure, including annotating unclear original table names or table fields to form a relatively clear database scheme for subsequent assembly of the prompt. Optionally, the data modeling page includes creating a new data model, checking the data source and the database under the data source in the new page, then selecting the corresponding table, clicking save, and forming a data model.

[0022] The present application does not directly operate the data source, but adds a layer of data model. On the one hand, it modifies the database table annotations to form a clearer database scheme without affecting the original source database table structure information. On the other hand, it avoids multiple queries of data source information and reduces query loss.

[0023] The prompt word management module is used to configure the prompt word templates used in each scene. The prompt word template contains placeholders that can be dynamically replaced with variable values. Optionally, the Prompt management page includes creating a new Prompt and inputting the Prompt type.

[0024] As a preferred example of the present embodiment, the prompt word management module includes an SQL generation template, an SQL result interpretation template, an SQL structure analysis template, and / or a question recommendation template. The SQL generation template is used to generate executable SQL statements. The SQL result interpretation template is used to call a large language model to output an explanation of the result dataset after obtaining the result dataset. The SQL structure analysis template is used to call a large language model to perform structural analysis on the result dataset, automatically decide and call corresponding chart components. The question recommendation template is used to generate at least one related follow-up recommended question based on the result dataset.

[0025] As a preferred example of the present embodiment, the automatic decision and calling of corresponding chart components include: Parsing the SELECT clause and the GROUP BY clause in the executed SQL statement, identifying the fields in the GROUP BY as dimensions, and identifying the fields in the SELECT clause that are different from the dimensions as indicators. According to the number and type of the dimensions and indicators, automatically selecting a corresponding chart type according to a preset chart matching rule set. The rule set at least includes: When the number of indicator fields exceeds a preset threshold or a specified identifier appears, a list table is preferentially selected for display.

[0026] Further, the SQL set management module is used for managing SQL question and answer pair sets, each of which is associated with at least one standard question text and one verified executable SQL statement.

[0027] The model management module is used for integrating multiple types of models, and unifying input and output parameter structures; the types include TTS (text-to-speech), ASR (automatic speech recognition) and LLM (large language model), the ASR is used for converting language into text, the TTS is used for reporting the finally generated result, and the LLM is used for text business processing. Each type of model includes multiple models, and a unified calling interface is provided for the models. Optionally, the model warehouse page includes a new model, which is used for selecting a specific model, filling in an API-key, and saving to create model information.

[0028] The application configuration module is used for responding to a user configuration instruction, performing flow arrangement on selected modules, and generating an executable data question and answer application; specifically, each node is configured through built-in flow nodes provided by the system, and a question and answer application is formed. After configuration is completed, testing experience can be performed, and then the application can be selected for publishing. Optionally, the application configuration page includes a new application, which is used for filling in basic information such as an application name, an application version and an application description, and then entering an application formal configuration page: selecting a data model from data modeling, selecting a dialogue configuration, sequentially selecting a SQL generation prompt, a SQL execution result explanation prompt, a SQL analysis prompt, a question recommendation prompt and corresponding model selection. After configuration is completed, a next step test run can be clicked, and dialogue questioning can be started on a test run page.

[0029] In an optional embodiment, referring to Figure 1 The system further includes a dialogue configuration module, which is used for configuring and managing interactive parameters of a dialogue application. For example, a new dialogue configuration is set, and opening words of the dialogue, context dialogue arguments, bottom terms, whether to display popular searches and recent searches, a time range of the popular searches and the recent searches and the like are managed.

[0030] In an optional embodiment, the system further includes a visualization module, which is used for creating a chart component, selecting a chart type, managing style configurations of the chart component, and displaying the chart component according to a corresponding chart component. In the present application, a set is separately established for each picture component used by an application, and the styles do not affect each other.

[0031] In an optional embodiment, the system further includes an application plug-in construction module, which is used for constructing the data question and answer application into a browser plug-in package after the test run is correct, that is, the saved and published application can be queried in the plug-in construction module, and after being downloaded and decompressed, the application is imported into an extension program and a management program page of the browser for installation. After installation, the application can be used after a website page is opened by using the browser.

[0032] Example 2 In this embodiment, the prompt word management module includes an SQL generation template, an SQL result interpretation template, an SQL structure analysis template, and / or a question recommendation template.

[0033] 1. Placeholders are provided for the SQL generation template to facilitate repeated use; in some implementation schemes, they take the following form: "You are now an SQL expert. You will be given a table structure in a database, and you need to generate an SQL query statement based on the problem." Here is a collection of question-and-answer pairs:

sql_examples

db_schema

sql_examples

[0034]

db_schema

[0035] The above is a successful example, and below is an example of a failed response: { "input": "Retrieve the total number of workers", "answerType": 1, "sql": "", "confirm": "No worker-related table information was found."} Output JSON data as required to facilitate result parsing and provide specific prompts to the large model when the response fails.

[0036] 2. Template for interpreting SQL results; example as follows: You are a data analyst. Please analyze the questions and answers, and keep your output to 100 words or less. question:{askText} answer:{res} 3. SQL structure analysis template; example as follows: “ You are now an SQL analyst who needs to analyze the composition of SQL queries, extracting the fields after the SELECT statement, the table names used, the query conditions, and the fields after the GROUP BY clause, and output the results in JSON format. The input SQL is: [sql], and the output should be in JSON format. For example: { "input": "SELECT degree, COUNT(*) AS count FROM teachers WHERE status= 1 GROUP BY degree;", "select_fields": [ "degree", "COUNT(*) AS count" ], "table_name": "teachers", "where_condition": "status = 1", "group_by_fields": [ "degree" ] } " In this prompt template, "[sql]" refers to the specific SQL statement, and the JSON data within it represents the requested SQL structure. Analyzing the structure of the SQL statement is to better match it with the chart.

[0037] 4. Recommended question template; example as follows: “ As a query assistant, here is the content of my question bank:

questions

[0038] " Example 3 Assume that when the application was created, the data source it connected to and the model it used was a database of teachers and students at a certain school. After opening the ChatBI plugin, you will be taken to the dialog page. You can choose to input text or voice. If you choose voice input, turn on your system microphone, and the voice prompt will be something like: "Distribution of educational backgrounds among the school's teachers." As mentioned earlier, the core process of the entire application is SQL generation, SQL execution, SQL result interpretation, SQL structure analysis, and question recommendation.

[0039] 1. SQL generation The plugin first calls the ASR service to perform speech-to-text conversion and obtain the text. Then, the system retrieves the configuration information associated with the application during its creation. The configurations required in the SQL generation process are as follows: the application's data modeling information (i.e., the database table structure), the SQL generation prompt, the SQL question-answer pair set, and the LLM used.

[0040] The original Prompt is as follows: "You are now an SQL expert. You will be given a table structure in a database, and you need to generate an SQL query statement based on the problem." Here is a collection of question-and-answer pairs:

sql_examples

sql_examples

db_schema

[0041] The expected output of the large model is: { "input": "Distribution of educational backgrounds among school teachers", "answerType": 0, "sql": "SELECT degree, COUNT(*) AS count FROM teachers WHERE status =1 GROUP BY degree;", "confirm": "" } Parse the results to obtain the SQL statement, perform SQL security checks to ensure data integrity. Ensure the SQL is a query statement and does not contain UPDATE, INSERT, or DELETE statements. Execute the SQL; the result is: [ { "count": 20, "top_degree": "PhD" }, { "count": 74, "top_degree": "Undergraduate" }, { "count": 41, "top_degree": "Master's Degree" } ] Part Two: Interpretation of SQL Results After the SQL execution yields the results, the application-configured SQL execution result explanation prompt is retrieved, and the large model outputs the corresponding result explanation.

[0042] Part 3: SQL Structure Analysis While interpreting the SQL results, the SQL structure can be analyzed in parallel.

[0043] Original prompt content “ You are now an SQL analyst who needs to analyze the composition of SQL queries, extracting the fields after the SELECT statement, the table names used, the query conditions, and the fields after the GROUP BY clause, and output the results in JSON format. The input SQL is: [sql], and the output should be in JSON format. For example: { "input": "SELECT native_place, COUNT(*) AS teacher_count FROM teachers WHERE status = 1 GROUP BY native_place ORDER BY teacher_count DESCLIMIT 3;", "select_fields": [ "native_place", "COUNT(*) AS teacher_count" ], "table_name": "teachers", "where_condition": "status = 1", "group_by_fields": [ "native_place" ] } " After placeholder replacement,

sql

SELECT degree,COUNT(*) AS count FROM teachers WHERE status = 1 GROUP BY degree;

[0044] Next, some more complex cases are illustrated. If the select field is greater than 2, in addition to the fields in the group, the remaining fields can be used as indicator fields. If the upper limit is exceeded, it is not easy to display using a column or pie chart, and a list table can be used for display.

[0045] If the group field is more than one, the group fields can be combined and marked as dimensions. When the select field appears *, the entire data result is marked for list table display.

[0046] For the mapping of English and Chinese in table field names, the corresponding table and table field can be queried from the previously annotated data model according to table_name (table name) and the field name appearing in select, so that the Chinese annotation can be matched. In this way, with the sql execution result and the analysis of the sql structure, the result can be visualized efficiently and accurately.

[0047] Four: problem recommendation; This step can be processed in parallel with the sql result explanation and the sql structure analysis, reducing the time consumption of the entire execution link.

[0048] In the problem recommendation process, first get the problem recommendation Prompt corresponding to the plug-in application, and then get a batch of problems searched in the last N days from the search record as the recommended range.

[0049] The above is the core process of ChatBI plug-in running once. After each step is executed, the plug-in displays the execution progress of the current link in real time to improve user experience. Finally, after a sentence of voice input, the corresponding result is visualized.

[0050] Embodiment four Based on the same inventive concept, the embodiment of the present application provides a data question and answer analysis method based on ChatBI, which is realized based on the ChatBI-based data question and answer analysis system according to any one of the above, and steps include: S1, configure a database, a prompt word template, a SQL question and answer pair and a large language model; S2, in response to a user query instruction, filling the database and the SQL question and answer pair corresponding to the query instruction into the placeholder corresponding to the prompt word template, calling the large language model to generate an executable SQL statement; S3, and executing in the corresponding database according to the executable SQL statement, obtaining a result data set.

[0051] Since the execution process of each step is consistent with the principles of each module in the foregoing system, it will not be repeated here, and please refer to the foregoing description.

[0052] Each embodiment in the specification is described in a progressive manner, and each embodiment focuses on the difference from other embodiments. The same or similar parts between each embodiment can be referred to each other. For the device disclosed by the embodiment, since it corresponds to the method disclosed by the embodiment, the description is relatively simple, and the related parts can be referred to the method part.

[0053] The above description of the disclosed embodiments enables a person skilled in the art to implement or use the present application. Various modifications to the embodiments will be apparent to those skilled in the art, and the general principles defined herein can be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A data question-answering analysis system based on ChatBI, characterized in that, include: The data source management module is used to configure database connections; The prompt word management module is used to manage the prompt word templates with preset placeholders; The SQL set management module is used to manage sets of SQL question-answer pairs; The model management module is used to create and manage models for multiple large languages; The application configuration module is used to respond to user configuration commands, orchestrate the selected modules in a process-oriented manner, and generate executable data request and response applications; The process orchestration includes responding to user query commands, filling the corresponding database and SQL question-and-answer pairs into the placeholders of the prompt word template, calling the large language model to generate an executable SQL statement, and executing the executable SQL statement in the corresponding database to obtain the result dataset.

2. The ChatBI-based data question-answering analysis system according to claim 1, characterized in that, It also includes a data modeling module, which is used to select table structures based on the database and to annotate and explain table names and fields to enhance database semantics.

3. The ChatBI-based data question-answering analysis system according to claim 1, characterized in that, The executable SQL statement is subjected to syntax and / or security checks, and is executed only after passing these checks.

4. The ChatBI-based data question-answering analysis system according to claim 1, characterized in that, The prompt word management module also includes SQL result interpretation templates, SQL structure analysis templates, and / or question recommendation templates. The SQL result interpretation template is used to call the large language model to output an interpretation of the result dataset; The SQL structure analysis template is used to call a large language model to perform structure analysis on the result dataset, automatically make decisions and call the corresponding chart components; The question recommendation template is used to generate at least one relevant subsequent recommendation question based on the result dataset.

5. The ChatBI-based data question-answering analysis system according to claim 4, characterized in that, Automatically make decisions and invoke the corresponding chart components, including: Parse the SELECT and GROUP BY clauses in the executed SQL statement, identify the fields appearing in GROUP BY as dimensions, and identify the fields in the SELECT clause that are different from the dimensions as metrics; Based on the number and type of the dimensions and indicators, and according to a preset set of chart matching rules, the corresponding chart type is automatically selected; wherein, the set of chart matching rules includes at least: When the number of indicator fields exceeds the preset threshold or a specified identifier appears, a list table will be selected for display.

6. The ChatBI-based data question-answering analysis system according to claim 5, characterized in that, It includes a visualization module for managing the style configuration of chart components.

7. The ChatBI-based data question-answering analysis system according to claim 1, characterized in that, It also includes a dialogue configuration module for configuring and managing the interaction parameters of the dialogue application.

8. The ChatBI-based data question-answering analysis system according to claim 1, characterized in that, It also includes an application plugin building module, used to build the data request and response into a browser plugin package.

9. A data question-answering analysis method based on ChatBI, characterized in that, The steps of using the ChatBI-based data question-answering analysis system as described in any one of claims 1-8 include: S1. Configure the database, prompt word templates, SQL question-answer pairs, and large language model; S2. Respond to the user's query command, fill the corresponding database and SQL question-and-answer pair into the placeholders of the prompt word template, and call the large language model to generate an executable SQL statement; S3. Execute the executable SQL statement in the corresponding database to obtain the result dataset.

Images