Building YiPay's Big Data BI Analysis Platform: Architecture, OLAP Engine Practices, and Future Plans

This article introduces the construction practice of YiPay's big data BI analysis platform, organized into four parts: the application of big data in finance, the platform architecture, OLAP engine technology practice, and future planning.

Financial Big Data Applications : Four main scenarios are presented—data exploration using Redash with Hive (for long‑running queries) and Presto (for sampling), data visualization via Tableau and internal reporting platforms (with Kylin for modeling), real‑time query for risk control (200 billion‑row tables, <10 s latency, <5 s response), and offline fast query for user‑tag based profiling (using ClickHouse). The existing service architecture suffers from siloed JDBC/ODBC connections, performance and stability issues, heavy reliance on analysts, chaotic data permissions, and poor data standards.

Platform Architecture : The diagram (see image below) shows the data‑mid‑platform foundation and the BI platform's position. Data is ingested through a data development platform, then stored in an ODS layer where dimensional modeling occurs. Unified dimensions and metrics are managed and standardized, while a metadata platform handles lineage and permission services. The OLAP layer integrates Kylin, ClickHouse, Presto, Hive, and Spark, with a unified query router, caching, and security auditing. The BI platform aims to combine exploration, analysis, diagnosis, and reporting under a single data‑standard framework, providing robust permission control.

OLAP Engine Technology Practice : Permission control is performed by parsing SQL via a SQL‑Parser module, checking resources against the metadata platform, and then routing to the appropriate OLAP engine (Presto plugin, Kylin ACL, etc.). Monitoring includes cluster health and query‑level analysis. ClickHouse version 21.8 had a performance issue with Global IN/NOT IN due to block fragmentation; the problem is mitigated by using SquashingBlockInputStream to compact blocks. The PartialReplacingMergeTree engine is used for incremental tag updates. ClickHouse’s optimizer evolved from no planner to a simple rule‑based planner (still lacking CBO). Comparisons are drawn with Greenplum’s AST‑to‑logical‑plan process and DuckDB’s logical‑plan optimization before physical planning.

Future Planning : The roadmap includes native integration of OLAP engines (e.g., ClickHouse storage), adding an abstraction layer in the self‑service BI platform to unify SQL syntax across engines, and building an intelligent routing engine that directs queries to the most suitable engine based on rule‑based or machine‑learning models.

Thank you for your attention.