Method for generating sql statement based on intermediate semantic layer and related components

Abstract

The application discloses a method for generating SQL statements based on an intermediate semantic layer and related components, and the method comprises the following steps: receiving a natural language query request input by a user, and pre-processing the natural language query request; obtaining a semantic context by using a multi-dimensional semantic model; inputting the natural language query request and the semantic context into a large language model, and constraining the large language model to generate an MDX query statement as an intermediate semantic layer within an MDX grammar subset; performing two-stage checking on the MDX query statement, including syntax checking and semantic checking; after the MDX query statement passes the two-stage checking, performing deterministic compilation on the MDX query statement to obtain a target SQL statement; performing safety checking and parameterization processing on the target SQL statement, and then outputting the target SQL statement as a return result of the natural language query request. The application can enhance the effect of converting a natural language into a SQL statement.

Description

Technical Field

[0001] This invention relates to the field of natural language processing technology, and in particular to a method for generating SQL statements based on an intermediate semantic layer and related components. Background Technology

[0002] In enterprise digital business analytics scenarios, in addition to routine data queries on fixed visual dashboards, business managers and operations personnel generally have numerous exploratory, ad-hoc, and multi-dimensional data analysis needs. These queries are no longer limited to simple data filtering and statistics; they often incorporate complex OLAP (Enterprise Multidimensional Analytics) logic, such as multi-dimensional cross-aggregation, hierarchical drill-down analysis, time series comparison, and periodic cumulative calculations. Typical examples include year-on-year and month-on-month analysis of indicators across different time periods, cross-statistical analysis of multi-dimensional data combinations, and hierarchical drill-down and investigation from macro-level summaries to micro-level details. These complex multi-dimensional analysis queries often require specialized SQL syntax, such as multi-table joins, nested subqueries, and window function calculations. In the traditional model, this relies entirely on enterprise analysts and BI engineers manually understanding business requirements and writing corresponding SQL statements to complete the query development. This manual development model is not only time-consuming and inefficient—a single standardized multi-dimensional analysis SQL statement containing multi-table joins, time series calculations, and nested logic typically requires tens of minutes of development and debugging time—but it is also highly susceptible to human error in key areas such as time window definition, dimension association matching, and aggregation rule selection, leading to distorted query results. Meanwhile, mainstream OLAP databases, including ClickHouse, Apache Doris, and PostgreSQL, exhibit significant dialectal differences in window function syntax, date and time calculation rules, and aggregate function adaptation logic. This further increases the cost of manual writing, debugging, and adaptation, and significantly raises the probability of statement errors, thus hindering the efficiency and accuracy of enterprise data analysis. Given this industry situation, how to automate and accurately convert business natural language requirements into multidimensional analysis SQL through technological means has become a core technical challenge that urgently needs to be addressed in the field of enterprise intelligent data analysis.

[0003] To address the need for intelligent conversion of natural language to SQL, the industry has developed several mainstream technical routes around NL2SQL technology, which are widely used in scenarios such as intelligent data query, semantic parsing, and automatic SQL generation. However, all existing technologies have unavoidable inherent defects when adapting to complex multidimensional OLAP analysis scenarios, and cannot meet the needs of industrial-grade accurate querying.

[0004] The most widely used approach currently is the end-to-end sequence generation technology based on large-scale language models. This approach treats natural language to SQL conversion as a machine translation-like end-to-end task, directly inputting natural language query statements and database structure information, and relying on the model's probabilistic reasoning capabilities to directly output complete SQL text. Related technologies include semantic parsing schemes based on the T5 framework and SeaD model, as well as engineered generation schemes based on general-purpose large language models such as ChatGPT and GPT-4. Existing published patents also largely follow this end-to-end generation approach, extracting query elements through entity recognition and then concatenating prompts to drive a large model to generate SQL. This approach is well-suited for basic scenarios with simple structures and single-table queries, but its shortcomings become extremely prominent when applied to multi-dimensional, multi-table OLAP analysis scenarios. First, this approach cannot reliably derive multi-table join paths. Data models in multidimensional analysis scenarios typically include a single fact table and multiple dimension tables. The multi-table join logic directly determines the accuracy of query results. However, large language models can only rely on corpus statistical features and text similarity probabilistically derive JOIN relationships, lacking precise foreign key metadata constraints. This makes them highly susceptible to illusions such as incorrect matching of join fields and fabricated invalid joins. Moreover, the more data tables there are, the higher the probability of errors, with no fundamental solution. Second, this approach suffers from severe multidimensional semantic loss. The natural language statements of enterprise business queries implicitly contain a large number of standardized OLAP semantic rules, including dimension grouping logic, indicator aggregation methods, and time-series offset comparison rules. End-to-end models cannot rely on structured semantic systems to identify fixed business logic; they can only implicitly learn mapping relationships through training corpora. When faced with similar indicators in business systems with similar names, different aggregation granularities, and different statistical calibers, problems such as misjudgment of aggregation rules and disordered dimension matching are highly likely to occur. Finally, this approach lacks general cross-data source dialect adaptation capabilities. Model training is usually bound to a single database SQL dialect. Faced with the complex environment of multiple databases and multiple data warehouses in an enterprise, the model needs to be retrained and fine-tuned after switching data sources, resulting in extremely high adaptation costs and making it unsuitable for the diversified data architecture of enterprises.

[0005] Another mainstream technology in the industry is the template filling and classifier decomposition scheme. This scheme abandons the end-to-end holistic generation model and breaks down the SQL generation task into multiple sub-tasks such as field selection, aggregation method matching, filtering condition configuration, and grouping rule setting. Independent classifiers predict and fill in the blanks for each sub-task, with typical examples including X-SQL and SQLNet models. This scheme reduces the complexity of a single model prediction through task decomposition and improves stability in simple single-table query scenarios. However, its scenario adaptability is extremely limited. It can only adapt to basic queries without nesting or complex calculations, and cannot handle the complex syntax logic commonly found in multidimensional analysis scenarios, such as nested subqueries, multi-level calculations, and multi-condition composite filtering. The fixed template structure cannot adapt to the flexible calculation paradigms of multidimensional analysis. Furthermore, this scheme lacks business semantic verification capabilities, relying entirely on the classifier's prediction results to complete parameter filling. When problems such as misjudgment of indicator aggregation methods or mismatch between dimensions and indicators occur, the system cannot identify errors or automatically correct them, making it difficult to guarantee query fault tolerance and accuracy.

[0006] To circumvent the high uncertainty issues in end-to-end generation, the industry has developed a layered generation technology approach based on intermediate representations. This approach adds an independent intermediate representation layer between natural language and SQL, breaking down a single, highly complex generation task into two low-complexity stages: natural language to intermediate representation and intermediate representation to SQL. The IRNet model and its accompanying SemQL custom intermediate representation system proposed by Microsoft Research Asia are representative solutions of this approach. By constructing an abstract syntax tree to decompose the generation logic of nested SQL, it first completes the SQL skeleton prediction and then fills in the specific query entities, effectively reducing the difficulty of generating complex SQL. This layered decoupling technology approach has significant advantages and is the core reference direction of this invention; however, existing intermediate representation solutions still have key technical shortcomings. SemQL, as a general-purpose intermediate language, is designed to adapt to the nested logic of general SQL structures. It has not been semantically optimized for OLAP multidimensional analysis scenarios. Its syntax cannot support core business semantics such as dimension hierarchy, indicator attributes, time series offsets, and multidimensional topology. It can only guarantee the integrity of the SQL syntax structure and cannot verify the business rationality of the query content. In scenarios involving refined multidimensional time series comparisons and hierarchical drill-down analysis, it is highly prone to syntactically valid but semantically incorrect queries. More importantly, the existing IRNet solution's intermediate representation to SQL conversion still relies on a neural network decoder for probability generation, failing to completely eliminate the probabilistic limitations of the model. The entire conversion chain remains uncertain and cannot achieve stable industrial-grade output.

[0007] In addition, the traditional OLAP field has a mature technical system using MDX as the terminal query language. Mainstream multidimensional analysis engines such as Microsoft SSAS, Oracle Essbase, and the open-source Mondrian all use MDX as the standard query interface for multidimensional databases. Some existing technical solutions have also achieved conversion and adaptation between MDX syntax and SQL statements. However, the core application logic of these technical solutions is to use MDX as the final query endpoint, rather than as an intermediate carrier for semantic transformation. This either requires users to manually write professional MDX statements, making it extremely difficult to use, or it only completes the MDX-to-SQL compilation and conversion within the engine, never integrating MDX into the NL2SQL intelligent conversion chain. Therefore, they cannot rely on MDX's comprehensive multidimensional semantic system to solve the core pain points of intelligent natural language parsing, semantic verification, and accurate conversion, and cannot adapt to intelligent natural language query scenarios.

[0008] In recent years, the enterprise-level intelligent BI field has seen the rise of MQL intermediate expression technology based on the semantic layer of indicators. This has formed a layered architecture that converts natural language into indicator query language and then into SQL. It uses a large model to identify preset indicators and dimension information in user queries, generates standardized JSON-formatted indicator query commands, and then the semantic layer engine compiles these into executable SQL based on predefined indicator templates. This effectively reduces the probability of errors caused by the large model's free generation and achieves higher accuracy in standardized indicator query scenarios, becoming the mainstream solution for current commercial intelligent data products. However, this solution's technical architecture has inherent structural defects, creating a clear technological gap with this invention. First, the semantic expression capability of the intermediate layer is weak. MQL relies on a flat JSON structure for parameter configuration, only able to perform simple selection and definition of indicators, dimensions, and time ranges. It cannot support complex semantics such as axis layout, slice filtering, custom calculation members, parameterized time-series function calls, and hierarchical topological relationships—the core of multidimensional analysis—and is fundamentally different from the multidimensional expression capability of MDX's structured nesting. Secondly, this solution suffers from a serious contradiction between flexibility and maintainability. Query accuracy relies entirely on a manually predefined indicator dictionary, only adapting to combinations of fixed indicators. It cannot automatically generate query logic for exploratory, ad-hoc, or entirely new multidimensional analysis needs, requiring manual configuration of corresponding indicator templates. This makes it unsuitable for dynamic and diverse business analysis scenarios. Furthermore, the solution only supports basic JSON format compliance checks, verifying only field integrity and format validity, lacking business semantic validation capabilities. It cannot determine the compatibility of indicators and dimensions, or the rationality of aggregation rules, failing to proactively intercept numerous business semantic errors. At the compilation logic level, MQL-to-SQL conversion is essentially parameter replacement of fixed templates. Each indicator is manually pre-bound with a unique SQL fragment and association logic. When querying multiple indicators in combination, issues such as incorrect associations and time alignment errors easily arise, making automated and deterministic multi-table join derivations impossible. The most critical flaw is that the flat MQL structure cannot support the complex computational semantics specific to multidimensional analysis. Classic multidimensional analysis scenarios such as year-on-year and month-on-month comparisons, cumulative periods, ranking filtering, hierarchical proportions, and dimension drill-down cannot be implemented through the native MQL syntax. They require a large amount of manual template hard coding or secondary calculations at the application layer, which completely destroys the architecture design that isolates complexity in the middle layer and cannot truly achieve closed-loop support and accurate conversion of multidimensional analysis semantics.

[0009] A review of existing NL2SQL technologies reveals three core bottlenecks hindering their implementation when adapting to complex, multi-dimensional OLAP analysis scenarios in enterprises. First, at the semantic level, current solutions lack a structured, multi-dimensional semantic knowledge foundation spanning the entire process. They lack standardized definitions for dimensions, metrics, hierarchical topology, time-series functions, and table relationship metadata. The models' understanding of OLAP semantics relies entirely on corpus statistical features, failing to guarantee the determinism and accuracy of business semantic parsing. Second, at the transformation level, the natural language to SQL mapping process generally exhibits global probabilistic characteristics, lacking verifiable, interpretable, and automatically correctable intermediate semantic buffers. Numerous syntax, semantic, and logical errors cannot be systematically intercepted and corrected before execution, compromising query stability. Third, at the engine level, existing generation and compilation mechanisms lack deterministic rule constraints. Core processes such as multi-table relationship path derivation, aggregation function selection, and time-series window calculation still rely on model probability prediction or manual template pre-setting, failing to achieve highly reliable deterministic output and thus failing to meet the high reliability and accuracy requirements of enterprise-level industrial data analysis. Summary of the Invention

[0010] This invention provides a method, apparatus, computer device, and storage medium for generating SQL statements based on an intermediate semantic layer, aiming to enhance the effect of converting natural language into SQL statements.

[0011] In a first aspect, embodiments of the present invention provide a method for generating SQL statements based on an intermediate semantic layer, including: Receive a natural language query request input by a user, preprocess the natural language query request, and obtain the semantic context of the natural language query request through a multidimensional semantic model; The preprocessed natural language query request and the semantic context are input into the large language model, and the large language model is constrained to generate MDX query statements as intermediate semantic layers within a preset subset of MDX syntax. The MDX query statement is subjected to two levels of validation; wherein the two levels of validation include syntax validation and semantic validation. After the MDX query statement passes two levels of validation, it is deterministically compiled to obtain the target SQL statement; wherein the deterministic compilation process includes semantic analysis, logical plan generation and SQL code generation in sequence. The target SQL statement is subjected to security checks and parameterization, and then the target SQL statement is output as the return result of the natural language query request.

[0012] Secondly, embodiments of the present invention provide an SQL statement generation apparatus based on an intermediate semantic layer, comprising: The query preprocessing unit is used to receive a natural language query request input by a user, preprocess the natural language query request, and obtain the semantic context of the natural language query request through a multidimensional semantic model. The statement generation unit is used to input the preprocessed natural language query request and the semantic context into the large language model, and to constrain the large language model to generate MDX query statements as intermediate semantic layers within a preset subset of MDX syntax. A two-level verification unit is used to perform two-level verification on the MDX query statement; wherein the two-level verification includes syntax verification and semantic verification. The statement compilation unit is used to perform deterministic compilation on the MDX query statement after it has passed two levels of verification, to obtain the target SQL statement; wherein, the deterministic compilation process includes semantic analysis, logical plan generation and SQL code generation in sequence. The inspection return unit is used to perform security checks and parameterization on the target SQL statement, and then output the target SQL statement as the return result of the natural language query request.

[0013] Thirdly, embodiments of the present invention provide a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the SQL statement generation method based on an intermediate semantic layer as described in the first aspect.

[0014] Fourthly, embodiments of the present invention provide a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the SQL statement generation method based on an intermediate semantic layer as described in the first aspect.

[0015] This invention provides a method, apparatus, computer device, and storage medium for generating SQL statements based on an intermediate semantic layer. By adding a subset of standard MDX syntax as an intermediate semantic layer for natural language to SQL conversion, and relying on the robust multi-dimensional semantic expression capabilities of MDX, it fully carries the core OLAP business semantics such as dimension hierarchy, indicator aggregation, time-series offset, and multi-condition filtering, solving the problem of insufficient semantic capacity in traditional intermediate expression solutions. Simultaneously, through a layered architecture of large language model constraint generation + deterministic rule compilation, natural language semantic parsing is decoupled from the final SQL generation. The large language model is only responsible for the semantic parsing and conversion stage, while the SQL generation stage relies entirely on the deterministic rule engine for compilation. This eliminates the probabilistic illusion defects of end-to-end generation solutions, significantly improving the accuracy and stability of the generated results. Through a two-level verification mechanism of syntax and business semantics, illegal syntax and semantic mismatches can be intercepted before compilation. Combined with deterministic multi-table join path derivation and SQL code generation logic, controllable and stable output can be ensured, meeting the high reliability requirements of enterprise-level industrial multi-dimensional analysis scenarios. Furthermore, the intermediate layer based on MDX syntax in this embodiment of the invention can be directly compiled and adapted to multiple SQL dialects through the rule engine, without the need to retrain the model for different data sources, which greatly reduces the cost of cross-data source adaptation and can adapt to the diverse existing data architecture of enterprises. Attached Figure Description

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

[0017] Figure 1 A flowchart illustrating a method for generating SQL statements based on an intermediate semantic layer, provided in an embodiment of the present invention; Figure 2 An overall architecture diagram of an SQL statement generation device based on an intermediate semantic layer provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the MDX syntax subset range of an SQL statement generation method based on an intermediate semantic layer provided in an embodiment of the present invention; Figure 4 This invention provides an inter-module data flow and interface diagram for an SQL statement generation method based on an intermediate semantic layer, as provided in an embodiment of the invention. Figure 5 A system architecture diagram of an SQL statement generation method based on an intermediate semantic layer provided in this embodiment of the invention; Figure 6A closed-loop flowchart of the verification feedback for an SQL statement generation method based on an intermediate semantic layer provided in this embodiment of the invention; Figure 7 This is a schematic block diagram of an SQL statement generation device based on an intermediate semantic layer, provided for an embodiment of the present invention. Detailed Implementation

[0018] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0019] It should be understood that, when used in this specification and the appended claims, the terms "comprising" and "including" indicate the presence of the described features, integrals, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or collections thereof.

[0020] It should also be understood that the terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the invention. As used in this specification and the appended claims, the singular forms “a,” “an,” and “the” are intended to include the plural forms unless the context clearly indicates otherwise.

[0021] It should also be further understood that the term "and / or" as used in this specification and the appended claims refers to any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0022] Please see below. Figure 1 The present invention provides a method for generating SQL statements based on an intermediate semantic layer, which specifically includes steps S101 to S105.

[0023] Step S101: Receive a natural language query request input by the user, preprocess the natural language query request, and obtain the semantic context of the natural language query request through a multidimensional semantic model. Step S102: Input the preprocessed natural language query request and the semantic context into the large language model, and constrain the large language model to generate MDX query statements as intermediate semantic layers within a preset subset of MDX syntax. Step S103: Perform two-level validation on the MDX query statement; wherein the two-level validation includes syntax validation and semantic validation. Step S104: After the MDX query statement passes two levels of verification, the MDX query statement is deterministically compiled to obtain the target SQL statement; wherein, the deterministic compilation process includes semantic analysis, logical plan generation and SQL code generation in sequence. Step S105: Perform security checks and parameterization on the target SQL statement, and then output the target SQL statement as the return result of the natural language query request.

[0024] In this embodiment, combined with Figure 2 First, the system receives the user's natural language query request. After preprocessing the request, it extracts the semantic context of the request using a multidimensional semantic model. Then, the preprocessed request and semantic context are input into a large language model, which is constrained within a preset subset of MDX syntax to generate an MDX query statement as an intermediate semantic layer. The generated MDX query statement then undergoes two levels of validation: syntax validation and semantic validation. After passing the validation, the MDX query statement is deterministically compiled in the order of semantic analysis, logical plan generation, and SQL code generation to obtain the target SQL statement. Finally, after completing security checks and parameterization processing on the target SQL statement, it is output as the result of the natural language query request.

[0025] Combination Figure 3 and Figure 5 This embodiment adds a subset of standard MDX syntax as an intermediate semantic layer for natural language to SQL conversion. Leveraging MDX's native and robust multi-dimensional semantic expression capabilities, it fully supports core OLAP business semantics such as dimension hierarchy, indicator aggregation, time-series offset, and multi-condition filtering, solving the problem of insufficient semantic capacity in traditional intermediate expression solutions. Simultaneously, through a layered architecture of large language model constraint generation + deterministic rule compilation, natural language semantic parsing is decoupled from final SQL generation. The large language model is only responsible for the semantic parsing and conversion stage, while the SQL generation stage relies entirely on the deterministic rule engine for compilation. This eliminates the probabilistic illusion inherent in end-to-end generation solutions, significantly improving the accuracy and stability of the generated results. A two-level verification mechanism of syntax and business semantics can intercept illegal syntax and semantic mismatches before compilation. Combined with deterministic multi-table join path derivation and SQL code generation logic, it ensures controllable and stable output, meeting the high reliability requirements of enterprise-level industrial multi-dimensional analysis scenarios. Furthermore, the intermediate layer based on MDX syntax in this embodiment can be directly compiled and adapted to multiple SQL dialects through the rule engine, without the need to retrain the model for different data sources, which greatly reduces the cost of cross-data source adaptation and can adapt to the diverse existing data architecture of enterprises.

[0026] It should be noted that the core innovative architecture of this embodiment is the NL2MDX2SQL layered decoupling paradigm. It innovatively introduces the industry-standard multidimensional query language MDX into the NL2SQL intelligent transformation link, constructing a standardized intermediate semantic buffer layer between natural language and structured SQL. This architecture fundamentally distinguishes it from the mainstream NL2MQL2SQL technology route in the industry. Although both solutions adopt the idea of ​​layered decoupling in the intermediate layer, there are fundamental differences in the semantic carrying capacity and technical core of the intermediate carrier. Traditional MQL relies on a flat JSON structure to implement indicator-based instruction transmission, and can only record simple parameter information such as preset indicators, basic dimensions, and time ranges. It lacks the ability to express multidimensional semantic nesting. In contrast, the MDX selected in this embodiment is a standardized nested query expression for multidimensional analysis scenarios, possessing a complete OLAP semantic carrying system.

[0027] Unlike general query syntax, the MDX syntax system natively incorporates multidimensional analysis-specific semantic concepts such as dimensions, levels, grades, measures, and time series functions. It is not simply a statement query tool, but a multidimensional semantic expression system with a complete framework, capable of accurately matching the business logic of complex OLAP analysis in enterprises.

[0028] Building an intermediate semantic buffer layer based on MDX can fundamentally solve various structural defects in existing NL2SQL technologies, achieving multiple technological breakthroughs such as task difficulty reduction, semantic verifiability, multi-data source adaptation, and precise semantic delivery. This embodiment decomposes the high-difficulty, high-uncertainty global probabilistic generation task of direct NLP to SQL in traditional end-to-end solutions into two levels of low-risk, controllable sub-tasks. In the NLP to MDX stage, the large model generation space is constrained by a restricted grammatical subset, significantly reducing the probability of semantic errors. In the MDX to SQL stage, the probabilistic model is completely removed, and deterministic compilation is achieved based on fixed rules, completely avoiding the model illusion problem.

[0029] Meanwhile, the standardized MDX structured syntax provides a reliable semantic anchor for accurate end-to-end verification. The system can rely on MDX syntax features and multi-dimensional semantic models to accurately verify the validity of dimension entities, the matching degree of metric aggregation types, the legality of hierarchical path topology, and the rationality of time series function parameters. In contrast, the traditional verification method based directly on SQL text cannot achieve such in-depth semantic review. The dimension and metric information in the SQL statement has degenerated into ordinary table name strings, and the original semantics are completely lost, which cannot support business logic verification and error correction.

[0030] At the data source adaptation level, this embodiment achieves complete decoupling between the semantic layer and the execution layer. The core compilation rules for MDX to SQL are universal and can be adapted to the underlying logic of various databases. The dialectal syntax differences of different data sources are unified through pluggable adapters to achieve lightweight compatibility. One MDX generation system can be horizontally adapted to multiple data warehouses and databases without the need to repeatedly train models or reconstruct core logic.

[0031] Compared to the mainstream IRNet / SemQL intermediate representation solution in academia, this embodiment has a clear scenario-specific focus. SemQL is a custom abstract syntax tree designed to adapt to general SQL nested structures, which solves the problem of expressing complex SQL structures. It does not carry any OLAP-specific multidimensional business semantics. On the other hand, MDX is specifically designed for multidimensional analysis scenarios, which solves the problem of accurately expressing the semantics of complex aggregation, hierarchical drill-down, and time-series comparison. The two have completely different technical positioning, problem domains, and design philosophies.

[0032] More importantly, the MDX standard syntax has a complete built-in family of multidimensional analysis functions, comprehensively covering high-frequency analysis scenarios in enterprise OLAP. This includes time-series calculation functions such as year-on-year and month-on-month offsets and year-to-date cumulative calculations; various ranking and filtering functions; hierarchical operation functions for parent-child navigation and member traversal; multidimensional set intersection, union, and difference operations; and dedicated calculation capabilities such as rolling period aggregation, beginning-to-end comparison, and member percentage difference analysis. Based on this native function system, various complex multidimensional analysis logics can be directly and accurately expressed in the intermediate semantic layer, without relying on manually predefined indicator dictionaries or decomposing core semantics and sinking them to SQL templates or upper-level business logic. This fully preserves the architectural advantage of the intermediate layer isolating complexity. In contrast, the flat JSON structure of traditional MQL solutions lacks core multidimensional concepts such as member references, hierarchical navigation, time offsets, and set operations. Complex analysis scenarios can only be implemented by enumerating pre-configured indicators or external secondary calculations, resulting in inherent shortcomings in flexibility and scalability.

[0033] In summary, the academic IRNet / SemQL solution and the industrial NL2MQL2SQL solution, which are most similar to this embodiment, both have structural technical limitations. Their custom general abstract syntax cannot support the multidimensional semantics of OLAP, and their flattened indicator commands cannot support exploratory flexible queries and semantic verification. This invention relies on a standardized MDX to build an intermediate semantic buffer layer, achieving technical breakthroughs in four core dimensions: semantic depth, query flexibility, verification reliability, and compilation determinism, effectively making up for the various shortcomings of existing technologies.

[0034] Based on the common shortcomings of existing technologies and industry gaps, this embodiment specifically addresses the structural deficiencies of three types of existing solutions: end-to-end generation solutions suffer from insufficient reliability and frequent illusions in scenarios involving multi-table joins, aggregation logic matching, and cross-data source adaptation; academic intermediate representation solutions lack OLAP multidimensional semantic support and cannot adapt to professional analysis scenarios; and industrial-grade indicator semantic layer solutions rely on predefined indicators, cannot support exploratory combined queries, and lack intermediate layer semantic verification capabilities. This embodiment fills the technical gap in the existing NL2SQL chain, which lacks verifiable, error-correctable, and deterministic semantic anchors. For the first time, it fully introduces the industrial-grade standard MDX multidimensional query system into the NL2SQL transformation process, constructing a stable, accurate, traceable, and highly adaptable two-layer decoupled intelligent transformation architecture to meet the automated query needs of enterprises for various complex, dynamic, and exploratory multidimensional data analysis.

[0035] In one embodiment, step S101 includes: The natural language query request is identified based on a business vocabulary and rule matching, and non-standard terms in the natural language query request are mapped to standard business names in a multidimensional semantic model through a preset synonym mapping table; wherein, the objects of the entity identification include dimension value entities, metric entities and time expression entities; Convert the time expression entity into a standard time offset representation; The natural language query requests are categorized using keyword pattern matching; the types of query categorization include aggregate queries, comparison queries, and detailed queries. The natural language query request is subjected to context state maintenance; wherein, the context state maintenance includes storing the current active dimension, activity metric, global filter conditions and historical query records; When the natural language query request contains ambiguity that cannot be automatically determined, candidate enumeration and clarification prompts are used to resolve the ambiguity.

[0036] In this embodiment, a preprocessing mechanism combining lightweight rules and a business vocabulary is used to perform full-dimensional standardization and structured processing on the natural language query requests input by users. This effectively eliminates semantic ambiguity caused by users' colloquial and diverse inputs, transforming unstructured raw queries into well-structured, semantically controllable, and multi-dimensional semantic model-adapted query content. This provides a precise and reliable input foundation for the subsequent multi-dimensional semantic matching, intermediate semantic layer MDX statement generation, semantic verification, and compilation conversion. Before the large language model performs semantic conversion on the query request input, this embodiment pre-processes multi-dimensional standardization, achieving end-to-end processing including entity recognition normalization, synonym mapping, temporal expression standardization, query intent classification, multi-round context maintenance, and semantic ambiguity resolution.

[0037] Specifically, this embodiment employs a lightweight recognition method combining a business-specific thesaurus with rule matching to extract three types of core business entities from natural language queries: the first type is dimensional value entities containing various regional, category, and time period information; the second type is metric entities containing statistical indicators such as sales revenue, gross profit margin, and new customer numbers; and the third type is time expression entities containing various periodic descriptions such as last month, the same period last year, year-to-date, and the most recent 30 days. After accurately locating the entities, the diverse time expressions are uniformly standardized and converted, transforming the user's ambiguous natural language time descriptions into recognizable standard time offsets or dedicated time function identifiers. For example, the same period last year is uniformly mapped to an annual offset, year-to-date is uniformly adapted to a standard time function, and the most recent 30 days are standardized into a date range.

[0038] Furthermore, to address the mismatch between user-generated colloquialisms, non-standard business terminology, and the standard terminology of the multidimensional semantic model, this embodiment configures an initializable and dynamically expandable thesaurus. This automatically and accurately normalizes user-inputted non-standard expressions into standard business names defined within the model. For example, sales volume and number sold are uniformly mapped to sales quantity, profit and earnings are uniformly mapped to gross profit, and last month and previous month are uniformly standardized into standard monthly offsets, achieving precise alignment between user input and the system's semantic model.

[0039] Building upon this foundation, this embodiment uses a preset keyword pattern matching method to perform coarse-grained intent classification on the standardized query content. Based on query characteristics, user needs are categorized into various query types. For example, aggregate queries with features such as "how much," "average," "total," and "percentage" require matching corresponding aggregation functions to complete the analysis; comparison queries with features such as "comparison," "year-on-year," "month-on-month," and "difference" require time offset and cross-dimensional comparison logic for query parsing; and detailed queries with features such as "list," "details," and "which" do not require aggregation operations and can be completed simply by multi-condition filtering. This accurate classification of query intent provides a core basis for subsequent MDX syntax template selection and semantic validation strategy configuration.

[0040] For multi-turn progressive interactive query scenarios, this embodiment maintains a dedicated context state for each independent user session, continuously storing the confirmed active dimensions, active metrics, global filtering conditions, and multi-turn historical query records in the current session. This is used to resolve pronoun references and complete missing information. When it is identified that the user's current query is a completion-type query with missing key information or ambiguous references, the system automatically extracts the established semantic information from the context to complete the missing content of the current query, generating a semantically complete structured query instruction. This effectively avoids the user repeatedly entering known query conditions and ensures the semantic coherence of multi-turn dialogues. The session context is automatically cleared only when the user switches analysis topics or enters a reset command, eliminating semantic interference between different analysis scenarios.

[0041] When natural language queries contain semantic ambiguities that cannot be automatically resolved through rule matching and thesaurus mapping, this embodiment employs a flexible ambiguity resolution strategy combining candidate enumeration with user clarification prompts. It also incorporates an automated default adaptation mechanism, effectively resolving various semantic ambiguity issues. For ambiguities arising from unclear dimensions such as region or category, this embodiment automatically enumerates all legal candidate levels under that dimension for user confirmation. Alternatively, it can automatically execute the query by default selecting the lowest granularity level based on a preset strategy. For ambiguous scenarios involving vague descriptions of metrics or multiple related business metrics, it prioritizes accurate matching through synonym mapping. If matching fails, it enumerates all associated candidate metrics for user selection. For ambiguous scenarios where users only describe data changes without specifying year-on-year or month-on-month comparison rules, this embodiment marks the ambiguous information and incorporates it into the context. The subsequent MDX generation stage intelligently infers and adapts the corresponding time comparison rules based on conventional analysis practices of monthly and daily business data, ensuring the semantic accuracy of the query while maximizing the user's query interaction experience.

[0042] In one embodiment, step S101 further includes: The core elements of a pre-constructed multidimensional semantic model include: analysis topic, dimension system, hierarchical enumeration value range, metric definition, and foreign key association metadata between fact table and dimension table. Based on a multidimensional semantic model containing the core elements, the natural language query request is matched with a list of dimensions, a list of metrics, available time functions, and set operation functions, thereby summarizing to obtain the semantic context.

[0043] In this embodiment, the multidimensional semantic model serves as the underlying core knowledge foundation of the system. It integrates metadata scattered across various physical data tables into a multidimensional structured system with standardized business semantics. This provides accurate and reliable semantic support for core processes throughout the entire workflow, including large language model constraint generation, MDX statement semantic verification, and MDX-to-SQL compilation and conversion. It clarifies the dimension configurations, hierarchical rules, metric operation logic, and data table relationships corresponding to each business analysis topic, ensuring the standardization and accuracy of natural language query parsing and conversion from the ground up. Specifically, the core elements of the multidimensional semantic model in this embodiment encompass five categories: analysis topic, dimension system, hierarchical enumeration value range, metric definition, and foreign key relationships between the fact table and dimension tables. These core elements work together to collaboratively support the entire semantic parsing process.

[0044] The analysis topics correspond to independent business analysis domains, such as sales analysis, supply chain analysis, and user behavior analysis. Each analysis topic can be viewed as an independent multidimensional data cube, encapsulating a unique set of related dimensions and metrics to define the business analysis boundaries for a single query. The dimension system is a complete structured definition of each business dimension, encompassing the unique business name of the dimension, the physical data source table, the primary key of the dimension table, the internal hierarchical topology, and the subordinate attributes of dimension members. For example, the time dimension can construct a hierarchical progression path of year, quarter, month, and date; the regional dimension can construct a hierarchical system of country, region, province, and city. It can also be equipped with subordinate attributes such as category, brand, and specification, comprehensively covering the hierarchical decomposition needs of multidimensional analysis. The hierarchical enumeration value domain, for each level of each dimension, pre-sets a set of valid dimension member enumerations or configures a dynamic database query acquisition mechanism. This is the core basis for subsequent entity existence semantic verification, accurately determining the validity of dimension member references in MDX statements and quickly locating the error location of illegal entities. The metric definition standardizes the attribute parameters of various business statistical indicators, including the metric's unique business name, associated fact table, source fields, default aggregation operation type, compatible dimension range, and optional ratio calculation formula. Aggregation operation types can adapt to various types such as summation, count, deduplication count, average, extreme values, and ratios. For ratio-based composite metrics such as gross profit margin and profit margin, dedicated calculation expressions can be built-in. Furthermore, semantic errors caused by mismatches between dimensions and metrics are avoided through compatible dimension configuration. The foreign key relationship metadata between fact tables and dimension tables is a crucial foundation for the automatic compilation of MDX to SQL. It fully records the relationships between fact tables and each dimension table, including fact table name, associated dimension name, dimension table physical table name, fact table foreign key, dimension table primary key, and relationship type. It adapts to the star topology of the data warehouse, providing deterministic data support for automatically deriving multi-table JOIN relationship paths during the compilation phase.

[0045] This multidimensional semantic model supports two construction methods: interactive visual modeling and automatic extraction of database metadata. It can be manually configured by business personnel or automatically generated by the system by scanning database primary keys, foreign key constraints, and field naming rules to produce an initial model skeleton. After manual review and optimization, it is persistently stored in a structured configuration file. This embodiment can load the multidimensional semantic model into a high-efficiency in-memory index structure, constructing dimension name and measure name hash mappings, data table association adjacency tables, and hierarchical value domain multi-level indexes to achieve constant-level fast retrieval. Each business module calls the index data through a unified model query interface, ensuring query efficiency and data consistency. Simultaneously, the model has a robust operation and maintenance update mechanism, supporting single-field incremental updates, runtime index hot replacement, and version snapshot rollback. Model iteration and updates do not require system interruption, effectively adapting to business data model iteration and change scenarios and ensuring long-term stable system operation. Based on the fully constructed multidimensional semantic model described above, the system can accurately match the dimension list, metric list, legally available time function, and set operation function that are suitable for the current analysis scenario for the preprocessed natural language query request. It will summarize and integrate all the effective semantic resources obtained by matching to form a complete and regular semantic context, providing accurate semantic constraints for the subsequent generation of compliant MDX intermediate statements by the large language model.

[0046] In one embodiment, step S102 includes: Based on the natural language query request and semantic context, restrictive prompt words are constructed and input into the large language model; Configure decoding parameters, enforce grammatical prefix constraints, and stop word rules for the large language model; Obtain the original output content of the large language model and perform structured processing on the original output content to obtain the MDX query statement; wherein, the structured processing includes text cleaning, bracket matching verification and syntax format standardization.

[0047] In this embodiment, to achieve precise constraints on the model output space, a subset of MDX syntax adapted to conventional multidimensional analysis is predefined. Only frequently used syntax elements in OLAP scenarios are retained, while syntax functions that are prone to generation instability, have high verification difficulty, and low business usage are eliminated. For example, while ensuring that all conventional analysis requirements such as year-on-year and month-on-month comparisons, hierarchical drill-down, scrolling windows, multi-dimensional cross operations, and set operations can be fully expressed, the range of syntax that the model can generate is minimized to the greatest extent. This subset of syntax can include standard MDX row and column axis query structures, global filter slice definitions, calculation member definitions, dimension hierarchical expansion functions, various standard time period functions, scrolling period calculation functions, dimension first and last period functions, set intersection, union, and difference operation functions, basic aggregation operation functions, standard hierarchical path reference formats, and set and tuple construction syntax. At the same time, redundant and complex functions such as filtering condition iteration functions, multi-level logical branch expressions, multi-dimensional navigation functions, cell assignment scripts, custom naming sets, and three-axis and above multi-axis syntax are included, balancing the completeness of expression and the controllability of generation.

[0048] For example, the preprocessed user query content is the sales revenue of each month in the second quarter of 2024 in East China and the year-on-year growth rate compared with the same month last year. The injected semantic context clearly defines the analysis topic as sales analysis, with a hierarchical structure of three dimensions: time, region, and product, and three available measures: sales revenue, sales volume, and gross profit margin. After the large language model is controlled by the constraint prompt words, it outputs a complete and compliant MDX statement containing members for year-on-year comparison calculation, multiple indicator column axes, month row axes, and regional slice filtering. The statement does not use subset out-of-sequence syntax throughout, and all dimensions and measures are referenced from the injected semantic resources. It can directly enter the subsequent verification process to complete the compliance determination.

[0049] Existing natural language to SQL technologies generally adopt an end-to-end generation approach, directly relying on a large language model to complete multiple complex tasks in one go, such as data table identification, multi-table join path deduction, aggregate function matching, complex nested logic generation, and database dialect adaptation. In multidimensional analysis scenarios, this approach is prone to defects such as entity illusion, incorrect join logic, and non-standard syntax. This embodiment adopts a task dimensionality reduction design approach, which does not require the large language model to complete all data conversion work independently. Instead, it simplifies the model task to selecting legitimate business entities from given semantic resources and assembling compliant MDX statements by adding an intermediate MDX layer to compress the generation boundary of the model. Deterministic logic such as data table joins and multi-table JOIN deduction is processed in the post-processing stage, thereby reducing the probability of the large language model producing illusory outputs from the root.

[0050] In practical applications, the processing flow for generating MDX statements based on a large language model can include five layers of processing logic. The first layer is the injection of multidimensional semantic information, which combines the query intent classification results obtained in the preprocessing stage, selects all valid semantic resources that match the current query scenario from the loaded multidimensional semantic model, and assembles them into structured prompt fragments. Each fragment fully carries the current analysis topic description, all available dimensions and hierarchical structure, available metrics and corresponding aggregation types, compliant time function interpretation, and identified entity binding relationships. In multi-turn dialogue scenarios, additional historical query conditions with retained conversation context are added. The encoding is completed in a hybrid form of natural language and structured field identifiers and sent to the prompt word system as the basic context.

[0051] The second layer is the overall construction of constraint-type prompts. Standardized instruction content is concatenated after semantic context fragments. The whole adopts a form-filling-style strong constraint logic rather than an open and free generation rule. It clearly limits the model to calling the syntax within the preset MDX subset. Dimension and metric-related references must be strictly taken from the injected semantic resources. The optional range of time functions, set operations, and aggregation functions is defined simultaneously. The input query content and output format requirements are clearly marked. Only the model is allowed to return pure MDX statements and is prohibited from attaching additional textual explanations. The prompts also embed three to five sets of natural language and MDX pairing samples covering typical query scenarios, relying on a small number of samples to guide the grammatical output mode of the stable model.

[0052] The third layer configures the output control strategy for the decoding stage. It suppresses the divergent output of the model through dual constraints of parameters and syntax. The temperature parameter is set to a low value in the range of 0.1 to 0.3 to guide the model to prioritize the selection of high-probability standard syntax units. The sampling parameter with a threshold of 0.9 is used to balance the output stability and the diversity of basic expressions. At the same time, a fixed forced prefix constraint is set to force the model to generate content with the starting character of SELECT, which prevents invalid output that deviates from the MDX query syntax structure. An additional dedicated stop word combination is configured to prevent the model from continuing to write irrelevant explanatory text at the end of a valid MDX statement.

[0053] The fourth layer performs structured post-processing on the original text returned by the model. First, redundant whitespace characters at the beginning and end of the text and irrelevant text outside the scope of the MDX statement are removed. Keyword integrity screening and full sentence bracket matching are performed through regular expression rules. Then, a unified format standardization operation is performed to uniformly adjust the MDX keywords to uppercase, retain the original configuration text for the custom names of dimensions and measures, and fill in the missing hierarchical reference brackets to complete the text regularization.

[0054] The fifth layer connects to the subsequent two-level verification process to form a retry and error correction closed loop. The structured MDX statement will not directly enter the compilation stage, but will be sent to the syntax and business semantic verification module. Once the verification process outputs a structured report with error location, error type and correction suggestions, the system will use the report as a negative sample to supplement the context of the next round of prompt words, triggering the large language model to regenerate the MDX statement. At the same time, a maximum retry threshold is configured. After the retry limit is reached, a query failure prompt will be pushed directly to the user to avoid continuously generating erroneous statements.

[0055] It should be noted that the MDX subset definition in this embodiment can be expanded or contracted according to the needs of the actual business scenario. For example, if the business scenario does not involve single-member hierarchical navigation, the PrevMember function can be removed from the subset; if the business scenario requires ranking queries, the TOPCOUNT function can be added to the subset. The specific selection of the subset range does not affect the core architectural features of this solution, namely, MDX as an intermediate semantic buffer layer, a deterministic compilation pipeline, and a two-level verification mechanism.

[0056] Furthermore, although this embodiment describes in detail a preferred embodiment using MDX as the intermediate semantic language, the core protection scope of this solution should not be limited to MDX as the only syntax. Any intermediate language possessing the following characteristics can be used as an equivalent replacement for MDX: The language features a structured, nested multidimensional query syntax, built-in semantic slots for dimensions, levels, and metric names, and supports semantic expressions specific to multidimensional analysis, such as time-series offsets, set operations, and hierarchical expansion. The language's syntax supports extracting identifiers for dimensions / measures and deterministic information about aggregation types from query text; The language has a formally definable subset of syntax that can be used to constrain the LLM generation space and build deterministic parsers.

[0057] It should be noted that the aforementioned equivalent alternative languages ​​must possess the same level of multi-dimensional semantic nesting expression capabilities and formal syntax definition capabilities as MDX. Instruction structures that only support flattened parameter selection (such as JSON-based indicator query instructions) lack multi-dimensional semantic nesting expression and formal syntax validation capabilities, and therefore do not meet the aforementioned equivalent alternative conditions; thus, they are not within the scope of the equivalent alternatives described in this solution. MDX, with its mature industry standards and complete multi-dimensional semantic system, is currently the optimal implementation method.

[0058] In one embodiment, step S103 includes: A recursive descent parser is constructed based on the BNF grammar rules of the MDX grammar subset, and the recursive descent parser is used to perform grammar verification on the MDX query statement; wherein, the grammar verification includes illegal token detection at the lexical level, grammar order verification at the token sequence level, and structural integrity verification at the abstract syntax tree level. The recursive descent parser constructs an abstract syntax tree for the MDX query statement, and performs semantic verification on the MDX query statement based on the abstract syntax tree; wherein, the semantic verification includes entity existence verification, hierarchical path topology validity verification, metric and dimension compatibility verification, and time function parameter validity verification.

[0059] In this embodiment, the original MDX text output by the large language model constraint generation module cannot directly enter the subsequent compilation and conversion process. It must pass through the two-level deterministic verification mechanism mentioned above. This verification step, as the front-end risk control defense line of the entire conversion chain, abandons probabilistic model reasoning and heuristic fuzzy judgment throughout the process, and relies entirely on fixed rules to complete the review. As long as the MDX statement successfully passes the two-level verification, it can be guaranteed from the mechanism that the subsequent compilation stage can stably output semantically accurate and logically correct executable SQL statements, completely avoiding query errors caused by illegal intermediate statements flowing into the compilation stage.

[0060] Combination Figure 6 The first level of syntax checking is a deterministic form check based on BNF rules. Its core function is to ensure that MDX statements fully conform to the specification definition of the preset MDX syntax subset. This embodiment relies on the BNF syntax standard corresponding to the MDX subset to accurately define the legal combination rules of various syntactic structures, thereby constructing a highly adaptable recursive descent parser. The parser adopts a left-to-right line-by-line scanning mechanism, performing lexical analysis, sequence verification, and structural verification on the MDX text one by one. Once illegal characters, grammatical errors, or structural omissions are detected during the scanning process, the error location can be accurately located and an error message can be displayed immediately, achieving precise interception of syntax problems.

[0061] Syntax validation follows a three-tiered, progressive review logic to cover statement form compliance. First, lexical validation involves tokenizing the original MDX text, verifying the validity of each token, and blocking various prohibited characters and keywords. This includes special MDX keywords prohibited by subset rules, unclosed parentheses and quotation marks, unknown keywords outside the preset subset range, and invalid characters such as illegal operators and delimiters. Second, token sequence validation, combined with contextual syntax rules, verifies the compliance of the combination order and collocation logic of adjacent tokens, strictly constraining the arrangement rules of various syntax keywords to ensure that the arrangement order of core syntax modules such as SELECT axis definitions, FROM topic declarations, WHERE slice filtering, and WITH computed member definitions fully conforms to standard specifications. Finally, syntax tree integrity validation, after successfully constructing the abstract syntax tree, performs a fallback check on the overall structure to confirm that the query statement contains valid axis definition configurations, that custom computed members and model-built-in measures are validly defined, that all parentheses are fully closed, and that dimension hierarchy reference paths conform to standard formats, eliminating incomplete syntax structures.

[0062] When any level of syntax checking detects a violation, a standardized structured error report is generated, clearly indicating the error level, error type, precise error location, expected valid token, and actual invalid token, while also matching optional repair suggestions. This structured error information not only terminates the subsequent process of the abnormal statement immediately, but also serves as precise feedback for the retry generation of the large language model, guiding the model to specifically correct the MDX statement based on the specific error deviation, thus achieving an automated error correction closed loop.

[0063] MDX statements that pass syntax validation only guarantee formal compliance and cannot ensure the accuracy of business semantics. Therefore, this embodiment adds a second-level business semantic validation mechanism based on a multidimensional semantic model to specifically verify the business legality and adaptability of various referenced entities in the MDX statement. Furthermore, to improve semantic validation efficiency, multiple sets of hash-based fast index structures can be pre-built during the multidimensional semantic model loading stage, covering dimension definition indexes, metric attribute indexes, hierarchical enumeration indexes, and hierarchical topology path indexes. All indexes are stored in the form of hash mappings, enabling constant-level fast retrieval of entity matching and ensuring efficient operation of the validation process.

[0064] The second level of business semantic verification includes four core audit dimensions to control the business rationality of multi-dimensional queries. First, entity existence verification: This embodiment traverses all dimension, metric, and hierarchical reference entities in the abstract syntax tree, quickly matching and verifying them using hash indexes, and intercepting all invalid entity references not defined in the business model. Second, hierarchical path validity verification: For various dimension levels and member references, the system retrieves the standardized hierarchical topology sequence corresponding to the dimension, verifying the validity of the referenced hierarchy and the compliance of the hierarchical arrangement order, preventing issues such as illegal cross-level references and invalid hierarchical calls. Third, metric and dimension compatibility verification: This embodiment retrieves the preset compatible dimension list for each metric, verifying the adaptation relationship between the axis dimension and statistical metric in the MDX statement, accurately identifying unreasonable query combinations where the dimension and metric business do not match. Fourth, time function parameter validity verification: For various time-series analysis functions such as year-on-year and cumulative periodicity, this embodiment verifies whether the dimension called by the function is a valid time dimension and whether the offset hierarchical parameters match the standardized hierarchical system of the time dimension, preventing issues such as invalid time-series function call parameters and incorrect dimension types.

[0065] Preferably, in addition to the deterministic two-level verification, this embodiment also configures an optional enhanced error correction module to achieve intelligent fuzzy matching and automatic correction of entity names. This module relies on a lightweight Chinese vector embedding model to encode all dimensions and metric entities in the multidimensional semantic model into vectors and construct a vector retrieval index. After the deterministic verification detects that no entity is abnormal, the intelligent error correction mechanism is triggered. This embodiment performs vector encoding on abnormal entity names, retrieves the standardized model entity with the highest similarity, and automatically completes entity name correction and MDX statement optimization when the similarity reaches a preset threshold, while retaining operation logs. For abnormal entities with similarity below the threshold, candidate correction suggestions are pushed in the error report to assist the large language model in completing retry optimization. This enhancement module is optional; disabling it does not affect the normal operation of the core two-level verification mechanism and can further improve the fault tolerance and accuracy of statement generation without changing the core logic of deterministic verification.

[0066] It should be noted that, in practical applications, the recursive descent parser used in this embodiment can be replaced by an equivalent PEG (Parsing Expression Grammar) parser, LL(k) parser, or a parser built based on parser generation tools such as ANTLR / YACC. The essence of the verification mechanism is to perform deterministic formal verification on the text generated by LLM.

[0067] In one embodiment, step S104 includes: Traverse the entity reference nodes of the abstract syntax tree, and bind nodes carrying semantic information to the entity reference nodes through the index of the multidimensional semantic model to obtain the annotated abstract syntax tree; Based on the annotated abstract syntax tree, a relational algebra expression tree is derived; The relational algebra expression tree is traversed, and the target SQL statement is generated based on the preset mapping rules between MDX semantic elements and SQL structure.

[0068] This embodiment relies on the verified MDX abstract syntax tree for structured compilation processing. First, it traverses all entity reference nodes within the abstract syntax tree, and completes precise matching and binding based on the memory index of the multidimensional semantic model. It attaches complete semantic information such as physical tables, fields, aggregation types, and relationships to each syntax entity, generating an annotated abstract syntax tree carrying complete business and physical semantics. Then, it performs logical decomposition and derivation based on the annotated abstract syntax tree to construct a relational algebra expression tree that can completely represent the query execution logic. Finally, it traverses and parses the relational algebra expression tree one by one, and combines the system's preset MDX semantic elements and SQL structured mapping rules to automatically assemble and generate target SQL statements that can be directly executed in the underlying database, achieving a lossless and deterministic conversion from intermediate MDX semantics to underlying executable SQL.

[0069] Specifically, in this embodiment, valid MDX statements that have undergone the aforementioned syntax and semantic checks will enter the deterministic compilation module. This module is a key innovative step that distinguishes it from traditional neural network decoding and generation schemes. It adopts a rule-driven, zero-probability perturbation, and fully traceable compilation pipeline architecture, eliminating the uncertainty and illusion problems caused by end-to-end large model probability generation. Unlike existing schemes such as IRNet and SemQL, which still rely on neural network decoders to complete the generation work in the semantic conversion stage, this compilation engine performs step-by-step parsing and mapping transformation entirely based on preset structured rules. Each step of the transformation logic has clear rule basis and execution standards, and the same input can stably output a unique result, greatly improving the industrial-grade stability and traceability of multi-dimensional query transformation.

[0070] This compilation engine references a mature OLAP compilation architecture and incorporates a lightweight, customized subset of MDX defined in this embodiment. The overall process is a five-stage pipeline: lexical analysis, syntax analysis, semantic analysis, logic plan generation, and SQL code generation. The syntax analysis stage reuses the abstract syntax tree generated in the semantic verification stage, eliminating the need for repeated parsing. This effectively improves system efficiency and ensures that verified MDX statements can be compiled correctly, completely eliminating the risk of logical inconsistencies between the verification and compilation stages. Semantic analysis, built upon this foundation, is the core step in connecting to the multidimensional semantic model. It traverses all entity reference nodes (dimensions, levels, measures, members, etc.) in the abstract syntax tree and quickly matches and binds corresponding physical metadata information using the hash index of the multidimensional semantic model. This upgrades plain text nodes with only syntactic structure into labeled nodes carrying complete business semantics and physical storage information, achieving precise mapping between MDX multidimensional analysis semantics and the physical structure of the relational database.

[0071] After semantic binding is completed and annotated abstract syntax tree is obtained, the logical plan generation stage begins. This stage relies on the complete semantic information after annotation to deduce and construct a standardized relational algebra expression tree. This expression tree can completely reconstruct the underlying execution logic of the query, clearly defining the data table scan range, dimension filtering conditions, multi-table join rules, grouping and aggregation fields, and aggregation operation types. It provides standardized and structured logical support for subsequent SQL code generation, ensuring that all multidimensional analysis logic can be transformed into an executable logical structure that conforms to relational algebra specifications.

[0072] During the SQL code generation phase, each level of the relational algebra expression tree is traversed sequentially. Based on predefined, fully covered MDX and SQL semantic mapping rules, various MDX syntax elements are accurately converted into corresponding standard SQL structures. These mapping rules cover all syntax units of the MDX subset in this embodiment, enabling precise conversion of multidimensional analysis logic such as analysis topics and fact tables, dimension levels and grouping fields, metrics and aggregate functions, slice filtering and query conditions, multidimensional cross-associations and multi-field grouping, various standard time functions and window calculations, period cumulative calculations, rolling period statistics, set intersection, union, and difference operations, and ratio composite metrics. Simultaneously, it is compatible with refined analysis scenarios such as dimension parent-child hierarchical references, null value filtering, and period start and end value retrieval, comprehensively adapting to the needs of conventional multidimensional data analysis business.

[0073] To address the most error-prone issue in multi-dimensional analysis scenarios—multi-table joins—this embodiment leverages pre-stored metadata about foreign key relationships between fact tables and dimension tables within a multi-dimensional semantic model. This enables fully deterministic automatic derivation of join paths, resolving the illusionary errors arising from traditional end-to-end solutions that rely on probabilistic model guessing of table relationships. Using the core fact table corresponding to the analysis topic as a benchmark, this embodiment aggregates all dimension table resources involved in the query, matches pre-defined primary and foreign key relationships and join types one by one, automatically generates standardized multi-table left join statements, and deduplicates and merges multiple references to the same source dimension tables. This results in a standardized multi-table join structure with a logically ordered topology and unique join logic, ensuring the accuracy and stability of multi-dimensional join queries.

[0074] For the core time-based computation logic of multidimensional analysis, this embodiment configures dedicated, finely crafted compilation templates for various MDX standard time functions that are adaptable to multiple database dialects. Based on the granularity of the time hierarchy and the syntax characteristics of the target database, it can intelligently select window function operations or subquery self-join operation strategies to accurately realize the standardized conversion of complex time-series analysis logic such as same-period comparison, intra-period cumulative, previous period value, rolling period aggregation, and period beginning and end value. It unifies the SQL generation rules for various time analysis scenarios and avoids logical deviations caused by manual adaptation.

[0075] Preferably, to adapt to the syntactic differences of different relational databases, this embodiment sets up an independent, pluggable dialect adaptation layer. For example, it specifically encapsulates the syntactic differences of mainstream databases such as MySQL, ClickHouse, PostgreSQL, and Apache Doris in terms of date truncation, window functions, string processing, pagination syntax, and aggregation operations. During the compilation process, fragments involving differentiated syntax can be uniformly generated through the dialect adapter. The core compilation logic does not need to be modified separately for different databases; it can adapt to multiple data source scenarios simply by switching dialect configurations, greatly improving the system's versatility and scalability.

[0076] Compared to existing natural language to SQL technologies, this embodiment abandons uncertain generation mechanisms such as large-scale probabilistic decoding, neural network prediction, and fixed template replacement. It relies on structured semantic models and fixed mapping rules to achieve traceable and reproducible SQL generation results across the entire chain. It forms a systematic technical barrier in core links such as JOIN path derivation, aggregation logic matching, temporal semantic parsing, cross-dialect adaptation, and intermediate layer verification and error correction. It can effectively meet the stringent requirements of industrial-grade multidimensional data analysis scenarios for query accuracy, stability, and maintainability.

[0077] In practical applications, the window function compilation strategy for time offset functions (such as ParallelPeriod) can be replaced with an equivalent subquery self-join strategy (when the target database does not support window functions, such as MySQL 5.7 and earlier versions). The selection of the compilation template can be automatically determined by the dialect adapter based on the capabilities of the target database, without affecting the semantic correctness of the compilation results.

[0078] Furthermore, although the example uses a relational database as the SQL execution target, the SQL generated by the compilation engine can be sent to any data query system that supports the standard SQL interface for execution, including but not limited to data warehouses (such as MaxCompute, Redshift), query engines (such as Presto / Trino, Spark SQL), and HTAP databases (such as TiDB / TiFlash). The design of the dialect adapter layer allows for the expansion of new data sources to be achieved by implementing only a new set of syntax mapping rules.

[0079] In one embodiment, step S105 includes: The target SQL statement is subjected to a security check according to a preset security policy; wherein, the security policy includes intercepting target SQL statements containing write operation keywords through regular expression matching, configuring query timeout thresholds, and automatically injecting a maximum number of rows that are adapted to the target dialect. For the target SQL statement, identify the dimension columns and measure columns, generate visual recommendation information based on the dimension columns and measure columns, and attach re-aggregated metadata.

[0080] In this embodiment, the target SQL statement output after the deterministic compilation process first enters a unified security control processing stage. A multi-layered protection mechanism mitigates database operation risks caused by malicious operations, long-running queries, and excessively large result sets. All security verification logic can be uniformly implemented using a dialect adaptation layer to adapt to the syntax features of different databases. This embodiment first scans the complete SQL text using regular expression pattern matching to identify various data writing and table structure modification keywords such as DROP, DELETE, UPDATE, INSERT, TRUNCATE, ALTER, and CREATE. Once a relevant operation identifier is detected, the SQL execution process can be directly blocked, and a standardized security interception prompt can be pushed outwards to prevent unauthorized data modification operations. Simultaneously, this embodiment configures a fixed query timeout threshold for database connections. For example, the default threshold is set to 30 seconds. If the query execution time exceeds the threshold, the background can proactively terminate the database query task to avoid a single inefficient query occupying database connection resources for a long time and causing overall service blockage. It can also automatically append row count limits based on the target database type, such as a default limit of 10,000 rows. This value can be configured and adjusted uniformly in the backend or manually specified by the user during the query phase, thus preventing system lag caused by users unintentionally triggering full table scans. To address the injection risk posed by user-inputted filter values, all user-submitted dimension values ​​within the WHERE filter conditions can be converted into database parameterized placeholders. Different databases have their own dedicated placeholder formats, and the parameter isolation mechanism completely blocks SQL injection attack paths.

[0081] After completing all security checks and confirming SQL compliance, the legitimate query statement is pushed to the database for execution. Upon receiving the original result set from the database, result enhancement processing is performed, adding structured business metadata to the original data to provide standardized decision-making basis for front-end visualization rendering. This embodiment can automatically distinguish between dimension columns and measure columns in the result set and complete the labeling and storage. Simultaneously, it automatically matches and adapts chart display schemes based on the number of the two types of fields and their business attributes. For a single dimension paired with a single measure, a bar chart is recommended; for a time dimension paired with a single measure, a line chart is used; when a time dimension is overlaid with a regular dimension paired with a single measure, multiple series line charts are generated; two sets of non-time dimensions paired with measures can display grouped bar charts or cross-tab detail tables; for scenarios with simultaneous output of multiple measures, faceted charts or radar charts are available as options. The generated aggregation metadata fully records the aggregation operation type, underlying calculation logic, and data table source corresponding to each measure field, facilitating secondary processing, numerical decomposition, and data traceability of the query results by the front end.

[0082] In addition, to reduce compilation resource consumption in repetitive query scenarios, this embodiment implements a two-level caching mechanism. Constant-level cache read / write is achieved through hash indexes, minimizing the burden on overall query response latency. The first-level cache stores the compilation mapping between MDX statements and SQL statements. This embodiment first performs standardization and cleaning on the MDX text, uniformly removing redundant whitespace characters, standardizing keyword case, and arranging axis member references in a fixed order. Then, the hash value of the standardized text is used as the cache index key to store the corresponding fully compiled SQL text. When a newly queried MDX statement hits the cache index, the system directly retrieves the cached SQL statement, skipping the entire compilation pipeline and significantly reducing the processing time for repetitive queries. The second level is an optional query result cache, suitable for offline low-timeliness analysis scenarios. The cache index is generated by combining the SQL text hash and parameter value hash. The data lifespan can be customized and is kept off by default to meet the data freshness requirements of real-time data analysis. The retrieval, writing, and eviction operations of both levels of cache are all completed using in-memory hash indexes, resulting in extremely low read / write overhead. This significantly reduces the computational cost of repetitive queries without affecting system response speed.

[0083] The following examples, using multidimensional analytics business scenarios, demonstrate the end-to-end processing flow from natural language to MDX and then to executable SQL. They cover core OLAP scenarios such as single-dimensional time-series year-on-year comparisons, multi-dimensional cross-aggregation, multi-round hierarchical drill-down, and custom complex month-on-month calculations, to verify the effectiveness of the two-layer decoupled architecture, deterministic compilation, and two-level verification mechanism in this embodiment. Each case relies on a unified multidimensional semantic model to complete semantic constraints and metadata-driven transformations. This differs from the probabilistic generation of traditional end-to-end models and the template limitations of MQL solutions, demonstrating the significant advantages of this embodiment in terms of flexibility, accuracy, and versatility.

[0084] Example 1: Application scenarios of single-dimensional aggregation and time-year comparison This scenario caters to the routine operational comparative analysis needs of retail enterprises. It uses a natural language query to calculate the sales revenue and year-on-year growth rate of each product category in the East China region during the second quarter of 2024. The system performs dimension filtering, category aggregation, and year-on-year calculation. This query encompasses regional dimension slicing, product category dimension grouping, and quarterly time-series year-on-year analysis, representing a typical scenario for routine multi-dimensional comparative queries by enterprises. During the system preprocessing stage, the system automatically identifies the core elements of the query, defining the time range of the second quarter of 2024, the regional filtering conditions for the East China region, the analysis dimensions of product categories, and the sales revenue statistical metrics, thus determining the query intent to be a time-series comparative multi-dimensional query.

[0085] The system loads a multi-dimensional semantic model for sales analysis topics. The model pre-defines a three-dimensional hierarchical topology of time, region, and product, while also fixing the summation and aggregation rules for sales revenue, as well as standardized foreign key metadata relationships between the fact table and each dimension table. This provides deterministic semantic support for statement generation and compilation. The large language model generates compliant query statements within a restricted MDX subset space. Custom calculation members define last year's sales revenue and year-on-year growth rate. The column axis displays three types of statistical measures, the row axis traverses all product categories, and a slicer locks in the target time and region range. After the statement undergoes two levels of syntax and semantic verification, it enters the compilation stage.

[0086] The compilation engine automatically derives the relationship between the fact table and the time, region, and product dimension tables based on model metadata, using left joins to complete multi-table data association. To address the time-series offset requirement for quarterly year-on-year comparisons, the standard MDX year-on-year comparison function is precisely compiled into a fourth-order lag window function, enabling cross-year data matching within the same quarter. Simultaneously, slice filtering conditions are converted into equality query conditions. Finally, standard SQL statements adapted for PostgreSQL databases are compiled, using subqueries to match year-on-year sales figures and calculate year-on-year growth rates, accurately outputting current sales, last year's sales, and year-on-year growth data for each product category, achieving stable and traceable time-series year-on-year analysis.

[0087] Example 2: Application Scenarios of Multidimensional Cross-Aggregation and Multi-Measure Comparison This scenario addresses the need for refined operational analysis. It targets the query requirement of "analyzing sales and order volume in first-tier cities in the first quarter of 2025 by product category and customer level," and completes two-dimensional Cartesian product cross-aggregation, dual-measure differential statistics, and city-dimensional filtering. This scenario requires simultaneous implementation of dimension cross-combination, parallel computation of measures with different aggregation rules, and multi-member set filtering, fully demonstrating the solution's adaptability to complex multi-dimensional queries.

[0088] The system leverages a multidimensional semantic model to clearly define the relationships between entities and the rules for aggregation of metrics. Sales revenue is aggregated using summation, while order volume is aggregated using deduplication counting. It also matches the complete relationship links between the fact table and the product, customer, city, and time dimension tables. The large language model generates standard MDX statements containing dimensional cross-operations, and uses cross-combination functions to achieve a two-dimensional Cartesian product expansion of product category and customer level, limiting the target city set and quarterly time range. Compared to traditional end-to-end solutions that rely on model probabilistic inference of multi-table relationships and are prone to table field matching errors, this solution directly reads all table relationship paths from the model's fixed metadata, ensuring uniquely determined relationship logic and eliminating the risk of fictitious errors.

[0089] The compilation engine maps the two-dimensional cross-logic to two-field grouping and aggregation rules. Multi-dimensional independent associations correspond to the generation of multiple left join statements for data tables. The multi-value filtering conditions for cities are compiled into set matching queries. At the same time, the engine automatically matches the corresponding aggregation functions according to the model's preset rules, and finally generates executable SQL statements adapted to the ClickHouse database. It accurately outputs sales and order volume data for different product categories and customer levels within the target city range, and efficiently completes multi-dimensional and multi-metric cross-statistical analysis.

[0090] Example 3: Application Scenarios of Hierarchical Drill-Down and Cumulative Multi-Turn Dialogue This scenario simulates the progressive query needs of a company's quarterly financial review. Leveraging the system's multi-round session context maintenance capabilities, it enables hierarchical drill-down analysis, from category summaries to brand details. Business personnel initially request a summary query for "total cumulative profit for each product category since the beginning of 2025." After obtaining the category-level summary data, they further request a refined query to "drill down the mobile phone category to the corresponding brand," representing a typical multi-round progressive hierarchical drill-down scenario.

[0091] After the first round of queries is completed, the session module automatically retains the currently valid metrics, analysis dimensions, and year-to-date time filtering conditions, forming a stable session state. When the second round of incomplete queries is initiated, the system automatically identifies the missing metrics and time ranges, completes the query semantics based on historical context, and only updates the display level of the product dimension, eliminating the need for users to repeatedly input complete query conditions. The large language model generates compliant MDX statements based on the completed semantics, and uses sub-member expansion functions to achieve drill-down from the mobile phone category to the subordinate brand level.

[0092] The compilation engine accurately parses the hierarchical drill-down logic, adds mobile phone category filtering conditions based on the hierarchical topology, and replaces the grouping dimension from category to brand dimension. It also normalizes the cumulative statistical rules from the beginning of the year to the present into a natural year start and end date range filtering, and finally generates SQL statements adapted to the MySQL database. It outputs the cumulative total profit data by brand dimension, and fully realizes smooth hierarchical drill-down analysis in multi-turn dialogue scenarios, demonstrating the technical advantages of this solution in contextual semantic coherence and accurate hierarchical parsing.

[0093] Example 4: Application Scenarios of Complex Formula Calculation and Chain Comparison Time Series Analysis This scenario caters to analysts' needs for refined time-series comparisons, automating the calculation of "month-on-month growth rate of sales revenue in each region over the past six months." Relying on standard MDX time-series functions and custom calculation members, it completes continuous period data comparisons and custom indicator calculations, adapting to enterprises' routine monthly trend analysis scenarios.

[0094] The system constructs standardized month-on-month calculation logic through the built-in member reference function of MDX, encapsulates the complete month-on-month growth rate formula with custom calculation members, and limits the statistical interval to the past six months through period value functions. The aggregation rules are fixedly defined by the multi-dimensional semantic model. Sales revenue is aggregated by default using the summation method, eliminating the need for subjective judgment by the large language model. Even if the user does not explicitly express the summation statistical logic, the system can still accurately match the corresponding aggregation rules, completely avoiding the problem of model semantic misjudgment.

[0095] The compilation engine stably transforms time-series member reference logic into first-order lag window functions, achieving accurate matching of adjacent monthly data. It converts six-month rolling statistical intervals into continuous date range filtering conditions and losslessly compiles the MDX custom month-on-month calculation formula into conditional expressions with null protection, avoiding calculation anomalies caused by null values ​​in the previous period. Finally, it generates executable SQL statements adapted for PostgreSQL databases, outputting continuous monthly sales figures and standardized month-on-month growth rate data for each region, stably achieving automated and accurate calculation of complex time-series composite indicators.

[0096] In summary, this invention constructs a novel end-to-end processing architecture for converting natural language to multidimensional analysis SQL. Compared to existing solutions such as end-to-end large models, IRNet semantic parsing, and NL2MQL2SQL, it features several fundamentally differentiating core innovations, with each feature collaboratively building a complete technological barrier. This embodiment abandons the traditional end-to-end probabilistic generation model for direct natural language to SQL conversion, adopting a two-stage decoupled architecture of NL2MDX2SQL, splitting the process into two independent stages: natural language to MDX and MDX to SQL. The front-end uses a large language model to complete semantic mapping within a restricted subset of MDX syntax space, while the back-end relies on a purely rule-driven deterministic compilation engine to complete the conversion, without depending on a probabilistic model throughout. Compared to the NL2MQL2SQL solution, which also uses an intermediate layer, this embodiment uses the standard OLAP query language MDX as the intermediate semantic carrier, rather than a flat JSON-formatted MQL command that only supports indicator selection, resulting in a fundamental difference in semantic carrying capacity.

[0097] This embodiment employs MDX, a unified intermediate buffer layer with complete multidimensional semantics, which differs from semantically barren custom intermediate expressions such as the lightweight JSON configuration structure of MQL and the general abstract syntax tree of IRNet. MDX natively incorporates dimensions, metrics, hierarchical topology, and a standard time function system, enabling the direct implementation of complex multidimensional analysis logic such as year-on-year and month-on-month comparisons, cumulative periodicity, hierarchical drill-down, and ranking filtering within the intermediate layer. Furthermore, the standardized syntax of MDX possesses clear semantic anchors, supporting dual validation of end-to-end syntax and business semantics—a core capability lacking in flat MQL structures.

[0098] This embodiment uses a unified multidimensional semantic model as the foundation for end-to-end knowledge. The model encapsulates complete dimension definitions, metric parameters, hierarchical topology, and metadata related to fact tables and dimension tables. It provides a unified and standardized semantic basis for large model prompt word constraints, pre- and post-level validation, and compilation mapping transformation, ensuring semantic consistency and logical closure throughout the entire chain. Unlike this embodiment, traditional MQL solutions adopt a discrete management mode, with each indicator independently bound to a dedicated SQL template. They lack a unified multidimensional semantic association system, resulting in poor scalability and maintainability.

[0099] This embodiment sets up a two-level deterministic verification and self-correcting closed-loop mechanism after MDX generation and before SQL compilation. The first level performs syntax parsing verification based on BNF rules of a subset of MDX, screening for statement format and structural legality. The second level relies on the in-memory hash index of the multidimensional semantic model to perform business semantic verification, checking entity validity, hierarchical topology, dimensional metric adaptability, and function parameter legality. When verification fails, the system outputs structured error information and sends it back to the large model for rewriting, achieving automatic error correction. In contrast, the MQL solution only supports single-layer JSON format verification, lacks syntax and business semantic verification capabilities, and cannot identify deep logical errors.

[0100] In this embodiment, the MDX-to-SQL conversion is entirely based on deterministic rule compilation, without the involvement of neural network probabilistic prediction. The compilation process is entirely driven by multidimensional semantic model metadata, and multi-table JOIN paths are automatically derived from foreign key association metadata, eliminating the need for manual template pre-binding. Time-series calculations and aggregate function selection are determined by standardized compilation rules and inherent metric definitions, avoiding human configuration bias. Simultaneously, the system employs a layered dialect abstraction design, encapsulating the syntactic differences between different databases into pluggable adapters. The core compilation logic is universal and stable, and can be lightweight and adaptable to multiple data sources.

[0101] Based on the above-mentioned core architectural innovations, this embodiment can achieve a comprehensive improvement in accuracy, semantic fidelity, adaptation flexibility and cache reuse rate compared with existing technologies in multidimensional analysis scenarios. The specific technical advantages are as follows.

[0102] This embodiment significantly improves the accuracy of multidimensional queries from the root. Addressing the error-prone nature of multi-table joins, this embodiment relies on structured foreign key metadata and a fixed algorithm to automatically deduce the JOIN path, ensuring its correctness and completely resolving the shortcomings of end-to-end models that rely on text similarity probability inference and suffer significant accuracy degradation in multi-table joins. Regarding the issue of misjudgment in aggregation logic, this embodiment directly reads the fixed metric aggregation types in the model, eliminating probabilistic errors caused by confusion in large models with operations such as SUM, COUNT, and AVG. Simultaneously, it employs pre-processing syntax and semantic checks to intercept illegal statements in advance and automatically correct errors through a retry loop, preventing erroneous intermediate statements from entering the compilation process.

[0103] The MDX middleware layer achieves lossless fidelity and traceability of multidimensional semantics across the entire chain. It fully preserves native OLAP semantics such as dimension hierarchy, metric attributes, time offsets, and multidimensional intersections. The compilation engine accurately and losslessly maps MDX semantics to the grouping, association, windowing calculation, and aggregation logic of SQL, forming a complete traceability chain of "natural language requirements - MDX middleware semantics - executable SQL." Compared to end-to-end solutions that directly jump to generate SQL, lack intermediate semantic carriers, and are prone to semantic loss, this embodiment enables verification and review of each step of the transformation logic.

[0104] Regarding cross-data source adaptation, the system's core compilation rules are universal across all data sources, with only differentiated syntax implemented through independent dialect adapters. When adding new data warehouses and databases, adaptation can be completed by configuring only ten to twenty syntax mapping rules, without retraining large models or modifying the core compilation logic, significantly reducing deployment and iteration costs in multi-data source environments.

[0105] This embodiment effectively improves the compiler cache hit rate and query response speed. The system performs standardized cleaning of MDX statements and uses normalized MDX hash values ​​as cache keys. Frequently repeated queries such as high-frequency periodic reports and routine business analysis can reliably hit the cache, directly skipping the complete compilation pipeline and reusing existing SQL results, significantly reducing end-to-end latency and system computing power overhead for repeated queries.

[0106] Regarding the accuracy of multi-table joins, both end-to-end large model solutions and IRNet / SemQL solutions rely on probabilistic generation, resulting in low and unstable accuracy in multi-table scenarios. The NL2MQL2SQL solution relies on fixed metric templates and cannot adaptively customize queries. This embodiment relies on deterministic derivation based on metadata, ensuring completely accurate join logic. Regarding aggregation function selection, traditional models suffer from probabilistic misjudgments, and the MQL solution only supports preset aggregation logic. This embodiment uses deterministic values ​​based on the inherent definition of the metric, eliminating the risk of errors.

[0107] Regarding multidimensional semantic preservation, neither end-to-end solutions nor the general IRNet AST structure can retain complete OLAP layers and temporal semantics. The MQL planar structure is prone to losing topological information. This embodiment relies on MDX to achieve complete retention of semantics across the entire link and layer-by-layer tracing. In terms of verification capabilities, traditional solutions lack effective verification mechanisms, and MQL only supports format verification. This embodiment has two-level deterministic verification of syntax and semantics, as well as an automatic error correction closed loop.

[0108] Regarding time function processing, end-to-end model interpretation is unstable, IRNet lacks native time series support, and MQL requires manually predefined metrics to implement time series logic. This embodiment relies on standardized MDX time functions and fixed compilation templates, resulting in unified and stable time series calculations. In terms of cross-dialect adaptation, traditional solutions all require retraining the model or reconstructing the underlying mapping. This embodiment only requires a lightweight adapted dialect adapter, resulting in extremely low expansion costs.

[0109] In terms of interpretability and cache adaptation, the end-to-end solution is basically untraceable and has a low caching rate, IRNet is only partially traceable and has a moderate caching capacity, MQL has limited traceability and a high cache hit rate. This embodiment has full-link rules that can be reviewed, and the standardized MDX hash cache hit rate is the best, with significantly superior overall engineering practicality.

[0110] Figure 7 This is a schematic block diagram of an SQL statement generation device 700 based on an intermediate semantic layer provided in an embodiment of the present invention. The device 700 includes: The query preprocessing unit 701 is used to receive a natural language query request input by a user, preprocess the natural language query request, and obtain the semantic context of the natural language query request through a multidimensional semantic model. The statement generation unit 702 is used to input the preprocessed natural language query request and the semantic context into the large language model, and to constrain the large language model to generate MDX query statements as intermediate semantic layers within a preset subset of MDX syntax. The two-level verification unit 703 is used to perform two-level verification on the MDX query statement; wherein the two-level verification includes syntax verification and semantic verification. The statement compilation unit 704 is used to perform deterministic compilation on the MDX query statement after it has passed two levels of verification to obtain the target SQL statement; wherein the deterministic compilation process includes semantic analysis, logical plan generation and SQL code generation in sequence. The inspection return unit 705 is used to perform security checks and parameterization processing on the target SQL statement, and then output the target SQL statement as the return result of the natural language query request.

[0111] Since the embodiments of the apparatus and the embodiments of the method correspond to each other, please refer to the description of the embodiments of the method for the embodiments of the apparatus, which will not be repeated here.

[0112] This invention also provides a computer-readable storage medium storing a computer program thereon, which, when executed, can perform the steps provided in the above embodiments. The storage medium may include various media capable of storing program code, such as a USB flash drive, a portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0113] This invention also provides a computer device, which may include a memory and a processor. The memory stores a computer program, and when the processor calls the computer program in the memory, it can implement the steps provided in the above embodiments. Of course, the computer device may also include various network interfaces, power supplies, and other components.

[0114] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the systems disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the descriptions are relatively simple; relevant parts can be referred to in the method section. It should be noted that those skilled in the art can make various improvements and modifications to this application without departing from the principles of this application, and these improvements and modifications also fall within the protection scope of the claims of this application.

[0115] It should also be noted that, in this specification, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

Claims

1. A method for generating SQL statements based on an intermediate semantic layer, characterized in that, include: Receive a natural language query request input by a user, preprocess the natural language query request, and obtain the semantic context of the natural language query request through a multidimensional semantic model; The preprocessed natural language query request and the semantic context are input into the large language model, and the large language model is constrained to generate MDX query statements as intermediate semantic layers within a preset subset of MDX syntax. The MDX query statement undergoes two-level validation, including syntax validation and semantic validation. This two-level validation includes: constructing a recursive descent parser based on the BNF syntax rules of the MDX syntax subset, and using the recursive descent parser to perform syntax validation on the MDX query statement; wherein the syntax validation includes illegal token detection at the lexical level, syntax order validation at the token sequence level, and structural integrity validation at the abstract syntax tree level; constructing an abstract syntax tree for the MDX query statement using the recursive descent parser, and performing semantic validation on the MDX query statement based on the abstract syntax tree; wherein the semantic validation includes entity existence validation, hierarchical path topology validity validation, metric and dimension compatibility validation, and time function parameter validity validation. After the MDX query statement passes two levels of validation, it is deterministically compiled to obtain the target SQL statement; wherein the deterministic compilation process includes semantic analysis, logical plan generation and SQL code generation in sequence. The target SQL statement is subjected to security checks and parameterization, and then the target SQL statement is output as the return result of the natural language query request.

2. The SQL statement generation method based on an intermediate semantic layer according to claim 1, characterized in that, The steps of receiving a natural language query request input by a user, preprocessing the natural language query request, and obtaining the semantic context of the natural language query request through a multidimensional semantic model include: The natural language query request is identified based on a business vocabulary and rule matching, and non-standard terms in the natural language query request are mapped to standard business names in a multidimensional semantic model through a preset synonym mapping table; wherein, the objects of the entity identification include dimension value entities, metric entities and time expression entities; Convert the time expression entity into a standard time offset representation; The natural language query requests are categorized using keyword pattern matching; the types of query categorization include aggregate queries, comparison queries, and detailed queries. The natural language query request is subjected to context state maintenance; wherein, the context state maintenance includes storing the current active dimension, activity metric, global filter conditions and historical query records; When the natural language query request contains ambiguity that cannot be automatically determined, candidate enumeration and clarification prompts are used to resolve the ambiguity.

3. The SQL statement generation method based on an intermediate semantic layer according to claim 1, characterized in that, The steps of receiving a natural language query request input by a user, preprocessing the natural language query request, and obtaining the semantic context of the natural language query request through a multidimensional semantic model further include: The core elements of a pre-constructed multidimensional semantic model include: analysis topic, dimension system, hierarchical enumeration value range, metric definition, and foreign key association metadata between fact table and dimension table. Based on a multidimensional semantic model containing the core elements, the natural language query request is matched with a list of dimensions, a list of metrics, available time functions, and set operation functions, thereby summarizing to obtain the semantic context.

4. The SQL statement generation method based on an intermediate semantic layer according to claim 1, characterized in that, The step of inputting the preprocessed natural language query request and the semantic context into the large language model, and constraining the large language model to generate MDX query statements as intermediate semantic layers within a preset subset of MDX syntax, includes: Based on the natural language query request and semantic context, restrictive prompt words are constructed and input into the large language model; Configure decoding parameters, enforce grammatical prefix constraints, and stop word rules for the large language model; Obtain the original output content of the large language model and perform structured processing on the original output content to obtain the MDX query statement; wherein, the structured processing includes text cleaning, bracket matching verification and syntax format standardization.

5. The SQL statement generation method based on an intermediate semantic layer according to claim 1, characterized in that, After the MDX query statement passes two levels of validation, it is deterministically compiled to obtain the target SQL statement, including: Traverse the entity reference nodes of the abstract syntax tree, and bind nodes carrying semantic information to the entity reference nodes through the index of the multidimensional semantic model to obtain the annotated abstract syntax tree; Based on the annotated abstract syntax tree, a relational algebra expression tree is derived; The relational algebra expression tree is traversed, and the target SQL statement is generated based on the preset mapping rules between MDX semantic elements and SQL structure.

6. The SQL statement generation method based on an intermediate semantic layer according to claim 1, characterized in that, The step of performing security checks and parameterization on the target SQL statement, and then outputting the target SQL statement as the return result of the natural language query request, includes: The target SQL statement is subjected to a security check according to a preset security policy; wherein, the security policy includes intercepting target SQL statements containing write operation keywords through regular expression matching, configuring query timeout thresholds, and automatically injecting a maximum number of rows that are adapted to the target dialect. For the target SQL statement, identify the dimension columns and measure columns, generate visual recommendation information based on the dimension columns and measure columns, and attach re-aggregated metadata.

7. A SQL statement generation device based on an intermediate semantic layer, characterized in that, include: The query preprocessing unit is used to receive a natural language query request input by a user, preprocess the natural language query request, and obtain the semantic context of the natural language query request through a multidimensional semantic model. The statement generation unit is used to input the preprocessed natural language query request and the semantic context into the large language model, and to constrain the large language model to generate MDX query statements as intermediate semantic layers within a preset subset of MDX syntax. A two-level verification unit is used to perform two-level verification on the MDX query statement; wherein the two-level verification includes syntax verification and semantic verification; the two-level verification of the MDX query statement includes: constructing a recursive descent parser based on the BNF syntax rules of the MDX syntax subset, and using the recursive descent parser to perform syntax verification on the MDX query statement; wherein the syntax verification includes illegal token detection at the lexical level, syntax order verification at the token sequence level, and structural integrity verification at the abstract syntax tree level; constructing an abstract syntax tree on the MDX query statement through the recursive descent parser, and performing semantic verification on the MDX query statement based on the abstract syntax tree; wherein the semantic verification includes entity existence verification, hierarchical path topology validity verification, metric and dimension compatibility verification, and time function parameter validity verification; The statement compilation unit is used to perform deterministic compilation on the MDX query statement after it has passed two levels of verification, to obtain the target SQL statement; wherein, the deterministic compilation process includes semantic analysis, logical plan generation and SQL code generation in sequence. The inspection return unit is used to perform security checks and parameterization on the target SQL statement, and then output the target SQL statement as the return result of the natural language query request.

8. A computer device, characterized in that, The method includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the SQL statement generation method based on an intermediate semantic layer as described in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the SQL statement generation method based on an intermediate semantic layer as described in any one of claims 1 to 6.

Images