Data warehouse - what is OLAP

Students engaged in data warehouse or big data , You should often hear OLAP The word . what OLAP analysis ,OLAP Engine and so on . Today, let's talk about OLAP.

OLAP And OLTP

Speaking of OLAP, I have to mention his brother OLTP, The two are often compared .

First , Take a look at both

Definition ：

OLAP（On-Line Analytical Processing）： On line analytical processing ,OLAP Is the main application of data warehouse system , Support complex analysis operations , Focus on decision support , And provide intuitive and easy to understand query results .

OLTP（on-line transaction processing）： On line transaction processing , The main application of traditional relational database , Basically 、 Routine business .

difference ：

Generally speaking, it is ：OLTP Mainly for traditional “ Additions and deletions ” Transaction system , Data is mostly stored in entity object model , And satisfy 3NF( The third paradigm of database ), Pursue fast response in high concurrency scenarios . and OLAP It is a decision oriented analysis scenario , Using the idea of dimensional modeling to build the model , Performance analysis of large-scale query aggregation .

OLAP The classification of

Generally speaking , According to the modeling method OLAP Can be divided into 3 Types ： Relational online real-time analysis system (Relational-OLAP,ROLAP), Multidimensional online real-time analysis system (Multidimensional-OLAP,MOLAP), Hybrid on-line real-time analysis system (Hybrid-OLAP,HOLAP).

ROLAP

Through RDMS Establish an intermediate layer between the back-end service and the customer's front-end OLAP Realization way . adopt RDMS To store and manage data warehouse data , And by OLAP Middleware to realize the mapping of operations on multidimensional data into standard relational operations .

ROLAP The main processing engines are ：Presto,Impala,GreenPlum,Clickhouse、Doris.

ROLAP It is applicable to the query mode that is not fixed 、 Scenarios requiring high query flexibility , But the amount of data processed is limited by engine performance , For complex queries with large amount of data, the performance is not very good

MOLAP

MOLAP It is generally based on user-defined data dimensions 、 Measure （ It can also be called index ） Generate pre aggregated data when data is written ;Query When the query comes , In fact, the query is the pre aggregated data rather than the original detailed data , In a scenario where the query mode is relatively fixed , This optimization speed-up is obvious .

MOLAP The main processing engines are ：Druid and Kylin

MOLAP It is suitable for scenarios with relatively fixed query scenarios and high query performance requirements , Poor flexibility .

HOLAP

HOLAP yes MOLAP and ROLAP A fusion of . When querying aggregate data , Use MOLAP technology ; When querying detailed data , Use ROLAP technology .

General business OLAP Engine is HOLAP framework , In order to meet the needs of different customers .

OLAP Basic operation

OLAP The multidimensional analysis operations include ： Drilling （Drill-down）、 Scroll up （Roll-up）、 section （Slice）、 cutting （Dice） And rotation （Pivot）

Drilling （Drill-down）：

Changes between different levels of dimension , From the top down to the next , Or split the summary data into more detailed data , For example, by dealing with 2010 Drill down the total sales data for the second quarter of 2010 Second quarter 4、5、6 Monthly consumption data , Pictured above ; Of course, you can also drill into Zhejiang Province to see Hangzhou city 、 Ningbo City 、 Wenzhou City …… Sales figures for these cities .

Scroll up （Roll-up）：

Reverse operation of drilling , From fine-grained data to high-level aggregation , For example, Jiangsu Province 、 The sales data of Shanghai and Zhejiang Province are summarized to view the sales data of Jiangsu, Zhejiang and Shanghai , Pictured above .

section （Slice）：

Select a specific value in the dimension to analyze , For example, select only the sales data of electronic products , perhaps 2010 Data for the second quarter of 2007 .

cutting （Dice）：

Select the data of a specific interval in the dimension or a batch of specific values for analysis , Such as choice 2010 First quarter to 2010 Sales figures for the second quarter of 2007 , Or the sales data of electronic products and daily necessities .

rotate （Pivot）：

That is, the interchange of the positions of dimensions , It's like a row column conversion in a two-dimensional table , As shown in the figure, the product dimension and regional dimension can be exchanged through rotation .

common OLAP Engine comparison