Solving Massive Data Retrieval Demands: From Root Causes to OLAP Multidimensional Reporting Solutions

Problem Causes

1. Mindset

Product operations rely heavily on data, but operators often think "ask the engineer for data" instead of trying to solve the problem themselves, leading engineers to become mere "data fetchers" without growth.

2. Demand Method

Operators submit requests based on immediate field needs; after receiving data they often discover it does not solve the problem and request more fields, creating a never‑ending loop. Data demand differs from functional demand and requires a product manager to design a data product.

3. Tool Deficiency

When existing reporting tools cannot meet large‑scale data requests, operators resort to ad‑hoc reports that are fragmented, incomplete, and require constant engineering effort.

Wrong Directions in Data Construction

1. Fully Demand‑Driven Development

Engineers build a separate report table (RPT) for each request, stacking logic in the reporting layer, which traps data engineers in endless iteration.

2. No Model Table Concept

Without proper dimensional modeling, developers create many DWD/DWS/DIM tables that lack reusability, leading to "SQL‑boy" syndrome where massive manual SQL patches are needed.

3. No OLAP Construction Concept

Poorly abstracted subject areas cause a proliferation of fact tables, making maintenance and unified metrics difficult. (See Image 1: Data Construction Ordinary Method)

4. "Mining" Behavior

Only extracting needed fields for each request leads to fragmented tables and unnecessary engineering effort.

5. SQL Monopoly

Engineers hoard reusable SQL scripts, preventing shared maintenance and forcing others to duplicate effort.

Problem‑Solving Approaches

Administrative methods like priority scheduling and value‑based filtering only mitigate symptoms. The real solution is to build a dimension‑modeled data warehouse and an OLAP multi‑dimensional reporting system (see Image 2).

With a solid data model, a single OLAP table can serve most queries, reducing development effort and enabling self‑service analytics (e.g., ClickHouse for ad‑hoc queries).

OLAP Multi‑Dimensional Reporting System

As shown in Image 3, the OLAP report interface provides:

Dimensions: Hundreds of fields, including degenerated dimensions, draggable to the right panel.

Metrics: Common base metrics (50+), also draggable.

Filters: Mandatory partition fields such as date and data flag.

Building this system requires experienced dimensional model designers; it cannot be achieved by simple table creation.

Knowledge Empowerment

A four‑module curriculum is proposed, covering data access permissions, acquisition methods, knowledge acquisition, and event‑tracking understanding, followed by product‑specific courses on report systems, SQL writing, and business metrics.

Conclusion

Kimball’s "Data Warehouse Toolkit" highlights that tools like Google Analytics are essentially data‑warehouse applications; adopting rigorous dimensional modeling can dramatically improve efficiency, as illustrated by the analogy of mechanized farming versus traditional methods.

Thank you for reading.