Implementing Self-Service OLAP Analytics with Quick BI and StarRocks: Architecture, Optimizations, and Lessons Learned

The article introduces ZhaiZhai's practice of building an OLAP self‑service analytics platform, explaining why the platform was needed, the core problems it solves, and the early challenges faced during development.

Technical implementation combines Quick BI for flexible drag‑and‑drop visualizations with StarRocks as the high‑performance OLAP engine, supplemented by Hive, Spark, Flink, and Kafka. The architecture diagram and data flow are shown, and the data model uses a highly redundant wide table with three main field groups: data_type, dimension fields, and atomic metric fields.

The data set creation process involves linking Quick BI to StarRocks, defining dimensions and metrics, and building dashboards. Sample SQL for the data set is provided:

select 维度1 ... ,维度n,
       case when t.data_type = 'DAU' then DAU end as DAU,
       case when t.data_type = '曝光' then 曝光pv end as 曝光pv,
       case when t.data_type = '曝光' then 曝光uv end as 曝光uv,
       case when t.data_type = '商详' then 商详pv end as 商详pv,
       case when t.data_type = '商详' then 商详uv end as 商详uv,
       ... ,原子指标n
from sr_table t

StarRocks was chosen over ClickHouse because of MySQL compatibility, better elastic scaling, superior join support, higher concurrency handling, and closer data type alignment with Hive.

Optimization cases include:

Memory overflow mitigation by enabling spill‑to‑disk in StarRocks (v3.0.1).

Slow query reduction through effective data pruning, avoiding functions on filter columns, and leveraging predicate push‑down.

Dimension value loading speed improvement by using distinct queries on dedicated dimension tables with Quick BI's value‑acceleration feature.

Peak‑time query slowdown alleviation by pre‑caching dashboard queries during off‑peak hours using a Selenium‑driven Python script.

Write‑performance tuning for real‑time (Flink) and batch (SeaTunnel) ingestion by adjusting batch_max_rows, batch_max_bytes, checkpoint intervals, and parallelism.

After deployment, data retrieval time for business users dropped from days to hours or minutes, warehouse developers and analysts freed significant effort, a repeatable self‑service model was established across multiple business lines, and query latency reached sub‑second levels for most workloads.

The solution offers rapid development and quick impact but requires a capable BI tool and ongoing maintenance of the data sets.

Overall, the case study provides practical insights for teams building self‑service analytics on large‑scale data warehouses.