HQL Metric Definition Language: Design Principles and Enterprise Engineering Practices

Introduction

In the BI field, there is a long-overlooked but crucial technical question: what language should be used to define metric calculation logic?

SQL? Too low-level — SQL directly operates on database tables and fields, unintelligible to business users, and every metric definition change requires modifying SQL scripts in each report. Excel formulas? Too lightweight — unable to handle multi-dimensional cross-aggregation, time series calculations, or complex conditional branches. Natural language? Too vague — a description like “year-over-year growth rate” cannot precisely specify every detail of the calculation logic.

HENGSHI developed HQL (Hengshi Query Language) — a declarative definition language for business metrics. HQL’s design goal is to find a balance between “business comprehensibility” and “computational precision,” making metric calculation logic both understandable to business users and precisely executable by engines. This is not a simple technical choice, but the infrastructure design for enterprise BI data modeling.

This article provides an in-depth analysis of the technical content of the HQL metric definition language from four dimensions: design principles, syntax system, engineering practices, and the relationship with SQL.

---

1. Design Philosophy of HQL

1.1 Three Core Design Principles

Principle 1: Business Priority

HQL uses business terminology rather than database field names. In an HQL expression, sales_amount represents the business concept “sales amount,” not the database field t_order.total_price. This design allows metric definitions to be directly understood by business users without needing to understand the underlying data table structure.

The deeper meaning of the business priority principle is: metric definitions should be separated from physical data layer implementation details, describing “what to compute” at the semantic level rather than “how to compute.” When the underlying data table structure changes (such as field renaming or table splitting/merging), HQL definitions do not need modification — only dataset field mappings need updating.

Principle 2: Declarative Definition

HQL uses declarative rather than imperative definition. Users declare “what they want” (such as SUM(order_net_price)), and the HQL engine is responsible for converting it into an execution plan for “how to do it” (such as the corresponding SQL query statement, aggregation path, and join strategy).

The core advantage of declarative definition is “separation of concerns” — metric definers focus on business logic, while the complexity of execution optimization is handled by the engine. When the data engine’s performance optimization strategy changes (such as from full table scan to index scan), HQL definitions do not need modification.

Principle 3: Dimension Independence

HQL metric definitions are not bound to specific dimension combinations. The definition of an atomic metric SUM(order_net_price) contains no dimensional filter conditions — it defines “the calculation logic for sales amount,” not “sales in East China” or “monthly sales.”

The core value of dimension independence is “maximum reusability” — an atomic metric can be referenced by countless business metrics, each business metric binding a different dimension combination. When a new analysis dimension is added, you only need to create a new business metric referencing the existing atomic metric, without modifying the atomic metric definition.

1.2 Comparison of HQL with Traditional Metric Definition Methods

Dimension	SQL Fragments	Excel Formulas	HQL
Expression Target	Database tables and fields	Excel cell references	Business metrics and dimensions
User Role	Data engineers	Business users	Business analysts/business operations experts
Dimension Handling	WHERE conditions hardcoded	Manual filtering	Dimension parameterization, dynamic combination
Calculation Logic	Manually defined per query	Manually defined per sheet	Defined once, reused globally
Maintainability	Scattered across reports	Scattered across Excel files	Centrally managed, version-controlled
Multi-Engine Adaptation	Need to write different SQL for each engine	N/A	Dialect adapter automatically converts

---

2. HQL Syntax System

2.1 Basic Expressions

HQL basic expressions consist of dataset fields and aggregation functions:

Simple Aggregation

// Sales amount: sum of order net price
measure sales_amount = SUM(order_net_price)

// Order count: count of order IDs
measure order_count = COUNT(order_id)

// Average order value: sales amount divided by order count
measure avg_order_value = sales_amount / order_count

Simple aggregation expressions are the most basic form of HQL — defining aggregation computation on a single dataset, with results dynamically changing based on user-selected dimensions.

Conditional Aggregation

// Refunded order count: count of orders meeting refund condition
measure refund_order_count = COUNT_IF(order_status = 'refunded', order_id)

// High-value customer count: customers with cumulative spending over 100K
measure high_value_customer_count =
  COUNT_IF(SUM(order_amount) > 100000, customer_id)

Conditional aggregation achieves dynamic filtering of aggregation scope through conditional functions like IF and COUNT_IF — filtering aggregation scope based on business conditions.

2.2 Time Series Expressions

Time series calculations are core requirements for BI analytics, and HQL provides rich time series functions:

Year-over-Year and Month-over-Month

// Year-over-year growth rate: current period sales amount growth relative to same period last year
metric yoy_growth =
  (sales_amount - lag(sales_amount, 12)) / lag(sales_amount, 12)

// Month-over-month growth rate: current period sales amount growth relative to previous period
metric mom_growth =
  (sales_amount - lag(sales_amount, 1)) / lag(sales_amount, 1)

The lag function retrieves metric values at historical time points — lag(sales_amount, 12) represents the sales amount 12 periods ago. The “period” for the lag function is determined by the metric’s granularity declaration: if granularity is monthly, lag(x, 12) means 12 months ago; if granularity is daily, it means 12 days ago.

Cumulative and Moving Average

// Year-to-date sales amount
metric ytd_sales = running_sum(sales_amount)

// 7-day moving average sales amount
metric ma7_sales = avg(sales_amount, 7)

running_sum achieves cumulative computation from the start of the period to the current point, and the avg function with window parameters achieves moving average.

Time Offset

// Last month sales amount
measure last_month_sales = offset(sales_amount, -1, 'month')

// Same period last year sales amount
measure last_year_same_period = offset(sales_amount, -12, 'month')

The offset function retrieves values relative to the current time point — positive numbers represent the future, negative numbers represent the past.

2.3 Complex Expressions

HQL supports combining multiple metrics into more complex computation logic:

Ratio Metrics

// Gross margin
measure gross_margin = gross_profit / sales_amount

// Inventory turnover
measure inventory_turnover =
  cost_of_goods_sold / avg(inventory_value)

Ratio metrics achieve division of two metrics — numerator and denominator each aggregate independently, then the ratio is calculated. This design ensures that even if the numerator and denominator have different dimension combinations, the ratio calculation result remains correct (e.g., in “East China gross margin,” both gross profit and sales amount aggregate by East China dimension before division).

Ranking Expressions

// Regional sales ranking
measure region_sales_rank =
  rank() over(partition by region order by sales_amount desc)

// Category sales proportion
measure category_sales_pct =
  sales_amount / sum(sales_amount) over(partition by category)

Ranking expressions are achieved through window functions — rank() calculates ranking, and sum() over(partition by) calculates proportion within groups. The introduction of window functions enables HQL to express common business analytics needs such as “ranking,” “proportion,” and “cumulative proportion.”

2.4 Granularity Declaration

HQL granularity declaration is an important tool for metric management precision — it defines the aggregation level for metric computation:

// East China monthly sales — granularity: by region, by month
metric east_monthly_sales =
  sales_amount[region='East China']
  [granularity='region,month']

// National daily GMV — granularity: by day
metric national_daily_gmv =
  sales_amount[granularity='day']

The core value of granularity declaration is “avoiding aggregation conflicts” — when the same dashboard simultaneously displays “by store, by day” and “by region, by month” sales amounts, granularity declarations ensure each metric uses the correct aggregation path without computation conflicts.

2.5 Custom Functions

For industry-specific computation needs, HQL supports user-registered custom functions:

// "Sales per sqm" metric for retail industry — sales amount per unit area
measure sales_per_sqm =
  sales_amount / store_area  // store_area is a custom function returning store area

// "Sharpe ratio" for financial industry — risk-adjusted return
measure sharpe_ratio =
  (return_rate - risk_free_rate) / stddev(return_rate, 252)

Custom function registration is achieved through HQL’s extension mechanism — data teams write custom function computation logic (typically Python or Java), register it with the HQL engine, and it can then be used in HQL expressions. Custom functions are used exactly like built-in functions, and business users do not need to care about underlying implementation.

---

3. HQL Engine Execution Mechanism

3.1 Transformation Process from HQL to SQL

The HQL engine transforms HQL expressions into SQL queries for the corresponding data engine at runtime. This transformation process is divided into four steps:

Step 1: Expression Parsing

The HQL engine parses expressions, constructing an Abstract Syntax Tree (AST). The AST contains all semantic information such as metric references, function calls, dimension conditions, and granularity declarations.

Step 2: Semantic Verification

The engine verifies the semantic correctness of expressions:

• Whether metric references point to defined metrics
• Whether function call parameter types match
• Whether dimension conditions exist in the dataset
• Whether granularity declarations are compatible with metric dimensions

When semantic verification fails, the engine returns clear error messages (such as “Metric ‘sales_amount’ is not defined” or “Dimension ‘region’ does not exist in the dataset”), helping the data team quickly locate problems.

Step 3: Query Plan Generation

The engine generates a query execution plan based on the AST and the current query’s dimension context:

• Determine which datasets need to be joined and join conditions
• Determine aggregation level and grouping fields
• Determine where to inject filter conditions
• Select execution optimization strategies (such as pre-computation hits and index usage)

Step 4: SQL Generation and Dialect Adaptation

The engine transforms the query execution plan into SQL statements for the target data engine, handling engine-specific syntax differences through dialect adapters.

3.2 Dynamic Aggregation Execution Logic

The core capability of the HQL engine is “dynamic aggregation” — the same metric aggregates differently under different dimension combinations:

Scenario: User queries “view sales by region”

1. Engine parses dimension context: dimension=region
2. Engine queries the metric’s HQL definition: measure sales_amount = SUM(order_net_price)
3. Engine generates aggregation SQL: SELECT region, SUM(order_net_price) FROM orders GROUP BY region
4. Dialect adapter converts SQL to the target engine’s syntax
5. Data engine executes SQL, returning sales by region

Scenario: User drills down to “view sales by region, by store”

1. Engine parses new dimension context: dimensions=region, store
2. Engine queries the same metric’s HQL definition (definition unchanged)
3. Engine generates new aggregation SQL: SELECT region, store, SUM(order_net_price) FROM orders GROUP BY region, store
4. Dialect adaptation and execution as above

The core of this entire process is: HQL definition remains unchanged, dimension combinations are dynamically determined at query time. This is the embodiment of the “dimension independence” principle at the execution level — metric definitions are decoupled from dimensions, and dimension combinations are dynamically determined by users’ query behavior.

---

4. Engineering Practices for HQL

4.1 Metric Definition Naming Conventions

Good naming conventions are the foundation of HQL engineering practices:

Atomic Metric Naming

Use the “business concept_measurement type” naming pattern:

• sales_amount (sales amount_value)
• order_count (order_quantity)
• customer_count (customer_quantity)
• avg_order_value (average_order_value)

Business Metric Naming

Use the “dimension condition_metric_granularity” naming pattern:

• east_monthly_sales (East China_monthly_sales)
• national_daily_gmv (National_daily_GMV)
• top10_store_sales (TOP10_store_sales)

Semantic Annotation Conventions

Each metric’s semantic annotations should include:

• Standard name: formal business terminology
• Alias list: common synonyms and abbreviations
• Business description: 1-2 sentences explaining the metric’s business meaning
• Usage scenario annotation: which analytics scenarios this metric is commonly used in

4.2 Metric Reuse and Combination Patterns

HQL’s metric reuse pattern is the key to engineering efficiency:

Base Metric Layer

Define the most底层 atomic metrics — typically 10-20 core metrics covering the enterprise’s main business measurements. These metrics are the foundation of all business metrics, with the highest quality requirements for definitions.

Derived Metric Layer

Simple transformations of base metrics — such as year-over-year, month-over-month, cumulative, and moving average. Derived metrics reference base metrics and do not directly operate on dataset fields.

Composite Metric Layer

Complex computations based on multiple metrics — such as gross margin, inventory turnover, and Sharpe ratio. Composite metrics reference base metrics and derived metrics, forming a hierarchical structure of metrics.

The core value of this three-layer structure is “change propagation” — when a base metric’s definition changes, all derived metrics and composite metrics referencing it automatically update without requiring individual modifications.

4.3 Quality Management for Metric Definitions

Definition Consistency Check

When a new metric’s definition overlaps with existing metrics in terms of logic, the system automatically detects and prompts. For example, when newly defining net_sales = SUM(order_net_price), if gross_sales = SUM(order_gross_price) already exists, the system prompts that the two metrics have similar names but different calculation logic, recommending clear distinction in semantic annotations.

Dimension Coverage Check

The system checks each metric’s dimension coverage — which dimensions’ aggregations for this metric are valid, and which may be misleading. For example, “inventory turnover” aggregated by “store” dimension is valid, but aggregated by “customer” dimension may have no business meaning.

Performance Impact Assessment

For complex metrics (especially multi-layered nested window functions and custom functions), the system assesses their query performance impact. If a metric’s typical query response time exceeds the threshold, the system recommends optimization — such as increasing pre-computation, simplifying expressions, or splitting into multiple simple metrics.

---

5. Synergy Between HQL and Agentic BI

5.1 HQL as the Technical Foundation of Text2Metrics

The core of the Text2Metrics architecture is “mapping natural language to the metric semantic layer” — and the content of the metric semantic layer is metrics defined by HQL. HQL’s definition quality directly determines Text2Metrics’ reasoning accuracy:

• Definition Precision: HQL expression calculation logic must be precise and error-free — an incorrect HQL definition will cause all ChatBI queries referencing it to return incorrect results
• Dimension Completeness: Dimension coverage in HQL definitions determines which dimension combinations ChatBI can support — missing dimension combinations cannot be queried through ChatBI
• Semantic Annotation Quality: Semantic annotations (aliases, descriptions, usage scenarios) attached to HQL metrics determine ChatBI semantic matching accuracy

5.2 HQL Supporting ChatBI Drill-Down Reasoning

When ChatBI Agent performs drill-down reasoning, it needs to understand dimensional hierarchy relationships — and dimensional hierarchy relationships are found in HQL’s granularity declarations and dimension definitions:

// Dimension hierarchy definition
dimension store_hierarchy = {
  level1: store,
  level2: city,
  level3: region,
  level4: country
}

When a user queries “sales in East China” and then asks “which store is highest,” the Agent understands through the dimensional hierarchy definition that “East China → store” is a valid drill-down path, automatically generating a query drilling down by the store dimension.

---

6. Future Evolution of HQL

6.1 AI-Assisted HQL Generation

With the enhancement of large model capabilities, HQL generation methods are evolving from “manual writing” to “AI-assisted”:

• Natural Language to HQL: business users describe metric requirements in natural language (such as “year-over-year growth rate of monthly sales in East China”), AI automatically generates HQL expressions, reviewed and published after manual approval
• Metric Suggestions: AI automatically recommends possibly needed new metric definitions based on dataset fields and existing metrics
• Definition Detection: AI automatically detects whether there are logic conflicts between new and existing metrics

6.2 HQL Cross-Engine Consistency Guarantee

As enterprises increasingly use multiple data engines, HQL’s cross-engine consistency becomes particularly important. HENGSHI is continuously expanding dialect adapter coverage to ensure the same HQL definition produces identical computation results across different engines — this includes alignment at the detail level such as data type conversion, null value handling, and rounding rules.

---

Conclusion

HQL is not “just another query language” — it is the infrastructure for enterprise BI data modeling. Its core value is not “how powerful the syntax is,” but “transforming metric definitions from scattered to centralized, from one-time to reusable, from IT-exclusive to business-comprehensible.”

When enterprises evolve from “manually writing SQL in each report” to “centralized HQL definition in the semantic layer,” from “modifying each report for definition changes” to “modifying atomic metrics with automatic propagation,” from “uncertain ChatBI answers” to “precise reasoning based on HQL semantic layer” — data modeling truly evolves from “technical work” to “business asset.”

A good metric definition language is not a tool making it easier for data engineers, but infrastructure enabling business users to understand and participate in data asset construction. This is the ultimate goal of HQL design.