OLAP Technology Overview, Selection, and Optimization Practices

1. OLAP Technology Introduction and Selection

OLAP (On‑Line Analytical Processing) supports enterprise decision‑making analysis and differs from OLTP (On‑Line Transaction Processing), which records real‑time transactional events. When accumulated data reaches a certain scale, analytical needs arise to summarize and analyze historical data, which is the OLAP scenario.

OLTP is used for systems such as e‑commerce, ERP, CRM, and OA, requiring high real‑time performance and stability. OLAP, on the other hand, provides rich data presentation, efficient queries, and multi‑dimensional analysis for decision support.

1.1 Basic OLAP Operations

Typical multi‑dimensional operations include Drill‑down, Roll‑up, Slice, Dice, and Pivot.

Drill‑down: move from coarse to fine granularity (e.g., from quarterly sales to monthly sales).

Roll‑up: aggregate from fine to coarse granularity (e.g., from monthly sales to quarterly or yearly totals).

Slice: select a specific dimension while keeping others.

Dice: select a range of a dimension (e.g., sales from Q1 to Q2).

Pivot: swap rows and columns to view data from a different perspective.

1.2 OLAP Classification

OLAP can be classified by storage format into ROLAP (Relational OLAP), MOLAP (Multi‑dimensional OLAP), and HOLAP (Hybrid OLAP).

MOLAP : stores data in multi‑dimensional cubes, pre‑computes aggregates for fast analysis, but cube generation consumes significant time and space; updates have latency.

ROLAP : uses relational databases without pre‑computation, providing real‑time data freshness, but query performance can be slower.

HOLAP : hybrid approach, keeping detailed data in ROLAP and aggregates in MOLAP, offering flexibility and efficiency.

1.3 Mainstream OLAP Features and Use‑Case Analysis

Druid : pre‑computes aggregates for high‑throughput time‑series queries, offers low latency and multi‑tenant support; suitable for user‑behavior and network‑traffic analysis with high QPS.

Kylin : a MOLAP system that builds cubes for billions of rows, enabling sub‑second queries on pre‑aggregated data; ideal for large‑scale data with many dimensions and infrequent schema changes.

Doris : an MPP query engine with a simple FE/BE architecture, supporting real‑time analytics on PB‑scale data, high concurrency, multi‑table joins, and online schema changes.

ClickHouse : column‑store designed for OLAP, providing vectorized execution, ordered storage, primary‑key and sparse indexes, sharding, partitioning, TTL, and replication; best for moderate QPS analytical reports, not for very high‑concurrency or heavy multi‑table joins.

2. Application Scenarios and Overall Solution

Typical scenarios include self‑service analytics for daily transactions and after‑sales, real‑time monitoring of order volume, inventory, and performance during large promotional events, and the need for sub‑second query response for tens of thousands of daily queries.

Key requirements gathered from multiple business teams are massive data volume (billions of rows per day), millisecond‑level query latency, real‑time data freshness, high concurrency, and platform stability.

The resulting architecture diagram is shown below:

3. OLAP Optimization Practices

3.1 Druid Optimization

Materialized Views

When certain dimension groups appear frequently together, a materialized view can pre‑aggregate those dimensions, reducing query I/O and improving performance by trading storage for speed.

Cache Query

Redis is used as a cache. Instead of caching whole query results, the cache is built on the finest time granularity (day/hour). Queries only fetch uncached time spans and merge them with cached data, improving cache hit rate.

Hot/Cold Data Tiering

Data is distributed across brokers based on access frequency, relieving pressure on individual nodes.

#集群分片，不写默认_default_tier
druid.server.tier=hot  
#查询优先级，不写默认0，_default_tier分片的两个节点为0，hot节点的都改为100。这样，热数据只会查hot节点的机器。
druid.server.priority=100
#processing.buff，默认是1G
processing.buff = 4G
#processing.numThreads:默认是繁忙时core-1做process，剩余的1个进程做与zk通信和拉取seg等。
druid.processing.buffer.sizeBytes=1073741824
druid.processing.numThreads=30

3.2 ClickHouse Optimization

Distributed Aggregation Queries

In distributed aggregation, each node returns its intermediate aggregation state to a coordinator, which then merges them. This allows early data filtering and improves query efficiency.

Skip Indexes

ClickHouse provides skip indexes such as minmax, set, and Bloom filter to reduce the amount of data scanned.

minmax : stores the minimum and maximum values of each block; ideal for loosely sorted columns.

set : stores a set of distinct values per block up to a configurable size; useful for low‑cardinality columns.

Bloom filter : probabilistic structure for fast existence checks, suitable for high‑cardinality fields like order_id or product_id.

In production, Bloom filters are used for highly cardinal fields, while other indexes are omitted to keep index size manageable.

Avoid Using FINAL

ReplacingMergeTree can deduplicate data based on a primary key, but using the FINAL keyword forces a full merge, severely degrading performance. Instead, custom SQL with functions like argMax can achieve deduplication without FINAL.

create table t_replacing_table(
  id UInt8,
  name String,
  age UInt8
) engine = ReplacingMergeTree(age)
order by id;

insert into t_replacing_table values (1,'张三',18),(2,'李四',19),(3,'王五',20);
insert into t_replacing_table values (1,'张三',20),(2,'李四',15);

#自己写SQL方式实现查询去重后的数据，这样避免使用final查询，效率提高
SELECT
    id,
    argMax(name, age) AS namex,
    max(age) AS agex
FROM t_replacing_table
GROUP BY id

4. Conclusion

This article presented the selection and practice of an OLAP analysis architecture for ZhiZhuan, introducing Druid and ClickHouse to meet current multi‑dimensional analysis needs. However, the current OLAP platform still struggles with high‑concurrency self‑service analysis and complex multi‑table joins, prompting future work on a more versatile OLAP solution that supports both detailed and aggregated queries.

Author: Wang Wenqiang, Senior Data R&D Engineer, ZhiZhuan Data Intelligence Department, responsible for real‑time and offline B2C data warehouse construction.