HENGSHI SENSE Precomputation: Aggregate Datasets and ClickHouse Materialized Views

Summary: Frequently used business dashboards repeatedly calculate the same orders, products, and organizational dimensions. Teams can prepare stable, reusable calculations in advance, but query speed alone is insufficient: analytical granularity, data freshness, metric definitions, and correction paths must all be specified. HENGSHI SENSE provides aggregate datasets, an acceleration engine, data updates, and multi-table joins. ClickHouse incremental materialized views provide a lower-level result-maintenance mechanism triggered by inserts into a source table. Using manufacturing orders, this article explains how to design query granularity, result maintenance, and metric reuse together.

1. Separate repeated queries into two questions

A manufacturer’s business dashboard needs order amounts, delivered quantities, and completion rates by day, factory, and product line. The order fact table receives ongoing writes, a product dimension stores product lines and models, and an organization dimension stores factories and regions. Users repeatedly filter, aggregate, and drill down across these dimensions. The team faces two distinct questions: which fixed analytical granularities the dashboard must serve, and which process maintains prepared results when new orders arrive or dimensions change.

The first question concerns modeling and analytical design. The second concerns data synchronization and result maintenance. Separating them lets a team preserve stable granularities such as day, factory, and product line in aggregate datasets, then select update plans, immediate updates, or a validated database-maintenance approach according to how data reaches the analytical layer. Query benefits must be tested with actual volume, filters, concurrency, and freshness objectives. Fixed row counts, response times, or speedup factors cannot replace acceptance criteria.

2. Use aggregate datasets to define reusable analytical granularity

HENGSHI defines an aggregate dataset as a pre-aggregation of an existing dataset, reducing a large table to a smaller table for analysis. During creation, a modeler selects the base dataset and adds dimensions, measures, and filters. Dimensions can contain fields; measures can contain fields or metrics; filters also support fields, metrics, and in-chart metrics. The interface turns decisions about retained dimensions, reusable metrics, and scope restrictions into a maintainable analytical object.

In the order example, the team can retain a detail dataset containing order number, order time, factory, product, amount, and quantity, then create an aggregate dataset at “day × factory × product line.” If product managers need to inspect fluctuations by model, include model in that granularity. If they only need a product-line overview, keep model in the detail dataset. Drill-down capability depends on retained dimensions. Without model, the aggregate dataset cannot answer model-level detail on its own, but the team can continue through the preserved detail dataset. This reduces repeated detail-table queries while retaining original granularity.

Check multi-table relationships in the data model first. HENGSHI SENSE multi-table joined datasets support field-based left, right, inner, and outer joins, and require the joined results to be checked in data preview. Datasets from different sources need the acceleration engine enabled for an engine-side join. Modelers should inspect whether a one-to-many relationship amplifies fact rows before choosing join types and aggregation granularity. The multi-table joined dataset manual documents these joins and the cross-source boundary.

3. Check metric reuse before pre-aggregating

Additive metrics such as amounts and quantities can be stored at a finer granularity and rolled up. Average delivery cycle time needs both total cycle time and order count, with the displayed average calculated as sum(total cycle time) / sum(order count). Completion rates need their numerator and denominator. Distinct customer counts, closing inventory, and balances across periods require detail, mergeable states, or explicit recomputation rules. Summing daily averages, daily completion rates, or daily distinct counts changes their business meaning.

HENGSHI metrics, datasets, and dashboards can reuse these definitions across entry points. Before publication, data owners should fix a time interval, factory scope, and product scope; calculate the same metric on the detail and aggregate datasets; and record differences, update times, and filters. Once that comparison passes, dashboards, reports, and controlled data Q&A can use the same approved definition rather than separately assembled aggregate expressions.

4. Acceleration and updates determine when data becomes visible

An aggregate dataset expresses calculation granularity. It does not arrange how source data reaches analysis. HENGSHI SENSE can enable the acceleration engine for a dataset: the system imports its data, and queries against that dataset directly access the engine table. The official manual also cautions against large-scale imports from large-data sources such as Hive, Spark, Impala, Presto, and MaxCompute. Teams should choose direct access or import according to volume and source capabilities. The acceleration-engine manual describes this path and synchronization-frequency settings.

When source rows change, imported datasets need a data update to remain current. The platform provides immediate updates, update plans, and full or incremental update settings. Incremental updates apply only to datasets created through SQL queries or direct connections, and the incremental field should identify the data that needs updating in the current run. For composite datasets, update plans can configure whether an upstream refresh triggers an update. Include the latest successful update time, failure alerts, and upstream dependencies in dashboard acceptance. The dataset-update manual documents these entry points and limits.

5. ClickHouse incremental materialized views maintain results at insert time

ClickHouse incremental materialized views move computation from query time to insert time. When a new block is inserted into the source table, the view executes its defining query and writes results into a target table. Later inserts continue adding incremental results. The target table’s engine and the query expression must match the aggregation or state-merging method. This mechanism can maintain aggregation, filtering, or transformation over an append-oriented fact stream at the database layer. ClickHouse documentation describes it as a query triggered by inserted blocks, rather than a refresher that automatically reacts to arbitrary table changes.

A JOIN between orders and a product dimension illustrates the boundary. If the order table is the leftmost table in the query, new order inserts trigger the view, and the right-hand product dimension participates in that join. A later product-name or product-line change in the right-hand table does not itself trigger the view or retroactively correct existing order results. Updates or deletions of existing source data likewise cannot be interpreted as arbitrary-change triggers. If the business needs current dimension attributes or corrected historical aggregates, the design must define how CDC or ingestion expresses changes, how the target table is recomputed or backfilled, and who verifies the corrected definition.

During deployment, the data team first establishes how HENGSHI connects to and models the ClickHouse result table, then checks the timing of source capture, fact writes, result maintenance, and dashboard queries. For appended orders, verify that amounts and quantities reach the result table promptly after insertion. For a product reclassification, choose whether to preserve transaction-time attributes or recompute history according to business needs. Recording both requirements in the maintenance plan lets insert-time computation handle new facts while helping users explain changes to historical figures.

6. The two precomputation layers can combine, with different responsibilities

Analytical modeling path: base datasets → aggregate dataset at day × factory × product line → configured update/query → business dashboard. Database maintenance path: new order INSERT → ClickHouse incremental materialized view → result table. Product-dimension changes do not trigger that view; recomputation or historical backfill is separate.

Layer	Question it handles	Acceptance checks for the order example
Aggregate dataset	Defines granularity such as day, factory, and product line, together with dimensions, measures, and filters.	Amount, quantity, and completion-rate definitions agree between aggregate and detail; drill-down matches the retained granularity.
Acceleration engine and data updates	Queries suitable imported datasets in the engine and synchronizes source changes through configured actions.	Latest successful update time, upstream dependencies, failure alerts, and permission scope are inspectable.
ClickHouse incremental materialized view	Maintains a target table as facts are inserted under a confirmed database design.	Inserts, dimension changes, updates/deletes, and historical backfill each have executable maintenance and validation steps.

Work backward from business queries to choose a combination. Aggregate datasets can provide standard dashboard granularities with scheduled engine-side refreshes. Alternatively, a validated ClickHouse write path can maintain a result table for an append-only order stream, which HENGSHI SENSE then connects to and models. Both choices must retain metric definitions, data permissions, freshness, and backfill records. Both reduce repeated work, but their trigger semantics differ.

7. Follow a verifiable implementation path

1. List the business questions the dashboard must answer, including minimum drill-down granularity, time range, user roles, and freshness objectives.
2. Build order, product, and organization datasets in HENGSHI SENSE. Preview multi-table joins and check for one-to-many amplification.
3. Create aggregate datasets retaining the dimensions required for core metrics and drill-down. Define reusable calculation states or recomputation rules for averages, ratios, distinct counts, and closing values.
4. Choose direct query or acceleration according to source conditions. When importing, configure and verify immediate updates or update plans, and record the latest successful update.
5. Define ClickHouse incremental materialized-view maintenance only when the architecture calls for it. Specify actions for source inserts, dimension changes, historical backfill, and updates/deletes, then check the target table with the same samples.
6. Compare detail results, aggregate results, and the final dashboard under the same conditions. Confirm definitions, permissions, and freshness before making metrics available to reports, dashboards, or controlled HENGSHI SENSE data Q&A.

8. Conclusion

Precomputation creates value through reusable analytical results and remains constrained by data-maintenance boundaries. HENGSHI SENSE combines aggregate datasets, acceleration, update management, and modeling in one analytical workflow, helping teams turn recurring business questions into governable assets. ClickHouse incremental materialized views suit a confirmed insert-driven database path. Designing analytical granularity, data arrival, and correction strategies separately lets teams pursue query efficiency while keeping metrics trustworthy and data traceable.