Shulou(Shulou.com)05/31 Report--

This article is about sample analysis of OLAP. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

I. Definitions

Online Analytical Processing (OLAP) is a method of analyzing data in a multidimensional manner and providing flexibility for Roll-up, drill-down, and pivot operations to present integrated decision-making information, often used in decision support systems, business intelligence, or data warehousing. It is the most important application of data warehouse system, specially designed to support complex analysis operations, focusing on decision support for decision makers and senior managers, which can quickly and flexibly process complex queries of large data according to the requirements of analysts, and provide query results to decision makers in an intuitive and understandable form, so that they can accurately grasp the business status of enterprises (companies), understand the needs of objects, and formulate correct solutions.

II. Classification

OLAP system can be divided into three types: Relational OLAP (ROLAP), Multidimensional OLAP (MOLAP) and Hybrid OLAP (HOLAP).

a.ROLAP

ROLAP stores multidimensional data for analysis in a relational database and selectively defines a batch of solid views as tables according to the needs of the application. It is not necessary to save every SQL query as a real view. Only those queries with high application frequency and large calculation workload are defined as real views. For each query against OLAP server, the computed solid views are used preferentially to generate query results to improve query efficiency. RDBMS, which is also used as ROLAP storage, is optimized for OLAP, such as parallel storage, parallel queries, parallel data management, cost-based query optimization, bitmap indexing, OLAP extensions to SQL (cube, rollover), and so on.

b.MOLAP

MOLAP physically stores multidimensional data used in OLAP analysis in the form of multidimensional arrays, forming a "cube" structure. Dimension attribute values are mapped to subscript values or ranges of subscripts of multidimensional arrays, and summary data is stored as multidimensional array values in array cells. Because MOLAP adopts a new storage structure, it is also called Physical OLAP (Physical OLAP) from the physical layer implementation; while ROLAP is mainly implemented by some software tools or middleware, and the physical layer still adopts the storage structure of relational database, so it is called Virtual OLAP (Virtual OLAP).

c.HOLAP

Because MOLAP and ROLAP have their own strengths and weaknesses (as shown in the table below), and their structures are very different, this presents analysts with challenges in designing OLAP structures. A new OLAP architecture, Hybrid OLAP (HOLAP), is proposed, which combines the advantages of MOLAP and ROLAP. To date, there is no formal definition of HOLAP. However, it is obvious that HOLAP structure should not be a simple combination of MOLAP and ROLAP structure, but an organic combination of the technical advantages of these two structures, which can satisfy various complex analysis requests of users.

III. Concept of Logic

OLAP presents multidimensional views to users.

Dimension: It is a specific angle from which people observe data. It is a class of attributes when considering problems. The collection of attributes constitutes a dimension (time dimension, geographical dimension, etc.).

Level of dimension: A particular angle from which one looks at the data (i.e., a dimension) can also have descriptive aspects with varying degrees of detail (time dimension: date, month, quarter, year).

Dimension Member: A value of a dimension that describes the position of a data item in a dimension. ("Day of the month of the year" is a description of the location in the time dimension).

Measure: The value of a multidimensional array. (January 2000, Shanghai, Laptop, 0000).

OLAP multidimensional analysis operations include: Drill-down, Roll-up, Slice, Dice, and Pivot. The following is still an example of the above data cube to explain one by one:

Drill-down: changes between different levels of dimensions, descending from the upper level to the lower level, or splitting summary data into more detailed data. For example, you can drill through the total sales data of the second quarter of 2010 to view the consumption data of April, May and June of the second quarter of 2010, as shown in the figure above. Of course, you can also drill into Zhejiang Province to view the sales data of Hangzhou City, Ningbo City, Wenzhou City, etc.

Roll-up: reverse operation of drilling, i.e. aggregation from fine-grained data to high-level, such as summarizing sales data of Jiangsu Province, Shanghai City and Zhejiang Province to view sales data of Jiangsu, Zhejiang and Shanghai regions, as shown in the figure above.

Slice: Select specific values in the dimension for analysis, such as sales data for electronics only, or data for the second quarter of 2010.

Dice: Select a specific interval or batch of data in the dimension for analysis, such as sales data from the first quarter of 2010 to the second quarter of 2010, or sales data for electronics and commodities.

Pivot: the interchange of dimension positions, just like the conversion of rows and columns of a two-dimensional table, as shown in the figure, the interchange of product dimension and geographical dimension is realized through rotation.

IV. Advantages of OLAP

First of all, it must be said that the advantages of OLAP are subject-oriented, integrated, historical and unchangeable data storage based on data warehouse, and multi-dimensional model multi-perspective multi-level data organization form. If these two points are separated, OLAP will no longer exist, and there will be no advantage to speak of.

Data presentation

Data organization based on multidimensional model makes data display more intuitive, it is like the way we usually look at all kinds of things, you can discover different characteristics of things from multiple angles and multiple levels, and OLAP is the application of this ordinary thinking model to data analysis.

query efficiency

Multidimensional models are built on the basis of optimization of OLAP operations, such as indexes based on various dimensions, views built for some common queries, etc. These optimizations make it easy to operate millions or even billions of orders of magnitude.

Flexibility of analysis

We know that multidimensional data model can observe data from different angles and levels, and at the same time, it can aggregate, subdivide and select data with various OLAP operations described above, which improves the flexibility of analysis, and can subdivide and summarize data from different angles and levels to meet the needs of different analysis.

Thank you for reading! About "OLAP sample analysis" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.