Article body
Full article
Introduction
“What caused the sales decline in East China convenience stores during Q2?” — When a regional manager types this into a Feishu group chat, they expect not a string of SQL code, but directly understandable business insights.
This is ChatBI’s core promise: natural language that leads directly to data conclusions. But reality is far more complex than the promise. Traditional ChatBI uses a Text2SQL architecture, translating natural language directly into SQL queries. While this approach can handle simple query scenarios, it often achieves less than 30% accuracy when facing real-world scenarios involving multi-dimensional cross-analysis, complex calculation definitions, and business semantic dependencies.
Hengshi Technology makes a breakthrough in HENGSHI SENSE 6.0 by adopting the Text2Metrics technical architecture — not translating SQL, but directly mapping natural language to a predefined metric semantic layer. This architectural choice boosts ChatBI’s complex query accuracy to over 80%, fundamentally solving the semantic gap problem of Text2SQL.
1. Text2SQL’s Technical Bottlenecks and Semantic Gap
1.1 Text2SQL’s Working Principles and Limitations
The core Text2SQL process is: Natural Language Input → LLM Reasoning → SQL Statement Generation → Data Engine Execution → Results Returned
This process appears simple, but it reveals three fundamental problems in real enterprise scenarios:
Problem 1: Semantic Gap
“Sales amount” in natural language may correspond to 5 different database fields in a business context: gross_sales, net_sales, adjusted_sales, etc. LLMs have no business context and can only guess based on literal field name matching, resulting in extremely low accuracy.
Problem 2: Calculation Definition Ambiguity
“Year-over-year growth rate” — this most common business metric can have completely different calculation definitions across enterprises: some use (current period - same period last year) / same period last year, some use (current period / same period last year) - 1, and others calculate after excluding seasonal factors. Text2SQL cannot distinguish these differences, and the directly generated SQL may have completely wrong calculation logic.
Problem 3: Missing Dimension Relationships
“East China convenience store Q2 sales” — this involves cross-filtering across three dimensions: region, store type, and time. Text2SQL needs to simultaneously infer the mapping relationships and filtering logic for all three dimensions from natural language. In enterprises with complex dimension hierarchies, this inference is nearly impossible for an LLM alone.
1.2 Quantitative Accuracy Gap
In Hengshi Technology’s customer实测 data, Text2SQL architecture accuracy performance across different complexity query scenarios:
Single dimension, single metric (typical scenario: “total sales this month”): Text2SQL accuracy 65%, Text2Metrics accuracy 95%.
Two-dimension cross (typical scenario: “East China region sales this month”): Text2SQL accuracy 40%, Text2Metrics accuracy 88%.
Multi-dimension + calculation (typical scenario: “East China convenience store Q2 year-over-year growth rate”): Text2SQL accuracy 18%, Text2Metrics accuracy 82%.
Cross-dataset association (typical scenario: “East China sales vs. inventory turnover comparison”): Text2SQL accuracy 8%, Text2Metrics accuracy 75%.
The data clearly shows: the more complex the query, the more severe the accuracy degradation for Text2SQL. Through the predefined metric semantic layer, Text2Metrics maintains complex query accuracy steadily above 80%.
2. Technical Principles of Text2Metrics Architecture
2.1 Core Design Philosophy: Semantic Layer as Intermediate Bridge
The core innovation of Text2Metrics is: adding a metric semantic layer as an intermediate bridge between natural language and the data engine.
This metric semantic layer is not a simple field mapping table, but a knowledge system containing three layers of information:
- Metric Definition Layer: Precise calculation definitions for each business metric, defined using HQL (Hengshi Query Language) expressions
- Dimension Relationship Layer: Dimension hierarchies, dimension value mappings, cross-dimension associations
- Semantic Annotation Layer: Natural language aliases, business descriptions, and usage scenario annotations for each metric and dimension, used for semantic matching with user queries
2.2 HQL: The Standard Language for Metric Definition
HQL is a self-developed metric definition language by Hengshi Technology, and it is the technical cornerstone of the Text2Metrics architecture. HQL’s design principles: business-friendly (uses business terminology rather than database field names), calculation transparency (metric calculation logic defined in declarative expressions, readable, verifiable, modifiable), dimension-agnostic (metric definitions are not bound to specific dimensions; users freely combine dimensions during queries, and the engine auto-adapts).
A typical HQL metric definition example:
// Atomic metric: Sales Amount
measure sales_amount = SUM(order_net_price)
// Business metric: East China Convenience Store Monthly Year-over-Year Growth Rate
metric east_convenience_yoy_growth =
sales_amount[region="East China", store_type="Convenience Store", period="month"]
/ sales_amount[region="East China", store_type="Convenience Store", period="month-12"]
- 12.3 Vector Index: The Acceleration Engine for Semantic Matching
The matching efficiency of the semantic annotation layer is key to Text2Metrics performance. Hengshi Technology uses vector index technology to accelerate semantic matching:
- Metric Vectorization: Converting each metric’s name, aliases, business description, and usage scenario annotations into vector representations
- Dimension Vectorization: Converting dimension names, hierarchy relationships, and value mappings into vector representations
- Real-time Semantic Retrieval: After user query input, first convert it to a vector, then search the vector index for the metrics and dimensions with the most semantically similar representations
The introduction of vector indexing reduces semantic matching response time from seconds to milliseconds, while significantly improving matching accuracy for fuzzy queries and synonym queries.
3. Detailed Text2Metrics Implementation Mechanism
3.1 Five-Step Reasoning Process
The complete Text2Metrics reasoning process is divided into five steps:
Step 1: Intent Recognition — LLM analyzes the natural language intent of user queries, determining query types: simple queries (single metric + single dimension), complex queries (multi-dimension cross-analysis + calculation definitions), analytical queries (requiring drill-down reasoning), comparative queries (cross-time period / cross-dimension comparison).
Step 2: Semantic Matching — Based on vector indexing, matching business terminology in user queries to semantic layer elements: “sales amount” → sales_amount metric, “East China region” → region="East China" dimension value.
Step 3: Query Construction — Based on matching results and metric definitions, automatically constructing structured query statements. This step is entirely driven by the predefined logic of the semantic layer, not relying on LLM-generated SQL.
Step 4: Result Calculation — Structured queries submitted to the data engine for execution, returning calculation results.
Step 5: Result Interpretation — LLM converts calculation results into natural language explanations, providing understandable insights to users combined with business context.
Throughout the entire process, LLM is only responsible for “semantic matching,” not “SQL generation” — the semantic layer has already predefined the calculation logic and dimension relationships, so LLM only needs to map business terminology in natural language to corresponding semantic layer elements.
3.2 Permission Sandbox Embedding Mechanism
In each step of Text2Metrics reasoning, the permission sandbox is embedded as a mandatory checkpoint:
- After Step 2 (semantic matching): Check if the user has permission to access the matched metrics and dimensions
- After Step 3 (query construction): Auto-inject row-level filtering conditions (e.g., regional managers can only query data for their region)
- After Step 4 (result calculation): Check if the returned results contain fields beyond the permission scope
This “embedded” permission checking mechanism ensures that Agents cannot bypass permission controls during reasoning.
3.3 Error Correction and Self-Optimization
The Text2Metrics architecture includes built-in error correction mechanisms:
- Query Validation: After Step 3 completion, automatically validate whether the generated structured query conforms to the semantic layer’s definition rules; non-conforming queries are blocked and the Agent is required to re-reason
- Result Validation: After Step 4 completion, automatically validate whether calculation results are within a reasonable range (e.g., sales amount should not be negative); results beyond reasonable range trigger recalculation
- Feedback Recording: Success/failure records from each reasoning are stored in the memory module for subsequent Prompt optimization and Few Shot case accumulation
4. Industry实测 Data and Effect Evaluation
4.1实测 Data from a Home Appliance Retail Enterprise
Implementation scenario: Full-channel operations data analysis, involving 20+ business systems, 500+ business metrics, and 8 dimension hierarchies.
Business personnel self-service data retrieval ratio: 30% before implementation, 85% after (6 months), change +183%.
Average data retrieval response time: 2 days before (manual queuing), 30 seconds after (ChatBI instant), change -99.98%.
Same-type query Token cost: 1,800 tokens before, 900 tokens after, change -50%.
Complex query accuracy: N/A before (cannot complete), 82% after, change from 0 to 82%.
4.2 Risk Control Scenario实测 from a Securities Company
Implementation scenario: Real-time anti-fraud models, intelligent investment advisory systems, involving millisecond-level response requirements.
Customer inquiry response time: 45 seconds before (manual), 2 seconds after (ChatBI automatic).
Anti-fraud fund interception rate: 0.5 per ten million before, 0.1 per ten million after.
Compliance audit report generation: 2 weeks before (manual compilation), 1 hour after (system automatic).
5. Text2Metrics vs Text2SQL: Architecture Comparison Summary
Core path: Text2SQL is “natural language → SQL”, Text2Metrics is “natural language → semantic layer → structured query”.
Semantic processing: Text2SQL is LLM one-time translation, Text2Metrics is semantic layer predefined + LLM semantic matching.
Simple query accuracy: Text2SQL 65%, Text2Metrics 95%.
Complex query accuracy: Text2SQL <20%, Text2Metrics >80%.
Calculation definition guarantee: Text2SQL none (relies on LLM inference), Text2Metrics strong (semantic layer predefined).
Dimension mapping guarantee: Text2SQL none (relies on LLM inference), Text2Metrics strong (semantic layer predefined).
Permission control: Text2SQL is post-query filtering, Text2Metrics is embedded checking during reasoning.
Maintainability: Text2SQL has high rule template maintenance cost, Text2Metrics has centralized semantic layer management.
Self-optimization capability: Text2SQL none, Text2Metrics has memory module closed-loop feedback.
6. Implementation Suggestions and Best Practices
6.1 Three Key Steps for Semantic Layer Construction
- Metric Inventory: Extract core business metric lists from business reports and operational analysis requirements, clarifying each metric’s calculation definitions, applicable dimensions, and data sources
- HQL Definition: Convert metric lists into HQL expressions, ensuring precise calculation logic and flexible dimension combinations
- Semantic Annotation: Add natural language aliases, business descriptions, and usage scenario annotations for each metric and dimension, completing vectorization and index construction
6.2 Gradual Deployment Strategy
- Phase 1: First cover high-frequency query scenarios (accounting for 60% of daily data retrieval needs), verifying Text2Metrics’ basic accuracy
- Phase 2: Expand to complex analytical scenarios (drill-down reasoning, cross-dimension comparison), enabling the Agent’s memory module and self-optimization capabilities
- Phase 3: Integrate with instant messaging tool ChatBots, achieving an “analysis embedded in business operations” experience
Conclusion
The core insight of Text2Metrics is: the gap between natural language and data is not a translation problem, but a semantic problem. Instead of having the LLM “re-translate” business semantics every time, it’s better to define the calculation definitions and dimension relationships of business metrics once and for all in the semantic layer, letting LLM only handle “intent recognition and semantic matching,” while delegating complex reasoning to predefined structured logic.
This architectural choice fundamentally changes the reliability ceiling of ChatBI — from “30% luck” to “over 80% certainty.” When enterprise decision-makers can trust every number returned by ChatBI, data analysis truly evolves from a “display tool” to a “decision partner.”
Through HENGSHI SENSE 6.0’s Text2Metrics architecture, Hengshi Technology lays the most critical technical foundation for Agentic BI — making natural language no longer a “foreign language” in the data world, but the native tongue leading directly to business insights.