Article body
Full article
Introduction
You type a sentence into ChatBI: “What were the top 5 products by sales in East China last month?” In less than two seconds, the system returns a chart with numbers. The experience feels simple, but underneath it sits a difficult engineering problem: how can a machine translate ambiguous, incomplete, conversational human language into a query that can run correctly on enterprise data?
Traditional BI works as “humans write SQL, machines execute it.” ChatBI turns that process around: humans speak, machines write the query. That reversal is exactly where the hardest problem appears. “Best-selling last month” might mean sales volume or sales revenue. “East China” may be a sales region in one company and a geographic label in another. “Top 5” may mean ranking by absolute value or by year-over-year growth, even when the user does not specify it.
HENGSHI ChatBI builds a query understanding engine around the metrics semantic layer. It splits “natural language → executable query” into four stages: semantic parsing, intent disambiguation, query rewriting, and safety validation. This article breaks down the underlying technology.
1. Why Is ChatBI Query Understanding Hard?
1.1 Three Natural Traps in Human Language
Ambiguity. The same word can point to very different concepts in different contexts. “Growth” may mean absolute increase or growth rate. “Users” may mean registered users, paying users, or active users. Without business context, a language model cannot reliably decide.
Omission and reference. Users rarely repeat every condition. “How does it compare with last month?” Which baseline does “last month” refer to? Does “compare” mean total value or growth rate? Human conversation relies on context, but machines do not have reliable memory by default.
Synonyms and informal phrasing. “GMV,” “transaction value,” and “gross sales” may mean the same thing in a business context, even though the surface words differ. “Hot seller,” “hit product,” and “best seller” point to high sales, even if none of those words exists as a database field.
These traps mean ChatBI cannot simply ask an LLM to generate SQL directly. Typical direct LLM-to-SQL accuracy is only around 50%-65%, and the errors are often hidden: the query runs and returns a number, but the number does not mean what the user intended.
1.2 HENGSHI’s Answer: Semantic Layer First
The core design philosophy of HENGSHI ChatBI is: do not let the model guess the database structure from scratch; let it choose from a business semantic layer.
The semantic layer maps business concepts to physical fields. It defines in advance:
- which business terms correspond to which metrics, such as “sales volume” mapping to the sum of the
sales_volumemetric - which dimensions are available, such as time, region, product, and channel
- how metrics are calculated, such as gross margin rate = (sales revenue - cost) / sales revenue
- how dimensions are organized, such as East China, North China, and South China under the region dimension
When a user asks a question, the model no longer needs to infer what exists in the database. It only needs to select the right metrics, dimensions, and filters from the semantic layer. This turns an open-ended “generate SQL” problem into a controlled selection problem, lifting accuracy from around 60% to above 90%.
2. Stage One: Semantic Parsing — Turning a Sentence into Structured Intent
2.1 Entity Recognition: Identifying the Key Information
When a user asks, “top 5 products by sales in East China last month,” the first step is entity recognition. The sentence is decomposed into four types of structured elements:
- Metric entity: “sales” maps to the
sales_volumemetric in the semantic layer. - Dimension entity: “East China” maps to the “East China” member under the region dimension; “product” maps to the product dimension.
- Time entity: “last month” is parsed as a relative time expression and converted into a concrete date range, such as 2026-06-01 to 2026-06-30.
- Modifier entity: “top 5” is parsed as a ranking limit, ordering by sales volume in descending order and taking the first five records.
HENGSHI uses a hybrid “rules + model” approach for entity recognition. Specialized time parsing rules ensure precision for expressions such as last week, this quarter, and the past 30 days. For metrics and dimensions, the engine combines the candidate vocabulary from the semantic layer with fuzzy matching. Even if a user writes “how many units did we move,” the synonym list can still map the phrase to the sales volume metric.
2.2 Intent Classification: Understanding What the User Wants
After entity recognition, the engine determines the user’s analysis intent. Common intent types include:
- detail query, such as “list all East China orders”
- aggregate statistics, such as “what is total sales in East China”
- ranking analysis, such as “top 5 products”
- period-over-period comparison, such as “how much did it grow compared with last month”
- trend analysis, such as “sales trend over the past six months”
- contribution or share, such as “what share does East China contribute to the total”
Intent classification determines the query template used next. For example, “top 5” triggers ranking logic, so the engine automatically adds sorting and limiting. “Trend” triggers a trend intent, so the engine enforces a time dimension and chooses a line chart as the default visualization.
3. Stage Two: Intent Disambiguation — Resolving the Definition
3.1 Metric Disambiguation
When one question matches multiple candidate metrics, the engine has to disambiguate. HENGSHI uses a three-layer strategy:
Layer 1: context first. If the conversation has been about sales volume, then a follow-up like “how much did it grow?” inherits “sales volume growth” instead of jumping to sales revenue.
Layer 2: default definitions in the semantic layer. Each metric can carry a default business definition. For example, “revenue” may default to “main business revenue” rather than “other operating revenue.” When the user does not specify, the engine uses the default and explicitly labels the answer with that definition.
Layer 3: proactive clarification. When ambiguity cannot be resolved through context or defaults, the engine does not guess. It asks a clarifying question, such as “Do you mean contract value or collected payment?” This “ask once rather than answer wrongly” design is central to ChatBI trust.
3.2 Dimension Member Disambiguation
“East China” may be a standard sales region in one company, a geographic region in another, or a parent node containing provinces such as Shanghai and Jiangsu. HENGSHI builds hierarchy trees and synonym dictionaries for dimension members in the semantic layer. When “East China” appears, the engine first checks the hierarchy and member relationships, then decides whether the query should filter at the regional aggregate level or drill down to provincial details based on the granularity in the user’s question.
3.3 Time Disambiguation
“Last month” is a relative time expression that must be anchored to the current month. Fiscal years are even more complex: some enterprises start the fiscal year in April, others in January. HENGSHI configures each tenant’s fiscal-year start month and timezone in the semantic layer. The time parser uses that configuration to convert expressions such as “this quarter” and “last fiscal year” into absolute date ranges, avoiding errors across timezones or fiscal calendars.
4. Stage Three: Query Rewriting — From Semantic Plan to Executable Query
4.1 Semantic Plan
After disambiguation, the engine generates a semantic plan, an intermediate representation between natural language and SQL. In natural language, it looks like this:
“Group the sales_volume metric by the product dimension, filter region = East China and date in 2026-06, sort by the metric in descending order, take the first five records, and return product names plus sales volume values.”
This plan is the core of ChatBI explainability. It can be translated by machines and reviewed by humans.
4.2 Translating the Plan into SQL
The translation from semantic plan to SQL is deterministic and verifiable, unlike asking an LLM to write SQL directly. The translator uses the physical mappings in the semantic layer to replace each abstract concept with concrete table names, field names, and aggregation functions. Because the mappings are strictly defined by the semantic layer, the generated SQL is controlled in both syntax and semantics.
4.3 Query Optimization and Pushdown
The generated SQL is not sent directly to the database. Before execution, HENGSHI performs query optimization:
- Partition pruning: scan only the relevant partitions based on time filters to avoid full-table scans.
- Pre-aggregation hits: if the semantic layer defines materialized views or summary tables and the query granularity matches, use the pre-aggregated result directly, reducing response time from seconds to milliseconds.
- Permission injection: automatically append row-level permission filters to SQL, such as limiting the current user to regions they manage. The user can only ask for data they are allowed to see.
5. Stage Four: Safety Validation — Accurate and Compliant
5.1 Three Validation Gates
After query rewriting and before execution, the request passes through three validation gates:
- Metric permission validation: does the user have permission to view the metric? If not, the engine refuses to answer and explains why.
- Parameter whitelist validation: are sorting, filters, and time ranges within allowed boundaries? For example, the engine can reject queries outside an authorized historical window.
- Resource circuit breaking: does the query involve too much data, or is the estimated scan size above the threshold? If so, the engine downgrades to sampling or asynchronous execution to prevent one question from overloading the cluster.
5.2 Working with RAG
In earlier technical articles, we introduced how HENGSHI ChatBI uses retrieval-augmented generation to suppress hallucinations. The semantic understanding stage is one place where RAG plays a key role. When a user asks questions involving business knowledge, such as “what is our definition of core metrics,” the engine retrieves relevant materials from the metric knowledge base, such as glossary entries and metric definition documents, then combines them with the semantic layer to generate an accurate response instead of relying on the model’s training memory.
6. A Complete Example: From One Sentence to One Chart
User question: “How did sales revenue in East China and North China in Q2 this year compare with last year?”
Semantic parsing: identify the metric “sales revenue,” the region dimension with members East China and North China, the time expressions “Q2 this year” and “last year,” and the comparison intent.
Intent disambiguation: “sales revenue” uses the default definition in the semantic layer, such as main business revenue; “Q2 this year” is converted into an absolute date range based on the tenant’s fiscal calendar; “last year” is aligned to the same quarter in the previous year.
Query rewriting: generate the semantic plan: “Group sales revenue by region, filter region in {East China, North China}, filter time to Q2 this year and Q2 last year, then calculate year-over-year growth.” Translate this into two SQL queries, or a single SQL query grouped by year, with year-over-year calculation logic appended.
Safety validation: confirm the current user has permission to view sales revenue for East China and North China, the time window is authorized, and the estimated scan size stays within threshold.
Execution and presentation: return a grouped bar chart with region on the x-axis, sales revenue on the y-axis, and the year-over-year growth percentage labeled for each region. The full process typically completes in one to two seconds.
7. Technical Approach Comparison
| Approach | Accuracy | Explainability | Controllability | Suitable Scenarios |
|---|---|---|---|---|
| LLM directly generates SQL | 50%-65% | Low, black box | Low | Personal exploration, non-critical scenarios |
| Text-to-SQL + few-shot | 70%-80% | Medium | Medium | Simple single-table queries |
| Semantic layer + plan rewriting (HENGSHI) | 90%+ | High, readable semantic plan | High, governed mappings | Enterprise-grade serious analytics |
8. FAQ
Q1: Can ChatBI still understand highly conversational questions or typos?
Yes. The semantic layer includes synonym dictionaries and fuzzy matching, and the language model itself provides tolerance for minor informal expressions and typos. If the question is completely off-topic or unclear, the engine asks for clarification rather than guessing.
Q2: Does the semantic layer require manual maintenance? Is it expensive?
It requires some initial configuration, but it is a one-time investment that benefits every ChatBI interaction afterward. Unified metric definitions also create governance value that far outweighs the setup cost. HENGSHI can also extract metric definitions from existing BI reports to reduce cold-start cost.
Q3: Why not use a smarter model and skip the semantic layer?
No matter how capable a model is, it does not know an enterprise’s internal metric definitions or data structures. The semantic layer is the bridge that injects private enterprise knowledge into ChatBI. A general-purpose model cannot replace it.
9. Conclusion
Helping ChatBI “understand human language” is far more than calling a large language model. Through a four-stage pipeline of semantic parsing → intent disambiguation → query rewriting → safety validation, HENGSHI ChatBI turns open-ended natural language into a controllable, explainable, auditable query plan, then deterministically translates that plan into optimized SQL.
The core idea is: use the semantic layer to turn guessing into selection, and use the plan to turn the black box into a white box. This is how ChatBI moves from a demo toy into a trustworthy enterprise analytics tool.
In the next article, we will discuss another easily overlooked but experience-defining capability: how ChatBI remembers what you said when the conversation spans more than one turn.